We had an interesting question on AskTOM the other day about LOB compression. Someone was very concerned that after applying compression to the LOBS in the column of their table, the table actually got larger! Cue much confusion and panic, but after a little exploration, the explanation was pretty simple.
By default, when you create a LOB column in a table, the default storage definition is ENABLE STORAGE IN ROW. As per the documentation:
If you enable storage in row, then the LOB value is stored in the row (inline) if its length is less than approximately 4000 bytes minus system control information
After getting some more information from the person who asked the question, the majority of their LOBs (they were CLOBs in this instance) sat around the 10 to 20kilobyte mark. What this means is that in uncompressed form, the LOB data would be deemed too large to be stored alongside the rest of the row – it would be moved to the separate LOB segment that holds the data for the LOB column. But after compressing the LOB, some of those LOBs then dropped to less than the ~4kilobyte threshold and thus would be now stored in the table itself. If you have a lot of such LOBs, then the table segment could indeed grow, even though the overall size of the table (ie, table data plus LOB data) should indeed shrink with the compression.
We can see that with a simple demo – I’ll put some very compress-able data into the LOB, but start with the segment being the default of not compressed:
SQL> create table t1 2 ( x int, 3 c clob 4 ) 5 lob (c) store as securefile ( 6 enable storage in row 7 nocompress); Table created. SQL> SQL> insert into t1 2 select rownum, rpad('b',12000,'b') 3 from dual 4 connect by level <= 10000; 10000 rows created. SQL> commit; Commit complete.
Now I’ll copy that table into a replica, the only difference between that the LOB column will be marked as compressed.
SQL> create table t2 2 ( x int, 3 c clob 4 ) 5 lob (c) store as securefile ( 6 enable storage in row 7 compress); Table created. SQL> SQL> insert into t2 2 select * from t1; 10000 rows created. SQL> SQL> commit; Commit complete.
Now we can compare the size of the LOB segments as well as the size of table
SQL> select segment_name, bytes from user_segments 2 where segment_name = 'T1' or 3 segment_name = ( select segment_name from user_lobs 4 where table_name = 'T1' ); SEGMENT_NAME BYTES ------------------------------ ---------- T1 655360 SYS_LOB0000268696C00002$$ 277020672 2 rows selected. SQL> SQL> select segment_name, bytes from user_segments 2 where segment_name = 'T2' or 3 segment_name = ( select segment_name from user_lobs 4 where table_name = 'T2' ); SEGMENT_NAME BYTES ------------------------------ ---------- T2 3145728 SYS_LOB0000268699C00002$$ 131072 2 rows selected. SQL> SQL> SQL>
So yes, the table (in red) has grown, but the size of the overall table (where “overall” means table plus LOB data), has shrunk thanks the compression of the LOB data.