SQL튜닝 - 파티셔닝

파티셔닝은 테이블 또은 인덱스 데이터를 파티션 단위로 나눠 저장하는 것을 말합니다. 파티션 키에 따라 물리적으로 별도의 세그먼트에 데이터혹은 인덱스를 저장합니다. 파티셔닝이 필요한 관리적 성능적 이유는 다음과 같습니다.

 

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

 

파티셔닝의 관리적 측면에서 이점이 있습니다. 보관주기가 지난 데이터를 별도 장치에 백업하고 지우는 작업 수행시, 파티션 없이 대용량 테이블에 수행하려면 시비효율적입니다. 대용량 테이블에 인덱스를 새로 생성하거나 재생성할 때도 파티션 기능을 이용하면 효과적입니다.

 

성능적 측면에서 데이터를 빠르게 검색할 목적으로 테이블을 파티션 단위로 나누어 관리하면 FULL TABLE SCAN이라 하더라도 일부 세그먼트만 읽고 작업을 마칠 수 있습니다.

 

테이블과 인덱스를 파티셔닝하면 DBMS는 내부에 2개 이상의 저장영역을 생성하고, 논리적으로 하나의 오브젝트임을 메타정보로 관리합니다. 파티션되지 않은 일반 테이블일때 테이블과 저장영역이 1:1 관계지만 파티션한 테이블 일때는 1:M 관계가 됩니다. 인덱스도 마찬가지 입니다.

 

파티션 유형

 

RANGE PATITIONING

 

- 파티션 키 값의 범위로 분할
- 파티션의 가장 일반적인 형태이며, 주로 날짜 칼럼을 기준으로 합니다.

 

create table order (orderNumber number, orderDate, varchar2(8), custId varchar2(5), )
partition by range(주문일자) (
  partition p2009_q1 value less then ('20090401')
  ,partition p2009_q2 value less then ('20090701')
  ,partition p2009_q3 value less then ('20091001')
  ,partition p2009_q4 value less then ('20100101')
  ,partition p2009_q1 value less then ('20100401')
  ,partition p9999_mx value less then (MAXVALUE) -> 주문일자 >= '20100401'
)

 

HASH 파티셔닝

 

- 파티션 키 값에 해시함수를 적용하고, 반환된 값으로 파티션을 매칭합니다.
- 데이터가 고르게 분산되도록 DBMS가 관리하기 때문에 각 로우의 저장 위치를 알 수 없습니다.
- 파티션 키의 분포가 고른 칼럼일때 가장 효과적입니다.
- 병렬처리 시 성능이 극대화 됩니다.
- DML 경합 분산에 효과적입니다.

 

LIST 파티셔닝

 

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

 

COMPOSITE 파티셔닝

 

- RANGE 파티션이나 LIST 파티션 내에 또 다른 서브 파티션을 구성합니다.
- RANGE 파티션과 LIST 파티션이 갖는 이점과 각 서브 파티션 구성의 이점을 동시에 누릴 수 있습니다.

 

RANGE + HASH 방식으로 파티셔닝 하는 예시입니다.

 

create table order (orderNumber number, orderDate, varchar2(8), custId varchar2(5), )
partition by range(주문일자)
subpartition by hash(고객id) suspartitions 8
(
  partition p2009_q1 value less then ('20090401')
  ,partition p2009_q2 value less then ('20090701')
  ,partition p2009_q3 value less then ('20091001')
  ,partition p2009_q4 value less then ('20100101')
  ,partition p2009_q1 value less then ('20100401')
  ,partition p9999_mx value less then (MAXVALUE) -> 주문일자 >= '20100401'
)

 

파티션 PRUNING

 

파티션 PRUNING은 옵티마이저가 SQL의 대상 테이블과 조건절을 분석해 불필요한 파티션을 엑세스 대상에서 제외하는 기능입니다.
이를 통해 엑세스 조건과 관련된 파티션만 작업을 수행할 수 있습니다. 파티션 테이블에 조회나 DML을 수행할때 PRUNING을 통해 성능을 개선할 수 있습니다.

 

기본 파티션 PRUNING에는 정적 PRUNING과 동적 PRUNING이 있습니다. DBMS 별로 서브쿼리 PRUNING, 조인필터 PRUNING 같은 고급기법을 지원합니다.

 

정적 파티션 PRUNING

 

엑세스할 파티션을 컴파일 시점에 미리 결정하며, 파티션 키 칼럼을 상수 조건으로 조회할때 동작합니다.

 

동적 파티션 PRUNING

 

액세스할 파티션을 실행 시점에 결정하며, 파티션 키 칼럼을 바인드 변수로 조회할때 동작합니다. NL 조인시, INNER 테이블이 조인 칼럼 기준으로 파티션닝돼 있으면 동적 PRUNING이 작동합니다.

 

파티션 PRUNING은 쿼리 조건절과 구성을 분석해 DBMS가 지능적으로 수행하는 메커니즘이므로 사용자가 신경쓰지 않아도 됩니다. 다만, 파티션 키 칼럼에 대한 가공이 발생하지 않도록 주의해야합니다.

 

인덱스 파티셔닝

LOCAL 파티션 인덱스

 

테이블 파티션과 1:1로 대응되도록 파티션한 인덱스입니다. 인덱스 파티션 키를 사용자가 따로 지정하지 않으며, 테이블과 1:1 관계를 유지하도록 DBMS가 관관리합니다.

 

GLOBAL 파티션 인덱스

 

테이블 파티션과 독립적인 구성을 갖도록 파티셔닝한 인덱스입니다.

 

PREFIXED 파티션 인덱스

 

파티션 인덱스를 생성할때 파티션 키 칼럼을 인덱스 키컬럼 선두에 두는 것을 말합니다.

 

NONPREFIXED 파티션 인덱스

 

파티션 인덱스를 생성할 때, 파티션 키 칼럼을 인덱스 키 칼럼 선두에 두지 않는것을 말합니다. 파티션 키가 인덱스 칼럼에 속하지 않을때도 여기에 해당됩니다.

 

결과적으로 인덱스는 총 5가지 유형으로 구분될 수 있습니다.

 

- NONPARTITION INDEX
- LOCAL PREFIXED PARTITION INDEX
- LOCAL NONPREFIXED PARTITION INDEX
- GLOBAL PREFIXED PARTITION INDEX
- GLOBAL NONPREFIXED PARTITION INDEX (oracle 미지원)

 

인덱스 파티셔닝 가이드

 

인덱스 파티션은 성능, 관리 편의성, 가용성, 확장성을 제공합니다. 테이블에 종속적인 LOCAL 파티션 인덱스와 테이블과 독립적인 GLOBAL 파티션 인덱스 모두 가능하지만, 관리적인 측면에서는 LOCAL 파티션 인덱스가 더 유용합니다.

 

테이블 파티션에 대한 DROP, EXCHANGE, SPLIT 작업 시 GLOBAL 인덱스는 UNUSABLE 상태가 되기 때문입니다. 인덱스를 다시 사용하려면 인덱스를 REBUILD 하거나 재생성해야합니다.

 

비파티션 인덱스

 

테이블 파티션 키 칼럼이 조건절에 없으면 여러 인덱스 파티션을 엑세스 해야하므로 오히려 비효율적입니다. 특히 OLTP 환경에서 성능에 미치는 영향이 크기때문에 비파티셔닝이 더 유용합니다.

 

NL 조인에서 파티션 키에 대한 넓은 범위검색 조건으로 INNER 테이블 엑세스 용도로 인덱스 파티션이 사용되면 비파티션 인덱스에 비해 비효율적입니다.

 

- 파티션 인덱스를 이용하면 SORT ORDER BY 대체 효과가 상실됩니다. 소트연산을 대체함으로써 부분범위처리를 활용하고자 할 땐 비파티셔닝 전략이 유용합니다.

 

GLOBAL PREFIXED

 

인덱스 경합 분산에 효과적입니다. 여러 LOCAL 인덱스 파티션을 엑세스하는 것이 비효율적일때 대안으로 활용가능합니다. 테이블 파티션 이동, 삭제 등의 작업시 UNUSABLE 됩니다.

 

LOCAL PREFIXED

 

테이블 파티션에 대한 추가, 삭제 등 작업이 빈번할때 유용합니다. 이력성 데이터를 주로 관리하는 DB 환경에 적합합니다. 파티션 키 칼럼이 = 조건으로 사용될 때 유용합니다. 파티셔닝 칼럼이 검색 조건에 없으면 인덱스 선두 칼럼이 조건절에 누락된 것이므로 정삭적인 사용이 불가합니다. 파티션 키 칼럼이 LIKE, BETWEEN 같은 범위검색 조건일 때 불리합니다.

 

LOCAL NON PREFIXED

테이블 파티션에 대한 추가, 삭제 등 작업이 빈번할때 유용합니다. 이력성 데이터를 주로 관리하는 DB 환경에 적합합니다. 파티션 키 칼럼이 = 조건으로 사용될 때 유용합니다. 파티셔닝 칼럼이 검색 조건에 없으면 인덱스 파티션 전체를 액세스하는 비효율이 발생합니다. 파티션 키 칼럼이 범위검색 조건으로 사용될때 유리합니다.