인덱스 스캔 효율과 Covering: Leaf Scan·Table Access 줄이기

핵심 요약

인덱스가 적은 결과를 반환했다는 사실과 인덱스를 적게 읽었다는 사실은 다릅니다.

최종 결과 10행
  → Leaf Block 3개·Table Block 2개
  → 효율적일 가능성

최종 결과 10행
  → Leaf Block 20,000개·Table Block 5개
  → Leaf Scan 비효율

최종 결과 10행
  → Leaf Block 200개·Table Block 80,000개
  → ROWID·Table Access 비효율

진단 비용을 세 단계로 분리합니다.

1. Index Leaf Scan
   → 어느 범위의 Leaf Block·Entry를 읽었는가

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

3. Table Access
   → 몇 Table Block을 방문하고 몇 Row를 최종 제거했는가

Covering은 별도의 Oracle Index 종류가 아니라 특정 Query가 필요한 조건·조인·반환 컬럼을 하나의 Index에서 모두 얻어 Table Access를 생략할 수 있는 상태입니다.

Covering 이득
  → Table ROWID Access·Table Buffers·Sort 감소 가능

Covering 비용
  → Entry 폭·Leaf Block·Cache·DML·Redo·Undo·공간 증가

Oracle 일반 B-tree에는 다른 DBMS의 비정렬 INCLUDE와 동일한 문법이 없습니다. 후행 조회 컬럼도 Index Entry의 일부가 되므로 조회 이득과 전체 Workload 유지비를 함께 검증합니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 테이블 액세스 최소화·인덱스 설계 연결 범위에 해당합니다. B-tree 구조와 Start·Stop Key는 앞선 이론을 전제로 하며, Clustering Factor의 세부 산출 원리와 Table Random Access 최소화는 후속 이론과 연결합니다.

학습 목표

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

• 선택도와 Index Scan 효율을 구분한다.
• Index Operation의 A-Rows·Buffers·Reads가 나타내는 범위를 설명한다.
• V$SQL_PLAN_STATISTICS_ALL의 LAST_OUTPUT_ROWS, LAST_CR_BUFFER_GETS, LAST_DISK_READS, LAST_STARTS를 연결한다.
• A-Rows만으로 내부 검사 Entry 전체를 알 수 없는 이유를 설명한다.
• Leaf Scan·ROWID 후보·Table Filter·Clustering 비용을 구분한다.
• Filter 컬럼 추가와 완전 Covering의 차이를 설명한다.
• Index-only Access, Index Join Scan과 Index Fast Full Scan의 차이를 설명한다.
• 모든 Key가 NULL인 Row가 Covering·COUNT(*)에 미치는 영향을 설명한다.
• Top-N에서 조건·정렬·반환 컬럼을 함께 지원하는 설계를 설명한다.
• 넓은 Covering Index의 조회 이득과 DML·공간·회귀 비용을 비교한다.
• Invisible Index와 동일 조건 반복 측정으로 후보를 검증한다.

1. 선택도와 스캔 효율은 다른 지표다

1.1 선택도

선택도 = 조건 결과 Row ÷ 전체 Row

선택도는 Predicate가 얼마나 많은 Row를 선택하는지 나타냅니다.

1.2 스캔 효율

스캔 효율은 필요한 결과를 위해 얼마나 많은 Index Entry·Block을 읽었는지를 보는 관점입니다.

Scan 효율
  → 최종 Row 대비 Leaf Scan 작업량

100만 Row Table에서 결과가 10행이라도 다음 두 실행은 전혀 다릅니다.

실행 A
  Index Buffers 3
  Index A-Rows 10
  최종 Row 10

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

실행 B는 선택 결과가 적어도 넓은 Leaf 범위를 읽고 내부 Filter에서 대부분 제거했을 가능성이 있습니다.

2. 중간 컬럼 누락과 Leaf Scan

Index를 다음처럼 정의합니다.

(region_code, customer_grade, customer_id)

Query는 다음과 같습니다.

WHERE region_code = 'SEOUL'
AND   customer_id = 1001

region_code는 고정됐지만 중간 customer_grade가 빠졌습니다.

(SEOUL,A,1001)
(SEOUL,A,1002)
...
(SEOUL,B,1001)
...
(SEOUL,C,1001)

customer_id=1001 Entry가 Grade Group마다 분산될 수 있으므로 서울 Prefix의 여러 Leaf Entry를 읽어야 할 수 있습니다. 후행 customer_id가 Predicate Information에 나타나더라도 실제 Leaf Scan이 짧다는 뜻은 아닙니다.

해당 SQL만 보면 다음 순서가 더 짧은 Range를 만들 수 있습니다.

(region_code, customer_id, customer_grade)

그러나 Grade가 필수 Equality인 다른 핵심 SQL과 DML 비용까지 Workload 기준으로 평가합니다.

3. 실행계획 통계의 정확한 의미

V$SQL_PLAN_STATISTICS_ALL은 Row Source별 실행 통계를 제공합니다.

DBMS_XPLAN.DISPLAY_CURSOR(...,'ALLSTATS LAST')의 Starts, A-Rows, Buffers, Reads는 이와 연결됩니다.

3.1 A-Rows의 한계

Index Operation의 A-Rows는 상위 Operation으로 반환한 Entry·ROWID 수입니다.

Leaf Entry 검사      100,000
Index Filter 통과      1,000
Index A-Rows           1,000
Index Buffers          5,000

A-Rows만 보면 1,000개만 읽었다고 오해할 수 있습니다. 내부 Filter에서 제거된 Entry는 A-Rows에 모두 나타나지 않을 수 있으므로 Buffers·Reads와 Predicate를 함께 봅니다.

3.2 Starts의 반복 비용

Starts      10,000
A-Rows      20,000
Buffers     80,000

Row / Start
  = 2

Buffers / Start
  = 8

한 번의 Probe가 작아도 Nested Loops·INLIST에서 수천 번 반복되면 총 Logical I/O가 커집니다.

4. Leaf Scan·ROWID·Table Access를 분리한다

4.1 Leaf Scan 비효율

INDEX RANGE SCAN
  A-Rows 10
  Buffers 20,000

TABLE ACCESS
  A-Rows 10
  추가 Buffers 50

핵심 비용은 Table Random Access보다 넓은 Leaf Scan일 가능성이 큽니다.

우선 확인합니다.

• 중간 Index Column 누락
• 첫 Range Predicate의 폭
• 후행 Predicate의 Index Filter
• INLIST·Skip Scan·Nested Loops Starts
• Index Column 순서

4.2 ROWID 후보 과다와 Table Filter

INDEX RANGE SCAN
  A-Rows 100,000
  Buffers 200

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

Index는 비교적 적은 Buffer로 많은 ROWID를 만들었지만 Table에서 99,000 Row가 제거됐습니다.

우선 확인합니다.

• Table Filter Column을 Index에서 더 일찍 평가할 수 있는가
• Equality Column을 첫 Range 앞에 배치할 수 있는가
• Query 결과 비율이 높아 Full·Partition Scan이 더 유리한가
• Clustering Factor가 Table Access를 악화시키는가

4.3 Table Block 분산

같은 10,000 ROWID 후보라도 Table Buffers는 다를 수 있습니다.

좋은 Clustering 방향
  → 가까운 Index Key가 같은·인접 Table Block
  → Table Buffers 상대적으로 적음

나쁜 Clustering 방향
  → ROWID가 넓은 Table Block에 분산
  → Random Access·반복 Block 방문 증가

TABLE ACCESS BY INDEX ROWID BATCHED는 ROWID 처리 순서를 개선할 수 있지만 후보 ROWID 자체를 줄이지는 않습니다.

5. Filter 컬럼 추가와 Covering을 구분한다

5.1 Filter 컬럼 추가

기존
  (customer_id, order_date)

후보
  (customer_id, order_date, status)

Query가 다음과 같다고 가정합니다.

WHERE customer_id = :customer_id
AND   order_date >= :from_date
AND   status = :status

후행 status를 Index에서 평가하면 Table로 전달할 ROWID를 줄일 수 있습니다.

Leaf Scan
  → 날짜 범위 전체가 남을 수 있음

Index Filter
  → status에 맞는 ROWID만 Table 전달

Table Access
  → 감소

amount가 Index에 없으면 Table Access 자체는 남습니다.

5.2 완전 Covering

(customer_id, order_date, status, order_id, amount)

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

Filter 컬럼 추가
  → Table 방문 횟수 감소

Covering
  → Table 방문 자체 생략 가능

필요한 최소 수준까지만 확장합니다. Covering을 위해 너무 많은 컬럼을 추가하면 Index Leaf Scan과 DML 비용이 커질 수 있습니다.

6. Oracle의 Index-only Access 방식

6.1 Range Scan 기반 Covering

CREATE INDEX emp_x1
ON emp(deptno, sal, empno);

SELECT empno, sal
FROM   emp
WHERE  deptno = 20
AND    sal >= 3000;

모든 조건과 반환 컬럼이 Index에 있으면 Table을 방문하지 않을 수 있습니다.

INDEX RANGE SCAN EMP_X1

6.2 Index Fast Full Scan

INDEX FAST FULL SCAN은 Index Block 전체를 정렬 순서 없이 Multiblock 방식으로 읽을 수 있는 Access Path입니다.

특징
  → Index 전체 Block을 읽음
  → Key 순서를 보장하지 않음
  → 필요한 컬럼이 Index에 있으면 Table 대신 사용할 수 있음
  → Full Table Scan과 비용 비교

COUNT·집계·넓은 결과에서 Table보다 작은 Index를 읽는 후보가 될 수 있습니다.

6.3 Index Join Scan

Optimizer는 여러 B-tree Index에서 필요한 컬럼을 얻어 RowID로 Hash Join하는 INDEX JOIN SCAN을 선택할 수 있습니다.

Index A
  → 조건 Column·ROWID

Index B
  → 반환 Column·ROWID

RowID Hash Join
  → Table Access 없이 결과 구성 가능

이는 하나의 넓은 Covering Index를 반드시 만들어야 한다는 뜻이 아닙니다. 하지만 Index Join은 여러 Index를 읽고 Join하는 비용이 있으므로 실행 빈도·각 Index Scan량과 Table Access 대안을 비교합니다.

7. 모든 Key가 NULL인 Row와 Index-only 처리

Oracle 일반 B-tree는 모든 Index Key Column이 NULL인 Row를 저장하지 않습니다.

CREATE INDEX t_nullable_ix
ON t(nullable_col);

nullable_col IS NULL인 Row는 Index에 없습니다.

따라서 다음 Query를 이 Index만 읽어 완전하게 처리할 수 없습니다.

SELECT COUNT(*)
FROM   t;

다음처럼 NOT NULL Column이 포함되면 모든 Row가 Index Entry로 존재할 수 있습니다.

CREATE INDEX t_id_ix
ON t(id);  -- id NOT NULL

Index Fast Full Scan이나 Index-only 처리에서 다음을 확인합니다.

• 모든 결과 Row가 Index에 존재하는가
• Index Column 중 NOT NULL 보장이 있는가
• Predicate가 All-Key-NULL Row를 결과에서 제외하는가
• 필요한 반환 컬럼이 Index에 있는가

8. Top-N·정렬·Covering 결합

Query입니다.

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_topn_ix
ON orders(
  customer_id,
  order_date DESC,
  order_id DESC,
  amount
);

가능한 이득입니다.

customer_id Prefix 고정
  → 최신 방향으로 Range Scan
  → 20건에서 조기 중단
  → amount까지 Index에서 조회
  → Sort·Table Access 감소 가능

실제 Plan에서 확인합니다.

• INDEX RANGE SCAN DESCENDING 또는 호환 방향
• SORT ORDER BY·STOPKEY 존재 여부
• Index A-Rows가 20 부근에서 멈추는가
• Index Buffers·Reads
• Partial Fetch와 전체 Fetch 조건
• amount Update·INSERT Redo 증가

ORDER BY 결과가 필요하면 현재 Index Scan 순서에만 의존하지 않고 SQL에 명시합니다.

9. 넓은 Covering Index의 비용

Oracle의 후행 반환 컬럼도 Index Entry 일부입니다.

다음 비용이 증가할 수 있습니다.

• Entry 길이와 Leaf Block 수
• Tree Height·Segment Size
• Buffer Cache 점유
• INSERT·DELETE 유지 작업
• 후행 컬럼 UPDATE 시 Entry 변경
• Undo·Redo·Storage·Backup
• Block Split·Hot Leaf
• 통계 수집 시간
• Optimizer의 후보 증가
• 기존 Index와 중복

특히 LOB·긴 문자열·자주 변경되는 컬럼은 Covering 후보에 부적합할 수 있습니다. Oracle Index Key 길이 제한과 지원 데이터 타입도 확인해야 합니다.

10. Covering이 유리한 경우와 제한적인 경우

유리할 가능성이 큰 경우

• 초당 매우 자주 실행되는 소량 조회
• NL Join Inner의 반복 Access
• Table Row가 넓고 필요한 컬럼은 적음
• Clustering이 나빠 Table Random Access가 큼
• 정렬된 Top-N에서 조기 중단 가능
• Table Filter 때문에 후보 대부분이 탈락

이득이 제한될 수 있는 경우

• 결과가 Table의 큰 비율
• 조회 컬럼이 많고 Index가 Table 크기에 가까움
• DML이 매우 빈번함
• 후행 컬럼이 자주 Update됨
• Index Buffers 자체가 큼
• Full·Partition·Parallel Scan이 더 효율적
• 기존 Index 중복이 크게 증가

Table Access 0
  ≠ 전체 비용 0

Index 자체의 Leaf Scan·Cache·DML 비용을 포함합니다.

11. 개선 전후 예제

변경 전

Index
  (customer_id)

Plan
--------------------------------------------------------------------------------
| Operation                         | Starts | A-Rows | Buffers | Reads |
--------------------------------------------------------------------------------
| TABLE ACCESS BY INDEX ROWID       |      1 |     20 |  12,000 | 1,000 |
|  INDEX RANGE SCAN                 |      1 |  8,000 |     120 |    10 |
--------------------------------------------------------------------------------
| SORT ORDER BY STOPKEY             |      1 |     20 |  12,050 | 1,000 |
--------------------------------------------------------------------------------

최신 20건을 얻기 위해 8,000 ROWID를 Table에서 읽고 정렬했습니다.

변경 후 후보

(customer_id, order_date DESC, order_id DESC, amount)

--------------------------------------------------------------------------------
| Operation                         | Starts | A-Rows | Buffers | Reads |
--------------------------------------------------------------------------------
| INDEX RANGE SCAN DESCENDING       |      1 |     20 |       5 |     0 |
--------------------------------------------------------------------------------

조건·정렬·반환 컬럼을 Index에서 처리해 Table Access와 Sort를 줄였습니다.

동시에 다음을 측정합니다.

• Index Segment 증가량
• INSERT TPS·Redo
• amount Update 비용
• 기존 Index 중복
• 다른 Query의 Plan 변화
• 대표·극단 customer_id

12. 조회 이득을 수치화한다

다음 누적 통계를 가정합니다.

Executions           10,000
변경 전 Buffers/Exec 200
변경 후 Buffers/Exec   5

절감 Logical I/O
  = (200 - 5) × 10,000
  = 1,950,000 Buffers

다음 비용도 같은 기간으로 계산합니다.

추가 INSERT·UPDATE 시간
추가 Redo·Undo Byte
Index Segment 증가
Cache 점유
다른 SQL의 Buffers·Reads 변화

조회 실행 빈도가 높으면 큰 누적 이득이 가능하지만, 하루 몇 번 실행되는 SQL을 위해 DML이 매우 많은 Table에 넓은 Index를 추가하면 전체 Workload 비용이 증가할 수 있습니다.

13. Invisible Index와 안전한 검증

신규 Covering 후보를 Invisible로 만들 수 있습니다.

CREATE INDEX orders_cover_ix
ON orders(
  customer_id,
  order_date DESC,
  order_id DESC,
  amount
)
INVISIBLE;

검증 Session에서만 사용합니다.

ALTER SESSION
SET optimizer_use_invisible_indexes = TRUE;

확인 항목입니다.

• 후보 사용 전후 Plan·Buffers·Reads·Sort
• 대표·극단 Bind
• Partial Fetch·전체 Fetch
• NL Join·INLIST Starts
• 다른 핵심 SQL 회귀

Invisible Index도 DML로 유지되므로 실제 쓰기 비용을 포함해 테스트할 수 있습니다. 기존 Index 제거 후보를 Invisible로 전환하면 Query Plan 의존성은 시험할 수 있지만 실제 Drop 후의 DML 비용 제거 효과는 별도 검증해야 합니다.

14. 실행계획과 Trace 검증 절차

SELECT /*+ GATHER_PLAN_STATISTICS */
       order_id,
       order_date,
       amount
FROM   orders
WHERE  customer_id = :customer_id
AND    status = :status
ORDER BY order_date DESC
FETCH FIRST 20 ROWS ONLY;

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

진단 순서입니다.

1. 최종 Row와 Index·Table A-Rows를 비교한다.
2. Index Buffers·Reads로 Leaf Scan을 측정한다.
3. Index Filter와 Table Filter 위치를 확인한다.
4. Table Buffers·Reads로 ROWID·Clustering 비용을 본다.
5. Starts로 반복 Probe 총비용을 계산한다.
6. Sort·TEMP·Stopkey를 확인한다.
7. Range Covering·Fast Full Scan·Index Join·Full Scan 후보를 비교한다.
8. All-Key-NULL Row와 결과 정합성을 검증한다.
9. SELECT·DML·Redo·공간과 다른 SQL 회귀를 측정한다.
10. Invisible·Canary·Rollback으로 배포한다.

15. 자주 혼동하는 판단