Nested Loops Join 기본 원리: Outer·Inner·반복 탐색

Outer Row 1
  → Inner Probe
  → Match 반환

Outer Row 2
  → Inner Probe
  → Match 반환

...

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

NL 결과 Row
≈ Outer Row 수
 × Outer 한 행당 Inner 평균 Match 수

FOR outer_row IN outer_row_source LOOP
    FOR inner_row IN inner_row_source(outer_row.join_key) LOOP
        두 Row를 결합해 반환
    END LOOP
END LOOP

원본 Table
  ≠ NL의 실제 Outer 크기

Predicate 적용 후 Row Source
  = NL 반복 횟수의 출발점

ORDERS
  한 행 = 주문 한 건

CUSTOMERS
  한 행 = 고객 한 명
  CUSTOMER_ID Primary Key

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

1. 조건을 만족하는 ORDERS Row 한 건을 읽음
2. CUSTOMER_ID를 얻음
3. CUSTOMERS_PK를 Probe
4. 고객 Row를 주문 Row와 결합
5. 다음 주문에 대해 반복

Outer A-Rows            3
CUSTOMERS_PK Starts     3
Inner 평균 Match        1
최종 Join A-Rows        3

NESTED LOOPS
  TABLE ACCESS FULL DEPARTMENTS        ← Outer
  TABLE ACCESS BY INDEX ROWID EMPLOYEES
    INDEX RANGE SCAN EMP_DEPARTMENT_IX ← Inner 탐색

NESTED LOOPS
  NESTED LOOPS
    Outer Row Source
    INDEX RANGE SCAN INNER_INDEX
  TABLE ACCESS BY INDEX ROWID INNER_TABLE

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

Outer NESTED LOOPS
  → 생성된 ROWID로 Inner Table Row를 읽음

TABLE ACCESS BY INDEX ROWID BATCHED
  INDEX RANGE SCAN

BATCHED
  → Random Access 제거 X
  → ROWID 기반 Table Access를 Batch로 개선 O

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

NESTED LOOPS
  ORDERS Row Source
  INDEX UNIQUE SCAN CUSTOMERS_PK

Index Probe
  + Table Access 여부
  = Inner 1회 비용

ORDERS 원본 1,000,000,000행
→ 날짜·상태 조건 후 5행
→ Outer 5행

첫 20행
  → 전체 Build·Sort 없이 빠를 수 있음

전체 100만행
  → 반복 Probe 총비용이 더 중요

Inner Total Buffers
≈ Inner Starts × Inner 평균 Buffers per Start

LAST_STARTS
  → 마지막 실행에서 Row Source가 시작된 횟수

LAST_OUTPUT_ROWS
  → 마지막 실행에서 Row Source가 생산한 누적 Row 수

LAST_CR_BUFFER_GETS
  → 마지막 실행의 Consistent Buffer Get

Starts
E-Rows
A-Rows
Buffers

Expected Total
≈ E-Rows × Starts

Actual Total
= A-Rows

Expected per Start
= E-Rows

Actual per Start
≈ A-Rows / Starts

Starts  = 100
E-Rows  = 5
A-Rows  = 300

예상 총 Row = 5×100 = 500
실제 총 Row = 300

예상 Start당 5행
실제 Start당 3행

| Id | Operation                              | Starts | E-Rows | A-Rows | Buffers |
|---:|----------------------------------------|-------:|-------:|-------:|--------:|
|  1 | NESTED LOOPS                           |      1 |     10 |    300 |   1,240 |
|  2 |  TABLE ACCESS FULL DEPARTMENTS         |      1 |      2 |    100 |      40 |
|  3 |  TABLE ACCESS BY INDEX ROWID BATCHED E |    100 |      5 |    300 |   1,200 |
|  4 |   INDEX RANGE SCAN EMP_DEPT_IX         |    100 |      5 |    300 |     300 |

Outer Actual Row          = 100
Inner Starts              = 100
Inner Total Output        = 300
Inner Actual per Start    = 3

Index Buffers   300
Table Buffers 1,200

→ Index에서 ROWID를 찾는 비용보다
  Table Row 방문 비용이 더 큼

TABLE ACCESS BY INDEX ROWID ORDERS
  filter(status='PAID')
  INDEX RANGE SCAN ORDERS_CUSTOMER_IX
    access(customer_id=:outer_customer_id)

Access Predicate
  → Index 탐색 범위를 정함

Table Filter
  → ROWID로 Table Row를 읽은 뒤 조건 평가

Index Entry 1,000건
→ Table Row 1,000건 접근
→ PAID 1건 반환

NESTED LOOPS
  NESTED LOOPS
    Row Source A
    Row Source B
  Row Source C

A Row 1
→ B Probe
→ A+B 중간 Row 1
→ C Probe

A Row 1
→ B에서 두 번째 Match
→ A+B 중간 Row 2
→ C Probe

A 100행
× B 평균 Match 20행
= A+B 2,000행
→ C Probe 최대 약 2,000회

STATISTICS COLLECTOR
→ 왼쪽 Row Source 실제 Row 수 관찰

Threshold 이하
→ NL 후보 사용

Threshold 초과
→ Hash 후보 사용

Plan A
  첫 20행 0.05초
  전체 100만행 30초

Plan B
  첫 20행 0.5초
  전체 100만행 8초

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

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

1. SQL_ID·Child Number·Bind를 고정한다.
2. NL 아래 논리적 Outer와 Inner Access 묶음을 식별한다.
3. Outer E-Rows·A-Rows를 비교한다.
4. Inner Starts와 Outer A-Rows의 관계를 본다.
5. Inner A-Rows/Starts로 평균 Match 수를 계산한다.
6. Index Buffers와 Table Buffers를 구분한다.
7. Access Predicate와 Table Filter를 구분한다.
8. BATCHED·Covering 여부를 확인한다.
9. First Row·Full Fetch를 동일 계약으로 측정한다.
10. Statistics 개선·Index 개선·Hash Join 대안을 검증한다.