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·동시성에서 후보 경로를 공정하게 비교한다.

1. 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가 필요함

2. 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

2.1 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로 나타나는 것은 아닙니다.

2.2 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

2.3 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 방문이 증가합니다.

3. 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행이라면 후보 수 자체를 줄이는 설계가 더 중요할 수 있습니다.

4. 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이 포맷돼 사용된 범위의 상한입니다.

4.1 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 수를 동일하게 보지 않습니다.

4.2 작은 Table

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

Index Root·Branch·Leaf
+ ROWID Table Access

vs

작은 Table Block 전체 Scan

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

5. Multiblock I/O·Direct Path·병렬

5.1 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가 없어도 남음

5.2 Direct Path Read

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

Direct Path
  Disk → PGA
  Buffer Cache 우회

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

5.3 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 부하를 함께 봅니다.

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

6.1 행 비율보다 Block 비용

동일 결과 10,000행

Plan A
  Table Block 150개 방문

Plan B
  Table Block 9,000개 방문

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

6.2 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이 저렴할 수 있습니다.

6.3 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 비용을 비교할 때 사용하는 중요 통계입니다.

6.4 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·공간 비용을 함께 봅니다.

7. Index Full Scan·Fast Full Scan과 Table Full Scan

7.1 Index Full Scan

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

7.2 Index Fast Full Scan

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

7.3 Table Full Scan과 비교

8. 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가 유리할 수 있습니다.

8.1 Partial Fetch 비교 주의

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

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

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

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

9. 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을 기준으로 판단합니다.

10. 통계정보와 비용 모델

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 비용 왜곡 가능

11. 실행계획과 실제 통계

11.1 Index 경로

TABLE ACCESS BY INDEX ROWID BATCHED ORDERS
  INDEX RANGE SCAN ORDERS_STATUS_IX

확인합니다.

11.2 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'
  )
);

11.3 상위 Buffers 중복 합산 주의

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

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

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

12. 후보 경로의 공정한 비교

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 변화

13. 진단 사례

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

14. 자주 혼동하는 판단

15. 최종 체크리스트

[범위]
□ 최종 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을 준비했는가