파티션 활용

파티션 개요

파티셔닝(Partitioning)은 테이블 또는 인덱스 데이터를 파티션(Partition) 단위로 나누어 저장하는 것을 말한다. 테이블을 파티셔닝하면 파티션 키에 따라 물리적으로는 별도의 세그먼트에 데이터를 저장하며, 인덱스도 마찬가지다.

• 관리적 측면 : 파티션 단위 백업, 추가, 삭제, 변경
• 성능적 측면 : 파티션 단위 조회 및 DML 수행, 경합 및 부하 분산

인덱스를 이용한 Random 액세스 방식은 일정량을 넘는 순간 Full Table Scan보다 오히려 성능이 나쁘다. 그렇다고 초대용량 테이블을 Full Scan 하는 것은 매우 비효율적이다. 이런 경우 테이블을 파티션 단위로 나누어 관리하면, Full Table Scan이라 하더라도 일부 세그먼트만 읽고 작업을 마칠 수 있다.

파티션되지 않은 일반 테이블일 때는 테이블과 저장영역(Oracle의 세그먼트)이 1:1 관계지만 파티션 테이블일 때는 1:M 관계다. 인덱스를 파티셔닝할 때도 마찬가지다.

파티션 유형

Range 파티셔닝

파티션 키 값의 범위로 분할하며 기간별로 입력되는 데이터를 분할할 때 사용할 수 있다.

CREATE TABLE T1 (c1 NUMBER, c2 DATE)
PARTITION BY RANGE (c2) (
    PARTITION p1 VALUES LESS THAN (DATE '2050-02-01'),
    PARTITION p2 VALUES LESS THAN (DATE '2050-03-01'));

이런 식으로 테이블을 생성하면서 RANGE 파티션 테이블을 생성한다.

INSERT INTO t1 VALUES (1, DATE '2050-03-01');

이제 테이블에 데이터를 넣어보자. 하지만 이 쿼리는 에러가 발생한다. 데이터를 삽입할 파티션이 존재하지 않기 때문이다.
(참고로 파티션 테이블 생성 시 LESS THAN 사용하면 그 값은 포함하지 않겠다는 뜻이다.)

위 쿼리에 에러가 발생하지 않도록 하기 위해서는...

CREATE TABLE T1 (c1 NUMBER, c2 DATE)
PARTITION BY RANGE (c2) (
    PARTITION p1 VALUES LESS THAN (DATE '2050-02-01'),
    PARTITION p2 VALUES LESS THAN (DATE '2050-03-01'),
    PARTITION p3 VALEUS LESS THAN (MAXVALUE));

MAXVALUE는 직전 파티션의 literal보다 큰 최고 값을 의미한다. 마지막 파티션(p3)의 값을 MAXVALUE로 지정하면 된다.

List 파티셔닝

불연속적인 값의 목록을 각 파티션에 지정하며 순서와 상관없이 사용자가 미리 정한 그룹핑 기준에 따라 데이터를 분할 저장한다.

CREATE TABLE t1 (c1 NUMBER, c2 NUMBER)
PARTITION BY LIST (c2) (
	PARTITION p1 VALUES (1, 2),
    PARTITION p2 VALUES (3, 4),
    PARTITION p3 VALUES (DEFAULT)
);

DEFAULT를 쓰면 더이상 파티션을 추가할 수 없다.

Hash 파티셔닝

파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션을 매핑한다. 데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리하기 때문에 각 로우의 저장 위치를 예측할 수 없다. 또한, OLTP 시스템의 블록 경합을 해소하기 위해 사용할 수 있다.

CREATE TABLE t1 (c1 NUMBER, c2 NUMBER) PARTITION BY HASH (c1) PARTITIONS 3;

파티션 명을 지정해서 HASH 파티션 테이블을 생성할 수 있지만 HASH 파티션은 다수의 파티션을 생성하기 때문에 파티션 명을 지정하지 않는 것이 일반적이다.

Composite 파티셔닝

Range나 List 파티션 내에 또 다른 서브 파티션(Range, Hash, List) 구성
Range나 List 파티션이 갖는 이점 + 각 서브 파티션 구성의 이점

파티션 Pruning

파티션 Pruning은 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외시키는 기능이다. 파티션 테이블에 조회나 DML을 수행할 때 극적인 성능 개선을 가져다 주는 핵심 원리가 바로 파티션 Pruning에 있다. 기본 파티션 Pruning에는 정적 Pruning과 동적 Pruning이 있고, DBMS별로 서브쿼리 Pruning, 조인 필터 Pruning 같은 고급 Pruning 기법을 사용한다.

정적(Static) 파티션 Pruning

액세스할 파티션을 컴파일 시점(Compile-Time)에 미리 결정하며, 파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동한다.

동적(Dynamic) 파티션 Pruning

액세스할 파티션을 실행 시점(Run-Time)에 결정하며, 파티션 키 컬럼을 바인드 변수로 조회하는 경우가 대표적이다. NL Join할 때도 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 되어있으면 동적 Pruning이 작동한다.

파티션 Pruning은 SQL에 사용된 조건절과 파티션 구성을 분석해 DBMS가 지능적으로 수행하는 메커니즘이므로 사용자가 굳이 신경쓰지 않아도 된다. 다만, 파티션 키 컬럼에 대한 가공이 발생하지 않도록 주의해야 한다. 사용자가 명시적으로 파티션 키 컬럼을 가공했을 때는 물론, 데이터 타입이 묵시적으로 변환될 때도 정상적인 Pruning이 불가능해지기 때문이다.
(인덱스 컬럼을 조건절에서 가공하면 해당 인덱스를 사용할 수 없게 되는 것과 같은 이치다.)

인덱스 파티셔닝

Local 파티션 인덱스 vs Global 파티션 인덱스

Local 파티션 인덱스 : 테이블 파티션과 1:1로 대응되도록 파티셔닝한 인덱스. 인덱스 파티션 키를 사용자가 따로 지정하지 않으며, 테이블과 1:1 관계를 유지하도록 DBMS가 자동으로 관리해준다.
(SQL Server에서는 '정렬된(aligned) 파티션 인덱스'라고 부른다.)

Global 파티션 인덱스 : 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝한 인덱스.
(SQL Server에서는 '정렬되지 않은(un-aligned) 파티션 인덱스'라고 부른다.)

Prefixed 인덱스 vs NonPrefixed 파티션 인덱스

Prefixed : 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두는 것을 말한다.
NonPrefixed : 파티션 인덱스를 생성할 때, 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두지 않는 것을 말한다. 파티션 키가 인덱스 컬럼에 아예 속하지 않을 때도 여기에 속한다.

인덱스 유형

• Local Prefixed 파티션 인덱스
• Local NonPrefixed 파티션 인덱스
• Global Prefixed 파티션 인덱스
• Global NonPrefixed 파티션 인덱스
• 비파티션 인덱스

Oracle은 이 중에서 Global NonPrefixed 파티션 인덱스를 허용하지 않는다.

인덱스 파티셔닝 가이드

인덱스 파티션은 파티션 테이블과 마찬가지로 성능, 관리 편의성, 가용성, 확장성 등을 제공한다. 테이블에 종속적인 Local 파티션, 테이블과 독립적인 Global 파티션 모두 가능하지만, 관리적인 측면에서는 Local 인덱스가 훨씬 유용하다. 테이블 파티션에 대한 Drop, Exchange, Split 등의 작업 시 Global 인덱스는 Unusable 상태가 되기 때문이다.

인덱스 파티셔닝 적용 가이드

1) 비파티션

• OLTP 환경에서 성능에 미치는 영향이 크므로 비파티셔닝 전략이 유용할 수 있다.
• NL Join에서 파티션 키에 대한 넓은 범위검색 조건을 가지고 Inner 테이블 액세스 시 비파티션 인덱스 사용을 고려한다.
• 소트 연산을 대체함으로써 부분범위 처리를 활용하고자 할 때는 비파티셔닝 전략이 유용하다.
• 테이블 파티션 이동, 삭제 등의 작업 시 unusable 되므로 적용 시 주의

2) Global Prefixed

• 인덱스 경합 분산에 효과적이다.
• 여러 Local 인덱스 파티션을 액세스하는 것이 비효율적일 때 대안으로 활용 가능
• 테이블 파티션 이동, 삭제 등의 작업 시 unusable 되므로 적용 시 주의

3) Local Prefixed

• 테이블 파티션에 대한 추가, 삭제 등의 작업이 빈번할 때 관리적 측면에서 유용함.
• 이력성 데이터를 주로 관리하는 DB 환경에 효과적
• 파티션 키 컬럼이 '=' 조건으로 사용될 때 유용함.
• 파티셔닝 컬럼에 대한 검색 조건이 없으면 인덱스 선두 컬럼이 조건절에 누락된 것이므로 정상적인 사용이 불가(Index Full Scan으로는 선택 가능)
• 파티션 키 컬럼(=인덱스 선두 컬럼)이 Like, Between, 부등호 같은 범위검색 조건일 때 불리함.

4) Local Non Prefixed

• 테이블 파티션에 대한 추가, 삭제 등의 작업이 빈번할 때 관리적 측면에서 유용함.
• 이력성 데이터를 주로 관리하는 DB 환경에 효과적
• 파티켠 키 컬럼이 조건절에 사용될 때 유용함.
• 파티셔닝 컬럼에 대한 검색 조건이 없으면 인덱스 파티션 전체를 액세스하는 비효율이 발생할 수 있음.
• 파티션 키 컬럼이 범위검색 조건으로 자주 사용된다면 Local Prefixed보다 Local Non Prefixed가 유리하지만 그렇더라도 좁은 범위검색이어야 함.