Partition Table의 Index Compress 하기

1. create Partitioned Table and create Local Index noncompress
SQL> conn eun/loveora77
Connected.

SQL> rem create Partitioned Table and create Local Index noncompress
SQL> CREATE TABLE eun.HE_EMP_TEST1
SQL> TABLESPACE SS_DB_DATA3
SQL> NOLOGGING
SQL> COMPRESS FOR QUERY HIGH
SQL> PARTITION BY RANGE(LD_OP_DTM)
SQL> (
SQL>   PARTITION PT_2011053112 VALUES LESS THAN ('20110531130000')
SQL> )
SQL> as
SQL> select * from eun.EMP
SQL> WHERE 1=0
SQL> ;
Table created.

Elapsed: 00:00:02.69

SQL> ALTER SESSION ENABLE PARALLEL DML;
Session altered.

Elapsed: 00:00:00.00

SQL> INSERT /*+ APPEND PARALLEL(x,16) */ INTO HE_EMP_TEST1 x
SQL> SELECT  /*+ FULL(y) PARALLEL(y,16) */
SQL> *
SQL> FROM eun.EMP PARTITION(PT_2011053112) y
SQL> ;
271798918 rows creaeun.

Elapsed: 00:05:55.64

SQL> COMMIT
SQL> ;
Commit complete.

Elapsed: 00:00:01.34

SQL> CREATE INDEX HE_EMP_TEST1_X2 ON eun.HE_EMP_TEST1 (
SQL> GRP_CD
SQL> )
SQL> LOCAL
SQL> NOLOGGING
SQL> PARALLEL 16
SQL> ;
Index created.

Elapsed: 00:42:17.72

SQL> ALTER INDEX HE_EMP_TEST1_X2 NOPARALLEL;
Index altered.

Elapsed: 00:00:00.19

SQL> commit;
Commit complete.

Elapsed: 00:00:00.00

2. create nonPartitioned Table and create compress Index
SQL> rem create nonPartitioned Table and create compress Index

SQL> CREATE TABLE eun.HE_EMP_TEST2
SQL> TABLESPACE SS_DB_DATA3
SQL> NOLOGGING
SQL> COMPRESS FOR QUERY HIGH
SQL> PCTFREE 0
SQL> AS
SQL> SELECT
SQL>   *
SQL> FROM eun.EMP y
SQL> WHERE 1=0
SQL> ;
Table created.

Elapsed: 00:00:00.88

SQL> CREATE INDEX HE_EMP_TEST2_X2 ON HE_EMP_TEST2 (
SQL> GRP_CD
SQL> )
SQL> TABLESPACE SS_DB_DATA3
SQL> NOLOGGING
SQL> PARALLEL 16
SQL> compress 1
SQL> ;
Index created.

Elapsed: 00:00:05.80

SQL> ALTER INDEX HE_EMP_TEST2_X2 NOPARALLEL;
Index altered.

Elapsed: 00:00:00.03

3. Select Index Status befroe Partition Exchange

SQL> rem Select Index Status befroe Partition Exchange
SQL> conn sys/loveora77 as sysdba
Connected.

SQL> select segment_name,partition_name,bytes from dba_segments where segment_name like 'HE_EMP_TEST%'
  2  order by segment_type, segment_name;

SEGMENT_NAME               PARTITION_NAME   BYTES
-------------------------- ---------------- ---------------
HE_EMP_TEST2_X2                 8388608
HE_EMP_TEST1_X2 PT_2011052312    6370099200
HE_EMP_TEST1_X2 PT_2011053112    10375987200
HE_EMP_TEST5_X2 PT_2011053112    65536
HE_EMP_TEST2                     8388608
HE_EMP_TEST1     PT_2011052312    8772911104
HE_EMP_TEST2     PT_2011052312    8772452352
HE_EMP_TEST1     PT_2011053112    5732106240
HE_EMP_TEST5     PT_2011053112    65536

SQL> SELECT index_name, partition_name, last_analyzed, status
  2   FROM dba_ind_partitions
  3   WHERE index_name like 'HE_EMP_TEST%';

INDEX_NAME                   PARTITION_NAME   LAST_ANALYZE STATUS
---------------------------- ---------------- ------------ --------
HE_EMP_TEST1_X2   PT_2011052312    24-MAY-11    USABLE
HE_EMP_TEST1_X2   PT_2011053112    31-MAY-11    USABLE
HE_EMP_TEST5_X2   PT_2011053112    31-MAY-11    USABLE

4. execute Index Compress : 테이블은 "ALTER TABLE ~"로 compress 변경이 가능하나, 인덱스는 변경이 불가능 하다.

  1) alter index ~ 명령어 사용으로 속성 변경 시도
SQL> alter index ted.HE_WIUX_CDR_TEST6_X1 compress1;
alter index ted.HE_WIUX_CDR_TEST6_X1 compress1
                                     *
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL> alter index ted.HE_WIUX_CDR_TEST6_X1 compress 1;
alter index ted.HE_WIUX_CDR_TEST6_X1 compress 1
                                     *
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL> alter index ted.HE_WIUX_CDR_TEST6_X1 compress;     
alter index ted.HE_WIUX_CDR_TEST6_X1 compress
                                     *
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL> alter index ted.HE_WIUX_CDR_TEST6_X1 nocompress;
alter index ted.HE_WIUX_CDR_TEST6_X1 nocompress
                                     *
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

  2) alter index ~ modify ~ 명령어 사용으로 속성 변경 시도

SQL> alter index ted.HE_WIUX_CDR_TEST6_X1 MODIFY compress1;
alter index ted.HE_WIUX_CDR_TEST6_X1 MODIFY compress1
                                            *
ERROR at line 1:
ORA-02243: invalid ALTER INDEX or ALTER MATERIALIZED VIEW option

SQL> alter index ted.HE_WIUX_CDR_TEST6_X1 MODIFY partition PT_2011052312 compress1;
alter index ted.HE_WIUX_CDR_TEST6_X1 MODIFY partition PT_2011052312 compress1
                                                                    *
ERROR at line 1:
ORA-14050: invalid ALTER INDEX MODIFY PARTITION option

  3) alter table ~ 테이블의 compress 속성 변경
SQL> alter table ted.HE_WIUX_CDR_TEST6 compress for query low;

Table altered.

SQL> alter table ted.HE_WIUX_CDR_TEST6 nocompress;

Table altered.

SQL> alter table ted.HE_WIUX_CDR_TEST6 compress;

Table altered.

5. execute Partition Exchange
SQL> rem execute Partition Exchange

SQL> conn eun/loveora77
Connected.

SQL> ALTER TABLE HE_EMP_TEST1 EXCHANGE PARTITION PT_2011053112
  2  WITH TABLE HE_EMP_TEST2 INCLUDING INDEXES WITHOUT VALIDATION;
WITH TABLE HE_EMP_TEST2 INCLUDING INDEXES WITHOUT VALIDATION
           *
ERROR at line 2:
ORA-28665: table and partition must have same compression attribute

SQL> alter index HE_EMP_TEST1_X2 rebuild partition PT_2011053112 tablespace SS_DB_DATA3 compress;
alter index HE_EMP_TEST1_X2 rebuild partition PT_2011053112 tablespace SS_DB_DATA3 compress
*
ERROR at line 1:
ORA-28659: COMPRESS must be specified at object level first
SQL> rem metalink ID 312843.1
SQL> rem 해결방법은 Index를 drop하고 다시 생성하는데, compress 옵션을 주고 재생성하면 됨
SQL> rem 대용량 데이터(1TB 이상)이고 실시간 배치작업(partition exchage) 및 조회가 되어야 하는데, index를 재생성 할 경우 시간이 너무 오래 걸림
SQL> rem 그래서 테이블 및 인덱스를 새로 생성하고, 기존 테이블은 rename으로 다른 이름을 변경(rename)하고, 변경된 테이블명을 view로 묶어 준다.

SQL> ALTER INDEX HE_EMP_TEST1_X2 REBUILD COMPRESS;
ALTER INDEX HE_EMP_TEST1_X2 REBUILD COMPRESS
            *
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

SQL> ALTER TABLE HE_EMP_TEST1 EXCHANGE PARTITION PT_2011053112
  2  WITH TABLE HE_EMP_TEST2 EXCLUDING INDEXES WITHOUT VALIDATION;

Table altered.

SQL> rem Select Index Status after Partition Exchange : Exchange : EXCLUDING INDEXES 옵션으로 수행하면 index 상태는 unusable이 됨
SQL> conn sys/loveora77 as sysdba
Connected.

SQL> SELECT index_name, partition_name, last_analyzed, status
  2   FROM dba_ind_partitions
  3   WHERE index_name like 'HE_EMP_TEST%';

INDEX_NAME                   PARTITION_NAME   LAST_ANALYZE STATUS
---------------------------- ---------------- ------------ --------
HE_EMP_TEST1_X2   PT_2011052312    24-MAY-11    USABLE
HE_EMP_TEST1_X2   PT_2011053112    31-MAY-11    UNUSABLE
HE_EMP_TEST5_X2   PT_2011053112    31-MAY-11    USABLE

SQL> select segment_name,partition_name,bytes from dba_segments where segment_name like 'HE_EMP_TEST%'
  2  order by segment_type, segment_name;

SEGMENT_NAME                 PARTITION_NAME          BYTES
---------------------------- ------------------ ----------
HE_EMP_TEST2_X2                         8388608
HE_EMP_TEST1_X2   PT_2011052312      6370099200
HE_EMP_TEST5_X2   PT_2011053112           65536
HE_EMP_TEST2                         5732106240
HE_EMP_TEST1      PT_2011052312      8772911104
HE_EMP_TEST2      PT_2011052312      8772452352
HE_EMP_TEST1      PT_2011053112         8388608
HE_EMP_TEST5      PT_2011053112           65536

==== 참고 ===
SQL> rem EXCLUDING INDEXES : 인덱스를 제외하고 exchange를 수행하는데, 이 경우 Local partitioned index는 unusable 상태가 됨
SQL> rem WITHOUT VALIDATION : exchage 수행 시 데이터를 검증 하지 않으므로, 해당 파티션 키와 맞지 않는 데이터가 들어갈 수 있음(데이터 정합성이 문제 될 수 있다)

6. 인덱스 압축은 8i에서 등장한 개념
   1) 참고 사이트 : (ORACLE 8I) COMPRESS INDEX 사용 방법 
   2) 기본 개념 (그림으로) : 상세내용은 ORACLE 11g R2 Manual 참고

       ① ALTER INDEX ~

   ② ALTER INDEX ~ REBUILD ~

   ② ALTER INDEX ~ REBUILD (NON)COMPRESS ~