파티션 Pruning은 하드파싱이나 실행 시점에 SQL 조건절을 분석하여 읽지 않아도 되는 파티션 세그먼트를 액세스 대상에서 제외시키는 기능이다.
파티션 테이블에 대한 쿼리나 DML을 수행할 때 극적인 성능 개선을 가져다 주는 핵심 원리가 파티션 Pruing에 있다!
(1) 기본 파티션 Pruning
- 정적 파티션 Pruning: 파티션 키 컬럼을 상수 조건으로 조회하는 경우에 작동하며, 액세스할 파티션이 쿼리 최적화 시점에 미리 결정되는 것이 특징이다.
실행계획의 Pstart, Pstop 컬럼에는 액세스할 파티션 번호가 출력된다.
-
동적 파티션 Pruning: 파티션 키 컬럼을 바인드 변수로 조회하면 쿼리 최적화 시점에는 액세스할 파티션을 미리 결정할 수 없다. 실행 시점이 돼서야 사용자가 입력한 값에 따라 결정되며, 'KEY'로 표시된다.
NL 조인할 때도 Inner 테이블이 조인 컬럼 기준으로 파티셔닝 돼 있다면 동적 Pruning이 작동한다.
파티션 컬럼에 IN-LIST 조건을 사용하면 KEY(I)라고 표시된다.
CREATE TABLE t ( key, no, data )
PARTITION BY RANGE (no) SUBPARTITION BY HASH (key) SUBPARTITIONS 16 (
PARTITION p01 VALUES LESS THAN (11),
PARTITION p02 VALUES LESS THAN (21),
PARTITION p03 VALUES LESS THAN (31),
PARTITION p04 VALUES LESS THAN (41),
PARTITION p05 VALUES LESS THAN (51),
PARTITION p06 VALUES LESS THAN (61),
PARTITION p07 VALUES LESS THAN (71),
PARTITION p08 VALUES LESS THAN (81),
PARTITION p09 VALUES LESS THAN (91),
PARTITION p10 VALUES LESS THAN (MAXVALUE)
)
AS
SELECT
LPAD(ROWNUM, 6, '0') AS key,
MOD(ROWNUM, 50) + 1 AS no,
LPAD(ROWNUM, 10, '0') AS data
FROM dual
CONNECT BY LEVEL <= 999999;SELECT COUNT(*) FROM t WHERE no BETWEEN 30 AND 50;
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Pstart| Pstop |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 13 | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | |
| 2 | PARTITION RANGE ITERATOR | | 576K | 7323K | 3 | 5 |
| 3 | PARTITION HASH ALL | | 576K | 7323K | 1 | 16 |
| 4 | TABLE ACCESS FULL | T | 576K | 7323K | 33 | 80 |
--------------------------------------------------------------------------------------------------> 서브파티션 키에 대한 조건이 없어서 1~16 모두 스캔!
(2), (3) Skip
(4) SQL 조건절 작성 시 주의사항
like 보단 between!
