Index Scan과 Table Full Scan: 선택도·I/O·손익분기점

핵심 요약

Oracle이 Table Row를 가져오는 방식을 Access Path라고 합니다. 이 이론의 핵심 비교 대상은 다음 두 경로입니다.

Index 경로
  B-tree Root·Branch 탐색
  → Leaf Entry Scan
  → ROWID 후보 생성
  → 필요한 Table Block 방문
  → Table Filter·결과 반환

Table Full Scan 경로
  High Water Mark 아래의 포맷된 Table Block Scan
  → 각 Row에 Predicate 적용
  → 결과 반환

Oracle Optimizer는 “전체 Row의 몇 %를 조회하는가”라는 한 가지 비율로 경로를 결정하지 않습니다.

Index 경로 비용
  ≈ Index 수직 탐색
   + 읽은 Leaf Block·Entry
   + 반환한 ROWID 후보
   + 방문한 Table Block
   + 반복 Starts
   + Sort·Table Access
   + DML·공간 비용

Full Scan 비용
  ≈ High Water Mark 아래 Scan 대상 Block
   + Multiblock·Direct Path·병렬 처리 특성
   + Predicate CPU
   + Sort·Join·Aggregate 후속 비용

따라서 “5% 이하면 Index, 10% 이상이면 Full Scan” 같은 수치는 절대 규칙이 아닙니다.

진단 기준
  → 최종 Row 수가 아니라 실제 Block 작업량
  → Index·Table Operation별 A-Rows·Buffers·Reads
  → 사용자 목표인 Elapsed·CPU·P95·처리량

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 인덱스 스캔 방식 범위에서 Index 경로와 Table Full Scan의 비용·손익분기점을 다룹니다. Index Full·Fast Full·Skip Scan의 상세 원리는 후속 이론에서 더 깊게 다룹니다.

학습 목표

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

• Access Path와 Index·Full Scan 경로를 구분한다.
• Index 비용을 Leaf Scan·ROWID 후보·Table Block 방문으로 분해한다.
• Full Scan이 High Water Mark 아래의 포맷된 Block을 읽는 원리를 설명한다.
• Small Table·Multiblock I/O·Direct Path·병렬 처리가 Full Scan 비용을 바꾸는 이유를 설명한다.
• Selectivity·Cardinality와 방문 Table Block 수를 구분한다.
• Clustering Factor가 Index와 Full Scan 선택에 미치는 방향을 설명한다.
• Covering Index·Index Fast Full Scan과 Table Full Scan을 비교한다.
• Top-N·정렬·Partial Fetch가 손익분기점을 바꾸는 이유를 설명한다.
• Partition Pruning과 Full Partition Scan을 구분한다.
• Starts·A-Rows·Buffers·Reads와 LAST_* 통계로 실제 비용을 측정한다.
• 같은 Bind·Fetch·Cache·동시성에서 후보 경로를 공정하게 비교한다.

본문

Access Path란 무엇인가

SQL은 원하는 결과를 선언하고, Optimizer는 여러 후보 Access Path 중 예상 비용이 가장 낮은 경로를 선택합니다.

SELECT order_id,
       customer_id,
       order_status,
       order_amount
FROM   orders
WHERE  order_status = :status;

ORDERS(order_status) Index가 있다면 대표 후보는 다음과 같습니다.

후보 A
  INDEX RANGE SCAN ORDERS_STATUS_IX
  → TABLE ACCESS BY INDEX ROWID

후보 B
  TABLE ACCESS FULL ORDERS

Index가 존재해도 다음과 같은 이유로 Full Scan이 선택될 수 있습니다.

• Predicate가 많은 Row·Block을 요구함
• Table이 매우 작음
• Index Column에 함수·묵시적 변환이 적용됨
• Leading Column을 사용하지 못함
• 통계가 오래되거나 실제 분포를 반영하지 못함
• Table의 Parallel 속성과 Multiblock 비용이 유리함
• Covering이 아니어서 많은 ROWID Table Access가 필요함

Index 경로의 비용을 세 단계로 분리한다

Heap Table의 일반적인 B-tree Index 경로입니다.

1. Root·Branch를 따라 Start Leaf 탐색
2. 조건 범위의 Leaf Entry Scan
3. Entry에서 ROWID 후보 생성
4. 필요한 Table Block 방문
5. Table Filter·Projection 후 결과 반환

예시 Plan입니다.

TABLE ACCESS BY INDEX ROWID BATCHED ORDERS
  INDEX RANGE SCAN ORDERS_STATUS_IX

Index Leaf Scan

확인할 질문입니다.

• 어느 조건이 access Predicate인가
• Start·Stop 범위가 얼마나 넓은가
• 후행 조건이 Index Filter로 남는가
• Index Buffers·Reads는 얼마인가
• Starts가 몇 번 반복됐는가

최종 10행
Index A-Rows 10
Index Buffers 20,000

→ 넓은 Leaf Scan 후 Index Filter 가능성

Index Operation의 A-Rows는 상위 Operation으로 반환한 Entry·ROWID 수입니다. 내부에서 검사한 모든 Leaf Entry가 A-Rows로 나타나는 것은 아닙니다.

ROWID 후보

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

→ Table로 100,000 ROWID 후보 전달
→ Table Filter에서 대부분 제거

후보 ROWID 자체를 줄이려면 다음을 검토합니다.

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

Table Block 방문

같은 10,000 ROWID도 비용이 다를 수 있습니다.

경우 A
  10,000 ROWID
  200 Table Block

경우 B
  10,000 ROWID
  8,000 Table Block

ROWID가 같은 Block에 모여 있으면 한 Block 방문으로 여러 Row를 처리할 수 있습니다. 넓게 흩어져 있으면 Random Access와 반복 Block 방문이 증가합니다.

TABLE ACCESS BY INDEX ROWID BATCHED

TABLE ACCESS BY INDEX ROWID BATCHED

Oracle은 Index에서 ROWID를 몇 개 모은 뒤 Table Block 순서에 가깝게 접근해 같은 Block의 반복 방문을 줄이려고 시도할 수 있습니다.

ROWID Batch 수집
  → Table Block 위치 고려
  → Table Row 접근

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

BATCHED
  ≠ Table Access 제거
  ≠ 후보 ROWID 자동 감소
  ≠ 항상 한 번의 Multiblock Physical Read
  ≠ Clustering 문제 완전 해결

100,000개의 후보 ROWID를 BATCHED로 처리해도 최종 결과가 1,000행이라면 후보 수 자체를 줄이는 설계가 더 중요할 수 있습니다.

Table Full Scan의 실제 동작

Oracle Full Table Scan은 High Water Mark 아래의 포맷된 Block을 읽으며, 그 Block의 Row에 Predicate를 적용합니다.

Segment Header↓High Water Mark 아래 Scan 대상 Block↓각 Row Predicate 평가

High Water Mark는 해당 Segment에서 Block이 포맷돼 사용된 범위의 상한입니다.

DELETE와 High Water Mark

대량 Row를 DELETE해 현재 Row 수가 줄어도 High Water Mark는 자동으로 낮아지지 않을 수 있습니다.

과거 100,000 Block 사용
대량 DELETE 후 실제 Row는 10,000 Block 분량

High Water Mark 유지
  → Full Scan이 넓은 포맷 Block 범위를 읽을 수 있음

High Water Mark를 낮추려면 Table 특성과 운영 조건에 따라 다음을 검토합니다.

• TRUNCATE
• ALTER TABLE ... SHRINK SPACE
• ALTER TABLE ... MOVE
• Partition 단위 재구성

단순 Row 수와 Full Scan Block 수를 동일하게 보지 않습니다.

작은 Table

Table이 매우 작으면 다음 경로가 더 저렴할 수 있습니다.

Index Root·Branch·Leaf
+ ROWID Table Access

vs

작은 Table Block 전체 Scan

Oracle 공식 설명에서는 High Water Mark 아래 Block 수가 Multiblock Read 단위보다 작으면, 반환 비율이 낮아도 Full Scan이 저렴할 수 있다고 설명합니다.

Multiblock I/O·Direct Path·병렬

Multiblock Read

Full Table Scan은 한 I/O 요청에서 여러 Database Block을 읽을 수 있습니다.

작은 I/O 다수
vs
큰 Multiblock I/O 소수

DB_FILE_MULTIBLOCK_READ_COUNT는 순차 Scan에서 한 I/O로 읽는 최대 Block 수와 Optimizer 비용 계산에 영향을 줄 수 있습니다.

그러나 다음을 고정 규칙으로 사용하지 않습니다.

• 실제 I/O 크기는 Platform·Storage·Session·Workload의 영향을 받음
• Cache·Direct Path·비동기 I/O가 결합될 수 있음
• Logical I/O와 CPU 비용은 Physical I/O가 없어도 남음

Direct Path Read

일부 대용량·병렬 Scan은 Buffer Cache를 거치지 않고 Disk에서 Process의 PGA로 직접 읽을 수 있습니다.

Direct Path
  Disk → PGA
  Buffer Cache 우회

Direct Path는 Full Scan의 가능한 실행 방식 중 하나이며 모든 Full Scan이 반드시 Direct Path라는 뜻은 아닙니다.

Parallel Full Scan

큰 Table·Partition Scan은 여러 Parallel Execution Server가 Block Range·Granule을 나눠 읽을 수 있습니다.

Serial Full Scan
  한 Process가 전체 Scan

Parallel Full Scan
  여러 PX Server가 Scan 범위 분담

Parallel은 Wall Clock을 줄일 수 있지만 전체 CPU·I/O Resource 사용량과 다른 Workload 영향이 증가할 수 있습니다. DOP와 System 부하를 함께 봅니다.

손익분기점이 고정되지 않는 이유

행 비율보다 Block 비용

동일 결과 10,000행

Plan A
  Table Block 150개 방문

Plan B
  Table Block 9,000개 방문

Optimizer와 실제 성능은 Row 수보다 Block 방문과 I/O 방식을 중요하게 봅니다.

Table·Index 크기

Table          100,000 Block
Covering Index   8,000 Block

결과 비율이 높아도 Table보다 작은 Covering Index를 전체 Scan하는 INDEX FAST FULL SCAN이 유리할 수 있습니다.

반대로 Table이 20 Block뿐이면 선택적인 Predicate여도 Full Scan이 저렴할 수 있습니다.

Clustering Factor

Clustering Factor는 B-tree Index 순서와 Table Block 배치의 유사도를 나타내는 통계입니다.

낮은 Clustering Factor 방향
  → 인접 Index Entry가 같은·인접 Table Block을 가리킴
  → 큰 Range에서 Table Block 방문이 상대적으로 적음

높은 Clustering Factor 방향
  → ROWID가 넓게 분산
  → Index Range Scan의 Table I/O 증가 가능

Clustering Factor는 Optimizer가 Index Scan과 Full Scan 비용을 비교할 때 사용하는 중요 통계입니다.

Covering 여부

CREATE INDEX orders_status_cover_ix
ON orders(order_status, order_amount, order_id);

SELECT order_id,
       order_amount
FROM   orders
WHERE  order_status = :status;

필요한 Column이 모두 Index에 있으면 Table ROWID Access를 생략할 수 있습니다.

INDEX RANGE SCAN 또는 INDEX FAST FULL SCAN
  → Table Access 없음

다만 넓어진 Index의 Leaf Block·DML·Redo·공간 비용을 함께 봅니다.

Index Full Scan·Fast Full Scan과 Table Full Scan

Index Full Scan

INDEX FULL SCAN
  → Start·Stop Key 없이 Index 전체를 Key 순서로 읽음
  → 정렬 순서 활용 가능
  → Multiblock보다 Ordered Scan 특성이 중요

Index Fast Full Scan

INDEX FAST FULL SCAN
  → Index 전체 Block을 Multiblock 방식으로 읽음
  → Key 정렬 순서 보장 없음
  → Index Column만 필요한 Query에서 Table 대안

Table Full Scan과 비교

Top-N·정렬·Partial Fetch

다음 SQL은 전체 비율보다 첫 20행을 얼마나 빨리 얻는지가 중요합니다.

SELECT order_id,
       order_date
FROM   orders
WHERE  customer_id = :customer_id
ORDER BY order_date DESC, order_id DESC
FETCH FIRST 20 ROWS ONLY;

후보 Index입니다.

CREATE INDEX orders_customer_date_ix
ON orders(customer_id, order_date DESC, order_id DESC);

가능한 이점입니다.

customer_id Prefix 탐색
  → 최신 방향 Range Scan
  → 20행에서 조기 중단
  → Sort 절감

전체 Table에서 고객 Row 비율이 커도 첫 페이지 목표에서는 Index가 유리할 수 있습니다.

Partial Fetch 비교 주의

변경 전에는 전체 결과를 Fetch하고 변경 후에는 첫 20행만 Fetch하면 공정한 비교가 아닙니다.

첫 페이지 목표
  → 양쪽 모두 첫 N행까지 Fetch

전체 처리 목표
  → 양쪽 모두 End-of-Fetch 완료

EXECUTIONS, FETCHES, ROWS_PROCESSED, END_OF_FETCH_COUNT와 Application Row Limit을 확인합니다.

Partition Pruning과 Full Partition Scan

Partitioned Table에서는 전체 Table이 아니라 필요한 Partition만 Full Scan할 수 있습니다.

WHERE order_date >= DATE '2026-07-01'
AND   order_date <  DATE '2026-08-01'

월별 Partition이면 다음처럼 처리될 수 있습니다.

PARTITION RANGE SINGLE
  TABLE ACCESS FULL

Full Table Scan
  → 모든 Partition 대상 가능

Full Partition Scan
  → Pruning된 Partition만 Scan

Index Range Scan과 비교할 때 전체 Table Block 수가 아니라 실제 Pruning 후 Scan Block을 기준으로 판단합니다.

통계정보와 비용 모델

Optimizer는 다음 정보를 이용해 Index와 Full Scan 비용을 예상합니다.

• Table Block 수·Row 수
• Index Leaf Blocks·BLEVEL
• NDV·NULL·Histogram
• Clustering Factor
• System Statistics
• Multiblock Read 관련 비용
• Parallel 속성
• Predicate Selectivity

통계가 오래되거나 Data Skew를 평균으로 추정하면 실제와 다른 경로가 선택될 수 있습니다.

예상 E-Rows 작음
실제 A-Rows 큼
  → Index 후보·Table Access 과소평가 가능

Table Block 통계 과대·과소
  → Full Scan 비용 왜곡 가능

실행계획과 실제 통계

Index 경로

TABLE ACCESS BY INDEX ROWID BATCHED ORDERS
  INDEX RANGE SCAN ORDERS_STATUS_IX

확인합니다.

Full Scan 경로

TABLE ACCESS FULL ORDERS

확인합니다.

• Starts
• E-Rows·A-Rows
• Buffers·Reads
• Parallel 여부
• Partition Start·Stop
• 후속 Sort·Join·Aggregate
• Elapsed·CPU

V$SQL_PLAN_STATISTICS_ALL의 다음 값과 연결할 수 있습니다.

LAST_STARTS
LAST_OUTPUT_ROWS
LAST_CR_BUFFER_GETS
LAST_CU_BUFFER_GETS
LAST_DISK_READS

실제 Cursor의 Operation별 통계는 다음처럼 확인합니다.

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

상위 Buffers 중복 합산 주의

실행계획의 상위 Operation Buffer 값은 하위 작업을 포함할 수 있습니다.

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

권장
  Operation별 작업 위치를 비교
  + Statement·주요 Branch 총량 확인

후보 경로의 공정한 비교

Hint는 운영 정답을 미리 고정하는 도구가 아니라 후보 경로의 실제 비용을 비교하는 실험 수단으로 사용할 수 있습니다.

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(o orders_status_ix) */
       order_id,
       order_amount
FROM   orders o
WHERE  order_status = :status;

SELECT /*+ GATHER_PLAN_STATISTICS FULL(o) */
       order_id,
       order_amount
FROM   orders o
WHERE  order_status = :status;

비교 계약입니다.

• 동일 SQL 결과
• 동일 Bind 값과 Data Type
• 동일 Fetch 범위
• 동일 동시성
• 동일 Optimizer·Parallel 환경
• 유사한 Cache 상태
• 여러 번 반복
• 대표·극단 Bind 포함
• DML·공간 회귀 포함

비교 지표입니다.

Query
  Starts·A-Rows·Buffers·Reads
  Elapsed·CPU·P95
  Sort·TEMP
  Physical I/O Request·Bytes

운영
  Index Size
  INSERT·UPDATE·DELETE TPS
  Redo·Undo
  다른 SQL Plan 변화

진단 사례

Table입니다.

ORDERS
  Rows         10,000,000
  HWM Blocks      500,000

Index입니다.

ORDERS_STATUS_IX
  Leaf Blocks       20,000
  Clustering Factor 9,500,000

ERROR Bind

결과 Row       5,000
Index Buffers     40
Table Buffers  4,800
Elapsed        0.08초

Index 경로가 적합할 수 있습니다.

COMPLETE Bind

결과 Row      8,000,000
Index Buffers    18,000
Table Buffers   450,000
Elapsed          42초

Full Scan 후보입니다.

Full Scan Buffers 500,000
Parallel DOP 8
Elapsed           12초

Index 경로의 Logical Block 총량은 약간 작아 보여도 Random Table Access와 낮은 Clustering 때문에 Wall Clock이 더 느릴 수 있습니다.

Covering 후보

(order_status, order_id, order_amount)
Leaf Blocks 90,000

INDEX FAST FULL SCAN
Buffers 90,000
Elapsed 7초

Query는 빨라질 수 있지만 다음을 확인합니다.

• DML Redo·TPS
• Index Segment·Cache
• 다른 상태 Bind
• 전체 Fetch·정렬 요구
• Full Scan과 병렬 Resource

자주 혼동하는 판단

최종 체크리스트

[범위]
□ 최종 Row와 Fetch 범위를 확인했는가
□ Partition Pruning 후 실제 Scan 범위를 확인했는가

[Index]
□ Start·Stop과 Leaf Buffers를 확인했는가
□ 후보 ROWID와 Table Buffers를 분리했는가
□ Clustering·Covering·정렬·Starts를 확인했는가

[Full Scan]
□ High Water Mark와 Table Blocks를 확인했는가
□ Multiblock·Direct Path·Parallel 조건을 확인했는가
□ 후속 Sort·Join·Aggregate를 포함했는가

[검증]
□ 동일 Bind·Fetch·동시성인가
□ 여러 번 측정했는가
□ A-Rows·Buffers·Reads·Elapsed·CPU를 함께 봤는가
□ 대표·편중 Bind와 Peak Workload를 포함했는가

[운영]
□ 신규 Index의 DML·Redo·공간을 확인했는가
□ 다른 SQL 회귀와 Rollback을 준비했는가