테이블 랜덤 액세스의 원리: ROWID·Buffer I/O·후행 조건

핵심 요약

B-tree Index는 조건에 맞는 Index Key와 ROWID 후보를 찾습니다. Query에 필요한 조건이나 반환 Column이 Index에 없으면 Oracle은 ROWID로 Heap Table Block을 찾아가 실제 Row를 읽습니다.

Index Leaf Scan
  → 후보 ROWID 생성
  → Table Block 방문
  → Table Filter·Projection
  → 최종 Row 반환

Table Random Access 비용은 세 단계로 분리해 봅니다.

1. Index 작업
   → 몇 Leaf Block·Entry를 읽었는가

2. 후보 ROWID
   → Table Operation으로 몇 Row를 전달했는가

3. Table 작업
   → 몇 Table Block을 방문하고 몇 Row를 버렸는가

최종 결과가 50행이어도 다음 두 실행은 전혀 다릅니다.

실행 A
  Index A-Rows       60
  Index Buffers      10
  Table Buffers      30
  최종 Row           50

실행 B
  Index A-Rows   10,000
  Index Buffers     120
  Table Buffers   8,100
  최종 Row           50

실행 B의 핵심 낭비는 10,000개의 후보 ROWID를 Table 단계까지 가져가 50개만 남긴 것입니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 테이블 액세스 최소화 범위에서 ROWID, Table Random Access, Index Filter, Covering과 대안 Access Path를 다룹니다.

학습 목표

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

• Index Scan과 TABLE ACCESS BY INDEX ROWID의 역할을 구분한다.
• Extended Physical ROWID의 구성과 변경 가능성을 설명한다.
• 후보 ROWID·Table Block·최종 Row의 차이를 설명한다.
• TABLE ACCESS BY INDEX ROWID BATCHED의 목적과 한계를 설명한다.
• Clustering Factor가 Table Block 방문량에 미치는 영향을 설명한다.
• Row Migration·Chaining이 ROWID Access 비용을 늘릴 수 있음을 설명한다.
• Index Column 추가의 Range 축소·Index Filter·Covering 효과를 구분한다.
• Covering·Index Join·Full Scan·Partition Pruning 대안을 비교한다.
• Starts·A-Rows·Buffers·Reads와 LAST_*를 이용해 실제 비용을 검증한다.
• 조회 이득과 신규 Index의 DML·Redo·공간 비용을 함께 판단한다.

본문

Index Scan과 Table Access는 서로 다른 Row Source다

다음 Index가 있습니다.

CREATE INDEX orders_status_ix
ON orders(status);

Query입니다.

SELECT order_id,
       customer_id,
       amount
FROM   orders
WHERE  status = 'READY'
AND    region = 'SEOUL';

ORDERS_STATUS_IX에는 region, order_id, customer_id, amount가 없습니다.

일반적인 처리 흐름입니다.

INDEX RANGE SCAN ORDERS_STATUS_IX
  → status='READY' Entry와 ROWID 찾기

TABLE ACCESS BY INDEX ROWID BATCHED ORDERS
  → ROWID가 가리키는 Row 읽기
  → region='SEOUL' Filter
  → SELECT Column 반환

INDEX RANGE SCAN
  = 후보 위치 탐색

TABLE ACCESS BY INDEX ROWID
  = 실제 Heap Table Row 읽기

Index가 사용됐다는 사실만으로 Table Access가 적다고 판단하지 않습니다. Index가 모든 필요한 Column을 제공하면 Table Access가 생략될 수도 있습니다.

Physical ROWID의 구성과 의미

Heap-Organized Table의 Extended ROWID는 개념적으로 다음 네 요소를 포함합니다.

Data Object Number
+ Tablespace-Relative File Number
+ Data Block Number
+ Row Number

표시 형식의 개념입니다.

OOOOOO FFF BBBBBB RRR

• Data Object: Row가 속한 Segment
• Relative File: Row가 있는 Data File
• Block: Row가 저장된 Data Block
• Row Number: Block Row Directory의 Entry

ROWID는 Row 위치에 빠르게 접근할 수 있게 하지만 “접근 비용이 0”이라는 뜻은 아닙니다.

ROWID 획득
  → Buffer Cache에서 Block 탐색
  → Block Pin·Consistent Read
  → Row Directory·Row Piece 확인
  → Predicate·Projection 처리

Cache Hit이면 Physical Read가 없을 수 있지만 Logical I/O와 CPU는 남습니다. Cache Miss이면 Physical Read와 I/O Wait가 추가됩니다.

ROWID는 영구 업무 Key가 아니다

ROWID는 다음 상황에서 바뀔 수 있습니다.

• Row Movement가 허용된 Partition Key Update
• SHRINK SPACE
• Flashback Table
• Export·Import
• 일부 Table Move·재구성

업무 Identifier로 ROWID를 장기간 저장하지 않고 PK·UK를 사용합니다.

후보 ROWID와 최종 결과를 비교한다

실측 예시입니다.

| Id | Operation                           | Starts | A-Rows | Buffers |
|---:|-------------------------------------|-------:|-------:|--------:|
|  1 | TABLE ACCESS BY INDEX ROWID BATCHED |      1 |     50 |   8,100 |
|* 2 |  INDEX RANGE SCAN ORDERS_STATUS_IX  |      1 | 10,000 |     120 |

Predicate입니다.

1 - filter(region='SEOUL')
2 - access(status='READY')

해석합니다.

Index 후보        10,000
Table 최종            50
Table 탈락          9,950

확인할 질문입니다.

1. Index가 만든 후보 ROWID는 몇 개인가
2. Table에서 어떤 조건이 늦게 평가되는가
3. Table Buffers가 왜 큰가
4. Clustering이 나쁜가
5. 후보 수를 Index에서 줄일 수 있는가
6. 결과 비율이 커서 Full Scan이 더 적절한가

Starts=1은 Row Source가 부모에 의해 한 번 시작됐다는 뜻이지 ROWID 접근이 한 번이라는 뜻이 아닙니다.

Index 작업과 Table 작업을 따로 진단한다

Leaf Scan 비효율

INDEX RANGE SCAN
  A-Rows      50
  Buffers 20,000

TABLE ACCESS
  A-Rows      50
  추가 Buffers 30

Index Filter에서 많은 Entry를 제거했을 수 있습니다.

우선 확인합니다.

• 중간 Index Column 누락
• 첫 Range가 너무 넓음
• 후행 Predicate가 Filter
• Skip Scan·INLIST·Nested Loops Starts
• Column 순서

후보 ROWID 과다

INDEX RANGE SCAN
  A-Rows 100,000
  Buffers     200

TABLE ACCESS
  A-Rows   1,000
  Buffers 80,000

Index 자체는 적은 Buffer로 많은 후보를 만들었지만, Table에서 대부분 제거했습니다.

개선 후보입니다.

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

Table Block 분산

같은 10,000 후보 ROWID라도 비용은 다릅니다.

Plan A
  10,000 ROWID
  Table Buffers 300

Plan B
  10,000 ROWID
  Table Buffers 9,000

Plan B는 ROWID가 더 많은 Block에 분산됐을 가능성이 큽니다.

TABLE ACCESS BY INDEX ROWID BATCHED

Oracle의 BATCHED Access는 Index에서 몇 개의 ROWID를 모은 뒤 Table Row를 Block 순서에 가깝게 접근하려고 시도합니다.

Index에서 ROWID Batch 수집
  → Block 위치 고려
  → 같은 Block Row를 묶어 접근

목표입니다.

• 같은 Block 반복 방문 감소
• Row가 어느 정도 흩어진 경우 Clustering 보완
• Buffer Access 순서 개선

BATCHED가 의미하지 않는 것:

BATCHED
  ≠ Table Access 제거
  ≠ 후보 ROWID 감소
  ≠ 한 번의 Multiblock Read 보장
  ≠ Clustering 문제 완전 해결

후보가 100,000개이고 최종 1,000행이라면 BATCHED보다 후보 ROWID를 줄이는 설계가 우선입니다.

Clustering Factor

Clustering Factor는 특정 B-tree Index의 Key 순서와 Table Block 배치가 얼마나 비슷한지를 나타냅니다.

Clustering Factor가 Table Block 수에 가까움
  → 인접 Index Entry가 같은·인접 Block
  → Range Scan Table Access에 유리

Clustering Factor가 Table Row 수에 가까움
  → ROWID가 넓은 Block에 분산
  → Random Table Access 증가 가능

확인 SQL입니다.

SELECT index_name,
       leaf_blocks,
       clustering_factor
FROM   user_indexes
WHERE  table_name = 'ORDERS';

Clustering Factor는 Table 속성이 아니라 각 Index별 속성입니다. 한 Index의 Clustering을 개선하는 Table 재배치는 다른 Index의 Clustering을 악화시킬 수 있습니다.

Clustering Factor를 절대값 하나로 판단하지 않는다

함께 확인합니다.

• Table NUM_ROWS, BLOCKS
• Index LEAF_BLOCKS
• 실제 후보 ROWID
• Table Buffers·Reads
• Buffer Cache와 동시성
• Range 크기와 Bind 편중

Row Migration·Chaining과 추가 Block Access

Row가 한 Block에 모두 들어가지 않거나 Update로 기존 Block 공간을 초과하면 Row Piece가 여러 Block에 저장될 수 있습니다.

원래 ROWID가 가리키는 Block
  → Forwarding·추가 Row Piece
  → 다른 Block Access

ROWID가 하나여도 실제 Row를 완성하기 위해 추가 Block을 읽을 수 있습니다.

• Row Migration: Update 후 Row가 다른 Block으로 이동
• Row Chaining: Row가 너무 커 여러 Block에 걸쳐 저장

확인 후보입니다.

• 넓은 Row·긴 Variable Column
• Update가 많은 Table
• CHAIN_CNT와 Segment 상태
• Table Buffers가 후보 ROWID 수보다 비정상적으로 큼

무조건 Table Move를 수행하지 않고 실제 영향과 가용성·Index 재구축 비용을 검증합니다.

Index Column 추가의 세 가지 효과

Access Range 축소

CREATE INDEX orders_x1
ON orders(status, region);

WHERE status='READY'
AND   region='SEOUL'

두 Column이 Equality Access에 사용되면 범위가 좁아집니다.

Leaf Scan 감소
→ 후보 ROWID 감소
→ Table Access 감소

Index Filter로 후보 감소

CREATE INDEX orders_x2
ON orders(status, order_date, region);

WHERE status='READY'
AND   order_date>=:from_dt
AND   order_date< :to_dt
AND   region='SEOUL'

order_date가 첫 Range라면 후행 region이 전체 Leaf 범위를 크게 줄이지 못할 수 있습니다.

Leaf Range 유지
→ Index에서 region Filter
→ Table 전달 ROWID 감소

Index Buffers와 Table Buffers를 별도로 봅니다.

Covering으로 Table Access 제거

CREATE INDEX orders_x3
ON orders(status, region, order_id, customer_id, amount);

조건과 반환 Column을 모두 Index에서 제공하면 Table Access를 제거할 수 있습니다.

INDEX RANGE SCAN
  → 결과 완성

Covering은 Query별 상태이지 별도 Index Type이 아닙니다.

Covering의 이득과 비용

이득

• Table ROWID Access 제거
• Table Buffer·Physical Read 감소
• Clustering 영향 제거
• Top-N·정렬과 결합 가능
• 넓은 Table Row를 읽지 않음

비용

• Index Entry 폭 증가
• Leaf Block·Segment 증가
• Buffer Cache 점유
• INSERT·DELETE 비용
• Index Column UPDATE
• Redo·Undo·Backup
• Block Split·Hot Leaf
• 다른 SQL Plan 변화

Table Access 0
  ≠ 전체 비용 0

넓은 Index의 Leaf Scan과 DML 비용이 커질 수 있습니다.

다른 Table Access 최소화 대안

불필요한 SELECT Column 제거

필요 없는 Column을 제거하면 기존 Index가 Covering이 될 수 있습니다.

-- 불필요한 SELECT *
SELECT order_id, amount
...

Index Join

여러 기존 Index가 Query의 모든 Column을 제공하면 ROWID Hash Join으로 Table Access를 제거할 수 있습니다.

VIEW index$_join$
  HASH JOIN
    INDEX RANGE SCAN
    INDEX FAST FULL SCAN

여러 Index Scan·Hash 비용을 비교합니다.

Partition Pruning

필요 Partition만 Scan해 Table·Index 범위를 줄입니다.

PARTITION RANGE SINGLE
  TABLE ACCESS FULL

전체 Table 크기가 아니라 Pruning 후 Block을 비교합니다.

Full Scan

후보 ROWID가 매우 많고 Table Block 대부분을 방문한다면 Full Scan이 더 저렴할 수 있습니다.

Index + Random Table Access
vs
Table Full·Parallel Scan

Top-N·Paging

정렬된 Index에서 첫 N행을 얻고 조기 중단하면 Table Access를 크게 줄일 수 있습니다.

INDEX RANGE SCAN DESCENDING
+ STOPKEY

실행계획과 Runtime 통계

SELECT /*+ GATHER_PLAN_STATISTICS */
       o.order_id,
       o.customer_id,
       o.amount
FROM   orders o
WHERE  o.status='READY'
AND    o.region='SEOUL';

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

V$SQL_PLAN_STATISTICS_ALL과 연결되는 주요 통계입니다.

비교 순서

1. Index A-Rows·Buffers·Reads
2. Table A-Rows·Buffers·Reads
3. Access·Index Filter·Table Filter 위치
4. Starts와 반복 Probe
5. 최종 Row·Fetch 범위
6. Sort·TEMP·Partition·Parallel
7. CPU·Elapsed·P95

Buffers 중복 합산 주의

상위 Operation의 Buffers는 하위 Row Source 작업을 포함할 수 있습니다.

잘못된 방법
  Plan 모든 Line의 Buffers 단순 합산

권장 방법
  Index·Table Branch의 비용 집중 위치 확인
  + Statement 전체 총량 확인

동일 조건 비교 계약

인덱스 개선 전후에는 다음을 맞춥니다.

• 동일 SQL 결과
• 동일 Bind 값과 Data Type
• 동일 Fetch 범위·End-of-Fetch
• 동일 통계·Optimizer 환경
• 동일 동시성·Parallel 설정
• 유사한 Cache 상태
• 대표값·편중값 반복
• DML·Redo·공간 측정

첫 20행 Fetch
vs
전체 Fetch

→ 직접 비교 불가

진단 예제

변경 전

INDEX RANGE SCAN ORDERS_STATUS_IX
  A-Rows  100,000
  Buffers     500

TABLE ACCESS BY INDEX ROWID BATCHED
  A-Rows    1,000
  Buffers  70,000

후보 A: Filter Column 추가

Index (status,region)

INDEX RANGE SCAN
  A-Rows   1,000
  Buffers     30

TABLE ACCESS
  A-Rows   1,000
  Buffers    700

후보 B: Covering

Index (status,region,order_id,customer_id,amount)

INDEX RANGE SCAN
  A-Rows   1,000
  Buffers    100

Table Access 없음

후보 C: Full Scan

TABLE ACCESS FULL
  A-Rows   1,000
  Buffers 60,000
  Parallel DOP 8

결론은 단순 Buffer 수만으로 내리지 않습니다.

• 후보 A는 DML 증가가 작을 수 있음
• 후보 B는 Query가 빠르지만 Index가 넓음
• 후보 C는 Parallel Resource를 많이 사용 가능
• 실행 빈도와 SLA를 포함해 결정

자주 혼동하는 판단