OS 환경 : Oracle Linux 8.1 (64bit)
DB 환경 : Oracle Database 19.3.0.0
방법 : 오라클 19c 데이터 1천만건 delete 시간 측정
오라클에서 컬럼이 12개인 테이블에 데이터를 1200만건 넣은 후 1000만건을 delete로 지우는 테스트를 진행해봄
이때 인덱스도 2개 생성하여 지울때 시간이 얼마나 걸리는지 확인해봄
테스트
테이블 생성
|
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
2
3
|
SQL>
create index sample_t_ix01 on sample_t(id1, id2, name);
create index sample_t_ix02 on sample_t(test1, price, qty);
|
데이터 삽입(10만건)
|
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
|
SQL>
DECLARE
TYPE tbl_ins IS TABLE OF sample_t%ROWTYPE INDEX BY BINARY_INTEGER;
w_ins tbl_ins;
BEGIN
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(2010,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;
/
PL/SQL procedure successfully completed.
|
크기 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col segment_name for a15
select segment_name, bytes/1024/1024 mb
from dba_segments
where segment_name like 'SAMPLE_T%';
SEGMENT_NAME MB
--------------- ----------
SAMPLE_T 10
SAMPLE_T_IX01 3
SAMPLE_T_IX02 4
|
테이블은 10mb, 인덱스1은 3, 인덱스2는 4mb임
itas 반복해서 데이터 늘리기
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
SQL>
insert /*+ parallel(s) append */ into sample_t s select /*+ parallel(s) */ * from sample_t s;
commit;
insert /*+ parallel(s) append */ into sample_t s select /*+ parallel(s) */ * from sample_t s;
commit;
insert /*+ parallel(s) append */ into sample_t s select /*+ parallel(s) */ * from sample_t s;
commit;
insert /*+ parallel(s) append */ into sample_t s select /*+ parallel(s) */ * from sample_t s;
commit;
insert /*+ parallel(s) append */ into sample_t s select /*+ parallel(s) */ * from sample_t s;
commit;
insert /*+ parallel(s) append */ into sample_t s select /*+ parallel(s) */ * from sample_t s;
commit;
insert /*+ parallel(s) append */ into sample_t s select /*+ parallel(s) */ * from sample_t s;
commit;
|
크기 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col segment_name for a15
select segment_name, bytes/1024/1024 mb
from dba_segments
where segment_name like 'SAMPLE_T%';
SEGMENT_NAME MB
--------------- ----------
SAMPLE_T 1216
SAMPLE_T_IX01 424
SAMPLE_T_IX02 296
|
테이블은 1216mb임
테이블 logging으로 변경
|
1
2
3
|
SQL> alter table sample_t logging;
Table altered.
|
테이블 카운트 확인
|
1
2
3
4
5
|
SQL> select count(*) from sample_t;
COUNT(*)
----------
12800000
|
로그 스위치 횟수 측정을 위해 수동 로그스위치 5회 및 체크포인트 5회 수행
|
1
2
3
4
|
SQL> @lscp
System altered.
..
|
delete로 1천만건 데이터 삭제(언두가 삭제할 데이터보다 커야함)
|
1
2
3
4
5
6
7
|
SQL>
set timing on
delete sample_t where rownum <= 10000000;
10000000 rows deleted.
Elapsed: 00:09:01.97
|
9분 1초 소요됨
커밋
|
1
2
3
|
SQL> commit;
Commit complete.
|
*참고로 현재 redo는 1gb로 설정되어 있고 작업시 로그스위치는 8회 발생함
용량 확인
|
1
2
3
4
5
|
SQL> select segment_name, bytes/1024/1024 mb from dba_segments where segment_name = 'SAMPLE_T';
SEGMENT_NA MB
---------- ----------
SAMPLE_T 1218
|
move online 진행
|
1
2
3
4
5
|
SQL> alter table sample_t move online;
Table altered.
Elapsed: 00:00:15.15
|
15초 소요됨
크기 확인
|
1
2
3
4
5
6
7
8
9
10
11
12
|
SQL>
set lines 200 pages 1000
col segment_name for a15
select segment_name, bytes/1024/1024 mb
from dba_segments
where segment_name like 'SAMPLE_T%';
SEGMENT_NAME MB
--------------- ----------
SAMPLE_T 256
SAMPLE_T_IX01 96
SAMPLE_T_IX02 72
|
reorg됨
인덱스 정보 확인
|
1
2
3
4
5
6
7
8
9
10
|
SQL>
set lines 200 pages 1000
col index_name for a15
col degree for a10
select index_name, status, degree, logging from dba_indexes where index_name like 'SAMPLE_T_IX%';
INDEX_NAME STATUS DEGREE LOG
--------------- -------- ---------- ---
SAMPLE_T_IX01 VALID 1 YES
SAMPLE_T_IX02 VALID 1 YES
|
모두 valid 정상 상태임
참고로 인덱스를 생성하지 않고 동일 테스트를 진행했을때는 1분 34초 소요되었고
인덱스를 하나만 생성하고 동일 테스트를 진행했을때는 4분 32초 소요되었음
delete 시 index 블록을 처리하는 시간이 빠져서 그런듯함
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
#no index
SQL>
set timing on
delete sample_t where rownum <= 10000000;
10000000 rows deleted.
Elapsed: 00:01:34.78
#one index
SQL>
set timing on
delete sample_t where rownum <= 10000000;
10000000 rows deleted.
Elapsed: 00:04:32.76
|
결론 :
동일한 1천만건으로 테스트를 했지만 인덱스 갯수에 따라 delete 시간이 차이가 났음
이렇게 인덱스가 많아으면 많을수록 delete 시간은 급격하게 늘어남
만약 delete 할 데이터가 더 많은 경우 이렇게 delete를 사용하기보다
테이블 껍데기만 새로 생성해서 남길 테이터만 해당 테이블에 넣고 기존 테이블을 truncate 해준뒤 다시 넣거나
기존 테이블 삭제 및 새로 생성한 테이블을 rename, 인덱스 재생성 해주는 방식으로 하는게 더 빠르게 작업할 수 있는 방법임
참고 : 대량 데이터 delete 작업 시 tip(database.sarang.net) ( https://positivemh.tistory.com/204 )
참조 :
https://positivemh.tistory.com/204
https://positivemh.tistory.com/808
'ORACLE > Admin' 카테고리의 다른 글
| 오라클 19c 로컬 파티션 인덱스의 기본 테이블스페이스 (0) | 2025.06.10 |
|---|---|
| 오라클 19c alter database default tablespace 명령어 (0) | 2025.06.07 |
| 오라클 19c temp 사용시 tempfile 내용 확인 (0) | 2025.05.25 |
| 오라클 19c templates 경로의 seeddata 데이터베이스 (2) | 2025.04.20 |
| 오라클 23ai 신기능 Select AI 사용 테스트(non-adb) (0) | 2025.04.17 |