Sunday Oct 14, 2012

Partition Table and Exadata Hybrid Columnar Compression (EHCC)

  • Create EHCC table
    • CREATE TABLE ... COMPRESS FOR [QUERY LOW|QUERY HIGH|ARCHIVE LOW|ARCHIVE HIGH];

      select owner,table_name,compress_for DBA_TABLES 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_TABLES 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_indexes where status = 'UNUSABLE';
      select index_name,partition_name from dba_ind_partitions where status = 'UNUSABLE';
      select index_name,subpartition_name from dba_ind_subpartitions 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_TABLES 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_indexes where status = 'UNUSABLE';
      select index_name,partition_name from dba_ind_partitions where status = 'UNUSABLE';
      select index_name,subpartition_name from dba_ind_subpartitions 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>];
About

Bandari Huang | Exadata Solution Consultant Phone: +86-21-2302-3838 | Mobile: +86-156-9213-8783 Oracle Exadata & Strategic Solutions Consulting, Great China

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today