Nested Loops Join 튜닝: 선행 집합·Predicate·ROWID 비용

핵심 요약

Nested Loops Join 튜닝은 단순히 Inner Join Key에 Index를 만드는 작업이 아닙니다.

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

Inner 1회 탐색 비용을 더 나누면 다음과 같습니다.

Inner 1회 비용
≈ Index Root·Branch 탐색
 + 조건 범위의 Leaf Entry Scan
 + 후보 ROWID 수
 + ROWID Table Block 방문
 + Index·Table Filter 평가

따라서 NL 튜닝의 핵심 질문은 두 가지입니다.

1. Outer에서 불필요한 반복 횟수가 얼마나 만들어지는가?
2. Inner 한 번의 Probe에서 불필요한 Leaf·ROWID·Table 작업이 얼마나 발생하는가?

실행계획에서는 다음 흐름으로 확인합니다.

Outer Index A-Rows
→ Outer Table A-Rows
→ Inner Starts
→ Inner Index A-Rows
→ Inner Table A-Rows
→ Join 최종 A-Rows

Oracle의 BATCHED ROWID Access는 ROWID를 묶어 Table Block 방문을 개선하지만, 후보 ROWID 자체와 Table Filter 탈락을 제거하지는 않습니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 조인 순서와 조인 방식 → NL 조인 튜닝 범위에서 Outer 후보 축소, Inner Predicate 위치, Index Column 구성과 ROWID 비용을 다룹니다.

학습 목표

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

• NL 비용을 Outer 반복 수와 Inner 1회 비용으로 분해한다.
• Outer Index 후보와 Outer Table Filter 후 결과를 구분한다.
• Access Predicate·Index Filter·Table Filter를 Operation 위치와 함께 설명한다.
• 복합 Index에서 선행 등치 조건과 첫 Range 조건 이후 Column의 역할을 설명한다.
• Inner Index A-Rows와 Table A-Rows 차이로 ROWID 낭비를 계산한다.
• 두 개의 NESTED LOOPS와 BATCHED ROWID Access를 해석한다.
• Covering Index가 Table Access를 제거할 수 있는 조건을 설명한다.
• Starts·E-Rows·A-Rows를 Total·Per-Start 단위로 맞춘다.
• Cardinality·Histogram·Column Correlation·Bind Skew를 NL 반복 비용과 연결한다.
• NL 유지·Join Order 변경·Hash Join 전환을 동일 결과·Bind·Fetch 조건에서 비교한다.

본문

업무 예제와 기본 실행 흐름

CUSTOMERS
  한 행 = 고객 한 명

ORDERS
  한 행 = 주문 한 건
  고객 한 명은 여러 주문 보유 가능

특정 지역의 VIP 고객이 최근 결제한 주문을 조회합니다.

SELECT /*+ GATHER_PLAN_STATISTICS */
       c.customer_id,
       c.customer_name,
       o.order_id,
       o.order_date,
       o.amount
FROM   customers c
JOIN   orders o
  ON   o.customer_id = c.customer_id
WHERE  c.region_code = :region_code
AND    c.grade = 'VIP'
AND    o.order_date >= :from_date
AND    o.status = 'PAID';

CUSTOMERS가 Outer, ORDERS가 Inner라면 논리적 흐름은 다음과 같습니다.

CUSTOMERS 조건을 만족한 Row 1건
→ CUSTOMER_ID 전달
→ ORDERS Index Probe
→ 주문 ROWID Table Access
→ 날짜·상태 조건 확인
→ Match 반환
→ 다음 고객 반복

개념적 Plan입니다.

NESTED LOOPS
  TABLE ACCESS BY INDEX ROWID CUSTOMERS
    INDEX RANGE SCAN CUSTOMERS_REGION_IX
  TABLE ACCESS BY INDEX ROWID ORDERS
    INDEX RANGE SCAN ORDERS_CUSTOMER_IX

성능 조건입니다.

작은 Outer 결과
×
작은 Inner 1회 후보
×
적은 Table Filter 탈락

Outer 후보 행 줄이기

후보와 최종 Row를 구분한다

다음 Index만 있다고 가정합니다.

CUSTOMERS_REGION_IX(region_code)

region_code 조건 후보 50,000행
→ Table Access
→ grade='VIP' Filter
→ 최종 Outer 500행

Outer Index A-Rows = 50,000
Outer Table A-Rows =    500

Outer Table에서 49,500행이 탈락했습니다.

이 낭비는 두 비용을 만듭니다.

1. 불필요한 CUSTOMER Table ROWID Access
2. 실제 500행만 Inner Probe를 수행하지만 Optimizer가 후보·최종 분포를 틀리게 추정하면 Join Order·Method가 왜곡될 가능성

복합 Index 후보

CUSTOMERS_REGION_GRADE_IX(region_code, grade)

두 Equality Predicate를 Index Access에 함께 사용하면 Table 방문 전에 후보를 줄일 수 있습니다.

하지만 다음을 함께 확인합니다.

• 다른 Query의 선두 Column 사용
• region_code와 grade 분포·상관관계
• Index 중복
• 정렬 요구
• 반환 Column Covering 가능성
• DML·Redo·공간 비용

한 SQL의 후보 감소
  ≠ 전체 Workload 최적 Index

Outer E-Rows 오차

Outer를 20행으로 예상했으나 실제 2,000행이면 Inner 반복도 약 100배 과소 평가될 수 있습니다.

Outer E-Rows 20
Outer A-Rows 2,000

→ Inner Starts 예상 방향 20
→ 실제 Starts 방향 2,000

확인합니다.

• NUM_DISTINCT·Density·Histogram
• (region_code,grade) Column Group Statistics
• Bind Peeking·Child Cursor
• Statistics 수집 시점
• Predicate Data Type·Implicit Conversion

최신 NL 실행계획 구조

Oracle 11g 이후 Plan에서는 Index ROWID 생성과 Table Access가 분리돼 두 개의 NESTED LOOPS로 보일 수 있습니다.

NESTED LOOPS
  NESTED LOOPS
    Outer Row Source
    INDEX RANGE SCAN ORDERS_INDEX
  TABLE ACCESS BY INDEX ROWID ORDERS

해석합니다.

Inner NESTED LOOPS
  → Outer Row와 Inner Index Entry·ROWID 결합

Outer NESTED LOOPS
  → 생성한 ROWID로 Inner Table Row 읽기

따라서 Inner Access를 다음 묶음으로 읽습니다.

Inner Index Scan
+
Inner Table by ROWID

Index A-Rows와 Table A-Rows 차이, 각각의 Buffers를 함께 비교합니다.

Access Predicate·Index Filter·Table Filter

Predicate Information은 filter라는 단어만 보지 않고 어느 Operation에 표시됐는지 확인합니다.

예시입니다.

5 - access(
      "O"."CUSTOMER_ID"="C"."CUSTOMER_ID"
      AND "O"."STATUS"='PAID'
      AND "O"."ORDER_DATE">=:FROM_DATE
    )

4 - filter("O"."CHANNEL_CODE"='APP')

CUSTOMER_ID·STATUS·ORDER_DATE가 Index Access에 사용되면 Leaf Scan 후보를 줄일 수 있습니다.

CHANNEL_CODE가 Table Filter라면 다음 비용이 발생합니다.

Index 후보 ROWID 생성
→ ORDERS Table Block 방문
→ CHANNEL_CODE 평가
→ 탈락 Row 폐기

복합 Index Column 순서

현재 조건입니다.

o.customer_id = c.customer_id
AND o.order_date >= :from_date
AND o.status = 'PAID'

후보 A

(customer_id, order_date, status)

customer_id Equality
→ 특정 고객 범위 시작

order_date Range
→ 해당 고객의 날짜 구간 제한

status Equality
→ 첫 Range 뒤에 있어
  일부 Plan에서는 Index Filter 역할이 커질 수 있음

후보 B

(customer_id, status, order_date)

customer_id Equality
+ status Equality
→ 고객·상태 조합 범위

order_date Range
→ 그 조합 안에서 날짜 범위

후보 B가 이 SQL에는 더 좁은 Start·Stop Key를 만들 수 있지만 항상 정답은 아닙니다.

비교 기준입니다.

• 고객별 Status별 주문 건수
• 날짜 범위 크기
• Status Data Skew
• ORDER BY order_date 활용
• 다른 Query의 Predicate 조합
• Index Skip·Range 가능성
• Index 폭·DML 비용

등치 Column은 항상 Range 앞
  → 단순 규칙으로 결정하지 않음

실제 Predicate 조합·분포·정렬·Workload
  → Column 순서 결정

Inner ROWID Table Access 비용

다음 통계를 가정합니다.

Inner Starts              2,000
Inner Index A-Rows       30,000
Inner Table A-Rows        1,500

계산합니다.

Index 후보 per Start
= 30,000 / 2,000
= 15행

최종 Table 반환 per Start
= 1,500 / 2,000
= 0.75행

후보와 최종 Row 차이입니다.

Index 후보 30,000
→ Table 접근
→ 최종 1,500
→ 최대 28,500 후보가 Table 단계에서 탈락 가능

우선 확인합니다.

• Table Filter Column
• Index에 없는 반환 Column
• Clustering Factor
• TABLE ACCESS BY INDEX ROWID BATCHED
• Table Buffers
• Row Migration·Chain
• Partition Pruning 여부

BATCHED ROWID Access

TABLE ACCESS BY INDEX ROWID BATCHED
  INDEX RANGE SCAN

Oracle은 Index에서 ROWID를 몇 개씩 모으고 Table Block 순서에 가깝게 접근해 동일 Block 방문 횟수를 줄이려 합니다.

BATCHED의 이점
  → Block 방문 순서 개선

BATCHED가 해결하지 못하는 것
  → 과도한 Index 후보 Entry
  → 많은 후보 ROWID
  → Table Filter 탈락
  → Outer Starts 폭증

따라서 BATCHED Operation이 보인다는 이유만으로 ROWID 비용이 적다고 판단하지 않습니다.

Covering Index

Query에 필요한 Inner Column이 모두 Index에 있으면 Table by ROWID를 생략할 수 있습니다.

SELECT o.order_id,
       o.order_date
FROM   customers c
JOIN   orders o
  ON   o.customer_id=c.customer_id
WHERE  ...

후보 Index가 다음 Column을 포함하고 있다고 가정합니다.

(customer_id,status,order_date,order_id)

Optimizer가 Index만으로 Predicate와 Projection을 해결하면 다음 형태가 가능합니다.

NESTED LOOPS
  Outer Row Source
  INDEX RANGE SCAN ORDERS_COVER_IX

Trade-off입니다.

• Index Entry 폭 증가
• LEAF_BLOCKS 증가
• Buffer Cache 점유
• DML·Redo·Undo 증가
• 다른 Index와 중복
• Range Scan당 읽는 Byte 증가

Table Access 제거 이득
vs
Index 유지 비용

전체 Workload에서 비교합니다.

실행통계로 반복 비용 계산

다음 Plan을 가정합니다.

| Id | Operation                           | Starts | E-Rows | A-Rows | Buffers |
|---:|-------------------------------------|-------:|-------:|-------:|--------:|
|  1 | NESTED LOOPS                        |      1 |     40 |  1,500 |  42,000 |
|  2 |  TABLE ACCESS BY INDEX ROWID CUST   |      1 |     20 |  2,000 |   4,000 |
|  3 |   INDEX RANGE SCAN CUST_REGION_IX   |      1 |     20 | 25,000 |     500 |
|  4 |  TABLE ACCESS BY INDEX ROWID ORDERS |  2,000 |      2 |  1,500 |  38,000 |
|  5 |   INDEX RANGE SCAN ORDERS_CUST_IX   |  2,000 |      2 | 30,000 |   8,000 |

Outer

Outer Index 후보     25,000
Outer 최종 Row        2,000
Outer 후보 탈락      23,000

Outer E-Rows 20
Outer A-Rows 2,000
→ 100배 과소 추정

Inner

Inner Starts              2,000
Index 실제 총 후보       30,000
Table 실제 최종           1,500

Index 후보 per Start = 15
최종 Row per Start   = 0.75

예상과 실제 단위

Expected Total
≈ Starts × E-Rows
= 2,000×2
= 4,000

Actual Total
= 30,000

Inner 한 번의 후보도 예상보다 7.5배 큽니다.

Buffer

Index Buffers  8,000
Table Buffers 38,000

Table by ROWID Branch의 비용이 더 큽니다.

주의합니다.

• Parent Operation Buffer에는 Child 작업이 포함될 수 있음
• 모든 Plan Line Buffer를 단순 합산하지 않음
• Statement 총량과 비용 집중 Branch를 분리함

Cardinality·Skew·Bind

다음 분포를 가정합니다.

일반 고객
  최근 PAID 주문 0~2건

인기 고객
  최근 PAID 주문 50,000건

평균 통계만으로 모든 고객을 같은 Cardinality로 추정하면 한 NL Plan이 모든 Bind에 적합하지 않을 수 있습니다.

확인합니다.

• 고객별 주문 수 분포
• Popular Customer
• status Histogram
• (customer_id,status) 상관관계
• Bind Peeking 값
• Child Cursor
• IS_BIND_SENSITIVE
• IS_BIND_AWARE
• 값별 Starts·A-Rows·Buffers

Adaptive Cursor Sharing은 Bind Cardinality 범위에 따라 다른 Child Plan을 사용할 수 있습니다.

일반 고객
  → NL·Index Probe 후보

인기 고객
  → Hash·Scan 후보

NL 유지·Join Order 변경·Hash 전환

NL 유지

다음이면 NL 개선이 우선일 수 있습니다.

• Outer 실제 Row가 작음
• Inner 후보 per Start가 작음
• Table Filter 탈락이 적음
• First Row·부분 Fetch 중요
• Bind별 규모가 안정적

Join Order 변경

현재:

CUSTOMERS Filter
→ 고객별 ORDERS Probe

대안:

최근 PAID ORDERS Filter
→ CUSTOMER PK Probe

비교합니다.

CUSTOMERS 선행 비용
≈ 고객 Outer 생성
 + 고객 수×주문 Probe

ORDERS 선행 비용
≈ 최근 주문 Outer 생성
 + 주문 수×고객 PK Probe

원본 Table 크기가 아니라 Filter 후 A-Rows와 다음 1회 비용으로 판단합니다.

Hash Join

다음이면 Hash Join을 비교합니다.

• Outer·Inner 실제 Row가 큼
• 대부분 결과를 End-of-Fetch까지 읽음
• NL Table Block Random Access 누적
• Partition Pruning 후 Scan이 효율적
• Build Data가 Workarea에 적합

NL
  반복 Index·ROWID Access

Hash
  Build Scan + Hash Workarea + Probe Scan

동일 결과·Bind·Fetch 범위에서 비교합니다.

진단 절차

1. SQL_ID·Child Number·Bind·Fetch 목표를 고정한다.
2. Outer Index A-Rows와 Outer Table A-Rows를 비교한다.
3. Outer의 최초 E/A 오차 Predicate를 찾는다.
4. Inner Starts를 Outer A-Rows와 연결한다.
5. Inner Access·Index Filter·Table Filter 위치를 확인한다.
6. Inner Index A-Rows/Starts로 후보 per Start를 계산한다.
7. Inner Table A-Rows/Starts로 최종 Match per Start를 계산한다.
8. Index·Table Buffers와 BATCHED·Clustering Factor를 확인한다.
9. 복합 Index·Covering·Statistics·Join Order·Hash 대안을 실험한다.
10. 동일 결과·Bind·Fetch에서 Runtime과 다른 Bind 회귀를 검증한다.

자주 혼동하는 판단