Why and How to know and compress Table as Hybrid Columnar Compression (HCC)?

Why ?

To get the most compress advance feature on the Oracle Exadata machine you may use the Hybrid Columnar Compression(HCC), however , there is a limitation on it where you have to use it for a certain object type, moreover, the way to apply this is online and offline , below I will simply mention the query to identified these objects and the option of doing this.

How ?

Below is a query that will identify and create executable scripts for the offline way where table will be locked, and indices will be unusable after doing this.

1- Identify and generate offline script for HCC:

select  -

'ALTER TABLE '||table_owner||'.'||table_name||' move PARTITION '||PARTITION_NAME||' compress for query high ;' Result

from dba_TAB_PARTITIONS

where (COMPRESS_FOR<> 'QUERY HIGH' or COMPRESS_FOR is null)

and table_name not in (select table_name from dba_tab_columns

where data_type in ('CLOB','LOB','LONG', 'BLOB', 'LONG RAW'))

and table_name not in(select TABLE_NAME from dba_indexes

where index_type in ('BITMAP')

)and TABLE_NAME not in(select OBJECT_NAME from dba_recyclebin dr where dr.OWNER=TABLE_OWNER)

--and TABLE_OWNER NOT IN ('SYS','SYSTEM')

ORDER BY TABLE_OWNER,TABLE_NAME,PARTITION_NAME ;

2- Using DBMS_REDEFINITION.REDEF_TABLE for the online HCC implementation:

BEGIN

  DBMS_REDEFINITION.REDEF_TABLE (

    uName                      => '<table_owner>'

   ,tName                      => '<table_name>'

   ,table_compression_type     => 'COLUMN STORE COMPRESS FOR QUERY HIGH');

END;

/