Mohamed Houri’s Oracle Notes

November 23, 2017

Compressed tables and DDL optimisation

Filed under: compression — hourim @ 8:15 pm

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;
Advertisements

4 Comments »

  1. Hi Mohamed,

    an interesting observation. Just one addition: if my memory serves me well the BASIC compression is only free in the Enterprise Edition – it’s not included in the Standard Edition (as too many other options).

    Regards

    Martin

    Comment by Martin Preiss — November 24, 2017 @ 3:33 pm | Reply

  2. After all it’s good to know that no
    unaware activation of Advanced Compression is possible.
    – Peter

    Comment by Peter — November 25, 2017 @ 4:00 am | Reply

  3. […] knew here that we can’t DDL optimise a BASIC compressed table. So I uncompressed my table in a […]

    Pingback by Compress enable novalidate | Mohamed Houri’s Oracle Notes — December 10, 2017 @ 7:47 pm | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

Create a free website or blog at WordPress.com.

Tony's Oracle Tips

Tony Hasler's light hearted approach to learning about Oracle

Richard Foote's Oracle Blog

Focusing Specifically On Oracle Indexes, Database Administration and Some Great Music

Hatem Mahmoud Oracle's blog

Just another Oracle blog : Database topics and techniques

Mohamed Houri’s Oracle Notes

Qui se conçoit bien s’énonce clairement

Oracle Diagnostician

Performance troubleshooting as exact science

Raheel's Blog

Things I have learnt as Oracle DBA

Coskan's Approach to Oracle

What I learned about Oracle

So Many Oracle Manuals, So Little Time

“Books to the ceiling, Books to the sky, My pile of books is a mile high. How I love them! How I need them! I'll have a long beard by the time I read them”—Lobel, Arnold. Whiskers and Rhymes. William Morrow & Co, 1988.

EU Careers info

Your career in the European Union

Carlos Sierra's Tools and Tips

Tools and Tips for Oracle Performance and SQL Tuning

Oracle Scratchpad

Just another Oracle weblog

OraStory

Dominic Brooks on Oracle Performance, Tuning, Data Quality & Sensible Design ... (Now with added Sets Appeal)

%d bloggers like this: