Partition Table and Exadata Hybrid Columnar Compression (EHCC)
Posted
by Bandari Huang
on Oracle Blogs
See other posts from Oracle Blogs
or by Bandari Huang
Published on Mon, 15 Oct 2012 06:19:15 +0000
Indexed on
2012/10/15
9:45 UTC
Read the original article
Hit count: 314
Filed under:
/Oracle
- 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';
- CREATE TABLE ... COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH];
- 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>];
- Compress Table&Partition&Subpartition to EHCC:
- 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>];
- Uncompress EHCC Table&Partition&Subpartition:
© Oracle Blogs or respective owner
