조인 최적화의 기본: 조인 순서·접근 경로·조인 방식

핵심 요약

Oracle Optimizer는 여러 Table을 조인할 때 다음 세 가지를 함께 비교합니다.

Access Path
  → 각 Table·View·중간 Row Source를 어떻게 읽을 것인가

Join Order
  → 어떤 Row Source를 먼저 만들고 다음 입력과 어떤 순서로 연결할 것인가

Join Method
  → 두 Row Source를 Nested Loops·Hash·Sort Merge 중 어떤 알고리즘으로 연결할 것인가

최종 실행계획은 다음 조합입니다.

각 입력의 Access Path
+ Join Order
+ 단계별 Join Method
+ Query Transformation
= 최저 Cost 실행계획

조인 판단의 기준은 원본 Table 크기가 아니라 Predicate 적용 후 각 Row Source의 Cardinality와 다음 작업 비용입니다.

원본 10억 행 Table
→ Filter 후 5행
→ 작은 Driving 후보 가능

원본 1만 행 Table
→ Filter 후 9,500행
→ 작은 입력이라고 단정할 수 없음

대표 Join Method입니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 조인 순서와 조인 방식 범위에서 Access Path·Join Order·Join Method, Join Type과 Runtime 검증을 다룹니다.

학습 목표

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

• Access Path·Join Order·Join Method의 차이를 구분한다.
• Table이 아닌 Row Source가 조인된다는 의미를 설명한다.
• 원본 크기보다 Filter 후 Cardinality가 중요한 이유를 설명한다.
• Nested Loops의 Outer·Inner 반복 구조와 Starts를 연결한다.
• Hash Join의 Build·Probe Input과 Workarea Spill을 설명한다.
• Sort Merge Join의 Sort·Merge 과정과 비등치 적용 조건을 설명한다.
• Join Type과 Join Method를 구분한다.
• Outer·Semi·Anti·Cartesian Join의 의미와 실행 방식을 설명한다.
• Adaptive Join이 NL과 Hash 후보 사이를 선택하는 원리를 설명한다.
• E-Rows·A-Rows·Starts·Buffers·Reads·Workarea·TEMP로 조인 비용을 검증한다.
• LEADING·ORDERED·USE_NL·USE_HASH·USE_MERGE Hint를 제한적으로 검증한다.

1. Optimizer가 결정하는 세 가지

다음 SQL을 가정합니다.

SELECT o.order_id,
       c.customer_name,
       p.product_name
FROM   orders o
JOIN   customers c
  ON   c.customer_id = o.customer_id
JOIN   products p
  ON   p.product_id = o.product_id
WHERE  c.region_code = 'SEOUL'
AND    o.order_date >= DATE '2026-07-01';

Optimizer는 FROM 절의 작성 순서를 그대로 실행해야 하는 절차형 처리기가 아닙니다.

1.1 Access Path

CUSTOMERS
  → Full Table Scan
  → REGION_CODE Index Range Scan

ORDERS
  → Full Table Scan
  → CUSTOMER_ID Index Range Scan
  → ORDER_DATE Index Range Scan
  → Bitmap Combination

PRODUCTS
  → Primary Key Index Unique Scan

1.2 Join Order

후보 A
  Filtered CUSTOMERS
  → ORDERS
  → PRODUCTS

후보 B
  Filtered ORDERS
  → CUSTOMERS
  → PRODUCTS

세 Table 조인은 두 단계의 Join Tree로 표현됩니다.

첫 두 Row Source Join
→ 중간 Row Source
→ 중간 결과와 세 번째 Row Source Join

1.3 Join Method

두 Row Source마다 다음 후보를 비교합니다.

Nested Loops
Hash Join
Sort Merge Join

Optimizer는 Join Order와 Method 조합별 Cost를 비교하고 탐색 한도 안에서 최저 Cost Plan을 선택합니다.

2. Table이 아니라 Row Source를 조인한다

Row Source는 실행계획 Operation이 생성하는 행 집합입니다.

• Table Full Scan 결과
• Index Scan 후 Table Access 결과
• Filter 결과
• Inline View
• Aggregate 결과
• 이전 Join 결과
• Partition Iterator 결과

예시입니다.

TABLE ACCESS FULL DEPARTMENTS
  filter(location_id=1800)

원본 DEPARTMENTS 10,000행
Filter 후 A-Rows 2행

조인 입력은 원본 10,000행이 아니라 Filter를 통과한 2행 Row Source입니다.

3. Join Order와 Driving Row Source

3.1 Driving Row Source

Join Tree의 한 단계에서 먼저 행을 공급하는 입력을 Outer 또는 Driving Row Source라고 합니다. 다른 입력은 Inner 또는 Driven-to Row Source입니다.

JOIN METHOD
  Outer·Driving Row Source
  Inner·Driven-to Row Source

3.2 작은 원본 Table이 항상 먼저가 아니다

CUSTOMERS 원본 1,000,000행
  SEOUL Filter 후 300,000행

ORDERS 원본 20,000,000행
  최근 10분 Filter 후 200행

최근 주문 200행을 먼저 읽고 Customer PK를 확인하는 경로가 더 저렴할 수 있습니다.

반대로 서울 고객이 100명이고 고객별 주문 Index가 선택적이면 Customers Driving NL이 유리할 수 있습니다.

3.3 Join Order 판단 요소

• Filter 후 예상·실제 Cardinality
• 후행 Join Key Access Path
• Key당 평균 매칭 Row 수
• Row 폭과 Projection
• Join 후 중간 결과 크기
• 첫 행 응답 또는 전체 처리 목표
• Outer Join의 보존 방향
• Transformation·Partition Pruning 가능성

작은 Driving Row Source
  + 비효율적인 Inner Full Scan 반복
  → 좋은 NL Plan이 아님

조금 큰 Driving Row Source
  + Inner Unique Scan
  → 충분히 경쟁력 있을 수 있음

4. Nested Loops Join

Oracle은 작은 Data Subset, FIRST_ROWS 목표, 또는 Inner Table을 효율적으로 찾을 수 있는 Join Condition에서 NL Join을 고려합니다.

4.1 처리 구조

Outer Row 1
  → Inner Probe
Outer Row 2
  → Inner Probe
...

개념적 비용입니다.

NL 총비용
≈ Outer Row Source 생성 비용
 + Outer Row 수 × Inner 1회 탐색 비용

4.2 실행계획

NESTED LOOPS
  TABLE ACCESS FULL DEPARTMENTS
  TABLE ACCESS BY INDEX ROWID EMPLOYEES
    INDEX RANGE SCAN EMP_DEPT_IX

Outer에서 10행을 만들면 Inner Operation은 대략 10회 시작할 수 있습니다.

Outer A-Rows 10
Inner Starts 10

Outer가 1,000만 행이면 Inner Probe도 1,000만 회가 될 수 있습니다.

4.3 NL Join이 유리한 조건

• Filter 후 Outer 결과가 작음
• Inner Join Key에 효율적 Index·Partition Access
• Key당 매칭 Row 수가 작음
• 첫 행·첫 페이지 응답이 중요
• Client가 전체 결과를 읽지 않고 중단 가능
• Inner Row Source가 Cache에 잘 유지됨

4.4 위험 신호

• Outer Cardinality 과소 추정
• Inner Index Range가 넓음
• Inner Table Filter에서 대부분 탈락
• 높은 Clustering Factor와 대량 ROWID Access
• Inner Starts·Buffers 누적
• Outer·Inner가 독립인데 NL로 Cartesian 반복

Inner Starts가 큼
  → 한 번당 비용 × Starts로 누적 판단

4.5 First Row와 Full Fetch

NL Join은 Build·전체 Sort 없이 첫 Row를 빨리 반환할 수 있습니다. 그러나 전체 결과를 끝까지 Fetch하면 반복 Probe 총비용이 Hash Join보다 클 수 있습니다.

첫 20행 비교
  ≠ 전체 End-of-Fetch 비교

같은 Fetch 계약으로 측정합니다.

5. Hash Join

Hash Join은 일반적으로 많은 Data를 등치 조건으로 조인할 때 고려됩니다.

5.1 Build와 Probe

작은 Post-Filter Input
  → Join Key로 Hash Table Build

다른 Input
  → Join Key Hash
  → Hash Table Probe
  → 일치 Row 반환

중요한 기준입니다.

원본 Table 크기
  X

Filter 후 Build Row 수
× Build Row 폭
  O

5.2 기본 처리

HASH JOIN
  Build Input
  Probe Input

Optimizer는 일반적으로 두 입력 중 작은 Data Set으로 Hash Table을 만들고, 다른 입력은 가장 낮은 Cost의 Access Path로 읽어 Probe합니다. 두 입력 모두 항상 Full Scan이어야 하는 것은 아닙니다.

5.3 Hash Join이 유리한 조건

• 대량 등치 Join
• NL의 반복 Index·ROWID Access가 큼
• 전체 결과를 끝까지 처리
• Build Input이 Memory에 적합
• Parallel Execution과 대량 Throughput 중요

5.4 Workarea와 Spill

Build Hash Table이 PGA Workarea에 들어가면 두 입력을 한 번씩 읽는 One-Pass에 가까운 처리가 가능합니다.

Memory가 부족하면 Hash Partition을 TEMP에 기록합니다.

Optimal
  → Memory 안에서 처리

One-Pass
  → Partition을 Disk에 쓰고 한 번 추가 처리

Multi-Pass
  → Partition을 여러 번 재분할·읽기
  → TEMP I/O 증가

실행계획에서 확인합니다.

• OMem, 1Mem, Used-Mem
• O/1/M
• Used-Tmp
• Reads·Writes·Temp Space
• Build A-Rows·Row 폭
• PGA 설정과 동시 Workarea

5.5 Hash Join의 제한

Hash Join은 기본적으로 Equality Join Key가 필요합니다.

a.key = b.key

범위·부등호 Join은 Sort Merge 또는 NL 후보가 됩니다.

6. Sort Merge Join

Sort Merge Join은 두 입력을 Join Key 순서로 준비한 뒤 병합합니다.

Input 1 Sort
Input 2 Sort
→ Merge

6.1 고려 조건

Oracle Optimizer는 대량 Data Join에서 다음 조건이면 Sort Merge를 Hash보다 선택할 수 있습니다.

• <, <=, >, >= 같은 Non-Equijoin
• 다른 Operation에서 이미 Sort가 필요
• Hash Table Spill보다 Sort·Merge가 저렴
• 입력 일부가 Index 순서로 준비됨

Oracle의 Sort Merge 구현에서는 Index로 첫 입력 Sort를 피할 수 있으나 두 번째 입력은 Sort가 필요할 수 있습니다.

6.2 장점

• Non-Equijoin 지원
• 정렬 후 Merge 단계가 순차적
• 기존 Sort·Index Order 활용 가능
• Hash Multi-Pass보다 TEMP 처리 특성이 유리할 수 있음

6.3 위험

• 두 입력 Sort 비용
• 첫 Row 반환 전 초기 지연
• Workarea 부족과 TEMP Spill
• 넓은 Row의 Sort Volume
• 중복 Join Key로 결과 Row 폭증

확인합니다.

SORT JOIN
MERGE JOIN
OMem·1Mem·Used-Mem
Used-Tmp
A-Time

7. Join Method 비교

이 표는 규칙이 아니라 출발점입니다.

8. Cardinality와 Join 선택

Cardinality는 Join Order·Method Cost의 핵심입니다.

Outer Row 과소 추정
→ NL Inner Probe 횟수 과소 평가
→ 실제 Starts·Buffers 폭증

Build Row 과소 추정
→ Hash Table Memory 과소 평가
→ TEMP Spill

Sort Input 과소 추정
→ Workarea 과소
→ Sort Spill

실행계획 아래쪽에서 E-Rows와 A-Rows가 처음 크게 갈라지는 Row Source를 찾습니다.

반복 Operation은 단위를 맞춥니다.

Expected Total
≈ E-Rows × Starts

Actual per Start
≈ A-Rows / Starts

9. Join Type과 Join Method

9.1 Join Type

어떤 Row를 결과에 보존할지 결정합니다.

• Inner Join
• Left·Right·Full Outer Join
• Semi Join
• Anti Join
• Cartesian Join

9.2 Join Method

Row Source를 어떤 Algorithm으로 연결할지 결정합니다.

• Nested Loops
• Hash
• Sort Merge

예시입니다.

Left Outer Join
  → NESTED LOOPS OUTER
  → HASH JOIN OUTER
  → MERGE JOIN OUTER

SQL의 결과 의미가 먼저이고, Optimizer는 그 의미를 보존하는 Method 후보를 비교합니다.

9.3 Outer Join과 Order 제약

Outer Join은 Preserved Row Source와 Optional Row Source가 있으므로 Inner Join보다 Join Order 변환에 제약이 있을 수 있습니다.

왼쪽 보존 Row
→ 매칭이 없어도 결과 유지

Hint로 Order를 바꿀 때 Result Semantics가 유지되는지 확인합니다.

10. Semi Join과 Anti Join

10.1 Semi Join

EXISTS나 IN에서 한 번 Match를 찾으면 Inner의 추가 Match를 결과에 중복 반환하지 않고 다음 Outer Row로 진행할 수 있습니다.

NESTED LOOPS SEMI
HASH JOIN SEMI
MERGE JOIN SEMI

10.2 Anti Join

NOT EXISTS, 일부 NOT IN, Outer Join+IS NULL Pattern에서 Match가 없는 Outer Row를 반환합니다.

NESTED LOOPS ANTI
HASH JOIN ANTI
MERGE JOIN ANTI

Anti Join은 첫 Match를 찾으면 해당 Outer Row를 버리고 Inner 탐색을 중단할 수 있습니다.

NULL 의미와 Predicate Transformation 여부를 확인합니다.

11. Cartesian Join

Join Predicate가 없으면 두 입력의 모든 조합을 만들 수 있습니다.

A 100행 × B 1,000행
→ 최대 100,000행

실행계획 예입니다.

MERGE JOIN CARTESIAN

대부분은 누락 Predicate를 확인하지만 다음은 의도적일 수 있습니다.

• 한쪽 Row Source가 1행
• 작은 Dimension 조합 생성
• Optimizer가 작은 두 집합을 먼저 결합

확인합니다.

• SQL의 Join Predicate
• 두 입력 E-Rows·A-Rows
• 예상 곱과 실제 결과
• 상위 Join에서 Filter되는지
• Business 결과 중복이 올바른지

12. Adaptive Join Method

Adaptive Query Plan은 Optimization 시 NL과 Hash 같은 여러 Subplan을 준비하고 실행 중 Row Count를 기준으로 하나를 선택할 수 있습니다.

STATISTICS COLLECTOR
  → 초기 Row 수 관찰

Row 수가 Threshold 이하
  → Nested Loops 후보

Threshold 초과
  → Hash Join 후보

DBMS_XPLAN의 ADAPTIVE Format에서 비선택 Operation은 -로 표시될 수 있습니다.

Adaptive Join이 Object Statistics 문제를 영구 해결하는 것은 아닙니다. 근본 Cardinality 오차가 반복되면 Statistics·Predicate를 보완합니다.

13. 실행계획에서 Join Tree 읽기

HASH JOIN
  NESTED LOOPS
    TABLE ACCESS FULL DEPARTMENTS
    TABLE ACCESS BY INDEX ROWID EMPLOYEES
      INDEX RANGE SCAN EMP_DEPT_IX
  TABLE ACCESS FULL JOBS

읽는 순서입니다.

1. DEPARTMENTS Row Source 생성
2. EMPLOYEES Inner Probe
3. 중간 Row Source 생성
4. JOBS Build·Probe 단계와 결합

13.1 주요 Runtime 지표

상위 Operation 통계가 하위 작업을 포함할 수 있으므로 모든 Buffers·A-Time을 단순 합산하지 않습니다.

13.2 EXPLAIN PLAN과 실제 Cursor

EXPLAIN PLAN은 설명 시점 환경의 예상 Plan이며 실제 실행 Cursor와 다를 수 있습니다.

실제 검증은 다음을 우선합니다.

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

14. Join Hint

대표 Hint입니다.

/*+ LEADING(d e) USE_NL(e) */
/*+ LEADING(d e) USE_HASH(e) */
/*+ LEADING(d e) USE_MERGE(e) */
/*+ ORDERED */

주의합니다.

• SQL Alias를 정확히 지정
• Query Block 범위 확인
• Join Order가 맞지 않으면 Method Hint가 무시될 수 있음
• Outer Join·Transformation 제약 확인
• Hint Report에서 Used·Unused·Invalid 확인
• Hint Plan의 실제 Runtime을 비교

Hint는 통계와 SQL 구조를 대신하지 않습니다. 대안 Plan 검증과 제한적 안정화 수단입니다.

15. 공정한 비교 절차

1. 같은 SQL 결과와 Join Type을 유지한다.
2. 동일 Bind 값·Data Type을 사용한다.
3. 첫 행 또는 전체 Fetch 목표를 통일한다.
4. 동일 Parallel·Optimizer·Statistics 환경을 사용한다.
5. 후보 Join Order·Method를 Hint로 제한적으로 비교한다.
6. E-Rows·A-Rows·Starts를 Row Source별 기록한다.
7. Buffers·Reads·CPU·Elapsed·P95를 반복 측정한다.
8. Hash·Sort Workarea와 TEMP Spill을 확인한다.
9. 다른 Bind·Data 분포·동시 Workload 회귀를 확인한다.
10. 근본 원인이 Cardinality라면 Statistics·Predicate를 먼저 개선한다.

16. 자주 혼동하는 판단