Nested Loops Join 고급 실행 구조: ROWID Batching·Hint 검증

핵심 요약

Oracle의 Nested Loops Join은 논리적으로 Outer 한 행마다 Inner를 탐색하지만, 실제 실행계획은 다음처럼 더 세분화될 수 있습니다.

Outer Row Source
→ Inner Index Probe
→ Outer Column + Inner ROWID 중간 Row Source
→ ROWID로 Inner Table Access
→ 최종 Join Row

Oracle의 현재 NL 구현에서는 한 업무 Join에 두 개의 NESTED LOOPS Row Source가 나타날 수 있습니다.

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

핵심 해석입니다.

첫 번째 NL
  → Outer 값과 Inner Index에서 얻은 ROWID를 결합

두 번째 NL
  → 중간 ROWID로 Inner Table Row를 읽음

TABLE ACCESS BY INDEX ROWID BATCHED는 Index에서 얻은 여러 ROWID의 Table Block 방문 방식을 개선하지만 다음 작업량은 그대로 남을 수 있습니다.

• 큰 Outer Row Source
• Inner Starts 증가
• 넓은 Index Range Scan
• 과도한 후보 ROWID
• Table Filter에서 대량 탈락
• 대량 결과 Full Fetch

Hint는 다음 결정을 각각 다르게 유도합니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 조인 순서와 조인 방식 → NL 조인 고급 실행 구조·Hint 검증 범위에서 두 단계 NL, ROWID Batching, Cache 재사용, Join Hint와 실제 Cursor 검증을 다룹니다.

학습 목표

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

• 최신 NL Plan에 두 개의 NESTED LOOPS가 나타나는 이유를 설명한다.
• Inner Index Probe와 Inner Table by ROWID를 하나의 논리적 Inner Access로 읽는다.
• TABLE ACCESS BY INDEX ROWID BATCHED의 목적과 한계를 설명한다.
• Batching과 Outer 반복량 감소를 구분한다.
• Physical Read 감소와 Logical I/O·CPU 반복 비용을 구분한다.
• LEADING과 ORDERED의 우선순위·제약을 설명한다.
• USE_NL이 지정 Row Source를 Inner로 사용할 때 의미가 있음을 설명한다.
• USE_NL_WITH_INDEX의 Join Predicate Key 조건을 설명한다.
• Alias·Query Block을 정확히 지정해야 하는 이유를 설명한다.
• DISPLAY_CURSOR의 ALLSTATS LAST·PREDICATE·ALIAS·OUTLINE·NOTE로 Hint 적용을 검증한다.
• Index·NL·BATCHED Plan이 결과 순서를 보장하지 않음을 설명한다.
• Hint Plan과 기본 Plan을 동일 Bind·Fetch 조건에서 비교한다.

1. 현재 NL 실행 구조

1.1 기존에 익숙한 형태

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

논리적 흐름입니다.

Outer Row
→ Inner Index Probe
→ ROWID 획득
→ Inner Table Row 읽기

1.2 두 개의 NESTED LOOPS가 보이는 형태

Oracle의 현재 NL 구현에서는 다음 Plan이 나타날 수 있습니다.

NESTED LOOPS
  NESTED LOOPS
    TABLE ACCESS BY INDEX ROWID OUTER_TABLE
      INDEX RANGE SCAN OUTER_INDEX
    INDEX RANGE SCAN INNER_INDEX
  TABLE ACCESS BY INDEX ROWID BATCHED INNER_TABLE

각 Row Source의 역할입니다.

안쪽 NESTED LOOPS
  Outer Row Source
  + Inner Index Entry·ROWID
  → 중간 Row Source 생성

바깥 NESTED LOOPS
  중간 ROWID
  + Inner Table Row
  → 필요한 Inner Column을 포함한 결과 생성

두 NL을 각각 별개의 업무 Join으로 단정하지 않습니다. Object Alias·Predicate·Operation Parent-Child 관계를 통해 하나의 Join에서 Index Probe와 Table Access가 분리된 구조인지 확인합니다.

1.3 Covering Index

필요한 Inner Predicate와 반환 Column을 모두 Index에서 해결하면 Table Access가 생략될 수 있습니다.

NESTED LOOPS
  Outer Row Source
  INDEX RANGE SCAN INNER_COVERING_INDEX

이 경우에도 Index Entry 수와 Leaf Scan 비용은 남습니다. Covering을 위해 Column을 과도하게 추가하면 Index 폭·LEAF_BLOCKS·DML·Redo·Buffer Cache 비용이 증가할 수 있습니다.

2. TABLE ACCESS BY INDEX ROWID BATCHED

2.1 기본 의미

Index Scan은 후보 Row의 ROWID를 반환합니다.

Index Entry
→ ROWID
→ Table Block·Row

BATCHED Table Access는 여러 ROWID를 모아 Table Block 방문 순서를 효율화하는 방식입니다.

ROWID를 하나씩 즉시 방문
vs
ROWID를 일정 단위로 모아 관련 Block 접근을 조정

대표 목적입니다.

• 같은 Table Block의 반복 방문 감소 가능
• Clustering이 좋지 않은 Range Scan의 Block 접근 완화
• ROWID 요청 처리 오버헤드 완화
• Buffer Cache 재사용 가능성 향상

2.2 해결하지 못하는 것

BATCHED
  ≠ Outer Starts 감소
  ≠ Index Leaf 후보 감소
  ≠ 후보 ROWID 제거
  ≠ Table Filter 제거
  ≠ Table Access 제거

다음 Plan은 Table Row를 계속 읽는다는 뜻입니다.

TABLE ACCESS BY INDEX ROWID BATCHED ORDERS
  INDEX RANGE SCAN ORDERS_CUSTOMER_IX

Index 후보 100,000건 중 Table Filter 후 1,000건만 반환한다면 Batching으로 순서는 개선될 수 있어도 대량 Table 방문 자체는 남습니다.

2.3 Operation 이름만으로 단정하지 않는 항목

BATCHED 표기만으로 다음을 확정하지 않습니다.

• 고정 Batch 크기
• 특정 Multiblock Read 방식
• 실제 Physical Read Call 수
• 결과 Row 순서
• 성능 개선 크기

확인은 Starts·A-Rows·Buffers·Reads·A-Time과 Wait·Trace가 필요합니다.

3. Buffer Cache 재사용과 반복 비용

Outer에서 같은 Inner Key 또는 인접 Key가 반복되면 같은 Index Branch·Leaf·Table Block을 다시 방문할 수 있습니다.

Outer Key
10, 10, 10, 20, 20

Block이 Cache에 남아 있으면 Physical Read가 줄 수 있습니다. 그러나 다음 비용은 남습니다.

• Consistent Buffer Get
• 반복 Index Probe
• CPU·Predicate 평가
• Latch·Mutex 관련 처리
• 결과 Row 생성·전달

Physical Read 0
  ≠ NL 반복 비용 0

Buffers·CPU·Starts가 큼
  → Cache Hit 상태에서도 비효율 가능

3.1 Outer Key 정렬

Outer를 Join Key 순으로 정렬하면 인접 Probe의 Cache 재사용성이 좋아질 수 있습니다.

하지만 다음 비용이 추가됩니다.

• Sort CPU
• Workarea Memory
• TEMP Spill 가능성
• 첫 행 응답 지연

정렬은 일반 규칙이 아니라 측정할 대안입니다. 결과 순서가 목적이면 최종 ORDER BY를 명시합니다.

4. Join Order Hint

4.1 LEADING

SELECT /*+ LEADING(c o) */
       ...
FROM   customers c
JOIN   orders o
  ON   o.customer_id=c.customer_id;

LEADING(c o)는 c에서 시작해 o를 연결하는 Join Order를 유도합니다.

주의합니다.

• Join Graph 의존성 때문에 지정 순서로 먼저 Join할 수 없으면 무시될 수 있음
• 서로 충돌하는 여러 LEADING Hint는 무시될 수 있음
• ORDERED가 함께 있으면 ORDERED가 LEADING보다 우선함
• Query Transformation 후 Query Block·Alias가 달라질 수 있음

4.2 ORDERED

SELECT /*+ ORDERED */
       ...
FROM   customers c
JOIN   orders o
  ON   o.customer_id=c.customer_id
JOIN   order_items i
  ON   i.order_id=o.order_id;

ORDERED는 FROM 절의 작성 순서를 Join Order로 사용하도록 유도합니다.

ORDERED
  → SQL Text 순서와 강하게 결합

LEADING
  → 원하는 선두·부분 순서를 명시

SQL Refactoring으로 FROM 절이 바뀌면 ORDERED의 의미도 달라질 수 있습니다.

5. Join Method Hint

5.1 USE_NL

SELECT /*+ LEADING(c o) USE_NL(o) */
       ...
FROM   customers c
JOIN   orders o
  ON   o.customer_id=c.customer_id;

USE_NL(o)는 o를 다른 Row Source와 연결할 때 o를 Inner로 하는 NL Join을 유도합니다.

중요합니다.

USE_NL(o)
  → Join Order를 직접 지정하지 않음
  → o가 Outer이면 Hint가 의도대로 적용되지 않을 수 있음

따라서 LEADING·ORDERED와 함께 대안 Plan을 시험합니다.

5.2 NO_USE_NL

SELECT /*+ NO_USE_NL(o) */
       ...

o를 Inner로 하는 NL Join 후보를 제외하도록 유도합니다. Hash·Merge가 반드시 선택된다는 보장은 없으며 다른 유효한 후보 중 Cost를 비교합니다.

5.3 USE_NL_WITH_INDEX

SELECT /*+
          LEADING(c o)
          USE_NL_WITH_INDEX(o orders_customer_ix)
       */
       c.customer_id,
       o.order_id
FROM   customers c
JOIN   orders o
  ON   o.customer_id=c.customer_id;

의미입니다.

o를 NL Inner로 사용
+
ORDERS_CUSTOMER_IX의 Key 중 적어도 하나에
Join Predicate를 탐색 Key로 사용

공식 조건입니다.

• Index를 지정하지 않으면, Join Predicate를 Index Key로 사용할 수 있는 어떤 Index가 필요
• Index를 지정하면, 그 Index가 Join Predicate 중 적어도 하나를 Key로 사용할 수 있어야 함
• Index 존재만으로 충분하지 않음
• Join Predicate·Column 순서·Data Type·Transformation을 확인

6. Access Path Hint와의 조합

SELECT /*+
          LEADING(c o)
          USE_NL(o)
          INDEX(o orders_customer_date_ix)
       */
       ...

각 Hint가 제어하는 가설입니다.

LEADING(c o)
  → c를 먼저 읽고 o를 다음에 연결

USE_NL(o)
  → o를 NL Inner로 연결

INDEX(o idx)
  → o의 Access Path로 지정 Index Scan 유도

하나의 Hint가 Join Order·Method·Access Path를 모두 결정한다고 생각하지 않습니다.

강제 Plan이 더 느릴 수 있는 대표 사례입니다.

• Outer A-Rows가 예상보다 큼
• 지정 Index Range가 넓음
• 많은 후보 ROWID
• Table Filter에서 대량 탈락
• 인기 Bind 값
• Full Fetch 작업
• Index Clustering이 불리함

7. Alias와 Query Block

7.1 Alias 사용

FROM orders o

Alias가 있으면 Hint에서도 o를 사용합니다.

/*+ USE_NL(o) INDEX(o orders_customer_ix) */

다음은 대상이 일치하지 않을 수 있습니다.

/*+ USE_NL(orders) */
FROM orders o

7.2 Query Block

Inline View·CTE·Subquery·Transformation이 있으면 Object Alias가 여러 Query Block에 존재하거나 사라질 수 있습니다.

SELECT /*+ QB_NAME(main) */
       ...
FROM (
    SELECT /*+ QB_NAME(order_qb) */
           ...
    FROM orders o
) v;

확인 도구입니다.

• QB_NAME
• +ALIAS
• +OUTLINE
• Query Block Name
• Object Alias
• Hint Report의 Used·Unused·Invalid

Hint Text만 보고 적용됐다고 판단하지 않습니다.

8. DBMS_XPLAN으로 검증

실행 통계를 수집합니다.

SELECT /*+
          LEADING(c o)
          USE_NL_WITH_INDEX(o orders_customer_ix)
          GATHER_PLAN_STATISTICS
       */
       ...
FROM   customers c
JOIN   orders o
  ON   o.customer_id=c.customer_id
WHERE  c.grade=:grade;

실제 Cursor를 확인합니다.

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

8.1 Format별 확인 내용

8.2 실제 적용 판단

확인합니다.

1. 의도한 Join Order인가?
2. Hint 대상 Object가 Inner인가?
3. NL Operation이 실제로 사용됐는가?
4. 지정 Index가 사용됐는가?
5. USE_NL_WITH_INDEX의 Join Predicate Key 조건을 충족했는가?
6. Starts·A-Rows·Buffers·Reads가 개선됐는가?
7. 결과 Row와 업무 의미가 같은가?
8. 다른 Bind·Full Fetch에서 회귀하지 않는가?

9. Plan 비교

후보 A: NL·Index·BATCHED

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

후보 B: Hash Join

HASH JOIN
  TABLE ACCESS FULL CUSTOMERS
  TABLE ACCESS FULL ORDERS

공정한 비교 조건입니다.

• 동일한 SQL 결과
• 동일한 Bind 값·Data Type
• 동일한 Statistics·Optimizer Parameter
• 동일한 Parallel Degree
• 동일한 Fetch 범위
• 동일한 Projection
• 유사한 Cache 상태

측정합니다.

10. 결과 순서

SQL 결과 순서는 ORDER BY가 있을 때만 보장됩니다.

다음은 순서를 보장하지 않습니다.

• Index Range Scan
• Index Descending Scan만 존재
• NL Join
• BATCHED ROWID Access
• 현재 Version에서 우연히 같은 순서
• Hint로 Access Path 고정

결정적 정렬 예입니다.

ORDER BY order_date DESC,
         order_id DESC

order_date가 같은 Row의 순서를 안정화하려면 order_id 같은 Unique Tie-Breaker를 추가합니다.

Hint Plan을 변경하면 행 생산 순서도 달라질 수 있으므로 결과 순서에 Plan 특성을 이용하지 않습니다.

11. 안전한 적용 절차

1. 실제 SQL_ID·Child·Bind·Fetch 목표를 고정한다.
2. 두 개의 NL이 하나의 Index·Table Inner Access를 분리한 구조인지 확인한다.
3. Inner Starts·Index A-Rows·Table A-Rows를 비교한다.
4. BATCHED 아래 Table Buffers·Filter 탈락을 확인한다.
5. 결과 순서는 ORDER BY로 정의한다.
6. LEADING 또는 ORDERED로 Join Order 가설을 시험한다.
7. USE_NL 대상이 실제 Inner Alias인지 확인한다.
8. USE_NL_WITH_INDEX의 Join Predicate Key 조건을 확인한다.
9. DISPLAY_CURSOR와 Hint Report로 실제 적용 여부를 확인한다.
10. 기본 Plan과 동일 Bind·Fetch Runtime 및 다른 Bind 회귀를 비교한다.

자주 혼동하는 판단