UNIQUE 인덱스와 NON-UNIQUE 인덱스는 상이한 방식으로 블록을 저장하며, 이로 인한 오버헤드가 발생할 수 있다.
테스트를 위해 아래와 같이 테이블과 인덱스를 생성하자. 동일한 값을 가진 c1, c2 칼럼에 대해 각각 UNIQUE 인덱스(t1_u1)와 NON-UNIQUE 인덱스(t_x1)를 생성했다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT ROWNUM AS c1, ROWNUM AS c2 FROM XMLTABLE ('1 to 100000');
CREATE UNIQUE INDEX t1_u1 ON t1 (c1);
CREATE INDEX t1_x1 ON t1 (c2);
ANALYZE 문으로 인덱스의 구조를 검증해보자. 값을 비교하기 위해 임시 테이블에 통계정보를 삽입했다.
-- 2 ANALYZE INDEX t1_u1 VALIDATE STRUCTURE OFFLINE; DROP TABLE t_index_stats PURGE; CREATE TABLE t_index_stats AS SELECT * FROM index_stats; ANALYZE INDEX t1_x1 VALIDATE STRUCTURE OFFLINE; INSERT INTO t_index_stats SELECT * FROM index_stats; COMMIT;
통계정보를 비교해보면 UNIQUE 인덱스인 t1_u1 인덱스보다 NON-UNIQUE 인덱스인 t1_x1 인덱스가 더 많은 공간을 사용한다는 사실을 알 수 있다. 리프 블록의 로우 길이가 1바이트씩 증가함에 따라 리프 블록의 개수가 14개로 늘었고, 리프 블록의 개수만큼 브랜치 블록의 로우도 14개가 늘었다. 이로 인해 브랜치 블록의 로우 길이도 늘어났다.
-- 3
SELECT column_name, t1_u1, t1_x1, t1_x1 - t1_u1 AS diff
FROM t_index_stats
UNPIVOT (column_value
FOR column_name IN (height
, blocks
, lf_rows
, lf_blks
, lf_rows_len
, lf_blk_len
, br_rows
, br_blks
, br_rows_len
, br_blk_len
, del_lf_rows
, del_lf_rows_len
, distinct_keys
, most_repeated_key
, btree_space
, used_space
, pct_used
, rows_per_key
, blks_gets_per_access
, pre_rows
, pre_rows_len
, opt_cmpr_count
, opt_cmpr_pctsave))
PIVOT (MAX (column_value)
FOR name IN ('T1_U1' AS t1_u1
, 'T1_X1' AS t1_x1))
ORDER BY 1;
COLUMN_NAME T1_U1 T1_X1 DIFF
-------------------- ------- ------- ------
BLKS_GETS_PER_ACCESS 3 3 0
BLOCKS 256 256 0
BR_BLKS 1 1 0
BR_BLK_LEN 8032 8032 0
BR_ROWS 207 221 14 -- !
BR_ROWS_LEN 2256 2630 374 -- !
BTREE_SPACE 1672032 1784032 112000
DEL_LF_ROWS 0 0 0
DEL_LF_ROWS_LEN 0 0 0
DISTINCT_KEYS 100000 100000 0
HEIGHT 2 2 0
LF_BLKS 208 222 14 -- !
LF_BLK_LEN 8000 8000 0
LF_ROWS 100000 100000 0
LF_ROWS_LEN 1488892 1588892 100000 -- !
MOST_REPEATED_KEY 1 1 0
OPT_CMPR_COUNT 0 0 0
OPT_CMPR_PCTSAVE 0 0 0
PCT_USED 90 90 0
PRE_ROWS 0 0 0
PRE_ROWS_LEN 0 0 0
ROWS_PER_KEY 1 1 0
USED_SPACE 1491148 1591522 100374
23 행이 선택되었습니다.
브랜치 노드는 고유한 값을 기준으로 리프 노드를 분기해야 한다. 중복 값이 존재할 수 있는 NON-UNIQUE 인덱스는 브랜치 노드의 고유 값을 만들기 위해 ROWID를 칼럼 값으로 처리한다. 이로 인해 브랜치 노드에 ROWID가 저장될 수 있다. 그래서 더 많은 저장 공간을 사용한 것이다. 블록 덤프를 통해 저장 방식을 확인해보자. 아래는 t1_u1 인덱스의 블록 덤프 내용이다. col 0에 c1 칼럼 값이 저장되어 있다. 리프 블록의 로우 길이는 11이고, 제한 ROWID(01 c0 00 bb 00 00)가 함께 저장된 것을 확인할 수 있다.
-- 4-1 : T1_U1 Branch block dump ================= ... row#0[8052] dba: 29494981=0x1c20ec5 col 0; len 3; (3): c2 06 16 --> 521 row#1[8044] dba: 29494982=0x1c20ec6 col 0; len 3; (3): c2 0b 23 --> 1034 row#2[8036] dba: 29494983=0x1c20ec7 col 0; len 3; (3): c2 10 30 --> 1547 ... Leaf block dump =============== ... row#0[8025] flag: -------, lock: 0, len=11, data:(6): 01 c0 00 bb 00 00 col 0; len 2; (2): c1 02 --> 1 row#1[8014] flag: -------, lock: 0, len=11, data:(6): 01 c0 00 bb 00 01 col 0; len 2; (2): c1 03 --> 2 row#2[8003] flag: -------, lock: 0, len=11, data:(6): 01 c0 00 bb 00 02 col 0; len 2; (2): c1 04 --> 3 ...
아래는 t1_x1 인덱스의 블록 덤프 내용이다. 브랜치 블록과 리프 블록에 col 1이 추가된 것을 확인할 수 있다. 브랜치 블록은 TERM, 리프 블록은 제한 ROWID 값이 저장되어 있다. TERM은 해당 칼럼 값이 없더라도 값을 고유하게 구분할 수 있음을 나타낸다. 이로 인해 리프 블록의 로우 길이가 12로 늘어난 것이다. 고유한 값의 열 조합으로 NON-UNIQUE 인덱스를 생성하면 오버헤드가 발생할 수 있음을 알 수 있다.
-- 4-2 : T1_X1 Branch block dump ================= ... ow#0[8051] dba: 29495405=0x1c2106d col 0; len 3; (3): c2 05 57 --> 486 col 1; TERM row#1[8042] dba: 29495406=0x1c2106e col 0; len 3; (3): c2 0a 42 --> 965 col 1; TERM row#2[8033] dba: 29495407=0x1c2106f col 0; len 3; (3): c2 0f 2d --> 1444 col 1; TERM ... Leaf block dump =============== ... row#0[8024] flag: -------, lock: 0, len=12 col 0; len 2; (2): c1 02 --> 1 col 1; len 6; (6): 01 c0 00 bb 00 00 --> ROWID row#1[8012] flag: -------, lock: 0, len=12 col 0; len 2; (2): c1 03 --> 2 col 1; len 6; (6): 01 c0 00 bb 00 01 --> ROWID row#2[8000] flag: -------, lock: 0, len=12 col 0; len 2; (2): c1 04 --> 3 col 1; len 6; (6): 01 c0 00 bb 00 02 --> ROWID ...
아래 쿼리로 테이블의 ROWID와 블록 덤프에 저장된 값을 비교해볼 수 있다. 첫 번째 열의 ROWID 덤프 값(1,c0,0,bb,0,0)과 블록 덤프 값(01 c0 00 bb 00 00)이 일치하는 것을 확인할 수 있다. ROWID의 앞쪽 8바이트는 오브젝트 ID를 의미한다.
-- 5-1 SELECT c1, ROWID, DUMP (ROWID, 16) AS rowid_dump FROM t1 WHERE c1 <= 3; C1 ROWID ROWID_DUMP -- ------------------ -------------------------------------- 1 AAA5b8AAHAAAAC7AAA Typ=69 Len=10: 0,3,96,fc,1,c0,0,bb,0,0 -- 0,3,96,fc + 1,c0,0,bb,0,0 2 AAA5b8AAHAAAAC7AAB Typ=69 Len=10: 0,3,96,fc,1,c0,0,bb,0,1 3 AAA5b8AAHAAAAC7AAC Typ=69 Len=10: 0,3,96,fc,1,c0,0,bb,0,2 3 행이 선택되었습니다. -- 5-2 SELECT object_id, TO_CHAR (object_id, 'XXXXXX') AS object_id_hex FROM user_objects WHERE object_name = 'T1'; OBJECT_ID OBJECT_HEX --------- ---------- 235260 396FC 1개의 행이 선택되었습니다.
t1_x1 인덱스는 브랜치 블록에 ROWID가 저장되지 않았다. c1 칼럼 값이 고유하기 때문이다. 브랜치 블록에 ROWID가 저장되는 예제를 살펴보자. 테스트를 위해 아래와 같이 테이블과 인덱스를 생성하자.
-- 6
DROP TABLE t2 PURGE;
CREATE TABLE t2 AS SELECT 'A' AS c1 FROM XMLTABLE ('1 to 2000');
CREATE INDEX t2_x1 ON t2 (c1);
아래는 t2_x1 인덱스의 트리 덤프 내용이다. 4개의 리프 블록으로 구성되며, 첫 번째 리프 블록에 553개의 행이 저장된 것을 확인할 수 있다.
-- 7 ----- begin tree dump branch: 0x1c20cfb 29494523 (0: nrow: 4, level: 1) leaf: 0x1c20cfc 29494524 (-1: row:552.552 avs:824) leaf: 0x1c20cfd 29494525 (0: row:552.552 avs:824) leaf: 0x1c20cfe 29494526 (1: row:552.552 avs:824) leaf: 0x1c20cff 29494527 (2: row:344.344 avs:3528) ----- end tree dump
아래는 t2_x1 인덱스의 브랜치 블록 덤프 내용이다. col 1에 TERM이 아닌 ROWID가 저장된 것을 확인할 수 있다. c1 칼럼 값이 모두 A로 중복되므로 ROWID를 통해 b-tree 노드를 분기한 것이다.
-- 8 Branch block dump ================= ... row#0[8047] dba: 29494525=0x1c20cfd col 0; len 1; (1): 41 --> A col 1; len 6; (6): 01 c1 f3 93 02 28 --> ROWID row#1[8034] dba: 29494526=0x1c20cfe col 0; len 1; (1): 41 --> A col 1; len 6; (6): 01 c1 f3 94 01 bf --> ROWID row#2[8021] dba: 29494527=0x1c20cff col 0; len 1; (1): 41 --> A col 1; len 6; (6): 01 c1 f3 95 01 56 --> ROWID
아래 쿼리에서 553번째 행의 ROWID 덤프 값((1,c1,f3,93,2,28)이 첫 번째 브랜치 블록의 col 1 값(01 c1 f3 93 02 28)과 일치하는 것을 확인할 수 있다. 브랜치 블록은 리프 블록의 마지막 값을 저장한다.
-- 9 SELECT * FROM (SELECT ROWNUM AS rn, DUMP (ROWID, 16) AS rowid_dump FROM t2) WHERE rowid_dump LIKE '%1,c1,f3,93,2,28'; RN ROWID_DUMP --- ---------------------------------------- 553 Typ=69 Len=10: 0,3,97,e2,1,c1,f3,93,2,28 -- 0,3,97,e2 + 1,c1,f3,93,2,28 1개의 행이 선택되었습니다.
'Oracle > Administration' 카테고리의 다른 글
| CLOB #2 - NVL 함수 (0) | 2018.06.23 |
|---|---|
| CLOB #1 - BASICFILE vs SECUREFILE (0) | 2018.06.23 |
| 오라클 에러 조회 함수 (0) | 2018.03.04 |
| 오라클 에러 테이블 (0) | 2018.03.01 |
| PK 제약조건과 인덱스 #2 (0) | 2014.05.06 |
