파티션 테이블 TRUNCATE 성능 개선

다수의 파티션으로 구성된 테이블을 TRUNCATE하면 시간이 오래 걸릴 수 있습니다. 참고로 TRUNCATE 문은 아래의 세 가지 방식으로 수행할 수 있습니다. 기본값은 DROP STORAGE입니다. 기본값 대신 DROP ALL STORAGE 방식을 사용하면 TRUNCATE 문의 수행 시간을 단축시킬 수 있습니다.

DROP STORAGE: MINEXTENTS 파라미터에 의해 할당된 익스텐트를 제외한 모든 익스텐트를 해제
DROP ALL STORAGE: 모든 익스텐트를 해제
REUSE STORAGE: 익스텐트를 해제하지 않음

TRUNCATE TABLE [schema.]table [{ DROP [ALL] | REUSE } STORAGE] [CASCADE];

테스트를 위해 _partition_large_extents 파라미터를 FALSE로 설정하고, 512개의 파티션을 가진 t1, t2, t3, t4 테이블을 생성하겠습니다.

-- 1-1
ALTER SESSION SET "_partition_large_extents" = FALSE;

-- 1-2
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;
DROP TABLE t3 PURGE;
DROP TABLE t4 PURGE;

CREATE TABLE t1 (c1, c2) PARTITION BY HASH (c1) PARTITIONS 512 AS SELECT ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000000');
CREATE TABLE t2 (c1, c2) PARTITION BY HASH (c1) PARTITIONS 512 AS SELECT ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000000');
CREATE TABLE t3 (c1, c2) PARTITION BY HASH (c1) PARTITIONS 512 AS SELECT ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000000');
CREATE TABLE t4 (c1, c2) PARTITION BY HASH (c1) PARTITIONS 512 AS SELECT ROWNUM, LPAD ('X', 100, 'X') FROM XMLTABLE ('1 to 10000000');

t1, t2, t3, t4 테이블의 크기는 동일합니다.

-- 2
SELECT   segment_name
       , SUM (bytes) AS bytes
       , SUM (blocks) AS blocks
       , SUM (extents) AS extents
       , COUNT (*) AS segments
    FROM user_segments
   WHERE segment_name IN ('T1', 'T2', 'T3', 'T4')
GROUP BY segment_name;

SEGMENT_NAME      BYTES BLOCKS EXTENTS SEGMENTS
------------ ---------- ------ ------- --------
T1           1610612736 196608    9216      512
T3           1610612736 196608    9216      512
T2           1610612736 196608    9216      512
T4           1610612736 196608    9216      512

4 행이 선택되었습니다.

TRUNCATE를 수행하는데 3-1번 DROP STORAGE 방식은 2.22초, 3-2번 REUSE STORAGE 방식은 2.94초, 3-3번 DROP ALL STORAGE 방식은 0.93초가 소요됩니다. 3-4번은 익스텐트를 DROP STORAGE 방식과 동일하게 할당하기 위해 ALTER TABLE 문을 2회 수행합니다. 3-4번의 소요 시간 합계는 1.42초로 DROP STORAGE 방식보다 36% 더 짧습니다.

-- 3-1
TRUNCATE TABLE t1;
-- TRUNCATE TABLE t1 DROP STORAGE;

경   과: 00:00:02.22

-- 3-2
TRUNCATE TABLE t2 REUSE STORAGE;

경   과: 00:00:02.94

-- 3-3
TRUNCATE TABLE t3 DROP ALL STORAGE;

경   과: 00:00:00.93

-- 3-4
TRUNCATE TABLE t4 DROP ALL STORAGE;

경   과: 00:00:00.94

ALTER TABLE t4 ALLOCATE EXTENT;

경   과: 00:00:00.32

ALTER TABLE t4 DEALLOCATE UNUSED;

경   과: 00:00:00.16

아래 쿼리의 결과에서 t1, t4 테이블의 크기가 동일한 것을 확인할 수 있습니다. t2 테이블은 익스텐트가 해제되지 않았고, t3 테이블은 모든 익스텐트가 해제되었습니다. t3 테이블은 초기 익스텐트를 할당할 때 경합이 발생할 수 있습니다.

-- 4
SELECT   segment_name
       , SUM (bytes) AS bytes
       , SUM (blocks) AS blocks
       , SUM (extents) AS extents
       , COUNT (*) AS segments
    FROM user_segments
   WHERE segment_name IN ('T1', 'T2', 'T3', 'T4')
GROUP BY segment_name;

SEGMENT_NAME      BYTES BLOCKS EXTENTS SEGMENTS
------------ ---------- ------ ------- --------
T1             33554432   4096     512      512
T2           1610612736 196608    9216      512
T4             33554432   4096     512      512

3 행이 선택되었습니다.

티스토리툴바