Adding Columns and Exadata HCC compression

While everyone is aware of the issues of mixing EHCC compression and OLTP type activities, I had a customer who was interested in finding out what happens upon adding a column to a table that has EHCC compression enabled on it.

As I could not see any definitive statements in the documentation on this particular scenario I ran up some tests to see the behaviour.

First of all they are using partitioning by date range, so we create a partitioned table:

SQL: db01> create table t_part  ( 
username varchar2(30), 
user_id  number, 
created date ) 
partition by range (created) 
( partition p_2009 values less than (to_date('31-DEC-2009', 'dd-MON-YYYY')) tablespace users, 
partition p_2010 values less than (to_date('31-DEC-2010', 'dd-MON-YYYY')) tablespace users, 
partition p_2011 values less than (to_date('31-DEC-2011', 'dd-MON-YYYY')) tablespace users, 
partition p_2012 values less than (to_date('31-DEC-2012', 'dd-MON-YYYY')) tablespace users )

/

Table created

The customer is particularly interested in using partitioning for ILM type scenarios in that they will compress historical partitions but not more up-to-date ones. Lets enable HCC compression on the table and check that it’s on:

SQL: db01> alter table t_part compress for query high 
/

Table altered

SQL: db01> select table_name, partition_name, compression, compress_for 
from all_tab_partitions 
where table_name='T_PART' 
/

TABLE_NAME                     PARTITION_NAME                 COMPRESS COMPRESS_FOR 
------------------------------ ------------------------------ -------- ------------ 
T_PART                         P_2009                         ENABLED  QUERY HIGH 
T_PART                         P_2010                         ENABLED  QUERY HIGH 
T_PART                         P_2011                         ENABLED  QUERY HIGH 
T_PART                         P_2012                         ENABLED  QUERY HIGH

Lets insert some data and check that the actual row is compressed (thanks to Kerry Osborne)

SQL: db01>; insert /*+ APPEND */ into t_part select * from all_users 
/ 
3008 rows created
SQL: db01> commit
/
Commit complete

SQL: db01> select max(rowid) from t_part
/

MAX(ROWID) 
------------------ 
AAAexSAANAAHGoUAAN

SQL: db01> select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
   16, 'HCC Archive High', 
   32, 'HCC Archive Low', 
   'Unknown Compression Level') compression_type 
from dual;

Enter value for rowid: AAAexSAANAAHGoUAAN 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAexSAANAAHGoUAAN'),

COMPRESSION_TYPE 
------------------------- 
HCC Query High

So we are confident we have a row that is compressed. Now we add a new column to the table and give it a default value, we then check again what compression the row has:

SQL: db01> alter table t_part add city varchar2(30) default 'Oxford' 
/

Table altered.

select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
  2    3  1, 'No Compression', 
  4  2, 'Basic/OLTP Compression', 
  5  4, 'HCC Query High', 
  6  8, 'HCC Query Low', 
  7  16, 'HCC Archive High', 
32, 'HCC Archive Low', 
    'Unknown Compression Level') compression_type 
from dual; 
Enter value for rowid: AAAexSAANAAHGoUAAN 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAexSAANAAHGoUAAN'),

COMPRESSION_TYPE 
------------------------- 
Basic/OLTP Compression

Oh Dear! Our compression has changed.

This maybe is not that surprising. But what if you have a requirement to add a column but with no default value, and you just want to update more recent records, can we avoid downgrading all records from HCC compression?

So we are using the same table and data as before. We will focus on two rows, one in the 2011 partition and one in the 2012 partition.

SQL: db01> select max(rowid) from t_part where created  > TO_DATE('31-Dec-2010', 'DD-MM-YYYY') and created < TO_DATE('01-Jan-2012', 'DD-MM-YYYY');

MAX(ROWID) 
------------------ 
AAAezbAAHAAFwIKAE/

SQL: db01> select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
    16, 'HCC Archive High', 
    32, 'HCC Archive Low', 
    'Unknown Compression Level') compression_type 
from dual;  
Enter value for rowid: AAAezbAAHAAFwIKAE/ 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAezbAAHAAFwIKAE/'),

COMPRESSION_TYPE 
------------------------- 
HCC Query High

SQL: db01> select max(rowid) from t_part where created  > TO_DATE('31-Dec-2011', 'DD-MM-YYYY') and created < TO_DATE('31-Dec-2012', 'DD-MM-YYYY');

MAX(ROWID) 
------------------ 
AAAezcAAHAAHdoSADf

SQL:xldnc911001hdor:(SMALLDB1):PRIMARY> select decode( 
    DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
    16, 'HCC Archive High', 
    32, 'HCC Archive Low', 
    'Unknown Compression Level') compression_type 
from dual; 
Enter value for rowid: AAAezcAAHAAHdoSADf 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAezcAAHAAHdoSADf'),

COMPRESSION_TYPE 
------------------------- 
HCC Query High

Now we add a column to the table and update the records in only the 2012 partition:

SQL: db01> alter table t_part add city varchar2(30);

Table altered.

SQL: db01> update t_part set city='Oxford' where created > to_date('31-Dec-2011', 'DD-MM-YYYY');

448 rows updated.

SQL: db01> commit;

Commit complete.

And now we again check the compression status of our two rows:

SQL: db01> select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
   16, 'HCC Archive High', 
   32, 'HCC Archive Low', 
       'Unknown Compression Level') compression_type 
from dual;  
Enter value for rowid: AAAezbAAHAAFwIKAE/ 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAezbAAHAAFwIKAE/'),

COMPRESSION_TYPE 
------------------------- 
HCC Query High

SQL: db01> select decode( 
DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
    1, 'No Compression', 
    2, 'Basic/OLTP Compression', 
    4, 'HCC Query High', 
    8, 'HCC Query Low', 
    16, 'HCC Archive High', 
    32, 'HCC Archive Low', 
        'Unknown Compression Level') compression_type 
   from dual; 
Enter value for rowid: AAAezcAAHAAHdoSADf 
old   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', '&rowid'), 
new   2: DBMS_COMPRESSION.GET_COMPRESSION_TYPE ( 'SYS', 'T_PART', 'AAAezcAAHAAHdoSADf'),

COMPRESSION_TYPE 
------------------------- 
Basic/OLTP Compression

So that is great, we have a way of evolving table definitions without having to suffer the whole set of historical data to not be in HCC compression.