ODC Appreciation Day–LOB compression

LOBs tend to be large. Well duh…it’s right there in the name! “Large Object”. So one of the cool things I like with the SECUREFILE option in recent releases of Oracle Database is the ability to compress LOBs. Here’s a quick demo of that in action:


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> 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 t1
  2  select rownum, rpad(owner,6000,owner||','||object_type)
  3  from   dba_objects
  4  commit;

82902 rows created.

SQL>
SQL> insert into t2
  2  select * from t1;

82902 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select sum(bytes) from user_segments
  2  where
  3     segment_name = 'T1' or
  4     segment_name = ( select segment_name from user_lobs
  5                      where table_name = 'T1' );

SUM(BYTES)
----------
1548943360

1 row selected.

SQL>
SQL> select sum(bytes) from user_segments
  2  where
  3     segment_name = 'T2' or
  4     segment_name = ( select segment_name from user_lobs
  5                      where table_name = 'T2' );

SUM(BYTES)
----------
  19005440

1 row selected.

Now as you can see, the compression achieved here is pretty spectacular but a careful eye will quickly spot why this is the case – the data is lot of repeated pairings of the owner and object_type values from DBA_OBJECTS.

Just to balance the ledger, I’ll repeat the example using totally random strings


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> 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 t1
  2  select rownum, dbms_random.string('x',12000)
  3  from   dual
  4  connect by level <= 10000;

10000 rows created.

SQL> commit;

Commit complete.

SQL>
SQL> insert into t2
  2  select * from t1;

10000 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> select sum(bytes) from user_segments
  2  where
  3     segment_name = 'T1' or
  4     segment_name = ( select segment_name from user_lobs
  5                      where table_name = 'T1' );

SUM(BYTES)
----------
  93126656

1 row selected.

SQL>
SQL> select sum(bytes) from user_segments
  2  where
  3     segment_name = 'T2' or
  4     segment_name = ( select segment_name from user_lobs
  5                      where table_name = 'T2' );

SUM(BYTES)
----------
  84017152

1 row selected.

As you can see, even in this case, we still managed to get some benefit from the compression, although (as expected) the gains are not as dramatic. The benefit that you get from your data will probably fall somewhere in between these values. Also note that there is an overhead to storing the metadata for any LOB, and you would expect the amount of metadata to be larger for a compressed LOB. So compressing tiny LOBs may yield an actual growth in segment size – so always perform some measurements on realistic data before embarking down this path.

And finally, in the vein of “There is no such thing as a free lunch”, compressing anything, LOBs or otherwise, takes processing power. So always keep in mind the trade-off between less storage, faster access for smaller LOBs versus the processing cost of compressing them in the first place.

Thanks ODC!

2 responses to “ODC Appreciation Day–LOB compression”

thatravinmaharaj

October 12, 2018 at 2:37 am

Hi Chris. Great article. Please confirm if the compress option is separately licensed feature
Regards
Ravin Maharaj

1. Connor McDonald
  
  November 2, 2018 at 8:46 am
  
  Yes it is part of ACO