Fetching CLOB column Faster

so your clob data might be too big and instead of fetching some GBs of data, fetching some MB of data must be faster of course. check the total length of your lob data:

select sum(size_in_mb)/1024/1024 total_clob_size_in_mb,

       sum(case when size_in_mb > 4000 then 4000

                when size_in_mb <= 4000 then size_in_mb

           end

          ) size_for_first_4000_mb

from (select dbms_lob.getlength(col1)/1024/1024 size_in_mb from table1);

first column is total size and the second is the size when you get first 4000 char. probably there is a huge difference.

that's odd. did you delete a huge data from this table? there might be a too much empty space in your table or lob segment. use dbms_space_admin and check empty spaces in your table/lob segment. I assume this could be the problem.

sample code:

DECLARE  fs1_blocks  number;  fs1_bytes   number;  fs2_blocks  number;  fs2_bytes   number;  fs3_blocks  number;  fs3_bytes   number;  fs4_blocks  number;  fs4_bytes   number;  full_blocks number;  full_bytes  number;  unformatted_bl number;  unformatted_by number;BEGIN  DBMS_SPACE.space_usage('TABLE_OWNER HEREEEE' ,                         upper('TABLE_NAME OR SEGMENT NAME HEREEEE') ,                         'TABLE' ,                         unformatted_bl, unformatted_by,                         fs1_blocks , fs1_bytes  ,                         fs2_blocks , fs2_bytes  ,                         fs3_blocks , fs3_bytes  ,                         fs4_blocks , fs4_bytes  ,                         full_blocks, full_bytes                                                  );  DBMS_OUTPUT.PUT_LINE('fs1_blocks : ' || fs1_blocks );  DBMS_OUTPUT.PUT_LINE('fs1_bytes  : ' || fs1_bytes  );  DBMS_OUTPUT.PUT_LINE('fs2_blocks : ' || fs2_blocks );  DBMS_OUTPUT.PUT_LINE('fs2_bytes  : ' || fs2_bytes  );  DBMS_OUTPUT.PUT_LINE('fs3_blocks : ' || fs3_blocks );  DBMS_OUTPUT.PUT_LINE('fs3_bytes  : ' || fs3_bytes  );  DBMS_OUTPUT.PUT_LINE('fs4_blocks : ' || fs4_blocks );  DBMS_OUTPUT.PUT_LINE('fs4_bytes  : ' || fs4_bytes  );  DBMS_OUTPUT.PUT_LINE('full_blocks: ' || full_blocks);  DBMS_OUTPUT.PUT_LINE('full_bytes : ' || full_bytes );  DBMS_OUTPUT.PUT_LINE('unformatted_bl: ' || unformatted_bl);  DBMS_OUTPUT.PUT_LINE('unformatted_by : ' || unformatted_by );END;/

to check lob segment:

select segment_name from all_lobs where table_name = 'YOUR_TABLE_HERE';

change the code above as this:

.....

  DBMS_SPACE.space_usage('TABLE OWNER HEREEEE' ,

                         upper('SEGMENT NAME FROM ALL_LOBS') ,

                         'LOB' ,

.....

933257 wrote:Tried modifying the query as you mentioned. But not seeing any improvement. Below is the sql monitor for fetching ~1000 rows of LOB having length<4000bytes. Both using CASE and SUBSTR clause.Using CASE statement taking same time as it was for fetching whole LOB:- 

SQL Monitoring Report  SQL Text ------------------------------ SELECT c1,c2...,CASE WHEN LENGTH(C10) <= 4000 then CAST(C10 AS VARCHAR2(4000)) ELSE NULL END AS MY_CLOB_COLUMN_AS_VARCHAR2, CASE WHEN LENGTH(C10) > 4000 then NULL ELSE C10 END AS MY_CLOB_COLUMN_AS_CLOB FROM TAB1 where LENGTH (C10)<4000 and rownum<1000  Global Information ------------------------------  Status : DONE (ALL ROWS)   Instance ID : 4   SQL ID : 3m0j31tb3rd8x   SQL Execution ID : 67108864   Execution Started : 07/31/2019 05:41:21   First Refresh Time : 07/31/2019 05:41:21   Last Refresh Time : 07/31/2019 05:42:46   Duration : 85s   Module/Action : SQL*Plus/-   Program : sqlplus.exe   Fetch Calls : 1000   Global Stats ================================================= | Elapsed | Cpu | Other | Fetch | Buffer | | Time(s) | Time(s) | Waits(s) | Calls | Gets | ================================================= | 0.02 | 0.00 | 0.02 | 1000 | 1065 | =================================================  SQL Plan Monitoring Details (Plan Hash Value=4129443724) =========================================================================================================================================================== | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | (%) | (# samples) | =========================================================================================================================================================== | 0 | SELECT STATEMENT | | | | 86 | +0 | 1 | 999 | | | | 1 | COUNT STOPKEY | | | | 86 | +0 | 1 | 999 | | | | 2 | TABLE ACCESS STORAGE FULL FIRST ROWS | TAB1 | 999 | 2335 | 86 | +0 | 1 | 999 | | | ===========================================================================================================================================================  Predicate Information (identified by operation id): ---------------------------------------------------   1 - filter(ROWNUM<1000)  2 - filter(LENGTH("C10")<4000)   Statistics ----------------------------------------------------------  1 recursive calls  0 db block gets  1068 consistent gets  3 physical reads  0 redo size  608700 bytes sent via SQL*Net to client  306929 bytes received via SQL*Net from client  2000 SQL*Net roundtrips to/from client  0 sorts (memory)  0 sorts (disk)  999 rows processed

Using SUBSTR function Finishing in 3 seconds:-

SELECT c1,c2....,DBMS_LOB.SUBSTR (C10,(SELECT MAX (LENGTH (C10)) FROM TAB1)) C10 FROM TAB1 where LENGTH (C10)<4000 and rownum<1000   999 rows selected.  Elapsed: 00:00:03.59  Execution Plan ---------------------------------------------------------- Plan hash value: 2117026829  ------------------------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 999 | 195K| 2335 (1)| 00:00:29 | | 1 | SORT AGGREGATE | | 1 | 164 | | | | 2 | TABLE ACCESS STORAGE FULL | TAB1 | 151K| 23M| 7212 (1)| 00:01:27 | |* 3 | COUNT STOPKEY | | | | | | |* 4 | TABLE ACCESS STORAGE FULL FIRST ROWS| TAB1 | 999 | 195K| 2335 (1)| 00:00:29 | ------------------------------------------------------------------------------------------------------------  Predicate Information (identified by operation id): ---------------------------------------------------   3 - filter(ROWNUM<1000)  4 - filter(LENGTH("C10")<4000)   Statistics ----------------------------------------------------------  47 recursive calls  0 db block gets  41131 consistent gets  14 physical reads  572 redo size  25154 bytes sent via SQL*Net to client  1197 bytes received via SQL*Net from client  68 SQL*Net roundtrips to/from client  0 sorts (memory)  0 sorts (disk)  999 rows processed

You could use two queries, one to fetch the smaller lobs as a varchar2, this will use standard array fetches.

One query to fetch the remaining rows with lobs that cant be substr’ed, this will hopefully only be a few rows.