조인 전에 행 수 줄이기: 필터링·선집계·부분범위 처리

핵심 요약

조인 성능을 줄이는 가장 직접적인 방법은 같은 결과를 유지하면서 다음 연산으로 전달할 Row Source를 작게 만드는 것입니다.

원본 Row
→ Partition Pruning·Access Predicate
→ Filter Row
→ 필요 시 Aggregate·Distinct
→ Join
→ Sort·Window·Top-N
→ 최종 Row

하지만 “조건과 GROUP BY를 무조건 안쪽으로 이동한다”는 규칙은 없습니다.

성능상 행 수 감소
  + 결과 Grain 유지
  + Join 중복·NULL·Outer Join 보존 규칙 유지
  + Aggregate 계산 가능성 유지
= 안전한 조인 전 축소

Oracle Optimizer는 SQL Text의 CTE·Inline View 순서를 절차적으로 고정해 실행하지 않습니다.

View Merging
  → View Query Block을 바깥 Query Block과 합침
  → 더 많은 Join Order·Access Path 후보

Complex View Merging
  → GROUP BY·DISTINCT를 Join 전 또는 후에 둘지 Cost로 선택 가능

Predicate Pushing
  → Merge하지 않은 View 내부에 바깥 Predicate를 전달
  → 내부 Index Access·Filter 후보 확대

따라서 WITH 절에 선집계를 작성했다고 해서 실제 Plan에서 반드시 먼저 집계되는 것은 아닙니다. 반대로 Optimizer가 의미와 Cost를 만족하면 원래 SQL보다 일찍 Predicate·Aggregate를 적용할 수도 있습니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 조인 순서와 조인 방식 → 조인 전 행 수 감소·부분범위 처리 범위에서 Grain, 선집계, Predicate Pushdown, Top-N, Partition Pruning과 Runtime 검증을 다룹니다.

학습 목표

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

• Row Source의 Grain과 1:1·1:N·N:M 조인에 따른 행 증가를 설명한다.
• 원본→Filter→Aggregate→Join→Top-N 단계별 행 수를 계산한다.
• 조인 전 선집계가 안전한 조건과 결과가 달라지는 조건을 구분한다.
• SUM·COUNT·MIN·MAX와 AVG·COUNT DISTINCT의 재집계 차이를 설명한다.
• View Merging·Complex View Merging·Predicate Pushing을 설명한다.
• Join Predicate Pushdown의 이점과 반복 Starts 위험을 설명한다.
• 선택적 Dimension 선행과 Fact Scan·선집계를 비교한다.
• 전체범위 처리와 부분범위 처리의 목표를 구분한다.
• Top-N 대상 Grain·결정적 정렬·WITH TIES를 설명한다.
• Partition Pruning과 Full Scan도 선행 작업량 감소 수단임을 설명한다.
• ALLSTATS LAST의 A-Rows·Starts·Buffers·Workarea·TEMP·STOPKEY로 효과를 검증한다.

1. Grain을 먼저 확정한다

Grain은 Row Source 한 행이 의미하는 업무 단위입니다.

ORDERS
  한 행 = 주문 한 건

ORDER_ITEMS
  한 행 = 주문 안의 상품 한 건

PRODUCTS
  한 행 = 상품 한 건

상품별 주문 집계 View
  한 행 = 상품별 집계 한 건

주문 한 건에 상품이 5개라면 다음과 같습니다.

ORDERS 1행
× ORDER_ITEMS Match 5행
= Join 결과 5행

튜닝 전에 확인합니다.

• 최종 한 행은 주문·주문상품·상품·고객 중 무엇인가?
• Join Key가 각 입력에서 Unique한가?
• 관계가 1:1·1:N·N:M 중 무엇인가?
• Match가 여러 건일 때 행 증가가 업무상 필요한가?
• Aggregate는 어느 Grain에서 계산해야 하는가?
• Outer Join에서 어떤 입력을 보존해야 하는가?

불필요한 DISTINCT
  → 잘못된 Join 중복을 숨길 수 있음

잘못된 선집계
  → 필요한 Detail 관계를 제거할 수 있음

2. 단계별 작업량을 적는다

SQL을 다음 단계로 나누면 축소 효과가 명확해집니다.

1. 원본 Row·Block
2. Partition Pruning 후 범위
3. Access·Filter 통과 Row
4. Aggregate·Distinct 후 Row
5. Join 후 Row
6. Sort·Window 후 Row
7. Top-N·최종 Fetch Row

예시입니다.

ORDER_ITEMS 전체             120,000,000
월 Partition Pruning           8,000,000
날짜·할인 조건 통과            4,000,000
PRODUCT_ID별 집계                 80,000
PRODUCTS Unique Join               80,000
최종 Sort                         80,000

핵심 비율입니다.

Reduction Ratio
  = 다음 단계 A-Rows / 이전 단계 A-Rows

4,000,000 → 80,000
  = 2%
  → 큰 축소

4,000,000 → 3,800,000
  = 95%
  → 집계 비용 대비 Join 입력 감소가 작음

3. 조인 전 선집계

3.1 기본 구조

WITH order_sum AS (
    SELECT oi.product_id,
           SUM(oi.order_amount) AS total_amount,
           COUNT(*)             AS item_count
    FROM   order_items oi
    WHERE  oi.order_date >= :from_date
    AND    oi.discount_type = :discount_type
    GROUP BY oi.product_id
)
SELECT p.product_id,
       p.product_name,
       s.total_amount,
       s.item_count
FROM   order_sum s
JOIN   products p
  ON   p.product_id = s.product_id;

ORDER_ITEMS Detail Grain
→ PRODUCT_ID Aggregate Grain
→ PRODUCTS Product Grain과 Join

PRODUCTS.PRODUCT_ID가 Unique라면 집계 Row 한 건이 상품 Row 최대 한 건과 연결됩니다.

3.2 줄일 수 있는 비용

• Join 입력 A-Rows
• Hash Build·Probe Data Volume
• Nested Loops 반복 Starts
• Join 후 Row 폭과 중간 Result
• 후속 Sort·Window·Aggregate 입력
• Parallel Process 간 Data 이동
• Client 전송 후보 Row

3.3 Unique Key가 필요한 이유

Dimension Key가 중복되면 집계값이 복제됩니다.

order_sum product_id=10, total=1,000
PRODUCTS product_id=10 Row 2건

Join 결과
  → 1,000이 2행으로 반복
  → 이후 SUM하면 2,000으로 왜곡 가능

확인합니다.

• PK·UK Constraint가 활성화·검증됐는가?
• 실제 Data에 중복이 없는가?
• History Table처럼 Key당 여러 Version이 있는가?
• Effective Date Predicate가 한 행만 남기는가?

4. 선집계 위치가 결과를 바꾸는 경우

4.1 Detail 포함 여부를 바꾸는 Join Predicate

상품 상태가 상품 Grain의 단일 속성이고 Product Key가 Unique하면 다음 두 형태가 같을 수 있습니다.

Detail과 ACTIVE Product Join 후 집계
vs
Detail 선집계 후 ACTIVE Product Join

하지만 다음은 다를 수 있습니다.

• Product History가 Key당 여러 행
• Detail Row 날짜에 따라 유효한 Version이 다름
• Join 조건이 Detail Column에 의존
• Join 후 다른 Detail Table이 행을 증가
• Outer Join에서 미Match 행을 보존해야 함

4.2 Outer Join 보존

매출이 없는 상품도 보여야 한다면 Products를 보존합니다.

WITH order_sum AS (
    SELECT product_id,
           SUM(order_amount) AS total_amount
    FROM   order_items
    WHERE  order_date >= :from_date
    GROUP BY product_id
)
SELECT p.product_id,
       p.product_name,
       NVL(s.total_amount, 0) AS total_amount
FROM   products p
LEFT JOIN order_sum s
  ON   s.product_id = p.product_id;

Fact 집계 Row가 없음
→ Product Row는 보존
→ Aggregate Column NULL
→ 업무 요구에 따라 NVL

집계 View를 Driving으로 Inner Join하면 매출 없는 상품이 제거됩니다.

5. Aggregate를 다시 결합할 수 있는가

선집계를 여러 단계로 나눌 때 Aggregate의 재결합 가능성을 확인합니다.

5.1 직접 재집계 가능한 Aggregate

SUM
COUNT
MIN
MAX

예시입니다.

일별 상품 SUM
→ 월별 상품 SUM = 일별 SUM의 합

일별 COUNT
→ 월별 COUNT = 일별 COUNT의 합

5.2 AVG는 SUM과 COUNT가 필요하다

다음은 일반적으로 틀릴 수 있습니다.

월 AVG
  ≠ 일별 AVG의 단순 평균

안전한 형태입니다.

일별 SUM과 COUNT 보관
→ 월 SUM = SUM(day_sum)
→ 월 COUNT = SUM(day_count)
→ 월 AVG = 월 SUM / 월 COUNT

5.3 COUNT DISTINCT의 주의

Partition별 COUNT(DISTINCT customer_id)의 합
  ≠ 전체 COUNT(DISTINCT customer_id)

같은 고객이 여러 Partition·Group에 나타날 수 있기 때문입니다. Exact Distinct를 유지하려면 Grain·중복 범위를 보존하거나 적절한 별도 집합·Approximate 기법을 검토합니다.

6. View Merging과 Aggregate 위치

Oracle은 Query Block을 Cost 기반으로 변환할 수 있습니다.

6.1 Simple View Merging

SELECT·PROJECT·JOIN 형태의 단순 View를 바깥 Query Block과 합쳐 더 많은 Join Order와 Access Path를 고려할 수 있습니다.

6.2 Complex View Merging

GROUP BY·DISTINCT가 있는 View도 조건을 만족하면 Merge 후보가 될 수 있습니다.

GROUP BY를 Join 전에 수행
  → 다음 Join 입력 감소 가능

GROUP BY를 Join 뒤로 지연
  → Join Predicate가 먼저 많은 Row를 제거하면 더 저렴할 수 있음

Optimizer는 의미가 유지되는 후보의 Cost를 비교합니다.

6.3 CTE·Inline View는 순서 보장 문법이 아니다

WITH x AS (
  SELECT ...
  FROM ...
  GROUP BY ...
)
SELECT ...
FROM x
JOIN ...;

이 SQL이 다음을 보장하지는 않습니다.

x를 반드시 먼저 Materialize
GROUP BY를 반드시 Join 전에 완료

Plan의 VIEW, TEMP TABLE TRANSFORMATION, HASH GROUP BY, MATERIALIZE 여부와 실제 A-Rows를 확인합니다.

7. Predicate Pushing

7.1 일반 Predicate Pushdown

SELECT *
FROM (
    SELECT order_id,
           order_date,
           customer_id,
           order_amount
    FROM   orders
) v
WHERE v.order_date >= :from_date;

View가 Merge되지 않더라도 Optimizer가 Predicate를 내부로 Push하면 다음이 가능해집니다.

• 내부 Index Access
• Partition Pruning
• Scan 단계 Filter
• View 전체 생성 회피

7.2 의미 보존 경계

다음은 Detail Row Filter입니다.

WHERE order_date >= :from_date

다음은 Aggregate Result Filter입니다.

HAVING SUM(order_amount) >= :minimum_amount

HAVING 조건을 Detail WHERE처럼 Push하면 결과가 바뀔 수 있습니다.

또한 다음 경계를 확인합니다.

• Outer Join의 Preserved Row
• Analytic Function 계산 전·후
• DISTINCT 전·후
• Set Operator Branch
• Non-deterministic Function
• ROWNUM·Top-N Query Block

8. Join Predicate Pushdown

선행 Row Source의 Join Key를 Merge되지 않은 View 내부에 전달해 필요한 Key만 처리할 수 있습니다.

선행 Product Key 1건
→ Aggregate View 내부에서 해당 Product만 Scan·집계
→ 다음 Product Key에 대해 반복

Plan 표현 예입니다.

VIEW PUSHED PREDICATE

유리한 조건입니다.

• 선행 Key 수가 매우 적음
• 내부 Join Key Index·Partition Access가 효율적
• Key당 Detail Row가 적음
• 전체 Fact 집계보다 반복 소량 Access가 저렴

위험한 조건입니다.

선행 Key 100,000건
× View 내부 1회 Buffers 20
= 약 2,000,000 Buffers

확인합니다.

• Pushed View의 Starts
• 자식 Index·Table Starts
• Per-Start A-Rows·Buffers
• 전체 Fact Scan·선집계 대안
• Bind별 선행 Key 수 변화

PUSH_PRED, NO_MERGE 같은 Hint는 검증 도구이지 영구 정답이 아닙니다. Hint Report에서 적용 여부와 실제 Runtime을 확인합니다.

9. 선택 Dimension 선행과 Fact 선집계

전략 A: 선택 Dimension 선행

PRODUCTS 100,000
→ Category Filter 100
→ Product별 ORDER_ITEMS Index Probe

유리할 수 있는 조건입니다.

• Dimension Filter가 매우 선택적
• Fact Join Key Index가 효율적
• Key당 Detail Row가 적음
• 첫 행·첫 페이지 응답 중요
• 선행 Key 수가 Bind별로 안정적

개념 비용입니다.

Dimension Access
+ 선택 Key 수 × Fact 1회 Probe 비용

전략 B: Fact Filter·선집계

Fact Partition Pruning
→ 대상 Block Full Scan
→ Detail Filter
→ Join Key별 Aggregate
→ Dimension Hash Join

유리할 수 있는 조건입니다.

• 많은 Dimension Key가 필요
• 전체 결과를 끝까지 처리
• 날짜 Partition Pruning 가능
• 반복 Random Access보다 Scan이 저렴
• Detail→Group Reduction이 큼
• Parallel·Batch Throughput 중요

개념 비용입니다.

Pruned Fact Scan
+ Aggregate Workarea·TEMP
+ Group Result Join

Table 역할 이름이 아니라 실제 A-Rows·Blocks·Starts·Row Width로 비교합니다.

10. 전체범위 처리와 부분범위 처리

10.1 전체범위 처리

Report·Batch처럼 End-of-Fetch까지 모든 Row가 필요한 작업입니다.

중요 지표입니다.

• 전체 Buffers·Reads
• CPU·Elapsed
• Hash·Sort Workarea
• TEMP I/O
• Parallel Data 이동
• 최종 Result Row

대표 후보입니다.

• Partition Pruning 후 Full Scan
• Fact 선집계
• Hash Join
• 한 번의 전체 Sort·Group By

10.2 부분범위 처리

화면 첫 페이지·Top-N처럼 필요한 행을 얻은 뒤 중단할 수 있는 작업입니다.

SELECT o.order_id,
       o.order_date,
       c.customer_name
FROM   orders o
JOIN   customers c
  ON   c.customer_id = o.customer_id
ORDER BY o.order_date DESC,
         o.order_id DESC
FETCH FIRST 100 ROWS ONLY;

정렬과 일치하는 Index가 있다면 다음 후보가 생깁니다.

최신 ORDERS Index 순서 Scan
→ Customer PK Lookup
→ 100행 충족
→ Stop

확인합니다.

• INDEX RANGE SCAN DESCENDING
• COUNT STOPKEY
• WINDOW NOSORT STOPKEY
• Join Inner Starts
• STOPKEY 아래 실제 A-Rows
• 전체 Sort 발생 여부
• 첫 100행과 End-of-Fetch 계약 차이

FIRST_ROWS_n 목표는 첫 n행 응답을 최적화할 수 있지만 결과 의미와 실제 Fetch 범위를 바꾸지는 않습니다.

11. Top-N 대상 Grain과 정렬

11.1 최종 Join Row Top 100

SELECT o.order_id,
       oi.product_id
FROM   orders o
JOIN   order_items oi
  ON   oi.order_id = o.order_id
ORDER BY o.order_date DESC,
         o.order_id DESC
FETCH FIRST 100 ROWS ONLY;

대상 Grain입니다.

주문×주문상품 조합 100행

11.2 주문 100건 선선택 후 Detail Join

WITH recent_orders AS (
    SELECT order_id,
           order_date
    FROM   orders
    ORDER BY order_date DESC,
             order_id DESC
    FETCH FIRST 100 ROWS ONLY
)
SELECT r.order_id,
       oi.product_id
FROM   recent_orders r
JOIN   order_items oi
  ON   oi.order_id = r.order_id;

대상 Grain입니다.

주문 100건
→ 각 주문의 모든 상품
→ 최종 행은 100 초과 가능

11.3 결정적 정렬

Top-N은 ORDER BY가 결과 순서를 완전히 결정해야 재실행 결과가 안정적입니다.

order_date만 정렬
  → 같은 날짜·시간 Tie Row 순서 불명확 가능

order_date, order_id
  → Unique Tie-Breaker 추가

11.4 WITH TIES

FETCH FIRST 100 ROWS WITH TIES

100번째 Row와 같은 Sort Key를 가진 행을 추가 반환하므로 결과가 100행을 초과할 수 있습니다. WITH TIES의 대상 Sort Key와 업무 Grain을 확인합니다.

12. Partition Pruning과 Scan 범위 축소

Partition Pruning은 물리적으로 읽을 Segment 범위를 먼저 줄입니다.

ORDER_ITEMS 전체 120,000,000
→ 월 Partition 1개 8,000,000
→ Filter 4,000,000
→ Aggregate 80,000

대상 Partition의 큰 비율을 읽는다면 Index ROWID Random Access보다 Partition Full Scan이 저렴할 수 있습니다.

확인합니다.

• Plan의 PSTART·PSTOP
• Static·Dynamic Pruning
• 실제 Partition 수
• Local Index 후보 ROWID·Table Buffers
• Full Scan Blocks·Reads
• Parallel 여부
• Aggregate Reduction

Full Scan
  ≠ 행 수를 줄이지 못하는 Plan

Partition Pruned Full Scan
  → 전체 Table보다 훨씬 작은 범위를 한 번 읽는 전략

13. 실행계획 검증

SELECT /*+ GATHER_PLAN_STATISTICS */
       ...
FROM   ...;

SELECT *
FROM TABLE(
  DBMS_XPLAN.DISPLAY_CURSOR(
    :sql_id,
    :child_no,
    'ALLSTATS LAST +PREDICATE +ALIAS +NOTE'
  )
);

13.1 단계별 A-Rows

TABLE ACCESS ORDER_ITEMS     A-Rows 4,000,000
HASH GROUP BY                A-Rows    80,000
HASH JOIN                    A-Rows    80,000
SORT ORDER BY                A-Rows    80,000

13.2 반복 View

VIEW PUSHED PREDICATE
  Starts 100,000
  Buffers 2,000,000

Total과 Per-Start를 구분합니다.

Actual per Start
≈ A-Rows / Starts

Buffers per Start
≈ Buffers / Starts

13.3 Workarea

확인합니다.

• OMem
• 1Mem
• Used-Mem
• O/1/M
• Used-Tmp
• Sort·Hash A-Rows
• Row Width

Aggregate로 Row 수는 줄었더라도 Multi-Pass Spill이 크면 전체 이득이 제한될 수 있습니다.

13.4 STOPKEY와 Pruning

• STOPKEY Operation이 존재하는가?
• STOPKEY 아래에서 실제 몇 Row를 읽었는가?
• PSTART·PSTOP이 기대 범위인가?
• Top-N 전에 불필요한 Join·Sort를 완료하지 않았는가?

13.5 결과 검증

• 최종 행 수
• Row Grain
• SUM·COUNT·AVG·DISTINCT 값
• Outer Join 미Match 행
• Top-N 대상 Entity
• Tie 처리

14. Hint와 계획 강제 주의

대표 Hint입니다.

MERGE·NO_MERGE
PUSH_PRED·NO_PUSH_PRED
MATERIALIZE·INLINE
LEADING·USE_NL·USE_HASH
FIRST_ROWS(n)

주의합니다.

• Version·Query Block에 따라 적용 가능 여부가 다름
• Validity·Semantic 제한은 Hint로 우회할 수 없음
• Hint가 적용돼도 Runtime이 개선된다는 보장 없음
• Hint Report의 Used·Unused·Invalid 확인
• Bind별 Cardinality·전체 Workload 회귀 확인

Hint의 목적
  = 대안 Plan의 성능 가설 검증

Hint의 위험
  = Data 증가·분포 변화 뒤에도 과거 구조 고정

15. 적용 판단 절차

1. 각 입력과 최종 결과의 Grain을 적는다.
2. Join Key의 PK·UK·중복·유효기간 조건을 확인한다.
3. 원본→Pruning→Filter→Aggregate→Join→Top-N 행 수를 적는다.
4. Aggregate가 재결합 가능한지 확인한다.
5. Outer Join 보존과 Predicate 위치를 확인한다.
6. Dimension 선행 Probe와 Fact Scan·선집계 비용을 계산한다.
7. View Merging·Predicate Pushing 가능성과 반복 Starts를 확인한다.
8. 전체 Fetch·첫 n행 중 업무 목표를 확정한다.
9. Top-N 대상 Grain·Unique Tie-Breaker·WITH TIES를 확인한다.
10. ALLSTATS LAST와 결과 회귀 Test로 최종 채택한다.

자주 혼동하는 판단