ROWID Batching과 결과 순서: TABLE ACCESS BY INDEX ROWID BATCHED

핵심 요약

TABLE ACCESS BY INDEX ROWID BATCHED는 Oracle이 Index에서 몇 개의 ROWID를 가져온 뒤 Table Row를 Block 순서로 접근하려고 시도하는 Table Access 방식입니다.

Oracle 공식 설명의 핵심입니다.

Index에서 일부 ROWID 획득
→ ROWID가 가리키는 Table Block을 고려
→ Block 순서에 가깝게 Row 접근 시도
→ 같은 Block을 여러 번 접근하는 횟수 감소 목표

대표 Plan입니다.

TABLE ACCESS BY INDEX ROWID BATCHED EMP
  INDEX RANGE SCAN EMP_DEPT_JOB_IX

BATCHED가 의미하는 것은 다음과 같습니다.

의미
  → ROWID Table Access의 Block 방문 효율 개선 시도

의미하지 않는 것
  → 후보 ROWID 수 감소
  → Table Access 제거
  → 정확한 Batch 크기 공개
  → Multiblock Physical Read 보장
  → Index Key 순서와 같은 결과 순서 보장

결과 순서는 실행계획 모양이 아니라 SQL의 ORDER BY로 정의합니다.

업무 순서 필요
  → ORDER BY 필수

ORDER BY 없음
  → Index Scan·BATCHED·병렬·Join·Plan 변경에 따라 순서 변경 가능

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 테이블 액세스 최소화 범위에서 ROWID Batching, 결과 순서, Sort와 Top-N을 다룹니다.

학습 목표

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

• 일반 TABLE ACCESS BY INDEX ROWID와 BATCHED의 목적 차이를 설명한다.
• BATCHED가 일부 ROWID를 Block 순서로 접근하려는 이유를 설명한다.
• BATCHED가 후보 ROWID·Table Access·Multiblock Read를 보장하지 않는 이유를 설명한다.
• Index Key 순서와 SQL 결과 순서를 구분한다.
• BATCHED+Sort와 Ordered Index 경로를 비교한다.
• ORDER BY의 Index 호환 조건과 혼합 ASC·DESC를 설명한다.
• Top-N에서 동률 해소 컬럼과 Stopkey를 설명한다.
• Clustering Factor와 BATCHED 기대효과의 관계를 설명한다.
• Covering Index가 BATCHED 자체를 제거할 수 있는 이유를 설명한다.
• LAST_STARTS·LAST_OUTPUT_ROWS·LAST_CR_BUFFER_GETS·LAST_DISK_READS로 실제 비용을 검증한다.

1. 일반 ROWID Table Access

다음 Index를 가정합니다.

CREATE INDEX emp_dept_job_ix
ON emp(deptno, job, empno);

Query입니다.

SELECT empno,
       ename,
       job
FROM   emp
WHERE  deptno = 20;

Index에는 deptno, job, empno와 ROWID가 저장됩니다. ename은 Index에 없으므로 Heap Table Row를 읽어야 합니다.

INDEX RANGE SCAN EMP_DEPT_JOB_IX
  → deptno=20 Entry와 ROWID 반환

TABLE ACCESS BY INDEX ROWID EMP
  → ROWID가 가리키는 Row에서 ename 읽기

일반적인 흐름입니다.

ROWID A → Table Block 100
ROWID B → Table Block 205
ROWID C → Table Block 100
ROWID D → Table Block 310
ROWID E → Table Block 205

ROWID 순서대로 바로 접근하면 다음과 같은 Block 전환이 발생할 수 있습니다.

100 → 205 → 100 → 310 → 205

2. BATCHED 접근의 공식 목적

Oracle 공식 문서는 BATCHED를 다음처럼 설명합니다.

Database가 Index에서 일부 ROWID를 가져오고,
Clustering을 개선하며 같은 Block 접근 횟수를 줄이기 위해
Block 순서로 Row에 접근하려고 시도한다.

개념적 예입니다.

가져온 ROWID Batch

A → Block 100
B → Block 205
C → Block 100
D → Block 310
E → Block 205

접근 시도입니다.

Block 100 → A, C
Block 205 → B, E
Block 310 → D

이를 통해 같은 Block을 다시 찾는 횟수를 줄일 수 있습니다.

2.1 정확히 단정하면 안 되는 것

실행계획에 BATCHED가 있다는 사실만으로 다음을 알 수 없습니다.

• Batch에 포함되는 정확한 ROWID 수
• 실제 내부 정렬 알고리즘
• 한 I/O 요청의 Block 수
• 모든 후보가 Block 순서로 완벽하게 재배치됐는지
• Buffer Cache Hit·Physical Read 비율
• 실제 성능 향상 정도

실제 Buffers·Reads·Elapsed로 확인합니다.

3. BATCHED의 이득 조건

이득이 생길 가능성이 큰 조건입니다.

• Index가 복수의 후보 ROWID를 반환
• 여러 후보가 같은 Table Block을 공유
• 원래 Index Key 순서와 Table Block 순서가 다름
• 필요한 Column이 Index 밖에 있어 Table Access가 필수
• Sort·Join보다 Table Random Access 비용이 큼
• Cache와 Workload에서 Block 재방문이 실제 병목

효과가 제한될 수 있는 조건입니다.

• 후보가 0~2건으로 매우 적음
• 후보마다 거의 다른 Table Block을 가리킴
• Clustering Factor가 좋아 기존 순서로도 Block 재사용이 큼
• Covering Index로 Table Access가 없음
• Index Leaf Scan 자체가 병목
• Sort·Hash Join·TEMP가 전체 비용을 지배
• 후보 ROWID가 지나치게 많아 Full Scan이 더 저렴

BATCHED 효과
  = 같은 Block Row를 묶을 여지
  × Table Access가 전체 비용에서 차지하는 비중

4. BATCHED의 한계

4.1 후보 ROWID를 줄이지 않는다

Index A-Rows 100,000
Table A-Rows   1,000

BATCHED는 100,000개 후보의 접근 순서를 개선할 수 있지만 1,000개로 줄이지는 않습니다.

우선 검토합니다.

• Table Filter Column을 Index에 추가
• Equality Column을 첫 Range 앞에 배치
• 더 적합한 복합 Index
• Partition Pruning
• 결과 비율이 크면 Full Scan

4.2 Table Access를 제거하지 않는다

TABLE ACCESS BY INDEX ROWID BATCHED는 이름 그대로 Table Access입니다.

Covering Index
  → Table Access 자체 제거 가능

BATCHED
  → Table Access 유지
  → 접근 방법만 개선 시도

4.3 Multiblock Read를 보장하지 않는다

BATCHED의 공식 핵심은 Block 순서 접근 시도입니다. Operation 이름만으로 Database가 여러 비연속 Table Block을 한 번의 Physical I/O로 읽는다고 단정하지 않습니다.

확인합니다.

• LAST_DISK_READS
• I/O Request·Wait
• Cache 상태
• Storage Prefetch
• 실제 Elapsed

5. Index Key 순서와 SQL 결과 순서

EMP_DEPT_JOB_IX(deptno,job,empno)는 다음 순서로 정렬됩니다.

deptno
  → 같은 deptno 안에서 job
    → 같은 deptno·job 안에서 empno

deptno=20 Range Scan은 Index Entry를 job,empno 방향으로 읽을 수 있습니다.

하지만 다음 SQL은 결과 순서를 보장하지 않습니다.

SELECT empno,
       ename,
       job
FROM   emp
WHERE  deptno = 20;

순서를 바꿀 수 있는 요소입니다.

• BATCHED Table Access
• 병렬 실행
• 다른 Index·Full Scan 선택
• Join 방식·Join 순서
• Partition Iterator
• Adaptive·Optimizer 변화
• Database Version
• Row Migration·Block 상태

업무상 순서가 필요하면 명시합니다.

ORDER BY job, empno

Index가 정렬된 구조
  ≠ ORDER BY 없는 결과의 순서 계약

6. ORDER BY가 있을 때의 두 경로

6.1 BATCHED + Sort

SORT ORDER BY
  TABLE ACCESS BY INDEX ROWID BATCHED EMP
    INDEX RANGE SCAN EMP_DEPT_JOB_IX

가능한 이점입니다.

• Table Block 접근 효율 개선
• 그 뒤 SQL이 요구한 순서로 Sort

비용입니다.

• Table Buffers·Reads
• Sort CPU
• Workarea Memory
• TEMP Spill
• 첫 행 지연
• 전체 Fetch 시간

6.2 Ordered Index + 일반 ROWID Access

TABLE ACCESS BY INDEX ROWID EMP
  INDEX RANGE SCAN EMP_DEPT_JOB_IX

Index 순서와 ORDER BY job,empno가 호환되고 Table Access 과정에서 순서를 유지할 수 있다면 Sort를 생략할 수 있습니다.

가능한 이점입니다.

• Sort 제거
• 첫 행을 빠르게 반환할 가능성
• Top-N 조기 중단

비용입니다.

• BATCHED보다 같은 Block 재접근이 많아질 수 있음
• 나쁜 Clustering에서 Table Buffers 증가 가능

6.3 총비용 비교

BATCHED + Sort
  Table 방문 절감
  + Sort 비용

Ordered Index Path
  Sort 제거
  + Table Block 재접근 가능성

Plan 이름만으로 결론 내리지 않고 실제 사용자 목표와 통계로 비교합니다.

7. ORDER BY 호환성

다음 Index가 있습니다.

CREATE INDEX status_hist_x1
ON status_hist(
  equipment_id,
  changed_at DESC,
  history_id DESC
);

다음 Query는 정렬과 호환됩니다.

WHERE equipment_id = :id
ORDER BY changed_at DESC, history_id DESC

확인 조건입니다.

• 선행 equipment_id가 Equality로 고정
• ORDER BY Column 순서가 Index Key와 호환
• ASC·DESC 방향이 호환
• NULLS FIRST·LAST 의미
• INLIST·Partition 결과의 전역 순서
• Table Access가 순서 보존을 깨지 않는 Plan
• 다른 Access Path보다 총비용이 낮음

혼합 방향 예입니다.

ORDER BY changed_at DESC, history_id ASC

전체 Index 역방향 Scan으로는 두 Column 방향이 함께 반전되므로 별도 혼합 방향 Index가 필요할 수 있습니다.

8. Top-N과 결과 결정성

업무 요구가 최신 10건이라면 다음처럼 SQL 의미에 순서를 포함합니다.

SELECT equipment_id,
       changed_at,
       status
FROM   status_hist
WHERE  equipment_id = :id
ORDER  BY changed_at DESC,
          history_id DESC
FETCH FIRST 10 ROWS ONLY;

history_id가 필요한 이유입니다.

changed_at이 같은 Row가 여러 개
  → changed_at만으로 순서 미확정

history_id까지 정렬
  → 동률 해소
  → 반복 실행의 안정적인 Top-N

가능한 Plan입니다.

WINDOW NOSORT STOPKEY
  TABLE ACCESS BY INDEX ROWID
    INDEX RANGE SCAN

실제 확인합니다.

• SORT ORDER BY가 제거됐는가
• NOSORT·STOPKEY가 나타나는가
• Index·Table A-Rows가 N 근처에서 멈추는가
• 첫 행·전체 Fetch 시간이 어떤가
• 동률 Row의 결과가 결정적인가

8.1 Hint + ROWNUM의 한계

SELECT /*+ INDEX_DESC(h status_hist_x1) */
       ...
FROM   status_hist h
WHERE  equipment_id = :id
AND    ROWNUM <= 10;

Hint는 Access Path를 유도할 뿐 업무 의미의 정렬 계약을 대신하지 않습니다. Index나 Plan이 바뀌면 “최신 10건”이라는 의미가 깨질 수 있습니다.

9. Clustering Factor와 BATCHED

Clustering Factor는 Index Key 순서와 Heap Table Block 배치의 관계를 나타냅니다.

좋은 CF 방향

Index 순서의 ROWID가 같은·인접 Block
→ 일반 ROWID Access에서도 Block 재사용 가능
→ BATCHED 추가 이득이 작을 수 있음

나쁜 CF 방향

Index 순서의 ROWID가 여러 Block에 흩어짐
→ Block 전환·재방문 가능성
→ BATCHED의 접근 순서 개선 이득 가능

그러나 나쁜 CF가 BATCHED의 큰 이득을 보장하지 않습니다.

• 후보마다 모두 다른 Block이면 묶을 이점이 작음
• 후보가 너무 많으면 Full Scan이 더 저렴
• 후보가 너무 적으면 Batch Overhead 의미가 작음
• Cache 상태·Row Migration·동시성에 따라 결과가 달라짐

10. Covering과 BATCHED 제거

다음 Index를 검토합니다.

CREATE INDEX emp_dept_job_cover_ix
ON emp(deptno, job, empno, ename);

Query에 필요한 조건·반환 Column을 모두 제공합니다.

SELECT empno,
       ename,
       job
FROM   emp
WHERE  deptno = 20
ORDER  BY job, empno;

가능한 Plan입니다.

INDEX RANGE SCAN EMP_DEPT_JOB_COVER_IX

Table Access가 없으므로 BATCHED도 필요하지 않습니다.

이득입니다.

• Table Buffers·Reads 제거
• Clustering 영향 제거
• Sort 제거 가능성
• Top-N 조기 종료

비용입니다.

• Index 폭·Leaf Blocks 증가
• Buffer Cache 점유
• INSERT·DELETE
• ename Update 비용
• Redo·Undo·공간
• 다른 SQL Plan 변화

11. Runtime 통계 검증

SELECT /*+ GATHER_PLAN_STATISTICS */
       empno,
       ename,
       job
FROM   emp
WHERE  deptno = 20
ORDER  BY job, empno;

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

주요 통계입니다.

11.1 Index·Table 단계 분리

Index Operation
  Starts·A-Rows·Buffers·Reads
  access·filter Predicate

Table Operation
  A-Rows·Buffers·Reads
  Table Filter
  BATCHED 여부

Sort Operation
  A-Rows
  Memory·TEMP
  A-Time

11.2 A-Rows 주의

Index A-Rows는 상위로 반환한 ROWID 수입니다. Index Filter에서 제거한 내부 Entry 전체를 보여 주지 않을 수 있습니다.

Index A-Rows 50
Index Buffers 20,000

→ 50 Entry만 읽었다고 단정 금지

11.3 Starts 주의

Nested Loops
  TABLE ACCESS BY INDEX ROWID BATCHED
    Starts 100,000

한 번의 BATCHED Access가 작아도 100,000회 반복되면 누적 비용이 큽니다.

11.4 Buffers 합산 주의

상위 Operation의 Buffers는 하위 Row Source 작업을 포함할 수 있습니다. 모든 Line을 단순 합산하지 않고 비용 집중 Branch와 Statement 총량을 구분합니다.

12. 공정한 비교 계약

BATCHED+Sort, Ordered Index, Covering 후보를 비교할 때 다음을 맞춥니다.

• 동일 SQL 결과
• 동일 ORDER BY
• 동일 Bind 값·Data Type
• 동일 Fetch 범위
• 동일 통계·Optimizer 환경
• 동일 Parallel·동시성
• 유사 Cache 상태
• 대표값·편중값
• 반복 실행

후보 A: 첫 10건 Fetch
후보 B: 전체 100,000건 Fetch

→ 직접 비교 불가

Top-N 목표와 전체 처리 목표를 분리합니다.

13. 진단 예제

후보 A: BATCHED + Sort

SORT ORDER BY
  A-Rows     20,000
  TEMP       0
  A-Time     0.18s

TABLE ACCESS BY INDEX ROWID BATCHED
  A-Rows     20,000
  Buffers     4,000

INDEX RANGE SCAN
  A-Rows     20,000
  Buffers       200

후보 B: Ordered Index + 일반 ROWID

TABLE ACCESS BY INDEX ROWID
  A-Rows     20,000
  Buffers     7,500

INDEX RANGE SCAN
  A-Rows     20,000
  Buffers       200

후보 A는 Table Buffers를 줄였지만 Sort가 추가됐습니다.

후보 C: Covering

INDEX RANGE SCAN
  A-Rows     20,000
  Buffers       500

Table Access 없음
Sort 없음

후보 C가 Query에는 가장 빠를 수 있지만 DML·공간 비용을 포함해야 합니다.

14. 자주 혼동하는 판단