인덱스 생성시 자동 압축되게 하는 파라미터(DB_INDEX_COMPRESSION_INHERITANCE) 테스트

출처 : https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DB_INDEX_COMPRESSION_INHERITANCE.html

 

해당파라미터는 인덱스 생성시 압축옵션을 사용하지 않더라도 Table이나 Tablespace에 압축 attributes가 설정되어 있다면 압축인덱스로 생성되게 해주는 파라미터입니다.

 

default는 none이고 아무런 설정도 따라가지 않는것이 default입니다.

alter session / alter system 명령어로 수정가능하며 alter system set은 재기동없이 바로 적용가능합니다.

**파라미터 변경시 기존에 생성되어 있는 인덱스는 적용되지 않으며 옵션 적용 후 재생성(drop&create)시 적용됩니다.**

아래 테스트는 alter session으로 변경하여 진행하였습니다.

해당 테스트를 진행하게 된 계기는 파티션테이블에 생성된 로컬파티션 인덱스가 add partition 시 압축으로 생성될 수 있는지에 대한 여부를 확인하던 중 발견한 파라미터입니다.(파티션테이블의 경우에는 attribute 변경으로 압축을 설정시 add partition 되는 파티션에 대해서 압축옵션을 자동으로 걸고 생성되게 할 수 있습니다.)

인덱스의 재생성없이 attributes의 압축옵션을 부여할 수 있는지에 대한 테스트였고, 테스트 결과 재생성 없이는 되지 않고, 재생성시 압축옵션을 명시적으로 주면 해당 파라미터와 관계없이 attributes가 압축으로 설정되어 add partition 시에도 인덱스도 자동으로 압축으로 생성됩니다.

#################################

테스트버전 :12.2.0.1

테스트 시나리오

파티션 테이블(압축옵션 사용)생성하고 인덱스 생성(압축옵션 미사용)한 후 파라미터 적용 전/후로 add partition 시 새로 추가된 파티션인덱스가 압축옵션이 걸리는지에 대한 테스트로 진행

##################################

 

1) 테스트 테이블 및 인덱스 생성

CREATE TABLE YSBAE.TB_ADDPART_INDEX
(
    INVOKE_DT       VARCHAR2(6) NOT NULL,
    INVOKE_ID       VARCHAR2(20) NOT NULL,
    INVOKE_NO       VARCHAR2(20) NOT NULL,
    RSVC_ID         VARCHAR2(20) NOT NULL,
    SEQ             NUMBER(10) NOT NULL,
    ITEM_ID         VARCHAR2(21) NOT NULL,
    ITEM_VAL_NUM    NUMBER(25,7),
    ITEM_VAL_TXT    VARCHAR2(4000),
    ITEM_IO_DIV     VARCHAR2(1)
)
TABLESPACE USERS
COMPRESS FOR OLTP 
PARTITION BY RANGE (INVOKE_DT)
(
    PARTITION PART_202607 VALUES LESS THAN ('202608')
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    COMPRESS FOR OLTP,
    PARTITION PART_202608 VALUES LESS THAN ('202609')
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    COMPRESS FOR OLTP,
    PARTITION PART_202609 VALUES LESS THAN ('202610')
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    COMPRESS FOR OLTP
);

CREATE UNIQUE INDEX YSBAE.IDX_ADDPART_INDEX
ON YSBAE.TB_ADDPART_INDEX
(
    INVOKE_DT,
    INVOKE_ID,
    INVOKE_NO,
    RSVC_ID,
    SEQ,
    ITEM_ID
) 
LOCAL
(
    PARTITION PART_202607
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOLOGGING,
    PARTITION PART_202608
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOLOGGING,
    PARTITION PART_202609
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOLOGGING
)
TABLESPACE USERS
NOLOGGING;

 

딕셔너리 뷰 확인

테이블은 압축,인덱스는 비압축으로 생성된것을 확인하였습니다.

--인덱스
select owner,index_name,index_type,compression From dba_indexes
where index_name='IDX_ADDPART_INDEX';  


OWNER      INDEX_NAME           INDEX_TYPE                  COMPRESSION
---------- -------------------- --------------------------- --------------------
YSBAE      IDX_ADDPART_INDEX    NORMAL                      DISABLED

select index_owner,index_name,partition_name,compression,logging
From dba_ind_partitions  
where index_name='IDX_ADDPART_INDEX';  

INDEX_OWNER    INDEX_NAME           PARTITION_NAME       COMPRESSION	LOGGING
-------------- -------------------- -------------------- --------------------
YSBAE          IDX_ADDPART_INDEX    PART_202607          DISABLED		NO
YSBAE          IDX_ADDPART_INDEX    PART_202608          DISABLED		NO
YSBAE          IDX_ADDPART_INDEX    PART_202609          DISABLED		NO

select owner,index_name,table_name,def_logging,interval,def_parameters From dba_part_indexes
where table_name='TB_ADDPART_INDEX';  


OWNER      INDEX_NAME           TABLE_NAME           DEF_LOGGIN INTERVAL   DEF_PARAMETERS
---------- -------------------- -------------------- ---------- ---------- --------------------
YSBAE      IDX_ADDPART_INDEX    TB_ADDPART_INDEX     NO         (null)     (null)

--테이블
select table_owner,table_name,partition_name,compression,compress_for,logging
From dba_tab_partitions  
where table_name='TB_ADDPART_INDEX';  
TABLE_OWNER          TABLE_NAME           PARTITION_NAME       COMPRESSION          COMPRESS_FOR         LOGGING
-------------------- -------------------- -------------------- -------------------- -------------------- ----------
YSBAE                TB_ADDPART_INDEX     PART_202607          ENABLED              ADVANCED             YES
YSBAE                TB_ADDPART_INDEX     PART_202608          ENABLED              ADVANCED             YES
YSBAE                TB_ADDPART_INDEX     PART_202609          ENABLED              ADVANCED             YES

select owner,table_name,tablespace_name,compression,compress_for from dba_tables
where table_name='TB_ADDPART_INDEX' 
;

OWNER      TABLE_NAME           TABLESPACE_NAME      COMPRESSION          COMPRESS_FOR
---------- -------------------- -------------------- -------------------- --------------------
YSBAE      TB_ADDPART_INDEX     (null)               (null)               (null)

select owner,table_name,status,def_compression,def_compress_for,def_logging from dba_part_tables
where table_name='TB_ADDPART_INDEX';  

OWNER      TABLE_NAME           STATUS   DEF_COMP DEF_COMPRESS_FOR               DEF_LOGGIN
---------- -------------------- -------- -------- ------------------------------ ----------
YSBAE      TB_ADDPART_INDEX     VALID    ENABLED  ADVANCED                       NONE

 

1)파라미터가 none(default)일때 add partition 시도

show parameter DB_INDEX_COMPRESSION_INHERITANCE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_index_compression_inheritance     string      NONE

 

ALTER TABLE YSBAE.TB_ADDPART_INDEX ADD PARTITION PART_202610 VALUES LESS THAN ('202611')
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
;

 

인덱스는 비압축으로 생성된것을 확인할 수 있습니다.

--테이블
select table_owner,table_name,partition_name,compression,compress_for,logging
From dba_tab_partitions  
where table_name='TB_ADDPART_INDEX';  
TABLE_OWNER          TABLE_NAME           PARTITION_NAME       COMPRESSION          COMPRESS_FOR         LOGGING
-------------------- -------------------- -------------------- -------------------- -------------------- ----------
YSBAE                TB_ADDPART_INDEX     PART_202607          ENABLED              ADVANCED             YES
YSBAE                TB_ADDPART_INDEX     PART_202608          ENABLED              ADVANCED             YES
YSBAE                TB_ADDPART_INDEX     PART_202609          ENABLED              ADVANCED             YES
YSBAE                TB_ADDPART_INDEX     PART_202610          ENABLED              ADVANCED             YES

--인덱스
select index_owner,index_name,partition_name,compression,logging
From dba_ind_partitions  
where index_name='IDX_ADDPART_INDEX';  

INDEX_OWNER    INDEX_NAME           PARTITION_NAME       COMPRESSION	LOGGING
-------------- -------------------- -------------------- --------------------
YSBAE          IDX_ADDPART_INDEX    PART_202607          DISABLED		NO
YSBAE          IDX_ADDPART_INDEX    PART_202608          DISABLED		NO
YSBAE          IDX_ADDPART_INDEX    PART_202609          DISABLED		NO
YSBAE          IDX_ADDPART_INDEX    PART_202610          DISABLED		NO

 

2)파라미터를 TABLE로 변경 후 재생성 후 add partition

 

ALTER SESSION SET DB_INDEX_COMPRESSION_INHERITANCE=TABLE;

show parameter DB_INDEX_COMPRESSION_INHERITANCE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_index_compression_inheritance     string      TABLE

 

ALTER TABLE YSBAE.TB_ADDPART_INDEX ADD PARTITION PART_202611 VALUES LESS THAN ('202612')
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
;

 

파라미터를 변경하고 app partition 했지만 추가된 PART_202611 는 비압축인것을 확인하였습니다.

기존에 생성되어 있는 인덱스에 대해서는 설정이 적용되지 않는것을 확인 할 수 있습니다.

select table_owner,table_name,partition_name,compression,compress_for,logging
From dba_tab_partitions  
where table_name='TB_ADDPART_INDEX';  
TABLE_OWNER          TABLE_NAME           PARTITION_NAME       COMPRESSION          COMPRESS_FOR         LOGGING
-------------------- -------------------- -------------------- -------------------- -------------------- ----------
YSBAE                TB_ADDPART_INDEX     PART_202607          ENABLED              ADVANCED             YES
YSBAE                TB_ADDPART_INDEX     PART_202608          ENABLED              ADVANCED             YES
YSBAE                TB_ADDPART_INDEX     PART_202609          ENABLED              ADVANCED             YES
YSBAE                TB_ADDPART_INDEX     PART_202610          ENABLED              ADVANCED             YES
YSBAE                TB_ADDPART_INDEX     PART_202611          ENABLED              ADVANCED             YES

 

3) 파라미터가 TABLE일때 신규테이블에 신규인덱스 생성

CREATE TABLE YSBAE.TB_ADDPART_INDEX_2
(
    INVOKE_DT       VARCHAR2(6) NOT NULL,
    INVOKE_ID       VARCHAR2(20) NOT NULL,
    INVOKE_NO       VARCHAR2(20) NOT NULL,
    RSVC_ID         VARCHAR2(20) NOT NULL,
    SEQ             NUMBER(10) NOT NULL,
    ITEM_ID         VARCHAR2(21) NOT NULL,
    ITEM_VAL_NUM    NUMBER(25,7),
    ITEM_VAL_TXT    VARCHAR2(4000),
    ITEM_IO_DIV     VARCHAR2(1)
)
TABLESPACE USERS
COMPRESS FOR OLTP
PARTITION BY RANGE (INVOKE_DT)
(
    PARTITION PART_202607 VALUES LESS THAN ('202608')
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    COMPRESS FOR OLTP,
    PARTITION PART_202608 VALUES LESS THAN ('202609')
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    COMPRESS FOR OLTP,
    PARTITION PART_202609 VALUES LESS THAN ('202610')
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    COMPRESS FOR OLTP
);

--인덱스는 압축옵션을 따로 주지않고 생성
CREATE UNIQUE INDEX YSBAE.IDX_ADDPART_INDEX_2
ON YSBAE.TB_ADDPART_INDEX_2
(
    INVOKE_DT,
    INVOKE_ID,
    INVOKE_NO,
    RSVC_ID,
    SEQ,
    ITEM_ID
) 
LOCAL
(
    PARTITION PART_202607
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOLOGGING,
    PARTITION PART_202608
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOLOGGING,
    PARTITION PART_202609
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOLOGGING
)
TABLESPACE USERS
NOLOGGING;

 

압축으로 생성된것을 확인할 수 있습니다.

select index_owner,index_name,partition_name,compression,logging
From dba_ind_partitions       
where index_name='IDX_ADDPART_INDEX_2';   

INDEX_OWNER		INDEX_NAME           PARTITION_NAME       COMPRESSION          LOGGING
----------------------------------------------------------------------------------------------------
YSBAE			IDX_ADDPART_INDEX_2  PART_202607          ADVANCED LOW         NO
YSBAE			IDX_ADDPART_INDEX_2  PART_202608          ADVANCED LOW         NO
YSBAE			IDX_ADDPART_INDEX_2  PART_202609          ADVANCED LOW         NO

 

add partition 해보기

ALTER TABLE YSBAE.TB_ADDPART_INDEX_2 ADD PARTITION PART_202610 VALUES LESS THAN ('202611')
    TABLESPACE USERS
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
;

 

추가되는 파티션도 정상적으로 압축으로 생성되는 것을 확인할 수 있습니다.

select index_owner,index_name,partition_name,compression,logging
From dba_ind_partitions       
where index_name='IDX_ADDPART_INDEX_2';   

INDEX_OWNER		INDEX_NAME           PARTITION_NAME       COMPRESSION          LOGGING
----------------------------------------------------------------------------------------------------
YSBAE			IDX_ADDPART_INDEX_2  PART_202607          ADVANCED LOW         NO
YSBAE			IDX_ADDPART_INDEX_2  PART_202608          ADVANCED LOW         NO
YSBAE			IDX_ADDPART_INDEX_2  PART_202609          ADVANCED LOW         NO
YSBAE			IDX_ADDPART_INDEX_2  PART_202610          ADVANCED LOW         NO

 

4)파라미터 none으로 변경 후 재생성(drop&create) 

 

ALTER SESSION SET DB_INDEX_COMPRESSION_INHERITANCE=NONE;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_index_compression_inheritance     string      NONE

 

DROP INDEX  YSBAE.IDX_ADDPART_INDEX_2;

CREATE UNIQUE INDEX YSBAE.IDX_ADDPART_INDEX_2
ON YSBAE.TB_ADDPART_INDEX_2
(
    INVOKE_DT,
    INVOKE_ID,
    INVOKE_NO,
    RSVC_ID,
    SEQ,
    ITEM_ID
) 
LOCAL;

 

파라미터가 none일때는 재생성하면 비압축인덱스로 생성됩니다.

--테이블
select table_owner,table_name,partition_name,compression,compress_for,logging
From dba_tab_partitions    
where table_name='TB_ADDPART_INDEX_2';   

TABLE_OWNER          TABLE_NAME           PARTITION_NAME       COMPRESSION	COMPRESS_FOR         LOGGING
-------------------- -------------------- -------------------- --------------------
YSBAE                TB_ADDPART_INDEX_2   PART_202607          ENABLED		ADVANCED             YES
YSBAE                TB_ADDPART_INDEX_2   PART_202608          ENABLED		ADVANCED             YES
YSBAE                TB_ADDPART_INDEX_2   PART_202609          ENABLED		ADVANCED             YES
YSBAE                TB_ADDPART_INDEX_2   PART_202610          ENABLED		ADVANCED             YES

--인덱스
select index_owner,index_name,partition_name,compression,logging
From dba_ind_partitions       
where index_name='IDX_ADDPART_INDEX_2';   

INDEX_OWNER		INDEX_NAME           PARTITION_NAME       COMPRESSION          LOGGING
----------------------------------------------------------------------------------------------------
YSBAE			IDX_ADDPART_INDEX_2  PART_202607          DISABLED         YES
YSBAE			IDX_ADDPART_INDEX_2  PART_202608          DISABLED         YES
YSBAE			IDX_ADDPART_INDEX_2  PART_202609          DISABLED         YES
YSBAE			IDX_ADDPART_INDEX_2  PART_202610          DISABLED         YES

 

 

5)파라미터값 테이블스페이스로 변경 후 테스트

 

--테스트용 테이블스페이스 생성
CREATE TABLESPACE TS_TEST01 DATAFILE
SIZE 1G;

 

생성직후 확인해보면 INDEX_COMPRESS_FOR가 null입니다.

select tablespace_name,status,logging,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,COMPRESS_FOR,DEF_INMEMORY_COMPRESSION,INDEX_COMPRESS_FOR
From dba_tablespaces  
where tablespace_name in ('USERS','TS_TEST01');  

TABLESPACE_NAME      STATUS               LOGGING    EXTENT_MANAGEMENT    ALLOCATION_TYPE	SEGMENT_SPACE_MANAGE COMPRESS_FOR         DEF_INMEMORY_COMPRES INDEX_COMPRESS_FOR
-------------------- -------------------- ---------- -------------------- --------------------
TS_TEST01            ONLINE               LOGGING    LOCAL                SYSTEM			AUTO                 (null)               (null)               (null)
USERS            	 ONLINE               LOGGING    LOCAL                SYSTEM			AUTO                 (null)               (null)               (null)

 

ALTER TABLESPACE TS_TEST01 DEFAULT INDEX COMPRESS ADVANCED LOW;

 

TS_TEST01의 값이 ADVANCED LOW로 변경된것을 확인할 수 있습니다.

select tablespace_name,status,logging,EXTENT_MANAGEMENT,ALLOCATION_TYPE,SEGMENT_SPACE_MANAGEMENT,COMPRESS_FOR,DEF_INMEMORY_COMPRESSION,INDEX_COMPRESS_FOR
From dba_tablespaces  
where tablespace_name in ('USERS','TS_TEST01');  

TABLESPACE_NAME      STATUS               LOGGING    EXTENT_MANAGEMENT    ALLOCATION_TYPE	SEGMENT_SPACE_MANAGE COMPRESS_FOR         DEF_INMEMORY_COMPRES INDEX_COMPRESS_FOR
-------------------- -------------------- ---------- -------------------- --------------------
TS_TEST01            ONLINE               LOGGING    LOCAL                SYSTEM			AUTO                 (null)               (null)               ADVANCED LOW
USERS            	 ONLINE               LOGGING    LOCAL                SYSTEM			AUTO                 (null)               (null)               (null)

 

ALTER SESSION SET DB_INDEX_COMPRESSION_INHERITANCE=TABLESPACE;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_index_compression_inheritance     string      TABLESPACE

 

테이블도 비압축으로 생성하고 인덱스도 비압축옵션으로 생성시 결과 확인

CREATE TABLE YSBAE.TB_ADDPART_INDEX_4
(
    INVOKE_DT       VARCHAR2(6) NOT NULL,
    INVOKE_ID       VARCHAR2(20) NOT NULL,
    INVOKE_NO       VARCHAR2(20) NOT NULL,
    RSVC_ID         VARCHAR2(20) NOT NULL,
    SEQ             NUMBER(10) NOT NULL,
    ITEM_ID         VARCHAR2(21) NOT NULL,
    ITEM_VAL_NUM    NUMBER(25,7),
    ITEM_VAL_TXT    VARCHAR2(4000),
    ITEM_IO_DIV     VARCHAR2(1)
)
TABLESPACE TS_TEST01
NOCOMPRESS
PARTITION BY RANGE (INVOKE_DT)
(
    PARTITION PART_202607 VALUES LESS THAN ('202608')
    TABLESPACE TS_TEST01
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOCOMPRESS,
    PARTITION PART_202608 VALUES LESS THAN ('202609')
    TABLESPACE TS_TEST01
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOCOMPRESS,
    PARTITION PART_202609 VALUES LESS THAN ('202610')
    TABLESPACE TS_TEST01
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOCOMPRESS
);

CREATE UNIQUE INDEX YSBAE.IDX_ADDPART_INDEX_4
ON YSBAE.TB_ADDPART_INDEX_4
(
    INVOKE_DT,
    INVOKE_ID,
    INVOKE_NO,
    RSVC_ID,
    SEQ,
    ITEM_ID
) 
LOCAL
(
    PARTITION PART_202607
    TABLESPACE TS_TEST01
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOLOGGING,
    PARTITION PART_202608
    TABLESPACE TS_TEST01
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOLOGGING,
    PARTITION PART_202609
    TABLESPACE TS_TEST01
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOLOGGING
)
TABLESPACE TS_TEST01
NOLOGGING;

 

테이블은 비압축이지만 인덱스는 압축으로 생성되었습니다.

--인덱스
select index_owner,index_name,partition_name,compression,logging
From dba_ind_partitions              
where index_name='IDX_ADDPART_INDEX_4';   

INDEX_OWNER		INDEX_NAME           PARTITION_NAME       COMPRESSION          LOGGING
----------------------------------------------------------------------------------------------------
YSBAE			IDX_ADDPART_INDEX_4  PART_202607          ADVANCED LOW         NO
YSBAE			IDX_ADDPART_INDEX_4  PART_202608          ADVANCED LOW         NO
YSBAE			IDX_ADDPART_INDEX_4  PART_202609          ADVANCED LOW         NO

--테이블
select table_owner,table_name,partition_name,compression,compress_for,logging
From dba_tab_partitions        
where table_name='TB_ADDPART_INDEX_4';   

TABLE_OWNER          TABLE_NAME           PARTITION_NAME       COMPRESSION	COMPRESS_FOR         LOGGING
-------------------- -------------------- -------------------- --------------------
YSBAE                TB_ADDPART_INDEX_4   PART_202607          DISABLED		(null)               YES
YSBAE                TB_ADDPART_INDEX_4   PART_202608          DISABLED		(null)               YES
YSBAE                TB_ADDPART_INDEX_4   PART_202609          DISABLED		(null)               YES

 

6) 파라미터 NONE으로 설정 변경 후 테스트 (테이블스페이스의 옵션은 여전히 ADVANCED LOW)

ALTER SESSION SET DB_INDEX_COMPRESSION_INHERITANCE=NONE;
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_index_compression_inheritance     string      NONE

 

6) 파라미터를 NONE으로 변경 후 테이블은 압축으로,인덱스는 비압축으로 생성

CREATE TABLE YSBAE.TB_ADDPART_INDEX_5
(
    INVOKE_DT       VARCHAR2(6) NOT NULL,
    INVOKE_ID       VARCHAR2(20) NOT NULL,
    INVOKE_NO       VARCHAR2(20) NOT NULL,
    RSVC_ID         VARCHAR2(20) NOT NULL,
    SEQ             NUMBER(10) NOT NULL,
    ITEM_ID         VARCHAR2(21) NOT NULL,
    ITEM_VAL_NUM    NUMBER(25,7),
    ITEM_VAL_TXT    VARCHAR2(4000),
    ITEM_IO_DIV     VARCHAR2(1)
)
TABLESPACE TS_TEST01
COMPRESS FOR OLTP
PARTITION BY RANGE (INVOKE_DT)
(
    PARTITION PART_202607 VALUES LESS THAN ('202608')
    TABLESPACE TS_TEST01
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    COMPRESS FOR OLTP,
    PARTITION PART_202608 VALUES LESS THAN ('202609')
    TABLESPACE TS_TEST01
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    COMPRESS FOR OLTP,
    PARTITION PART_202609 VALUES LESS THAN ('202610')
    TABLESPACE TS_TEST01
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    COMPRESS FOR OLTP
);

CREATE UNIQUE INDEX YSBAE.IDX_ADDPART_INDEX_5
ON YSBAE.TB_ADDPART_INDEX_5
(
    INVOKE_DT,
    INVOKE_ID,
    INVOKE_NO,
    RSVC_ID,
    SEQ,
    ITEM_ID
) 
LOCAL
(
    PARTITION PART_202607
    TABLESPACE TS_TEST01
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOLOGGING,
    PARTITION PART_202608
    TABLESPACE TS_TEST01
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOLOGGING,
    PARTITION PART_202609
    TABLESPACE TS_TEST01
    STORAGE
    (
        INITIAL 64K
        NEXT 1M
    )
    NOLOGGING
)
TABLESPACE TS_TEST01
NOLOGGING;

 

확인

파라미터가 NONE이라면 테이블스페이스자체에 압축옵션이 걸려있어도 비압축으로 생성됩니다.

select index_owner,index_name,partition_name,compression,logging
From dba_ind_partitions              
where index_name='IDX_ADDPART_INDEX_5';   

INDEX_OWNER		INDEX_NAME           PARTITION_NAME       COMPRESSION          LOGGING
----------------------------------------------------------------------------------------------------
YSBAE			IDX_ADDPART_INDEX_5  PART_202607          DISABLED             NO
YSBAE			IDX_ADDPART_INDEX_5  PART_202608          DISABLED             NO
YSBAE			IDX_ADDPART_INDEX_5  PART_202609          DISABLED             NO

select table_owner,table_name,partition_name,compression,compress_for,logging
From dba_tab_partitions          
where table_name='TB_ADDPART_INDEX_5';   

TABLE_OWNER          TABLE_NAME           PARTITION_NAME       COMPRESSION		COMPRESS_FOR         LOGGING
-------------------- -------------------- -------------------- --------------------
YSBAE                TB_ADDPART_INDEX_5   PART_202607          ENABLED			ADVANCED             YES
YSBAE                TB_ADDPART_INDEX_5   PART_202608          ENABLED			ADVANCED             YES
YSBAE                TB_ADDPART_INDEX_5   PART_202609          ENABLED			ADVANCED             YES

 

압축이 자동적으로 필요한 환경에서는 사용 시 유용할 수도 있겠네요.

하지만 자동압축으로 생성되는 요건이 필요하다면 생성시에 압축옵션을 추가하여 생성하여 관리하는 것이 더 편하겠습니다.