LOBs vs Data – when compression occurs

Just a quick tip for SECUREFILE lobs.

You may be familiar with basic compression on data in tables. It is a dictionary attribute of the table, which is then taken into account when you perform:

• a SQL Loader direct load, or
• an INSERT with the APPEND hint, or
• a bulk bind INSERT in PLSQL using the APPEND_VALUE hint.

Whichever of the above you perform does not really matter – the key thing is that when you set the COMPRESS attribute on a table, this only applies on operations subsequent to the moment at which you altered the table. It does not compress the existing data. Hence when you alter a table to add the attribute, it is instantaneous.

SQL> create table t (x int, c clob ) lob (c) store as securefile;

Table created.

SQL>
SQL> insert into t
  2  select rownum, dbms_random.string('x',20000)
  3  from dual
  4  connect by level < 100000;

99999 rows created.

SQL>
SQL> commit;

Commit complete.

SQL>
SQL> set timing on
SQL> alter table t compress;

Table altered.

Elapsed: 00:00:00.00

But do not make the mistake of assuming this is the case if you opt to set the compress attribute for a SECUREFILE LOB. Setting this attribute on a table will immediately read and update all of the existing LOB data in the table, which of course could be a very resource intensive operation.

SQL> alter table t modify lob (c ) ( compress );

Table altered.

Elapsed: 00:00:23.32

Whilst your best option is probably to specify LOB compression when you create the table, if you need to do it after the fact, be aware that you’ll probably want to schedule it in a quiet time.