인덱스 손익분기점: ROWID 액세스와 Full Scan 비용 비교

핵심 요약

인덱스 손익분기점은 Index 경로의 전체 비용과 Full Scan 경로의 전체 비용이 역전되는 지점입니다.

Index 경로
  Root·Branch 탐색
  + Leaf Block·Entry Scan
  + 후보 ROWID 생성
  + Heap Table Block 방문
  + Table Filter·Projection
  + Sort·Join·반복 Starts

Full Scan 경로
  High Water Mark 아래의 포맷된 Block Scan
  + Multiblock·Direct Path·Parallel 처리
  + 모든 Row의 Predicate 평가
  + Sort·Join·Aggregate

손익분기점은 “전체 Row의 5%”나 “10%” 같은 고정 비율이 아닙니다.

같은 결과 비율이라도

좋은 Clustering + Covering
  → Index 경로가 더 넓은 범위까지 유리 가능

나쁜 Clustering + 넓은 Row + 많은 Table Access
  → 작은 범위에서도 Full Scan이 유리 가능

가장 중요한 기준은 결과 Row 비율이 아니라 실제로 읽은 Index Block과 Table Block의 수, I/O 방식, 사용자 목표입니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 테이블 액세스 최소화 범위에서 Index 경로와 Full Scan 경로의 손익분기점을 다룹니다.

학습 목표

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

• Index 경로와 Full Scan 경로의 비용 구성요소를 분해한다.
• 결과 Row 비율만으로 손익분기점을 결정할 수 없는 이유를 설명한다.
• 후보 ROWID와 고유 Table Block 방문 수를 구분한다.
• Clustering Factor가 넓은 Range Scan 비용에 미치는 영향을 설명한다.
• Covering Index·Index Fast Full Scan이 손익분기점을 바꾸는 이유를 설명한다.
• Small Table·High Water Mark·Multiblock Read가 Full Scan 비용을 바꾸는 원리를 설명한다.
• Partition Pruning·Parallel·Direct Path를 포함해 Full Scan 후보를 평가한다.
• Top-N·Partial Fetch와 전체 처리량 목표를 구분한다.
• 편중된 Bind·Histogram·통계 최신성이 경계를 바꾸는 이유를 설명한다.
• 동일 조건에서 Range를 단계적으로 늘려 실제 손익분기점을 찾는다.

1. 손익분기점의 정확한 의미

손익분기점은 다음 두 경로의 총비용이 같아지는 근처입니다.

Index 경로 총비용
  = Index 수직 탐색
  + Leaf Scan
  + 후보 ROWID
  + Table Block Access
  + 후속 연산

Full Scan 총비용
  = Scan 대상 Table Block
  + Sequential·Multiblock I/O
  + Predicate CPU
  + 후속 연산

조회 범위를 넓히면 일반적으로 다음 현상이 발생합니다.

초기
  Index 후보가 매우 적음
  → Index 경로 유리

중간
  후보 ROWID·Table Block 증가
  → 두 경로 비용 접근

후기
  Table Block 대부분 방문
  → Full Scan 유리 가능

실제 경계는 Object·Data·Storage·Workload마다 다릅니다.

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

2.1 Index Leaf 작업

INDEX RANGE SCAN
  Starts
  A-Rows
  Buffers
  Reads

확인합니다.

• Start·Stop Key가 얼마나 정확한가
• 첫 Range가 어느 Column에서 시작되는가
• 중간 Column이 누락됐는가
• Index Filter가 얼마나 많은 Entry를 제거하는가
• INLIST·Nested Loops로 몇 번 반복되는가

Index A-Rows 100
Index Buffers 20,000

→ 상위로 100개만 반환했지만
  내부적으로 넓은 Leaf Range를 읽었을 가능성

2.2 후보 ROWID

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

→ Table로 100,000개 후보 전달
→ Table Filter에서 99,000개 제거

후보 ROWID 자체를 줄이는 것이 가장 직접적인 개선일 수 있습니다.

• 선택적 Equality Column을 선두에 배치
• Table Filter Column을 Index에 추가
• 더 적합한 복합 Index
• Partition Key 조건
• 결과 비율이 크면 Full Scan

2.3 Table Block 방문

같은 후보 수라도 실제 비용이 다릅니다.

10,000 ROWID → 300 Table Blocks
10,000 ROWID → 9,000 Table Blocks

Index 경로의 핵심 비용은 후보 Row 수보다 고유 Table Block 방문과 반복 접근에 가까울 수 있습니다.

3. Clustering Factor와 손익분기점

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

CF가 Table Blocks에 가까움
  → 인접 Entry가 같은·인접 Block을 가리킬 가능성
  → 넓은 Range에서도 Block 재사용 가능
  → 손익분기점이 뒤로 이동 가능

CF가 Table Rows에 가까움
  → ROWID가 여러 Block에 흩어짐
  → Random Access 증가
  → 손익분기점이 앞으로 이동 가능

CF는 특정 SQL의 정확한 Block 수가 아니라 Index 전체 배치 관계를 요약한 통계입니다.

함께 봅니다.

• 실제 Range 선택도
• Index A-Rows
• Table Buffers·Reads
• BATCHED 여부
• Partition별 통계
• Row Migration·Chaining
• Bind 편중

4. Covering Index와 Index-Only 경로

다음 Index를 가정합니다.

CREATE INDEX orders_date_cover_ix
ON orders(order_date, amount, order_id);

SELECT order_id,
       amount
FROM   orders
WHERE  order_date >= :from_dt
AND    order_date <  :to_dt;

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

INDEX RANGE SCAN
  → Index에서 결과 완성

Table Random Access가 사라지므로 Index 경로는 더 넓은 범위까지 경쟁력을 가질 수 있습니다.

그러나 비용이 사라지는 것은 아닙니다.

• Index Entry 폭 증가
• Leaf Blocks·Segment 증가
• Buffer Cache 점유
• INSERT·DELETE
• 포함 Column UPDATE
• Redo·Undo
• 다른 SQL Plan 변화

4.1 Index Fast Full Scan

정렬이 필요 없고 Query가 Index만으로 완성된다면 작은 Covering Index 전체를 Multiblock 방식으로 읽는 INDEX FAST FULL SCAN이 Table Full Scan보다 유리할 수 있습니다.

Table Blocks 100,000
Index Blocks  12,000

정렬 불필요·Index-only
  → Index Fast Full Scan 후보

5. Full Table Scan의 실제 비용

Oracle Full Table Scan은 High Water Mark 아래의 포맷된 Block을 순차적으로 읽습니다.

Segment Header
  → Low HWM 이하 Block
  → HWM 사이의 포맷 Block 확인
  → 각 Row Predicate 평가

5.1 High Water Mark

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

과거 100,000 Block 사용
현재 Row는 10,000 Block 분량
HWM 유지

→ Full Scan이 넓은 Block 범위를 읽을 수 있음

필요 시 TRUNCATE, SHRINK SPACE, MOVE, Partition 재구성을 검토하되 가용성과 Index 재구축 비용을 포함합니다.

5.2 Small Table

Table이 매우 작으면 선택적 조건이어도 다음이 더 저렴할 수 있습니다.

Index Root·Branch·Leaf
+ ROWID Table Access

vs

작은 Table Block 전체 Scan

Oracle은 HWM 아래 Block 수가 Multiblock Read 단위보다 작은 경우 Full Scan이 Index Range Scan보다 저렴할 수 있다고 설명합니다.

5.3 Multiblock I/O

DB_FILE_MULTIBLOCK_READ_COUNT는 Sequential Scan에서 한 I/O로 읽는 최대 Block 수와 비용 계산에 영향을 줍니다.

적은 수의 큰 I/O
  vs
많은 Random·Single Block 접근

실제 효율은 Platform·Storage·System Statistics·동시 부하에 따라 달라집니다.

6. Direct Path와 Parallel Full Scan

6.1 Direct Path Read

일부 대량·병렬 Scan은 Buffer Cache를 우회해 Disk에서 PGA로 직접 읽을 수 있습니다.

Disk → PGA

모든 Full Scan이 Direct Path라는 뜻은 아닙니다. 실제 Wait·Plan·Parallel 상태를 확인합니다.

6.2 Parallel Full Scan

Parallel Execution Server가 Scan 범위를 분담하면 Wall Clock을 줄일 수 있습니다.

Serial Scan
  한 Process가 전체 범위 처리

Parallel Scan
  여러 PX Server가 Block Range 분담

주의합니다.

• 전체 CPU·I/O 사용량
• DOP
• 동시 Workload
• PX Server 확보
• Resource Manager
• 작은 Query의 Parallel Overhead

7. Partition Pruning이 경계를 바꾸는 이유

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

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

PARTITION RANGE SINGLE
  TABLE ACCESS FULL

비교 기준은 전체 Table Blocks가 아니라 Pruning 후 대상 Partition Blocks입니다.

전체 Table 10억 Row
대상 월 Partition 1천만 Row

→ 월 Partition Full Scan이
  Global Index + Random Table Access보다 유리할 수 있음

Local Index와 Partition별 CF·Statistics도 함께 확인합니다.

8. Row 폭과 Projection

Table Row가 넓으면 한 Block에 저장되는 Row 수가 적어지고, 필요한 Column에 따라 Access Path 이득이 달라집니다.

넓은 Projection

SELECT *
FROM orders
WHERE order_date BETWEEN :from_dt AND :to_dt;

많은 Table Column이 필요하므로 Index 이후 Table Access 비용이 커집니다.

좁은 Projection

SELECT order_id, amount
FROM orders
WHERE order_date >= :from_dt
AND   order_date <  :to_dt;

Covering Index 후보가 될 수 있습니다.

불필요한 SELECT *를 제거하면 새 Index 없이 기존 Index가 Covering이 될 수도 있습니다.

9. 정렬·Top-N과 첫 행 목표

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

SELECT order_id,
       order_date,
       amount
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);

가능한 Plan입니다.

WINDOW NOSORT STOPKEY
  TABLE ACCESS BY INDEX ROWID
    INDEX RANGE SCAN

전체 고객 이력 비율이 커도 정렬된 첫 N건에서 중단하면 Index 경로가 유리할 수 있습니다.

9.1 Partial Fetch 주의

Index 경로: 첫 20행 Fetch
Full Scan: 전체 결과 Fetch

→ 공정한 비교 아님

첫 페이지 목표면 두 경로 모두 같은 N행까지, 전체 처리 목표면 둘 다 End-of-Fetch까지 비교합니다.

10. Data Skew와 Bind별 손익분기점

다음 분포를 가정합니다.

status='ERROR'      0.01%
status='COMPLETE'  80%

같은 SQL이라도 Bind에 따라 최적 경로가 다를 수 있습니다.

ERROR
  → Index 후보 적음
  → Index Range Scan 유리 가능

COMPLETE
  → Table Block 대부분 방문
  → Full Scan 유리 가능

확인합니다.

• Histogram
• E-Rows와 A-Rows
• Bind Peeking
• Adaptive Cursor Sharing
• Child Cursor
• 대표·극단 Bind의 Runtime 통계

평균 선택도 하나로 모든 Bind의 손익분기점을 결정하지 않습니다.

11. 실측으로 손익분기점을 찾는다

Range를 단계적으로 늘립니다.

1일
7일
30일
90일
180일
1년

각 범위에서 Index와 Full Scan 후보를 비교합니다.

Index 후보

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(o orders_date_ix) */
       SUM(o.amount)
FROM   orders o
WHERE  o.order_date >= :from_dt
AND    o.order_date <  :to_dt;

Full Scan 후보

SELECT /*+ GATHER_PLAN_STATISTICS FULL(o) */
       SUM(o.amount)
FROM   orders o
WHERE  o.order_date >= :from_dt
AND    o.order_date <  :to_dt;

집계 SQL은 양쪽 모두 조건에 맞는 Row를 끝까지 처리하게 만들어 Partial Fetch 차이를 줄이는 데 유용합니다.

12. 실행계획과 Runtime 통계

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

확인합니다.

12.1 Index 경로

Index A-Rows·Buffers
Table A-Rows·Buffers
후보와 최종 Row 차이
Access·Index Filter·Table Filter
Clustering·BATCHED

12.2 Full Scan 경로

Scan 대상 Blocks
Buffers·Reads
Direct Path·Parallel
Partition Pruning
Predicate CPU

상위 Operation Buffers가 하위 Row Source 작업을 포함할 수 있으므로 모든 Line을 단순 합산하지 않습니다.

13. 개념적 손익분기 모델

실제 Oracle Cost Formula를 단순화한 개념 모델입니다.

Index 경로
  ≈ Index Leaf Blocks
   + 예상 Table Block 방문
   + 반복·Sort·Join

Full Scan 경로
  ≈ Scan 대상 Table Blocks / 유효 Multiblock 처리
   + Predicate·Parallel Overhead

이 모델의 핵심은 다음입니다.

결과 Row 비율
  → 참고값

실제 Block 비용
  → 최종 판단 기준

14. 업무 유형별 출발점

이 표는 출발점이며 실제 실행통계로 검증합니다.

15. 적용 판단 절차

1. 사용자 목표가 첫 행·첫 페이지인지 전체 처리인지 정한다.
2. Index Leaf 작업·후보 ROWID·Table Block 방문을 분리한다.
3. CF·Covering·Row 폭·Bind 편중을 확인한다.
4. Full Scan의 HWM·Small Table·MBRC·Parallel을 확인한다.
5. Partition Pruning 후 실제 Scan Block을 계산한다.
6. 동일 SQL 의미·Bind·Fetch·동시성으로 후보를 측정한다.
7. Range를 단계적으로 늘려 비용 역전 지점을 찾는다.
8. Sort·TEMP·CPU·Elapsed·P95를 포함한다.
9. 신규 Index의 DML·Redo·공간·다른 SQL 회귀를 검증한다.
10. Canary·Monitoring·Rollback 조건으로 적용한다.

자주 혼동하는 판단