HCC compression on Partitioned Table....caution!

In My Previous post, i explained and showed you what are different types of Compression available in Exadata HCC and also helped to show you in live box what ratios we get for each of these compression types.

In dataware house system generally tables are partitioned and partition use Range partition method( which can be monthly or yearly basis). Having said, it is also possible that there can be global primary key(or others) constraint on partitioned table. So before implementing we need to check what are depended on this table.

For example if there is a partitioned table and we have defined a GLOBAL primary key on this table, then we need to take extra caution as when we implement the HCC compression on some of the partitions, the index used to enforce primary will become UNUSABLE and result of which you cannot INSERT/UPDATE any data on any partition.


15:45:42 SQL> select constraint_name,status from dba_constraints where table_name='LOAN_TRANSACTION_PART';

CONSTRAINT_NAME                STATUS
------------------------------ --------
PRI                            ENABLED


15:45:42 SQL> select constraint_name,status,index_name from dba_constraints where table_name='LOAN_TRANSACTION_PART';

CONSTRAINT_NAME                STATUS      INDEX_NAME
------------------------------ ----------------------
PRI                            ENABLED     LOAN_PART_IND

15:45:53 SQL> alter table LOAN_TRANSACTION_PART move partition LOAN_TRANSACTION_PART_TWO compress for archive high parallel 12;

Table altered.

15:47:38 SQL> select index_name,status from dba_indes where index_name='LOAN_PART_IND';

PARTITION_NAME                 STATUS

------------------------------ --------

LOAN_PART_IND                  UNUSABLE

So we have seen there is GLOBAL index on partitioned table and this index help constraint to enable enforcement. Once we move/implemented the HCC compression on particular partition our index got unusable state.

There is however one way to overcome this situation, we can have LOCAL index and use this index to enforce the primary key.By doing this, only affected table partition will have its Index(partition) will become unusable state and rest all wont be affected.

For example:

15:44:38 SQL> select partition_name,status from dba_ind_partitions where index_name='LOAN_TRANSACTION_PI';

PARTITION_NAME                 STATUS

------------------------------ --------

LOAN_PART_MAX                  USABLE

LOAN_TRANSACTION_PART_FA       USABLE

LOAN_TRANSACTION_PART_FO       USABLE

LOAN_TRANSACTION_PART_ONE      USABLE

LOAN_TRANSACTION_PART_THREE    USABLE

LOAN_TRANSACTION_PART_TWO      USABLE

15:45:53 SQL> alter table bob_uat_ods.LOAN_TRANSACTION_PART move partition LOAN_TRANSACTION_PART_TWO compress for archive high parallel 12;

Table altered.

Elapsed: 00:01:11.11

15:47:38 SQL> select partition_name,status from dba_ind_partitions where index_name='LOAN_TRANSACTION_PI';

PARTITION_NAME                 STATUS

------------------------------ --------

LOAN_PART_MAX                  USABLE

LOAN_TRANSACTION_PART_FA       USABLE

LOAN_TRANSACTION_PART_FO       USABLE

LOAN_TRANSACTION_PART_ONE      USABLE

LOAN_TRANSACTION_PART_THREE    USABLE

LOAN_TRANSACTION_PART_TWO      UNUSABLE

So we have seen only LOAN_TRANSACTION_PART_TWO index partition is affected. And other index partitions are not, since its LOCAL partition index so this index can be still used to enforce the primary key.

Now we can simply rebuild the partition using "alter index LOAN_TRANSACTION_PI rebuild partition LOAN_TRANSACTION_PART_TWO parallel 12;"

Conclusion: Before implementing HCC compression on Partitioned table check if there are any GLOBAL indexes or constraint. If there are then whole Index or constraint will get in unusable state. So we have to rebuild or create whole global index again which will affect the ongoing activity. Better way is to have LOCAL partition index on Partitioned table.

Note:- To covert any partition of table to have HCC compression on it we have use MOVE command (which is direct path write/read operation) and segments are build once again. So if you remove the MOVE command HCC wont compress the already existing data.