Oracle 23c부터 IOT에 대한 Advanced LOW 압축이 가능해졌습니다. 이전 버전까지는 IOT에 대해 Prefiex 압축만 가능했습니다.
An index-organized table (IOT) is a table stored in a variation of a B-tree index structure where rows are ordered by primary key. IOTs are useful because they provide fast random access by primary key without duplicating primary key columns in two structures - a heap table and an index. In earlier releases, IOTs only supported Oracle's prefix key compression, which required additional analysis and had the possibility of negative compression (where the overhead of compression outweighed the compression benefits). Advanced LOW IOT Compression allows you to reduce the overall storage for Oracle Databases in the IOT space.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다. t1 테이블은 비압축, t2 테이블은 Prefiex 압축, t3 테이블은 Advanced LOW 압축으로 생성합니다.
-- 1
DROP TABLE t0 PURGE;
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
CREATE TABLE t0 (c1, c2, c3, c4)
AS
SELECT CEIL (ROWNUM / 100)
, NVL (NULLIF (MOD (ROWNUM, 100), 0), 100)
, LPAD (ROWNUM, 10, '0')
, LPAD (ROWNUM, 10, '0')
FROM XMLTABLE ('1 to 1000000');
CREATE TABLE t1 (c1, c2, c3, c4, CONSTRAINT t1_pk PRIMARY KEY (c1, c2))
ORGANIZATION INDEX
AS
SELECT * FROM t0;
CREATE TABLE t2 (c1, c2, c3, c4, CONSTRAINT t2_pk PRIMARY KEY (c1, c2))
ORGANIZATION INDEX COMPRESS
AS
SELECT * FROM t0;
CREATE TABLE t3 (c1, c2, c3, c4, CONSTRAINT t3_pk PRIMARY KEY (c1, c2))
ORGANIZATION INDEX COMPRESS ADVANCED LOW
AS
SELECT * FROM t0;
*_INDEXES 뷰의 compression 칼럼에서 압축 유형을 조회할 수 있습니다. *_SEGMENTS 뷰를 조회하면 압축한 t2_pk, p3_pk 인덱스가 압축하지 않은 t1_pk 인덱스보다 작지만, 압축 유형이 다른 t2_pk, p3_pk 인덱스의 크기가 같은 것을 확인할 수 있습니다.
-- 2-1
SELECT index_name, compression
FROM user_indexes
WHERE index_name IN ('T1_PK', 'T2_PK', 'T3_PK');
INDEX_NAME COMPRESSION
---------- ------------
T1_PK DISABLED
T2_PK ENABLED -- Prefix Compression
T3_PK ADVANCED LOW
3 행이 선택되었습니다.
-- 2-2
SELECT segment_name, bytes, blocks
FROM user_segments
WHERE segment_name IN ('T1_PK', 'T2_PK', 'T3_PK');
SEGMENT_NAME BYTES BLOCKS
------------ -------- ------
T1_PK 42991616 5248
T2_PK 37748736 4608 -- !
T3_PK 37748736 4608 -- !
3 행이 선택되었습니다.
원인을 분석하기 위해 t2_pk, t3_pk 인덱스의 tree dump를 생성하겠습니다.
-- 3-1
SELECT object_name, object_id
FROM user_objects
WHERE object_name IN ('T2_PK', 'T3_PK');
OBJECT_NAME OBJECT_ID
----------- ---------
T2_PK 78825
T3_PK 78827
2 행이 선택되었습니다.
-- 3-2
ALTER SESSION SET EVENTS 'immediate trace name treedump level 78825';
ALTER SESSION SET EVENTS 'immediate trace name treedump level 78827';
t2_pk, t3_pk 인덱스의 tree dump를 살펴보면 압축 유형이 cmp, Acmp로 다르지만 리프 블록에 저장된 로우 수는 같은 것을 확인할 수 있습니다.
-- 4-1
branch: 0x3c0c853 62965843 (0: nrow: 8, level: 2)
branch: 0x3c0df05 62971653 (-1: nrow: 623, level: 1)
leaf: 0x3c0c854 62965844 (-1: row:223.223 avs:829 cmp:3.1.2)
leaf: 0x3c0c855 62965845 (0: row:223.223 avs:829 cmp:3.1.2)
leaf: 0x3c0c856 62965846 (1: row:223.223 avs:829 cmp:3.1.2)
leaf: 0x3c0c857 62965847 (2: row:223.223 avs:829 cmp:3.1.2)
-- 4-2
branch: 0x3c0eed3 62975699 (0: nrow: 8, level: 2)
branch: 0x3c10305 62980869 (-1: nrow: 623, level: 1)
leaf: 0x3c0eed4 62975700 (-1: row:223.223 avs:829 Acmp:3.1.2)
leaf: 0x3c0eed5 62975701 (0: row:223.223 avs:829 Acmp:3.1.2)
leaf: 0x3c0eed6 62975702 (1: row:223.223 avs:829 Acmp:3.1.2)
leaf: 0x3c0eed7 62975703 (2: row:223.223 avs:829 Acmp:3.1.2)
t2_pk, t3_pk 인덱스의 헤더 블록에 대한 block dump를 살펴봐도 특별한 차이를 찾을 수 없었습니다. 향후 추가 테스트가 필요할 것으로 보입니다.
-- 5-1
SELECT DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (62965843) AS t2_fno
, DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (62965843) AS t2_bno
, DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (62975699) AS t3_fno
, DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (62975699) AS t3_bno
FROM DUAL;
T2_FNO T2_BNO T3_FNO T3_BNO
------ ------ ------ ------
15 51283 15 61139
1개의 행이 선택되었습니다.
-- 5-2
ALTER SYSTEM DUMP DATAFILE 15 BLOCK 51283;
ALTER SYSTEM DUMP DATAFILE 15 BLOCK 61139;
아래 링크에서 tree dump, block dump 파일을 다운로드할 수 있습니다.
참고로 IOT를 생성하는 CREATE TABLE 문의 서브쿼리에 XMLTABLE 함수를 사용하면 에러가 발생하는 버그가 있는 것으로 보입니다.
-- 6
CREATE TABLE t4 (c1, c2, CONSTRAINT t4_pk PRIMARY KEY (c1))
ORGANIZATION INDEX
AS
SELECT ROWNUM, ROWNUM FROM XMLTABLE ('1 to 100');
ORA-00942: 테이블 또는 뷰가 존재하지 않습니다