프린트 하기

OS 환경 : Oracle Linux 7.6 (64bit)

 

DB 환경 : Oracle Database 19.3.0.0

 

방법 : 오라클 19c 인덱스 리빌드 전후 용량 차이 확인 테스트

오라클에서 데이터를 지울때 truncate로 지우지 않고 delete로 지우게 되면 인덱스 구조가 그대로 남아 있어 용량을 차지하고 있음

 

delete 작업에 의해 인덱스 블록 전반에 걸쳐 밀도(density)가 떨어지는 현상을 인덱스 Sparse 라고 부르는데
이 경우 지워진 자리에 인덱스 정렬 순서에 따라 새로운 값이 입력되면 그 공간은 재사용은 가능하지만 해당 공간이 다시 채워지기 전까지는 인덱스 스캔 효율이 떨어진다는 단점이 있음

 

그리고 delete 작업에 의해 인덱스 엔트리가 왼쪽 또는 오른쪽에 치우치는 현상을 인덱스 Skew 라고 부름
이 경우 재사용은 가능하지만 해당 공간이 다시 채워지기 전까지는 인덱스 스캔 효율이 떨어진다는 단점이 있음
참고(인덱스 Skew, 인덱스 Sparse)
오라클 19c 인덱스 Skew 발생시 공간 재사용 확인 ( https://positivemh.tistory.com/953 )
오라클 19c 인덱스 Sparse 발생시 공간 재사용 확인 ( https://positivemh.tistory.com/956 )

 

 

본문에서는 1,000만건 데이터를 삽입 한 뒤 80% 인 800만건을 delete로 지우고
인덱스 용량을 확인한 뒤 리빌드 후 다시 용량을 확인하는 테스트를 진행함

 

 

테스트
샘플테이블 생성

1
2
3
4
5
6
7
8
9
SQL>
drop table sample_t purge;
create table sample_t 
(id1 number, id2 number, name varchar2(10),  
date1 varchar2(8), date2 varchar2(8), date3 date, 
phone varchar2(13), price number, qty number, 
test1 number,  test2 varchar2(5), test3 varchar2(4)
)
nologging;

 

 

대량 데이터 삽입(1,000만건)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
FOR d IN 1..100 LOOP
FOR i IN 1..100000 LOOP 
   w_ins(i).id1   := i;
   w_ins(i).id2   := i||ceil(dbms_random.value(1, 10000000));
   w_ins(i).name  := dbms_random.string('x',5);
   w_ins(i).date1 := round(dbms_random.value(2010,2021))||to_char(round(dbms_random.value(1,12)), 'FM09')||to_char( round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).date2 := '2021'||to_char(round(dbms_random.value(1,12)) , 'FM09')||to_char(round(dbms_random.value(1,28)), 'FM09');
   w_ins(i).date3 := to_date(round(dbms_random.value(2019,2021))||'-'||round(dbms_random.value(1,12))||'-'||round(dbms_random.value(1,28))||' '||round(dbms_random.value(1, 23))||':'||round(dbms_random.value(0, 59))||':'||round(dbms_random.value(0, 59)), 'YYYY-MM-DD HH24:MI:SS');
   w_ins(i).phone := '010-'||ceil(dbms_random.value(1000, 9999))||'-'||ceil(dbms_random.value(1000, 9999));
   w_ins(i).price := ceil(dbms_random.value(1, 10))*1000;
   w_ins(i).qty   := ceil(dbms_random.value(1, 10));
   w_ins(i).test1 := 1234;
   w_ins(i).test2 := 'SQLP';
   w_ins(i).test3 := 'A'||ceil(dbms_random.value(100, 999));
END LOOP;
FORALL i in 1..100000 INSERT INTO sample_t VALUES w_ins(i);
   COMMIT;
END LOOP;
END;
/
 
PL/SQL procedure successfully completed.

 

 

샘플 인덱스 생성

1
2
3
4
SQL> 
create index smp_t_ix1 on sample_t(id1, id2, name, date1, price, phone);
 
Index created.

 

 

기존 인덱스 크기, 블록 조회

1
2
3
4
5
6
7
8
9
10
SQL> 
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, bytes/1024/1024 mb 
from dba_segments 
where segment_name = 'SMP_T_IX1';
 
SEGMENT_NAME             BLOCKS         MB
-------------------- ---------- ----------
SMP_T_IX1                 78848        616

78848 블록에 616MB를 사용중임

 

 

테이블 row수 확인

1
2
3
4
5
SQL> select count(*) from sample_t;
 
  COUNT(*)
----------
  10000000

1000만 row가 존재함

 

 

데이터 삭제 및 커밋

1
2
3
4
5
6
7
SQL> delete from sample_t where rownum <= 8000000
 
8000000 rows deleted.
 
SQL> commit;
 
Commit complete

 

 

삭제 후 기존 인덱스 크기, 블록 재조회

1
2
3
4
5
6
7
8
9
10
11
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, bytes/1024/1024 mb
, (select to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') from dba_indexes where index_name = a.segment_name) last_analyzed
from dba_segments a
where segment_name = 'SMP_T_IX1';
 
SEGMENT_NAME             BLOCKS         MB LAST_ANALYZED
-------------------- ---------- ---------- -------------------
SMP_T_IX1                 78848        616 2024/06/16 13:29:20

여전히 78848 블록에 616MB를 사용중임

 

 

통계를 수집해도 데이터는 동일함

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> exec dbms_stats.gather_table_stats('imsi', 'sample_t', cascade=>true);
 
PL/SQL procedure successfully completed.
 
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, bytes/1024/1024 mb
, (select to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') from dba_indexes where index_name = a.segment_name) last_analyzed
from dba_segments a
where segment_name = 'SMP_T_IX1';
 
SEGMENT_NAME             BLOCKS         MB LAST_ANALYZED
-------------------- ---------- ---------- -------------------
SMP_T_IX1                 78848        616 2024/06/16 13:32:21

데이터를 80%나 지웠지만 인덱스의 크기는 줄어들지 않은 상태

 

 

인덱스 리빌드 진행

1
2
3
SQL> alter index smp_t_ix1 rebuild online;
 
Index altered.

리빌드가 완료됨

 

 

리빌드 후 인덱스 블록 조회

1
2
3
4
5
6
7
8
9
10
11
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, bytes/1024/1024 mb
, (select to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') from dba_indexes where index_name = a.segment_name) last_analyzed
from dba_segments a
where segment_name = 'SMP_T_IX1';
 
SEGMENT_NAME             BLOCKS         MB LAST_ANALYZED
-------------------- ---------- ---------- -------------------
SMP_T_IX1                 16768        131 2024/06/16 13:35:51

리빌드 후 인덱스 블록 및 용량이 줄어듬

 

 

인덱스 리빌드 전후 비교

인덱스 리빌드 후 인덱스 크기 감소 (616MB -> 131MB)

 

 

참고용
테이블 블록 조회

1
2
3
4
5
6
7
8
9
10
11
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, bytes/1024/1024 mb
, (select to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') from dba_tables where table_name = a.segment_name) last_analyzed
from dba_segments a
where segment_name = 'SAMPLE_T';
 
SEGMENT_NAME             BLOCKS         MB LAST_ANALYZED
-------------------- ---------- ---------- -------------------
SAMPLE_T                 116736        912 2024/06/16 13:35:51

블록은 약 11만개, 용량은 912MB임

 

 

테이블 move 진행(동일 ts에 move함)

1
2
3
SQL> alter table sample_t move tablespace imsits online;
 
Table altered.

 

 

move 후 테이블 블록 조회

1
2
3
4
5
6
7
8
9
10
11
SQL>
set lines 200 pages 1000
col segment_name for a20
select segment_name, blocks, bytes/1024/1024 mb
, (select to_char(last_analyzed, 'yyyy/mm/dd hh24:mi:ss') from dba_tables where table_name = a.segment_name) last_analyzed
from dba_segments a
where segment_name = 'SAMPLE_T';
 
SEGMENT_NAME             BLOCKS         MB LAST_ANALYZED
-------------------- ---------- ---------- -------------------
SAMPLE_T                  23552        184 2024/06/16 13:40:08

블록과 용량이 모두 줄어듬

 

 

테이블 move 전후 비교

*참고로 truncate로 테이블 데이터를 지우면 인덱스도 같이 정리됨

 

 

결론 :
데이터를 delete로 삭제 시 그 공간이 재사용 될수도 있지만
재사용 되기까지 시간이 오래 걸리는 경우 그동안 공간이 오랫동안 낭비되고 스캔 효율이 떨어질 수 있음
이런 경우 인덱스 리빌드를 통해 성능을 향상시키고 공간을 회수할 수 있음

 

 

참조 : 

https://positivemh.tistory.com/953
https://positivemh.tistory.com/956

저작자표시

'ORACLE > Admin' 카테고리의 다른 글

오라클 23ai 10046 트레이스 이전 버전과 차이점 비교  (0) 2024.06.20
오라클 23ai 신기능 Table Value Constructor(TVC)  (0) 2024.06.18
오라클 23ai 신기능 UMM(Unified Memory Management) 통합메모리 관리  (0) 2024.06.14
오라클 19c 테이블 initrans 값에 따른 lock 테스트  (0) 2024.06.13
오라클 23ai 신기능 일반 에러 메세지 개선  (0) 2024.06.02

티스토리툴바