[SQLP] 파티셔닝

1. 파티션 개요

• 파티셔닝은 테이블 또는 인덱스를 파티션 단위로 나누어 저장하는 것을 말함.
• 테이블을 파티셔닝하면 파티션 키에 따라 물리적으로는 별도의 세그먼트에 데이터를 저장하며, 인덱스도 마찬가지다.
  • 파티셔닝이 필요한 이유에 대해 설명해보자면 인덱스 스캔도 결국 데이터가 일정량이 넘어가는 순간 Random Access가 많아지고, 그렇다고 Full Scan 하기에는 너무 비효율적이다.
• 관리적 측면 : 파티션 단위 백업, 추가, 삭제 변경
• 성능적 측면 : 파티션 단위 조회 및 DML 수행, 경합 및 부하 분산

2. 파티션 유형

1) Range 파티셔닝

• 파티션 키 값의 범위로 분할
• 파티셔닝의 가장 일반적인 형태이며, 주로 날짜 컬럼을 기준으로 함 (예를 들어, 판매 데이터를 월별로 분할)

2) Hash 파티셔닝

• 파티션 키 값에 해시 함수를 적용하고, 거기서 반환된 값으로 파티션 매핑
• 데이터가 모든 파티션에 고르게 분산되도록 DBMS가 관리하기 때문에 각 ROW의 저장 위치를 예측할 수 없다.
• 파티션 키의 데이터 분포가 고른 컬럼이어야 효과적임 (예를 들어, 고객번호나 주문번호)
• 병렬 처리 시 성능 효과 극대화
• DML 경합 분산에 효과적

3) List 파티셔닝

• 불연속적인 값의 목록을 각 파티션에 지정
• 순서와 상관없이, 사용자가 미리 정한 그룹핑 기준에 따라 데이터를 분할 저장 (예를 들어 판매 데이터를 지역별로 분할)

4) Composite 파티셔닝

• Range나 List 파티션 내에 또 다른 서브 파티션 구성 (예를 들어 Range + List 또는 List + Hash 등)
• Range 나 List 파티션이 갖는 이점 + 각 서브 파티션 구성의 이점

파티셔닝 예시

Range 파티셔닝

CREATE TABLE 주문 (
    주문번호 NUMBER,
    주문일자 VARCHAR2(8),
    고객ID VARCHAR2(5)
)
PARTITION BY RANGE (주문일자) (
    PARTITION p2009_q1 VALUES LESS THAN ('20090401'),
    PARTITION p2009_q2 VALUES LESS THAN ('20090701'),
    PARTITION p2009_q3 VALUES LESS THAN ('20091001'),
    PARTITION p2009_q4 VALUES LESS THAN ('20100101'),
    PARTITION p2010_q1 VALUES LESS THAN ('20100401'),
    PARTITION p9999_mx VALUES LESS THAN (MAXVALUE)  -- 주문일자 >= '20100401'
);

 

 

Range + Hash 파티셔닝

CREATE TABLE 주문 (
    주문번호 NUMBER,
    주문일자 VARCHAR2(8),
    고객ID VARCHAR2(5)
)
PARTITION BY RANGE (주문일자)
SUBPARTITION BY HASH (고객ID)
SUBPARTITIONS 8 (
    PARTITION p2009_q1 VALUES LESS THAN ('20090401'),
    PARTITION p2009_q2 VALUES LESS THAN ('20090701'),
    PARTITION p2009_q3 VALUES LESS THAN ('20091001'),
    PARTITION p2009_q4 VALUES LESS THAN ('20100101'),
    PARTITION p2010_q1 VALUES LESS THAN ('20100401'),
    PARTITION p9999_mx VALUES LESS THAN (MAXVALUE)
);

 

 

3. 파티션 Pruning

• 파티션 Pruning 은 옵티마이저가 SQL의 대상 테이블과 조건절을 분석해 불필요한 파티션 엑세스 대상에서 제외하는 기능을 말함
  • 이게 파티셔닝의 성능 개선의 핵심 원리이다.
• 기본 파티션 Pruning 에는 정적 Pruninig 과 동적 Pruning 이 있다.
• 파티션 키 컬럼에 대한 가공이 발생하지 않도록 주의해야한다.
  • 사용자가 명시적으로 파티션 키 컬럼을 가공했을 때는 물론, 데이터 타입이 묵시적으로 변환될 때도 정상적인 Pruning이 불가능해진다.

가. 정적 파티션 Pruning

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

SELECT *
FROM sales_range
WHERE sales_date >= '20060301'
  AND sales_date <= '20060401';

실행계획

• 파티션 3에서 4 까지 전체 테이블 스캔을 수행한다.

-----------------------------------------------------
| Id  | Operation                 | Name        | Pstart | Pstop |
-----------------------------------------------------
| 0   | SELECT STATEMENT          |             |        |       |
| 1   |  PARTITION RANGE ITERATOR  |             | 3      | 4     |
|* 2  |   TABLE ACCESS FULL         | SALES_RANGE | 3      | 4     |
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("SALES_DATE" >= '20060301' AND "SALES_DATE" <= '20060401')

나. 동적 파티션 Pruning

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

SELECT *
FROM sales_range
WHERE sales_date >= :a
  AND sales_date <= :b;

실행계획

--------------------------------------------------------
| Id  | Operation                 | Name        | Pstart | Pstop |
--------------------------------------------------------
| 0   | SELECT STATEMENT          |             |        |       |
|* 1  |  FILTER                    |             |        |       |
| 2   |   PARTITION RANGE ITERATOR  |             | KEY    | KEY   |
|* 3  |    TABLE ACCESS FULL         | SALES_RANGE | KEY    | KEY   |
--------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(:A <= :B)
3 - filter("SALES_DATE" >= :A AND "SALES_DATE" <= :B)

4. 인덱스 파티셔닝

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

• Local 파티션 인덱스: 테이블 파티션과 1:1 대응되도록 파티셔닝한 인덱스

• Global 파티션 인덱스: 테이블 파티션과 독립적인 구성을 갖도록 파티셔닝한 인덱스

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

• Prefixed :파티션 인덱스: 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두는 것을 말한다.
• 아래의 orders 테이블의 order_date 가 파티션 키이고 선두컬럼을 두는 예시이다.

CREATE INDEX order_idx ON orders(order_date, customer_id)
LOCAL;

• NonPrefiexed: 파티션 키 컬럼을 인덱스 키 컬럼 왼쪽 선두에 두지 않는 것을 말한다. 파티션 키가 인덱스 컬럼에 아예 속하지 않을 때도 말한다.
• 아래의 orders 테이블의 order_date 가 파티션 키이고 선두컬럼을 두지 않는 예시이다.

CREATE INDEX order_idx ON orders(customer_id, order_date)
LOCAL;