Oracle SQL Fast Retrieval of CLOBS

If a table contains CLOBS, depending on the method you use to connect, usually the CLOB-columns just return a handle that will trigger another connection/query to the database in order to retrieve the value. This makes it really slow. An easy way to speed it up is to retrieve everything which is small enough as string value. A string value however is limited to 4’000 characters. Thus you need to issue two queries:

select t.xml.getCLOBVal() from my_table t where length(t.xml.getclobval()) <> length(dbms_lob.substr(t.xml.getclobval(), 4000, 1))
select t.xml.getstringval() from my_table t where length(t.xml.getclobval()) = length(dbms_lob.substr(t.xml.getclobval(), 4000, 1))

The dbms_lob.substr part is there to avoid problems with multibyte characters and encodings.

Some database providers like ODP.net provide properties like InitialLOBFetchSize that can be set to retrieve the data in-line as well. Devart on the other hand does not support any in-line loading of CLOB data.

Leave a comment

Your email address will not be published. Required fields are marked *