Oracle 파티션 테이블과 파티션 인덱스

Oracle Partition

출처 : Oracle

(1)  파티셔닝 : 테이블 또는 인덱스 데이터를 특정 컬럼에 따라 별도 세그먼트에 나눠서 저장

1. 관리적 측면 장점 : 파티션 단위 백업,추가,삭제,변경 -> 가용성 향상

2. 성능적 측면 장점 : 파티션 단위 조회 및 DML 경합 또는 부하 분산

 

*  파티션 Pruning 

SQL 하드파싱이나 실행시점에 조건절을 분석하여 불필요한 부분의 제거

SELECT *
FROM INFO_TABLE
WHERE TIME >'20210101'
AND TIME < '20220101'

위의 SQL INFO_TABLE이 전체 3000만건 중 해당 기간동안의 데이터가 1000만건이 조회된다고 하면 인덱스로 랜덤엑세스하는것 보다 테이블엑세스의 성능이 나을 수 있다.

그러나 위의 Partitioned 테이블처럼 100만건 단위로 나눠서 저장한다면 해당 파티션 세그먼트만 읽고 멈출수 있어서 Full Scan을 하더라도 성능을 크게 향상 시킬 수 있다.

 

(2) 인덱스 파티션

1) Local index partition

출처 : Oracle

파티셔닝된 테이블마다 1:1 대응관계를 가지는 인덱스 파티션을 가진다.

로컬 인덱스 파티션을 생성할 경우 파티션 테이블과 1:1 관계를 유지하도록 자동으로 관리해준다

파티션구성을 변경(add,drop,exchange) 하더라도 인덱스를 재생성할 필요가없다.

변경작업이 빠르게 끝나므로 Peak시간대만 피하면 서비스를 중단하지 않고 작업을 진행할 수 있다.

 

2)Global index partition

출처 : Oracle

파티션을 테이블과 다르게 구성한 인덱스

파티션 유형이 다르거나, 파티션 키가 다르거나, 파티션 기준값 정의가 다른경우

비파티션 테이블에 경우에도 인데스는 파티션 인덱스로 적용 가능하다.

파티션 구성을 변경(drop,exchange,split) 하는 순간 Unusable상태로 바뀌므로 인덱스를 재생성해 줘야 한다.

그동안 해당 테이블을 사용하는 서비스는 중단된다.

 * 테이블과 인덱스가 정확하게 1:1 관계가 되도록 인덱스를 구성해준다고 해도 Local 인덱스가 되지 않는다.

 * 오라클이 자동으로 관리해주지 않으므로 구성요소에 상관없이 글로벌 파티션에 속하게 된다.

 * 일반적인 비파티션 인덱스와는 다르게 인덱스의 물리구조는 파티셔닝 되어있다.

 - 파티션테이블에도 비파티션 인덱스가 적용 가능하다.

 

3) Prefixed vs Nonprefixed

 - prefiexed : 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 왼쪽 선두에 위치한다.

 - nonprefixed: 인덱스 파티션 키 컬럼이 인덱스 키 컬럼 외쪽 선두에 위치 하지 않는다. 파티션 키가 인게스 컬럼에 아예 속하지 않을 때도 여기에 속한다.

 

* 글로벌 파티션 인덱스는 nonprefixed가 불가능하다, 즉 인덱스의 첫번째 컬럼은 파티션 키여야 한다.

 

 

(3) 중요한 인덱스 파티션 제약

 Unique 인덱스를 로컬 파티셔닝하려면 파티션 키가 모두 인덱스 구성 컬럼이어야 한다.

Ex) 파티션이 Time 컬럼으로 파티셔닝된 INFO_TABLE (Time, Value1, Value2)

만약 Value1 단일 컬럼으로 PK(로컬 인덱스)를 생성할 경우 (실제로는 제약 때문에 생성 불가)

SELECT *
FROM INFO_TABLE
WHERE VALUE1 = 'A'

위의 쿼리를 수행하려고 할때  VALUE1 = 'A' 인 레코드는 어느 파티션에서든 생성이 가능하기 때문에 모든 파티션을 조회해야한다.

 

INSERT INTO INFO_TABLE(VALUE1) 
VALUES ('A')

또한 데이터 입력시에도 Unique 제약 조건은 만족하기 위해서 해당 데이터가 입력시에 입력되는 파티션을 제외하고 모든 파티션에 Lock을 수행하는 메커니즘이 추가적으로 필요하다.

 

* 해당 제약으로 인해 로컬 파티셔닝 하지 못하는 경우 Drop,Truncate,Merge등 파티션 구조 변경 작업이 쉽지 않다.

* IML관리체계를 효과적으로 운영하려면 가급적 인덱스를 로컬 파티션으로 구성해야 한다.