Partition Pruning 해석: Static·Dynamic·PSTART·PSTOP

핵심 요약

Partition Pruning은 Optimizer가 SQL의 Predicate와 Partition 정의를 비교해 결과에 필요하지 않은 Partition을 Access 후보에서 제거하는 과정입니다. Pruning이 먼저 적용된 뒤, 남은 Partition 안에서 Table Full Scan·Index Range Scan·Bitmap Access 같은 Access Path를 선택합니다.

SQL Predicate와 Partition 정의 분석
→ 대상 Partition·Subpartition 결정
→ 불필요한 조각 제거
→ 남은 조각 내부 Access Path 선택
→ Row Source 실행과 실제 통계 확인

따라서 실행계획에 TABLE ACCESS FULL이 보여도 전체 논리 Table을 읽는다고 단정할 수 없습니다. PARTITION RANGE SINGLE 아래의 Full Scan이라면 선택된 Range Partition 하나 안을 Full Scan하는 것입니다.

Static Pruning은 Compile 시점에 대상 Partition을 확정할 수 있는 경우이고, Dynamic Pruning은 Bind·Subquery·Join 입력처럼 실행 중 정해지는 값으로 대상을 계산하는 경우입니다. 숫자 PSTART·PSTOP은 Compiler가 시작·종료 Partition 위치를 미리 정한 형태이고, KEY 계열은 Runtime에 위치를 계산한다는 뜻입니다. KEY는 Pruning 실패 표시가 아닙니다.

학습 목표

이 이론을 학습한 뒤에는 다음을 설명할 수 있어야 합니다.

1. Partition Pruning과 Partition 내부 Access Path의 역할을 구분한다.
2. Static Pruning과 Dynamic Pruning의 결정 시점을 설명한다.
3. PSTART·PSTOP 숫자와 KEY 계열을 해석한다.
4. KEY(I)·KEY(SQ)·KEY(ZM)·ROW LOCATION·INVALID를 구분한다.
5. PARTITION RANGE SINGLE·ITERATOR·ALL의 차이를 설명한다.
6. Range·List·Hash 방식별 Pruning Predicate를 판단한다.
7. 함수·묵시적 형변환이 Pruning을 약화하거나 제거하는 이유를 설명한다.
8. Composite Partition의 Partition·Subpartition Pruning을 각각 확인한다.
9. 명시적 Partition 접근과 Optimizer Pruning을 구분한다.
10. 실제 Cursor 통계로 Pruning 효과와 남은 병목을 검증한다.

1. Pruning과 Access Path는 다른 판단이다

다음 실행계획을 봅니다.

PARTITION RANGE SINGLE  Pstart 19  Pstop 19
  TABLE ACCESS FULL SALES

이 계획은 다음 두 판단을 나타냅니다.

PARTITION RANGE SINGLE
→ Range Partition 위치 19 하나만 선택

TABLE ACCESS FULL SALES
→ 선택된 Partition 안의 Block을 Full Scan

한 달 Partition의 대부분을 집계한다면 Partition Full Scan이 Index를 통한 많은 Random Access보다 효율적일 수 있습니다. 항상 다음 질문을 분리합니다.

1. 몇 개 Partition·Subpartition이 후보로 남았는가?
2. 남은 조각 안에서 어떤 Access Path를 사용했는가?
3. 그 Row Source가 실제로 몇 Row와 Buffer를 처리했는가?

Pruning은 Index를 대체하지 않고, Index도 Pruning을 대신하지 않습니다.

2. Static Partition Pruning

Static Pruning은 Optimizer가 Compile 시점에 대상 Partition의 연속 범위를 결정할 수 있는 경우입니다.

SELECT SUM(amount)
FROM   sales
WHERE  sale_date >= DATE '2025-07-01'
  AND  sale_date <  DATE '2025-08-01';

월별 Range Partition이라면 다음과 같은 계획이 나타날 수 있습니다.

PARTITION RANGE SINGLE  Pstart 31  Pstop 31
  TABLE ACCESS FULL SALES

31은 실행계획에서 사용하는 Partition 위치 정보입니다. 이것을 업무 Partition 이름으로 해석하려면 Data Dictionary의 PARTITION_POSITION과 PARTITION_NAME을 함께 확인합니다.

SELECT partition_position,
       partition_name,
       high_value
FROM   user_tab_partitions
WHERE  table_name = 'SALES'
ORDER BY partition_position;

Partition Split·Merge·Drop·Add 같은 유지보수 후에는 위치가 달라질 수 있으므로 숫자를 이름처럼 고정해서 기억하지 않습니다.

Static Pruning에 유리한 조건

• Literal 또는 Compile 시 평가 가능한 상수
• Partition Key에 직접 적용한 등치·범위·IN Predicate
• Partition Key와 비교값의 Data Type이 일치하는 표현식
• Range 경계와 자연스럽게 연결되는 반개구간
• List 값 또는 Hash Key를 직접 식별하는 Predicate

Static Pruning은 실행 전부터 대상 조각이 명확하므로 Plan에서 숫자 범위를 읽기 쉽고, Runtime 계산 비용도 줄일 수 있습니다.

3. Dynamic Partition Pruning

Dynamic Pruning은 Pruning 자체는 가능하지만 정확한 Partition 위치를 실행 중 계산해야 하는 경우입니다.

대표 사례는 다음과 같습니다.

• Partition Key에 전달되는 Bind Variable
• Subquery 결과로 결정되는 Partition Key
• Join의 바깥 Row에서 전달되는 Key
• Star Transformation 과정에서 얻은 Key
• Zone Map을 이용한 Partition Pruning

SELECT SUM(s.amount)
FROM   sales s
JOIN   calendar c
  ON   c.calendar_date = s.sale_date
WHERE  c.month_key = :month_key;

SALES의 대상 Partition이 CALENDAR에서 읽은 날짜에 따라 정해지면 다음과 같은 형태가 나타날 수 있습니다.

PARTITION RANGE ITERATOR  Pstart KEY  Pstop KEY
  TABLE ACCESS FULL SALES

Compile 시점
→ 정확한 Partition 위치를 확정하지 못함

Execute 시점
→ Bind·Join·Subquery 결과로 Partition 위치 계산
→ 계산된 Partition만 Access

Dynamic Pruning은 모든 Partition을 읽는다는 뜻이 아닙니다. 다만 같은 Plan이라도 Bind 값이나 Join 입력 분포에 따라 실제 접근 범위와 작업량이 달라질 수 있으므로 Runtime 통계를 확인해야 합니다.

4. PSTART·PSTOP 표기 해석

PSTART와 PSTOP은 접근할 Partition 범위 또는 그 계산 방식을 나타냅니다.

KEY 뒤의 괄호는 Dynamic Pruning의 구체적인 원인을 식별하는 데 도움이 되지만, 모든 실행계획이 항상 세부 Attribute를 표시하는 것은 아닙니다. Operation·Predicate·Query 구조를 함께 읽습니다.

KEY(I) 예시

SELECT *
FROM   sales
WHERE  sale_date IN (:d1, :d2, :d3);

INLIST ITERATOR
  PARTITION RANGE ITERATOR  Pstart KEY(I)  Pstop KEY(I)

Bind 값별로 Partition 위치를 Runtime에 계산하는 형태입니다.

KEY(SQ) 예시

SELECT SUM(amount)
FROM   sales
WHERE  sale_date IN (
    SELECT calendar_date
    FROM   calendar
    WHERE  fiscal_year = 2025
);

PARTITION RANGE SUBQUERY  Pstart KEY(SQ)  Pstop KEY(SQ)

Subquery 결과가 Pruning Key를 제공합니다.

5. Partition Operation과 후보 범위

PARTITION RANGE ALL은 일반적으로 Partition-level Pruning이 적용되지 않았다는 뜻입니다. 다만 Partition 내부 Index, Storage 기능, Zone Map 같은 다른 Access 최적화 가능성까지 부정하는 표시는 아닙니다. 실제 I/O와 Predicate를 확인합니다.

숫자 범위도 Operation과 함께 봅니다.

Pstart 12 / Pstop 12
→ 한 Partition 위치

Pstart 12 / Pstop 15
→ 연속된 Partition 위치 12~15

Pstart KEY / Pstop KEY
→ Runtime에 시작·종료 위치 계산

6. Partition 방식별 Pruning Predicate

Partition 방식마다 직접 사용할 수 있는 Predicate가 다릅니다.

Hash Partition에서 다음 조건은 특정 Hash Partition을 찾는 데 유리합니다.

WHERE customer_id = :customer_id

반면 다음 조건은 값의 숫자 범위가 물리적으로 연속된 Hash Partition에 저장되지 않으므로 많은 Hash Partition이 후보가 될 수 있습니다.

WHERE customer_id BETWEEN 100000 AND 200000

7. 날짜 Predicate와 Data Type

일반적인 날짜 Range Partition에서는 Partition Key를 그대로 두고, 같은 Data Type의 경계를 직접 비교하는 반개구간이 가장 명확합니다.

WHERE sale_date >= DATE '2025-07-01'
  AND sale_date <  DATE '2025-08-01'

다음 조건은 Partition Key에 함수를 적용합니다.

WHERE TO_CHAR(sale_date, 'YYYYMM') = '202507'

일반적인 sale_date 기준 Range Partition에서는 Partition Key에 함수나 변환이 적용되면 Pruning이 일어나지 않을 수 있습니다. 해당 표현식 자체로 Partitioning한 Table, Partition by Expression 또는 대응하는 Virtual Column Partitioning은 별도 설계입니다.

묵시적 형변환

WHERE sale_date = :value

:value를 문자열이나 다른 시간 Type으로 전달해 Oracle이 sale_date Column 쪽을 변환하면 Static Pruning을 Dynamic Pruning으로 바꾸거나 Pruning을 제거할 수 있습니다. 애플리케이션은 Partition Key와 호환되는 DATE 또는 정확한 시간 Type으로 Bind하는 것이 안전합니다.

좋은 방향
→ Column은 그대로 유지
→ 비교값을 Column Type에 맞춤
→ NLS 문자열 형식에 의존하지 않음

8. Composite Partition의 두 단계 Pruning

날짜로 Range Partition하고 고객번호로 Hash Subpartition한 Table을 봅니다.

SALES
├─ P202507
│  ├─ H1
│  ├─ H2
│  ├─ H3
│  └─ H4
└─ P202508
   ├─ H1
   ├─ H2
   ├─ H3
   └─ H4

날짜 조건만 있음

WHERE sale_date >= DATE '2025-07-01'
  AND sale_date <  DATE '2025-08-01'

Range Partition
→ P202507 하나로 Pruning

Hash Subpartition
→ customer_id 조건이 없으므로 P202507 안의 H1~H4가 후보

날짜와 고객 조건이 함께 있음

WHERE sale_date >= DATE '2025-07-01'
  AND sale_date <  DATE '2025-08-01'
  AND customer_id = :customer_id

Range Partition
→ P202507

Hash Subpartition
→ customer_id가 매핑되는 Hash Subpartition

Composite Table에서는 상위 Partition Pruning이 성공했다는 사실만으로 충분하지 않습니다. 실행계획에서 Partition과 Subpartition의 시작·종료 위치와 Operation을 각각 확인합니다.

9. 명시적 Partition 접근은 Optimizer Pruning과 다르다

다음 문법은 SQL 작성자가 Partition 이름을 직접 지정합니다.

SELECT COUNT(*)
FROM   sales PARTITION (p202507);

Partition Extended Syntax
→ 작성자가 대상 Partition을 명시적으로 제한

Optimizer Partition Pruning
→ Predicate와 Partition 정의를 Optimizer가 분석해 대상 결정

특정 Partition 점검·관리·DML에는 명시적 접근이 유용할 수 있습니다. 그러나 애플리케이션 SQL에 Partition 이름을 고정하면 Add·Split·Merge·Rename 등 Partition 생명주기에 강하게 결합되므로 유지보수 위험을 평가해야 합니다.

10. 실제 Cursor 통계로 검증한다

예상 계획만으로 실제 Pruning 효과를 확정하지 않습니다. SQL을 끝까지 실행하고 실제 Cursor를 확인합니다.

SELECT /*+ GATHER_PLAN_STATISTICS */
       SUM(amount)
FROM   sales
WHERE  sale_date >= :from_date
  AND  sale_date <  :to_date;

SELECT *
FROM TABLE(
    DBMS_XPLAN.DISPLAY_CURSOR(
        NULL,
        NULL,
        'ALLSTATS LAST +PARTITION +PREDICATE +ALIAS +NOTE'
    )
);

다음 순서로 읽습니다.

1. PARTITION Operation과 PSTART·PSTOP
2. Access Predicate와 Filter Predicate
3. Table·Index Row Source의 Starts와 A-Rows
4. Buffers·Reads·A-Time
5. 실행한 Bind 값과 조회 기간
6. E-Rows와 A-Rows 차이
7. Parallel·Nested Loop·재시작 여부

SUM 결과가 한 행이어도 아래 Table Operation은 수천만 Row와 많은 Buffer를 처리할 수 있습니다. 최종 결과 행 수가 아니라 Pruning 아래 Row Source의 실제 작업량을 봅니다.

Starts 해석 주의

단순한 Serial Iterator 계획에서는 Table Row Source의 Starts가 접근한 Partition 수를 추정하는 데 도움이 될 수 있습니다. 그러나 Nested Loop의 반복 실행, Parallel Execution, Rescan이 있으면 Starts는 Partition 수와 일치하지 않을 수 있습니다.

Starts가 큼
→ 많은 Partition일 수도 있음
→ 바깥 Row마다 같은 Partition Operation이 반복된 것일 수도 있음
→ PX Server별 시작 횟수가 합산된 것일 수도 있음

따라서 Starts 하나로 접근 Partition 수를 확정하지 않고 Operation 구조, Bind·Join 입력, SQL Monitor·Trace 등 필요한 실측을 함께 봅니다.

11. Pruning 실패 또는 약화 진단

최종 판단 순서

Partition 정의와 위치 확인
→ Partition·Subpartition Key와 경계 확인
→ Predicate와 Bind Data Type 확인
→ Static·Dynamic 결정 시점 판단
→ Operation과 PSTART·PSTOP 해석
→ Partition 내부 Access Path 확인
→ A-Rows·Buffers·Reads·A-Time 검증
→ Pruning 이후 남은 병목 튜닝