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

OS환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.10.0.0

 

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

인덱스 Sparse 란 delete 작업에 의해 인덱스 블록 전반에 걸쳐 밀도(density)가 떨어지는 현상을 말함
이 경우 지워진 자리에 인덱스 정렬 순서에 따라 새로운 값이 입력되면 그 공간은 재사용은 가능하지만 해당 공간이 다시 채워지기 전까지는 인덱스 스캔 효율이 떨어진다는 단점이 있음
왼쪽, 오른쪽, 또는 중간 어느 위치든 Index Skew와 같이 블록이 완전히 비게되면 즉시 freelist로 반환되어 언제든 재사용됨
하지만 Index Sparse는 삭제된 위치에 새로운 값이 입력되지 않으면 영원히 재사용되지 않을 수도 있음
row 수가 일정하더라도 인덱스 공간 사용량이 계속해서 증가하는 현상은 주로 이러한 이유 때문임

 

 

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

 

 

Sparse 테스트
big_table 테이블 생성

1 2 3 4 5 6 7 8 9 10

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

 

 

샘플테이블 생성

1 2 3

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

 

 

샘플인덱스 생성

1 2 3

SQL> create index t_idx on tno pctfree 0;   Index created

 

 

기존인덱스 블록 조회

1 2 3 4 5 6 7 8

SQL>  set lines 200 pages 1000 col segment_name for a20 select segment_name, blocks from dba_segments where segment_name ='T_IDX';   SEGMENT_NAME             BLOCKS -------------------- ---------- T_IDX                      2048

2048 블록임

 

 

데이터 삭제 및 커밋

1 2 3 4 5 6 7

SQL> delete from t where modno 10 < 5;   500000 rows deleted   SQL> commit;   Commit complete

 

 

삭제 후 인덱스 블록 조회

1 2 3 4 5

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

2048 블록임

 

 

지워진 영역에 다시 삽입

1 2 3 4 5 6 7

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

 

 

삽입 후 인덱스 블록 조회

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

SQL> select segment_name, blocks from dba_segments where segment_name ='T_IDX';   SEGMENT_NAME             BLOCKS -------------------- ---------- T_IDX                      3072 3072 블록으로 블록 수 증가함   테스트용으로 한번 더 삽입 SQL> insert into t select rownum no from big_table where rownum <= 500000;   500000 rows created   SQL> commit;   Commit complete

 

 

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

1 2 3 4 5

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

4096 블록으로 증가함

 

 

위 Sparse 테스트시에는 delete 후에 insert 시 재사용을 제대로 못함

 

 

이유는 본문 상단에서 말한것 처럼 Index Sparse는 "삭제된 위치"에 새로운 값이 입력되지 않으면 영원히 재사용되지 않을 수도 있기 때문임
삭제된 위치에 정확하게 값이 들어가야 하는데, 그렇지 않은 경우 새로운 extent를 할당받아 삽입하게 됨

 

 

sparse 테스트 delete 시 t_bak 테이블에 delete 할 데이터를 미리 백업해두고 delete 해봄
기존 테이블 삭제 후 재생성

테이블 삭제
SQL> drop table t purge;
 
Table dropped
 
샘플테이블 생성
SQL> create table t as select rownum no from big_table where rownum <= 1000000;
 
Table created
 
샘플인덱스 생성
SQL> create index t_idx on tno pctfree 0;
 
Index created

 

 

기존인덱스 블록 조회

1 2 3 4 5

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

2048 블록임

 

 

delete 할 데이터 백업

1 2 3

SQL> create table t_bak as select * from t where modno 10 < 5;   Table created

 

 

데이터 삭제 및 커밋

1 2 3 4 5 6 7

SQL> delete from t where modno 10 < 5;   500000 rows deleted   SQL> commit;   Commit complete

 

 

삭제 후 인덱스 블록 조회

1 2 3 4 5

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

2048 블록임

 

 

백업해둔 t2 테이블 데이터를 insert 후 커밋

1 2 3 4 5 6 7

SQL> insert into t select * from t_bak;   500000 rows created   SQL> commit;   Commit complete

 

 

insert 후 인덱스 블록 조회

1 2 3 4 5

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

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

 

 

위 Sparse 테스트시에는 delete 후에 insert 시 삭제된 공간을 재사용함(이전과 다르게 blocks가 늘지않음)

 

 

결론 :
인덱스 Sparse 가 발생해도 그 공간은 재사용이 가능하지만
삭제된 위치에 정확하게 값이 들어가야 한다는 조건이 붙고, 그렇지 않은 경우 새로운 extent를 할당받아 삽입하게 됨
그리고 삭제된 공간이 다시 채워지기 전까지는 인덱스 스캔효율이 떨어짐

 

 

참조 : 오라클 성능 고도화 원리와 해법 2 174p
https://cafe.naver.com/dbian/6
https://positivemh.tistory.com/953

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