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!

Got some thoughts? Leave a comment

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d bloggers like this: