Reasons why SELECT * is bad for SQL performance | Tanel Poder Consulting

[utterances-bot]

Reasons why SELECT * is bad for SQL performance | Tanel Poder Consulting

https://tanelpoder.com/posts/reasons-why-select-star-is-bad-for-sql-performance/

[arnaldobjj]

Thanks for sharing! Now is more than proved.

[emanueol]

Great Tanel, good to know about the 12.2 sqlplus -fast options or the lob aggregation param, and overall pleasure to follow you step by step perf. analysis.

[tomasz-sroka]

Hi Tanel, thanks for sharing. Great post! Would you mind to shed some light on a way how to improve fetching milion LOB values to the app side?

[tanelpoder]

Thanks Tomasz.

You'd need to find a way to return lots of small LOB items by using some workarounds:

1. Stream of non-LOB chunks - I remember writing PL/SQL for special cases many years ago, where we converted CLOB items to arrays of VARCHAR (or BLOBs to arrays of RAW) so many of them could be fetched in one go

2. One single big LOB item that contains many smaller LOBs (probably the LOB items would be prefixed by a few bytes indicating the the next item size - runlength encoding).

3. Use the new Oracle driver LOB settings with some pivoting operation (fetch 1 row with pivoted 100 LOB columns in result set, each column containing a separate LOB item...)

4. Write the LOB items to filesystem and access them as files? (This might be useful during some migration or mass export perhaps)

The options #1/#2/#3 would require some app logic changes (or app-level stitching-together of the chunkified data)