[Oracle] range 파티션 split 작업 체크리스트

1. 파티션 테이블 구성하는 경우, 스크립트 예시

보관주기에 따라 Truncate하는 테이블,

또는 데이터의 규모가 커서 기간별로 관리하는 테이블의 경우.

 

Range 파티션을 구성하면 효과적으로 데이터를 관리할 수 있다.

 

고객 소비내역 테이블을 TABLE001 이라고 가정한다면,  

아래 그림처럼 결제시각에 따라 월 RANGE 파티션으로 구성할 수 있다.

 

각각의 파티션은 하나의 테이블과 물리적으로 동일하기 때문에

대량 배치작업시 enq tx - row lock contention을 방지할 수도 있고 

DML 성능을 향상시킬수도 있다.

 

* CREATE TABLE SCRIPT 예시

CREATE TABLE TABLE001
(
	CST_ID    NUMBER    NOT NULL,
	PMT_TIME  TIMESTAMP NOT NULL,
	PMT_NO    NUMBER    NOT NULL,
	PMT_AMT   NUMBER    NOT NULL,
	COLUMN_1 VARCHAR2(4),
	COLUMN_2 VARCHAR2(2),
	COLUMN_3 VARCHAR2(2),
	COLUMN_4 NUMBER
)
TABLESPACE TABLESPACE_NAME
STORAGE
(
	INITIAL 64K
	NEXT 1M
)
PARTITION BY RANGE ( PMT_TIME )
(
	PARTITION PT_R202201 VALUES LESS THAN (TIMESTAMP'2022-02-01 00:00:00 +0:00'),
	PARTITION PT_R202202 VALUES LESS THAN (TIMESTAMP'2022-03-01 00:00:00 +0:00'),
	PARTITION PT_R202203 VALUES LESS THAN (TIMESTAMP'2022-04-01 00:00:00 +0:00'),
	PARTITION PT_R202204 VALUES LESS THAN (TIMESTAMP'2022-05-01 00:00:00 +0:00'),
	PARTITION PT_MAX	   VALUES LESS THAN (MAXVALUE)
	TABLESPACE TABLESPACE_NAME
	STORAGE
	(
		INITIAL 64K
		NEXT 1M
	)
);

 

2. 파티션 RANGE를 추가하는 경우, 스크립트 예시

위 스크립트 예시처럼 range 파티션으로 구성할때 파티션을

Procedure나 Job을 통해 구성하거나 아니면 SCRIPT를 직접 작성 후 수행할 수 있다.

-- MAX 파티션을 2023년 1월 파티션, MAX 파티션으로 SPLIT
ALTER TABLE OWNER.TABLE_NAME SPLIT PARTITION PT_MAX AT (TIMESTAMP'2023-02-01 00:00:00') INTO (PARTITION PT_R202301 STORAGE (INITIAL 64K NEXT 1M), PARTITION PT_MAX);

-- 통계정보 복사
EXEC DBMS_STATS.COPY_TABLE_STATS( OWNNAME => 'OWNER', TABNAME => 'TABLE_NAME', SRCPARTNAME => 'PT_R202201', DSTPARTNAME => 'PT_R202301');
EXEC DBMS_STATS.COPY_TABLE_STATS( OWNNAME => 'OWNER', TABNAME => 'TABLE_NAME', SRCPARTNAME => 'PT_R202201', DSTPARTNAME => 'PT_MAX');

 

3. 파티션 RANGE SPLIT 작업 전/후 체크 리스트

1) 작업 전 CHECK

   - 테이블스페이스 용량이 충분한지

      ( 일 파티션 또는 복합 파티션의 경우 INITIAL EXTENT의 합의 크기가 상당할 수 있으니 미리 체크해야한다 )

   - MAX 파티션에 데이터가 들어있는지 

      ( MAX 파티션에 데이터가 있는 경우 SPLIT하면 INDEX가 UNUSABLE에 빠져 장애를 유발할 수 있다 )

        → INDEX UNUSABLE에 빠지면 PARTITION 단위로  REBUILD해서 USABLE 상태로 만들어야한다.

 

  2) 작업 후 CHECK

    - UNUSABLE에 빠진 인덱스는 없는지 (있는 경우 바로 REBUILD)

    - NOLOGGING인 인덱스는 없는지

    - 통계정보 누락 파티션은 없는지

-- 1) 파티션 테이블 UNUSABLE, NOLOGGING 조치
SELECT 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' REBUILD PARTITION '||PARTITION_NAME||' NOLOGGING ONLINE;'
FROM   DBA_IND_PARTITIONS
WHERE  INDEX_OWNER = :owner
       AND STATUS != 'USABLE'
       AND COMPOSITE != 'YES';

SELECT 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' LOGGING;'
FROM   DBA_IND_PARTITIONS
WHERE  INDEX_OWNER = :owner
       AND LOGGING != 'YES'
       AND COMPOSITE != 'YES'       
GROUP BY INDEX_OWNER, INDEX_NAME;

-- 2) 서브파티션 테이블 UNUSABLE, NOLOGGING 조치
SELECT 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' REBUILD PARTITION '||PARTITION_NAME||' NOLOGGING ONLINE;'
FROM   DBA_IND_SUBPARTITIONS
WHERE  INDEX_OWNER = :owner
       AND STATUS != 'USABLE';

SELECT 'ALTER INDEX '||INDEX_OWNER||'.'||INDEX_NAME||' LOGGING;'
FROM   DBA_IND_PARTITIONS
WHERE  INDEX_OWNER = :owner
       AND LOGGING != 'YES'      
GROUP BY INDEX_OWNER, INDEX_NAME;