오라클 19c 인덱스 Skew 발생시 공간 재사용 확인

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

방법 : 오라클 19c 인덱스 Skew 발생시 공간 재사용 확인

인덱스 Skew 란 delete 작업에 의해 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상을 말함

이 경우 재사용은 가능하지만 해당 공간이 다시 채워지기 전까지는 인덱스 스캔 효율이 떨어진다는 단점이 있음

오라클 19c 에서 인덱스 Skew 발생시 해당 공간이 재사용 되는지를 확인해봄

index Skew 출처 : 오라클 성능 고도화 원리와 해법 2 172p

 

 

Skew 테스트

테스트용 big_table 테이블 생성

SQL> 
create table big_table as
select no
from ( select level as no
from dual
connect by level <= 1000000
);Table created.

 

 

샘플테이블 생성

SQL> create table t as select rownum no from big_table where rownum <= 1000000 ;
 
Table created.

 

 

샘플인덱스 생성

SQL> create index t_idx on t(no) pctfree 0 ;
 
Index created.

 

 

기존인덱스 블록 조회

SQL> select segment_name, blocks from dba_segments where segment_name ='T_IDX';
 
SEGMENT_NAME BLOCKS
------------------- --------------
T_IDX 2048

2048블록

 

 

데이터 삭제(delete) 및 커밋

SQL> delete from t where no <= 500000 ;
 
500000 rows deleted.
 
SQL> commit ;
 
Commit complete.

이 시점에 인덱스 블록이 freelist 로 반환되지만 인덱스 구조는 그대로 남게됨

 

 

삭제 후 인덱스 블록 조회

SQL> select segment_name, blocks from dba_segments where segment_name ='T_IDX';
 
SEGMENT_NAME BLOCKS
------------------- --------------
T_IDX 2048

2048 블록으로 삭제(delete) 전과 동일(인덱스 구조가 남아있는 인덱스 skew 상태)

 

 

지워진 영역에 다시 삽입

SQL> insert into t select rownum no from big_table where rownum <= 500000 ;
 
500000 rows created.
 
SQL> commit;
 
Commit complete.

 

 

삽입 후 인덱스 블록 조회

SQL> select segment_name, blocks from dba_segments where segment_name ='T_IDX';
 
SEGMENT_NAME BLOCKS
------------------- --------------
T_IDX 2048

2048블록으로 블록 수 변함없음

=> freelist 에 등록되어 있던 해당 인덱스 블록이 재사용됨을 확인할 수 있음

 

 

테스트용으로 한번 더 삽입

SQL> insert into t select rownum no from big_table where rownum <= 500000 ;
 
500000 rows created.
 
SQL> commit;
 
Commit complete.

 

 

추가 삽입 후 인덱스 블록 조회

SQL> select segment_name, blocks from dba_segments where segment_name ='T_IDX';
 
SEGMENT_NAME BLOCKS
------------------- --------------
T_IDX 4096

4096블록으로 이후 추가되는 데이터는 확실하게 인덱스 블록수가 늘어남

 

 

결론 :

인덱스 Skew가 발생해도 그 공간은 재사용이 가능함

하지만 공간이 다시 채워지기 전까지는 인덱스 스캔효율이 떨어짐

 

 

참조 : 오라클 성능 고도화 원리와 해법 2 171p

https://cafe.naver.com/dbian/6