CLOB size matters! – Keep Learning Keep Living

Hi,

Many developer and DBA has an idea about the storing clobs. Basic rule is “if length of clob is higher tan 4000 then it will be stored in lob segment not in table segment” which is correct but sometimes misinterpreted!

if you have a clob column in your table, your data might be in the table. since LOBs(large objects) can be too big Oracle stores them in a separate segment. this is very important because while you are selecting your table, you might be reading not just your table also lob segment which will decrease your performance of course. as tuning step Oracle stores your clob data in the table segment if length of lob is less than 4000. This means Oracle act to your data as varchar2 which will be faster but as I said this is causing a misunderstanding.

So you check your clob data and all of them has 4000 bytes length unfortunately all of those data are in the lob segment not in the table! why? because of the large object overhead! every large object has some header information and this is the part of that magical number “4000”. Because of that your data must be less than 4000 and the new magical number is 3964. your data must be equal or less than 3964 to be stored in the table segment.

here is the proof:

create table tmp_lob (mydata clob);

insert into tmp_lob 
  select lpad('1', 4000, '1') 
  from dual 
  connect by level <= 10000;
commit;

select bytes/1024 size_in_KB 
from   user_segments 
where  segment_name ='TMP_LOB';
--------------------------------------
--576

select bytes/1024 size_in_KB 
from   user_segments 
where  segment_name = (select l.segment_name from dba_lobs l where table_name = 'TMP_LOB');
-----------------------------------
--98496

create table tmp_lob (mydata clob);

insert into tmp_lob

select lpad('1', 4000, '1')

from dual

connect by level <= 10000;

commit;

select bytes/1024 size_in_KB

from user_segments

where segment_name ='TMP_LOB';

--------------------------------------

--576

select bytes/1024 size_in_KB

from user_segments

where segment_name = (select l.segment_name from dba_lobs l where table_name = 'TMP_LOB');

-----------------------------------

--98496

I just create a table with a clob column and inserted 10.000 rows with 4000 length of clob data. when we check the user_segments for our table we will simply see that it has 576KB which is really small. After that checking LOB segment size, we are able to see the truth! our lob segment size is 98496KB. so all of our data has been stored in lob segment!

Let’s run the same code for 3964 byte characters:

truncate table tmp_lob;

insert into tmp_lob 
  select lpad('1', 3964, '1') 
  from dual 
  connect by level <= 10000;
commit;

select bytes/1024 size_in_KB 
from   user_segments 
where  segment_name ='TMP_LOB';
-------------------------------
--81920

select bytes/1024 size_in_KB 
from   user_segments 
where  segment_name = (select l.segment_name from dba_lobs l where table_name = 'TMP_LOB');
-------------------------------
--128

truncate table tmp_lob;

insert into tmp_lob

select lpad('1', 3964, '1')

from dual

connect by level <= 10000;

commit;

select bytes/1024 size_in_KB

from user_segments

where segment_name ='TMP_LOB';

-------------------------------

--81920

select bytes/1024 size_in_KB

from user_segments

where segment_name = (select l.segment_name from dba_lobs l where table_name = 'TMP_LOB');

-------------------------------

--128

Here it is. with 3964 bytes clob data, our table size is 81920KB and lob segment size is 128KB. Simply all the data is stored in the table not in the lob segment.

Is there a misdirection on documentation about this 4000 size? NO! Oracle says, Large objects will be stored in the table segments if their size is less than 4000 but this size is not the size of our data. this size includes the LOB overhead.

have a nice day.

Leave a Reply Cancel reply