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

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

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

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

CUSTOMERS
  한 행 = 고객 한 명

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

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 조건을 만족한 Row 1건
→ CUSTOMER_ID 전달
→ ORDERS Index Probe
→ 주문 ROWID Table Access
→ 날짜·상태 조건 확인
→ Match 반환
→ 다음 고객 반복

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 탈락

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

CUSTOMERS_REGION_GRADE_IX(region_code, grade)

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

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

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

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 Index Scan
+
Inner Table by ROWID

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

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

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

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

(customer_id, order_date, status)

customer_id Equality
→ 특정 고객 범위 시작

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

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

(customer_id, status, order_date)

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

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

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

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

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행

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

TABLE ACCESS BY INDEX ROWID BATCHED
  INDEX RANGE SCAN

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

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

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

(customer_id,status,order_date,order_id)

NESTED LOOPS
  Outer Row Source
  INDEX RANGE SCAN ORDERS_COVER_IX

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

| 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 Index 후보     25,000
Outer 최종 Row        2,000
Outer 후보 탈락      23,000

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

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

Index Buffers  8,000
Table Buffers 38,000

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

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

일반 고객
  → NL·Index Probe 후보

인기 고객
  → Hash·Scan 후보

CUSTOMERS Filter
→ 고객별 ORDERS Probe

최근 PAID ORDERS Filter
→ CUSTOMER PK Probe

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

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

NL
  반복 Index·ROWID Access

Hash
  Build Scan + Hash Workarea + Probe Scan

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 회귀를 검증한다.