Compressed tables and DDL optimisation

DDL optimisation is a very useful feature with which adding a column having a default value to a very very big table is almost instantaneous. And, as an icing on the cake, this feature comes free of license.

Free of license have I said?

Yes. Until you hit the followings in a 12.2.0.1.0 release

SQL> alter table t1 add c_ddl number default 42 not null;
alter table t1 add c_ddl number default 42 not null
                   *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

My real life very very big table was BASIC compressed as is the t1 table

SQL> select
       table_name
      ,compression
      ,pct_free
      ,compress_for
    from 
      user_tables
    where 
      table_name = 'T1';

TABLE_NAME COMPRESS   PCT_FREE COMPRESS_FOR
---------- -------- ---------- -------------
T1         ENABLED           0 BASIC
 

If you want to DDL optimise a BASIC compressed table you have to switch for the paid advanced compression as the following demonstrates:

SQL> alter table t1 move compress for oltp;

Table altered.

SQL> select
       table_name
      ,compression
      ,pct_free
      ,compress_for
    from 
      user_tables
    where 
      table_name = 'T1';

TABLE_NAME COMPRESS   PCT_FREE COMPRESS_FOR
---------- -------- ---------- -------------
T1         ENABLED           0 ADVANCED

SQL> alter table t1 add c_ddl number default 42 not null;

Table altered.

SQL> select count(1) from t1 where c_ddl=42;

  COUNT(1)
----------
   1000000

SQL_ID  59ysfcmfx0s7t, child number 0
-------------------------------------
---------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes |
---------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |
|   1 |  SORT AGGREGATE    |      |     1 |     8 |
|*  2 |   TABLE ACCESS FULL| T1   | 10000 | 80000 |
---------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(NVL("C_DDL",42)=42)

 

With a compressed table using advanced compression I succeeded to add a column to table t1 having a default value using the DDL optimisation feature. The presence of the NVL function in the predicate part of the above execution plan is what allows me to say that, indeed, the DDL optimisation feature has been used during the alter table command.

This is how Oracle implicitely force you to buy a new license. If you want to DDL optimise a compressed table than you have to use the OLTP paid option.

However, If you are in the same situation as that of my customer and you don’t want to pay for extra license fees then proceed as shown below:

SQL> alter table t1 move nocompress parallel 8;

Table altered.

SQL> alter table t1 add c_ddl number default 42 not null;

Table altered.

SQL> alter table t1 move compress basic parallel 8;

Table altered.

SQL> alter index idx_t1_pk rebuild parallel 8;

Index altered.

SQL> alter index idx_t1_pk noparallel;

Index altered.

Summary

In contrast to the free BASIC compression Advanced compression allows DDL optimisation. Take this into account when deciding about the type of compression you will choose at design time.

Model

create table t1 as select 
rownum n1
,mod(rownum,10) n2
from dual
connect by level <=1e6;

create unique index idx_t1_pk on t1(n1);

alter table t1 add constraint t1_pk primary key(n1) using index;