[Oracle] 오라클 인덱스 단편화, Index Fragmentation

* 오라클의 B*Tree Index는 Unbalanced 상태에 놓일 일은 없지만 Index Fragmentation에 의한 Index Skew 또는 Sparse 현상으로 인덱스 스캔 효율이 저하될 수 있음

☑ [Oracle] 오라클 인덱스 구조 - B*Tree Index 자세히 보기

Index Skew

- 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상

DELETE FROM T WHERE NO <= 500000;

COMMIT;

- 텅 빈 인덱스 블록은 커밋하는 순간 freelist로 반환되지만 인덱스 구조 상에는 그대로 남음

- 재사용 될 때까지 인덱스 스캔 효율 저하

- 대량의 데이터를 매일 지웠다가 새로 입력하는 통계성 테이블일 경우 Index Skew 현상 주의

-- 보관주기가 3일인 일별고객별판매집계 테이블을 위한 배치 프로그램

DELETE FROM 일별고객별판매집계 WHERE 판매일시 < TRUNC(SYSDATE) -2;

INSERT INTO 일별고객별판매집계

SELECT TO_CHAR(SYSDATE, 'YYYYMMDD'), 고객번호, SUM(판매량), SUM(판매금액)

   FROM  판매

WHERE 판매일시 BETWEEN TRUNC(SYSDATE) AND TRUNC(SYSDATE+1)-1/24/60/60

GROUP BY 고객번호;

COMMIT;

-- PK 인덱스 왼쪽에 놓인 상당수 리프 블록들이 DELETE문을 통해 EMPTY 상태가 되더라도 COMMIT 전까지는 freelist로 반환될 수 없음

-- DELETE문 직후에 COMMIT을 수행하면 지워진 블록들이 곧바로 INSERT 과정에 재사용 됨

Index Sparse

- 인덱스 블록 전반에 걸쳐 밀도가 떨어지는 현상

DELETE FROM T WHERE MOD(NO, 10) < 5;

COMMIT;

- 데이터의 50%를 삭제한 후에도 인덱스 블록 수가 동일

- Index Skew처럼 블록이 아예 텅 비면 곧바로 freelist로 반환돼 언제든 재사용되지만, Index Sparse는 지워진 자리에 인덱스 정렬 순서에 따라 새로운 값이 입력될 때까지 오래도록 인덱스 스캔 효율 저하 (영영 재사용되지 않을 수도 있음)

- 총 레코드 건수가 일정함에도 불구 인덱스 공간 사용량이 계속 증가한다면 Index Sparse를 의심

Fragmentation 현상의 대응 방안

* 대응 방안 고려 시점

- 인덱스 분할에 의한 경합이 현저히 높을 때

- 자주 사용되는 인덱스 스캔 효율을 높이고자 할 때, 특히 NL Join에서 반복 액세스되는 인덱스 높이가 증가했을 때

- 대량의 DELETE 작업을 수행한 이후 다시 레코드가 입력되기까지 오랜 기간이 소요될 때

- 총 레코드 수가 일정한대도 인덱스가 계속 커질 때

COALESCE

ALTER INDEX T_IDX COALESCE;

- 여러 인덱스 블록을 하나로 merge하고, 그 결과로 생긴 빈 블록들은  freelist에 반환

* 단, 인덱스 세그먼트에 할당된 나머지 미사용 공간은 반환되지 않음 (HWM는 동일)

☑ [Oracle] 오라클 HWM (High Water Mark) 자세히 보기

SHRINK

- ASSM(AUTO Segment Space Managed) Tablespace에 존재하는 세그먼트만 대상이 됨

ALTER INDEX T_IDX SHRINK SPACE;

-- 테이블과 HWM을 축소, 해당 테이블의 공간만 Tablespace로 환원

ALTER INDEX T_IDX SHRINK SPACE CASCADE;

-- 테이블 및 관련된 인덱스를 모두 Tablespace로 환원

ALTER INDEX T_IDX SHRINK SPACE COMPACE;

-- 테이블은 축소시키지만 HWM는 그대로, COALESCE와 동일

REBUILD

- COALESCE나 SHRINK는 레코드를 건건이 지웠다가 다시 입력하는 방식을 사용하므로 작업량이 많을 때는 REBUILD를 사용

ALTER INDEX T_IDX REBUILD;

ALTER INDEX T_IDX REBUILD ONLINE;