Updating clob in pl sql

01-Jul-2019 19:19

A requirement that comes up a lot is the need to store strings longer than 4000 characters in the database, and this presents a problem.Up to and including Oracle 11g R2 the longest varchar2 that can be stored in the database is 4000 characters.The remaining 3kb cannot be used for anything else.

For this post, I am not concerned about storing strings that are many mega or gigabytes long - I am more interested in strings that are generally a few thousand characters up to about 32kb in size.Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 16 consistent gets 0 physical reads 0 redo size 353 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client === In the first test 10 rows containing a CLOB were returned, but in the second, 10 rows containing a varchar2 were returned.The difference in round trips is 20 - 2 per CLOB, even though each CLOB only contains 5 characters.In the second case, the table row would then contain a pointer to the location of the CLOB data.Generally it is more efficient to store the CLOB with the table row, but if the CLOB is longer than about 4000 characters, it can no longer be stored in the row, and is stored in the CLOB segment regardless of whether storage in row in enabled or not.

For this post, I am not concerned about storing strings that are many mega or gigabytes long - I am more interested in strings that are generally a few thousand characters up to about 32kb in size.

Statistics ---------------------------------------------------------- 5 recursive calls 0 db block gets 16 consistent gets 0 physical reads 0 redo size 353 bytes sent via SQL*Net to client 365 bytes received via SQL*Net from client === In the first test 10 rows containing a CLOB were returned, but in the second, 10 rows containing a varchar2 were returned.

The difference in round trips is 20 - 2 per CLOB, even though each CLOB only contains 5 characters.

In the second case, the table row would then contain a pointer to the location of the CLOB data.

Generally it is more efficient to store the CLOB with the table row, but if the CLOB is longer than about 4000 characters, it can no longer be stored in the row, and is stored in the CLOB segment regardless of whether storage in row in enabled or not.

I decided to run a test inserting 2000 rows of CLOBs of different sizes into a table with a single CLOB column, storage in row enabled.