Lob Partition 의 partition과 lob partition의 default attributes 설정 변경하여 자동으로 추가되는 파티션 압축되게 하기

 

 

구문

일반 테이블 영역 default attributes 변경 구문

ALTER TABLE 테이블명 MODIFY DEFAULT ATTRIBUTES COMPRESS 압축옵션;

lob 테이블 영역 default attributes 변경 구문

ALTER TABLE 테이블명 MODIFY DEFAULT ATTRIBUTES LOB(LOB 컬럼명) (COMPRESS 압축옵션);

 

테스트를 위해 CLOB이 포함된 테이블 하나를 생성하고 옵션 설정 전/후를 비교하여 추가된 파티션이 압축옵션을 가지고 있는지 테스트해보겠습니다.

 

1.테스트테이블 생성

CREATE TABLE LOB_PART_TEST (
ID VARCHAR2(10) NOT NULL,
NAME VARCHAR2(10) NOT NULL,
MSG CLOB,
SEQ NUMBER
)
TABLESPACE USERS
LOB(MSG) STORE AS SECUREFILE
PARTITION BY RANGE (ID)
(
PARTITION PT_LOB_PART_TEST_P1 VALUES LESS THAN ('10')
TABLESPACE USERS
)
;

 

 

2.상태 확인

둘다 compress관련된 부분에 설정이 되어 있지 않습니다.

SELECT owner,table_name,PARTITIONING_TYPE,PARTITION_COUNT,DEF_COMPRESSION,DEF_COMPRESS_FOR FROM dba_part_tables WHERE table_name='LOB_PART_TEST';
OWNER |TABLE_NAME   |PARTITIONING_TYPE|PARTITION_COUNT|DEF_COMPRESSION|DEF_COMPRESS_FOR|
------+-------------+-----------------+---------------+---------------+----------------+
SYSTEM|LOB_PART_TEST|RANGE            |              2|NONE           |                |

SELECT TABLE_owner,table_name,column_name,lob_name,DEF_COMPRESS FROM dba_part_lobs WHERE table_name='LOB_PART_TEST';

TABLE_OWNER|TABLE_NAME   |COLUMN_NAME|LOB_NAME                 |DEF_COMPRESS|
-----------+-------------+-----------+-------------------------+------------+
SYSTEM     |LOB_PART_TEST|MSG        |SYS_LOB0000077784C00003$$|NO          |

 

3.파티션 add 하여 옵션 확인

ALTER TABLE LOB_PART_TEST ADD PARTITION PT_LOB_PART_TEST_P2 VALUES less than ('20');

 

4.파티션 확인

추가된 PT_LOB_PART_TEST_P2는 테이블영역과 LOB영역 둘다 압축이 되지 않고 추가되었습니다.

SELECT table_owner,table_name,partition_name,high_value,tablespace_name,compression,compress_for FROM dba_tab_partitions WHERE table_name='LOB_PART_TEST';
TABLE_OWNER|TABLE_NAME   |PARTITION_NAME     |HIGH_VALUE|TABLESPACE_NAME|COMPRESSION|COMPRESS_FOR|
-----------+-------------+-------------------+----------+---------------+-----------+------------+
SYSTEM     |LOB_PART_TEST|PT_LOB_PART_TEST_P1|'10'      |USERS          |DISABLED   |            |
SYSTEM     |LOB_PART_TEST|PT_LOB_PART_TEST_P2|'20'      |USERS          |DISABLED   |            |

SELECT table_owner,table_name,lob_name,tablespace_name,compression FROM dba_lob_partitions WHERE table_name='LOB_PART_TEST';

TABLE_OWNER|TABLE_NAME   |LOB_NAME                 |TABLESPACE_NAME|COMPRESSION|
-----------+-------------+-------------------------+---------------+-----------+
SYSTEM     |LOB_PART_TEST|SYS_LOB0000077784C00003$$|USERS          |NO         |
SYSTEM     |LOB_PART_TEST|SYS_LOB0000077784C00003$$|USERS          |NO         |

 

 

5. 설정변경

ALTER TABLE LOB_PART_TEST MODIFY DEFAULT ATTRIBUTES COMPRESS FOR OLTP;

 

ALTER TABLE LOB_PART_TEST MODIFY DEFAULT ATTRIBUTES LOB(MSG) (COMPRESS HIGH);

 

확인

SELECT owner,table_name,PARTITIONING_TYPE,PARTITION_COUNT,DEF_COMPRESSION,DEF_COMPRESS_FOR FROM dba_part_tables WHERE table_name='LOB_PART_TEST';
OWNER |TABLE_NAME   |PARTITIONING_TYPE|PARTITION_COUNT|DEF_COMPRESSION|DEF_COMPRESS_FOR|
------+-------------+-----------------+---------------+---------------+----------------+
SYSTEM|LOB_PART_TEST|RANGE            |              2|ENABLED        |ADVANCED        |

SELECT TABLE_owner,table_name,column_name,lob_name,DEF_COMPRESS FROM dba_part_lobs WHERE table_name='LOB_PART_TEST';
TABLE_OWNER|TABLE_NAME   |COLUMN_NAME|LOB_NAME                 |DEF_COMPRESS|
-----------+-------------+-----------+-------------------------+------------+
SYSTEM     |LOB_PART_TEST|MSG        |SYS_LOB0000077784C00003$$|HIGH        |

 

 

6.파티션 add하여 추가된 파티션 설정 확인

ALTER TABLE LOB_PART_TEST ADD PARTITION PT_LOB_PART_TEST_P3 VALUES less than ('30');

 

SELECT table_owner,table_name,partition_name,high_value,tablespace_name,compression,compress_for FROM dba_tab_partitions WHERE table_name='LOB_PART_TEST';
TABLE_OWNER|TABLE_NAME   |PARTITION_NAME     |HIGH_VALUE|TABLESPACE_NAME|COMPRESSION|COMPRESS_FOR|
-----------+-------------+-------------------+----------+---------------+-----------+------------+
SYSTEM     |LOB_PART_TEST|PT_LOB_PART_TEST_P1|'10'      |USERS          |DISABLED   |            |
SYSTEM     |LOB_PART_TEST|PT_LOB_PART_TEST_P2|'20'      |USERS          |DISABLED   |            |
SYSTEM     |LOB_PART_TEST|PT_LOB_PART_TEST_P3|'30'      |USERS          |ENABLED    |ADVANCED    |
SELECT table_owner,table_name,lob_name,tablespace_name,compression FROM dba_lob_partitions WHERE table_name='LOB_PART_TEST';
TABLE_OWNER|TABLE_NAME   |LOB_NAME                 |TABLESPACE_NAME|COMPRESSION|
-----------+-------------+-------------------------+---------------+-----------+
SYSTEM     |LOB_PART_TEST|SYS_LOB0000077784C00003$$|USERS          |NO         |
SYSTEM     |LOB_PART_TEST|SYS_LOB0000077784C00003$$|USERS          |NO         |
SYSTEM     |LOB_PART_TEST|SYS_LOB0000077784C00003$$|USERS          |HIGH       |

 

새로 추가된 파티션은 COMPRESS가 잘 되서 생성되었습니다.

 

참고로 LOB 의 compress 옵션에는 LOW/MEDIUM/HIGH가 있습니다.

압축률에 따른 차이로 보이며 성능에도 차이가 있을 수있습니다. 아무래도 압축률이 높은 HIGH로 수행하면 성능이 안나올 수 있을것도 같네요.

 

 

테스트 DB : Oracle 23ai Free 

 

참고 :https://docs.oracle.com/en/database/oracle/oracle-database/19/adlob/using-oracle-LOBs-storage.html#GUID-D4B9A527-6DF8-4451-B88B-B973E961D9F7

 

참고 : https://docs.oracle.com/en/database/oracle/oracle-database/21/adlob/LOBs-in-partitioned-tables.html#GUID-48A8158E-348A-44C7-8E85-C05EE0F0A6EF

 

참고 : https://docs.oracle.com/en/database/oracle/oracle-database/21/vldbg/partition-table-compression.html#GUID-F26AFD78-DC1D-4E6B-9B37-375C59FD1787