Partition Table and Exadata Hybrid Columnar Compression (EHCC)
- by Bandari Huang
Create EHCC table
CREATE TABLE ... COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH];
select owner,table_name,compress_for DBA_TAB_SUBPARTITIONS where compression = ‘ENABLED';
Convert Table/Partition/Subpartition to EHCC
Compress Table&Partition&Subpartition to EHCC:
ALTER TABLE table_name MOVE COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH] [PARALLEL <dop>];
ALTER TABLE table_name MOVE PARATITION partition_name COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH] [PARALLEL <dop>];
ALTER TABLE table_name MOVE SUBPARATITION subpartition_name COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH] [PARALLEL <dop>];
select owner,table_name,compress_for DBA_TAB_SUBPARTITIONS where compression = ‘ENABLED';
select table_owner,table_name,partition_name,compress_for DBA_TAB_PARTITIONS where compression = ‘ENABLED’;
select table_owner,table_name,subpartition_name,compress_for DBA_TAB_SUBPARTITIONS where compression = ‘ENABLED’;
Rebuild Unusable Index:
select index_name from dba_index where status = 'UNUSABLE';
select index_name,partition_name from dba_ind_partition where status = 'UNUSABLE';
select index_name,subpartition_name from dba_ind_partition where status = 'UNUSABLE';
ALTER INDEX index_name REBUILD [PARALLEL <dop>];
ALTER INDEX index_name REBUILD PARTITION partition_name [PARALLEL <dop>];
ALTER INDEX index_name REBUILD SUBPARTITION subpartition_name [PARALLEL <dop>];
Convert Table/Partition/Subpartition from EHCC to OLTP compression or uncompressed format:
Uncompress EHCC Table&Partition&Subpartition:
ALTER TABLE table_name MOVE [NOCOMPRESS|COMPRESS for OLTP] [PARALLEL <dop>];
ALTER TABLE table_name MOVE PARTITION partition_name [NOCOMPRESS|COMPRESS for OLTP] [PARALLEL <dop>];
ALTER TABLE table_name MOVE SUBPARTITION subpartition_name [NOCOMPRESS|COMPRESS for OLTP] [PARALLEL <dop>];
select owner,table_name,compress_for DBA_TAB_SUBPARTITIONS where compression = '';
select table_owner,table_name,partition_name,compress_for DBA_TAB_PARTITIONS where compression = '';
select table_owner,table_name,subpartition_name,compress_for DBA_TAB_SUBPARTITIONS where compression = '';
Rebuild Unusable Index:
select index_name from dba_index where status = 'UNUSABLE';
select index_name,partition_name from dba_ind_partition where status = 'UNUSABLE';
select index_name,subpartition_name from dba_ind_partition where status = 'UNUSABLE';
ALTER INDEX index_name REBUILD [PARALLEL <dop>];
ALTER INDEX index_name REBUILD PARTITION partition_name [PARALLEL <dop>];
ALTER INDEX index_name REBUILD SUBPARTITION subpartition_name [PARALLEL <dop>];
