Index Full Scan·Fast Full Scan·Table Full Scan: 정렬·I/O·Covering

핵심 요약

INDEX FULL SCAN, INDEX FAST FULL SCAN, TABLE ACCESS FULL은 모두 넓은 범위를 읽을 수 있지만 읽는 객체·순서·I/O 방식·결과 완성 방식이 다릅니다.

INDEX FULL SCAN
  → Index 한쪽 끝 Leaf로 이동
  → Leaf Link를 Key 순서로 전체 순회

INDEX FAST FULL SCAN
  → Index Segment Block 전체를 넓게 읽음
  → Key 순서 포기
  → Index 안의 값만으로 결과 생성

TABLE ACCESS FULL
  → Table High Water Mark 아래 Scan 대상 Block 읽기
  → Predicate 적용

FAST는 모든 상황에서 가장 빠르다는 뜻이 아닙니다. 정렬 순서를 포기해 Multiblock·병렬 처리 후보가 되는 Access Path의 이름입니다.

이 이론의 범위

이 이론은 SQLP의 SQL 고급활용 및 튜닝 → 인덱스 튜닝 → 인덱스 스캔 방식 범위에서 Index Full Scan·Fast Full Scan·Table Full Scan을 비교합니다.

학습 목표

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

• Range Scan과 Index Full Scan의 범위 차이를 설명한다.
• Index Full Scan의 Key 순서 보존과 Descending Scan을 설명한다.
• Index Full Scan도 Covering이면 Table Access 없이 끝날 수 있음을 설명한다.
• Index Fast Full Scan의 비정렬·Multiblock·병렬 특성을 설명한다.
• Fast Full Scan이 일반적으로 Query를 Index만으로 완성해야 하는 이유를 설명한다.
• 일반 B-tree의 All-Key-NULL 미저장 규칙과 COUNT(*)·COUNT(col)을 설명한다.
• Leaf Blocks와 전체 Index Segment Block의 차이를 설명한다.
• Table Full Scan의 HWM·Small Table·병렬 이점을 설명한다.
• Partition·압축·정렬·Partial Fetch가 후보 비용에 미치는 영향을 설명한다.
• A-Rows·Buffers·Reads·Sort·Table Access·Elapsed로 세 경로를 검증한다.

1. 먼저 Range Scan 가능성을 확인한다

다음 Index를 가정합니다.

CREATE INDEX emp_dept_empno_ix
ON emp(deptno, empno);

1.1 Index Range Scan

SELECT empno
FROM   emp
WHERE  deptno = 10;

INDEX RANGE SCAN EMP_DEPT_EMPNO_IX

deptno=10의 Start Key로 이동해 해당 Key Group이 끝나면 멈춥니다.

1.2 Index Full Scan

SELECT deptno, empno
FROM   emp
WHERE  deptno IS NOT NULL
ORDER  BY deptno, empno;

INDEX FULL SCAN EMP_DEPT_EMPNO_IX

좁은 Start·Stop Range가 아니라 Index 한쪽 끝부터 전체 Leaf Entry를 읽습니다.

Range Scan
  → 일부 Key 범위

Index Full Scan
  → 전체 Leaf Entry

전체 Scan 후보를 비교하기 전에 Predicate를 Sargable하게 바꾸거나 더 적절한 Leading Column Index로 Range를 줄일 수 있는지 먼저 확인합니다.

2. INDEX FULL SCAN

2.1 동작

Ascending Full Scan은 Root·Branch를 통해 왼쪽 끝 Leaf로 이동하고 Leaf Link를 따라 전체 Entry를 읽습니다.

Root·Branch
  ↓
왼쪽 끝 Leaf
  → 다음 Leaf
  → ...
  → 오른쪽 끝

Descending Full Scan은 반대 끝에서 시작합니다.

INDEX FULL SCAN DESCENDING

2.2 Key 순서와 Sort

SELECT deptno, empno
FROM   emp
WHERE  deptno IS NOT NULL
ORDER  BY deptno, empno;

Index Key 순서와 ORDER BY가 호환되면 SORT ORDER BY를 생략할 수 있습니다.

ORDER BY deptno DESC, empno DESC

전체 방향을 뒤집은 정렬은 INDEX FULL SCAN DESCENDING으로 지원할 수 있습니다. 혼합 방향은 해당 방향으로 정의한 Index가 필요할 수 있습니다.

업무상 순서는 반드시 SQL의 ORDER BY로 명시합니다. 현재 Plan이 정렬된 Row를 생산한다는 사실만으로 ORDER BY 없는 결과 순서를 보장하지 않습니다.

2.3 Covering과 Table Access

Index Full Scan은 두 형태가 모두 가능합니다.

Covering
  INDEX FULL SCAN
  → Index만 읽고 결과 완성

Non-Covering
  TABLE ACCESS BY INDEX ROWID
    INDEX FULL SCAN
  → Index 전체에서 ROWID 생성
  → 필요한 Table Column 읽기

따라서 “Index Full Scan에는 항상 Table Access가 붙는다”는 설명은 부정확합니다.

2.4 Filter 비용

SELECT deptno, empno
FROM   emp
WHERE  empno >= 9000;

선두 deptno가 없어 좁은 Range를 만들기 어렵다면 전체 Index를 읽고 empno를 Filter할 수 있습니다.

INDEX FULL SCAN EMP_DEPT_EMPNO_IX
  filter(empno>=9000)

A-Rows가 적음
  ≠ 적은 Entry만 읽음

Index Buffers·Reads로 전체 Leaf 작업량을 확인합니다.

3. INDEX FAST FULL SCAN

3.1 동작

Fast Full Scan은 Leaf Link를 Key 순서로 따라가지 않고 Index Segment Block을 넓게 읽습니다.

Index Segment Block Scan
  → Root·Branch·Leaf Block I/O 가능
  → 결과 값은 Leaf Entry에서 생성
  → Key 순서 보장 안 함

대표 Operation입니다.

INDEX FAST FULL SCAN EMP_DEPT_EMPNO_IX

3.2 Multiblock·병렬

Full Table Scan과 유사하게 Multiblock I/O를 사용할 수 있고 조건에 따라 병렬 실행 후보가 됩니다.

Index Full Scan
  → Ordered Scan
  → Key 순서 활용

Index Fast Full Scan
  → Unordered Block Scan
  → Multiblock·Parallel 가능

DB_FILE_MULTIBLOCK_READ_COUNT, System Statistics, Storage 처리량과 병렬 환경이 예상 비용에 영향을 줄 수 있습니다.

3.3 Index-only 조건

Fast Full Scan은 일반적으로 Query에 필요한 정보를 Index 안에서 얻을 수 있을 때 Table Full Scan의 대안이 됩니다.

SELECT deptno, empno
FROM   emp
WHERE  deptno IS NOT NULL;

필요한 Predicate·Projection을 Index에서 처리할 수 있습니다.

INDEX FAST FULL SCAN EMP_DEPT_EMPNO_IX

Index에 없는 ename을 조회한다면 작은 Index 전체를 읽고 대량 ROWID로 Table을 방문하는 것보다 Table Full Scan이 더 저렴할 가능성이 큽니다.

3.4 정렬 미보장

SELECT deptno, empno
FROM   emp
ORDER  BY deptno, empno;

Fast Full Scan이 선택되면 다음과 같이 Sort가 필요할 수 있습니다.

SORT ORDER BY
  INDEX FAST FULL SCAN EMP_DEPT_EMPNO_IX

Sort·TEMP가 큰 경우 Ordered Index Full Scan과 비교합니다.

4. TABLE ACCESS FULL

Table Full Scan은 Table Segment의 High Water Mark 아래 Scan 대상 Block을 읽고 각 Row에 Predicate를 적용합니다.

TABLE ACCESS FULL EMP

유리할 수 있는 상황입니다.

• Table의 많은 Row·Block이 필요
• Query가 Index에 없는 Column을 많이 조회
• Table이 작음
• 적합한 Index가 없음
• Index 경로의 ROWID Table Access가 과도
• 병렬·대량 I/O 처리량이 유리
• Partition Pruning 후 일부 Partition만 Scan

SELECT empno, ename, sal, hiredate, job, mgr, deptno
FROM   emp;

대부분의 Row와 폭넓은 Column이 필요하면 Table을 직접 읽는 편이 단순하고 저렴할 수 있습니다.

5. 같은 SQL에서 세 후보를 비교한다

SELECT deptno, empno
FROM   emp
WHERE  deptno IS NOT NULL;

후보 A: Index Full Scan

Key 순서로 전체 Leaf Entry
→ ORDER BY가 있으면 Sort 제거 가능

후보 B: Index Fast Full Scan

Index Segment 전체 비정렬 Scan
→ Index가 Table보다 작으면 유리 가능
→ Multiblock·Parallel 후보

후보 C: Table Full Scan

Table HWM 아래 Block Scan
→ Table이 작거나 Index 이점이 작으면 유리

비교 항목입니다.

6. COUNT와 B-tree NULL 저장 규칙

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

6.1 COUNT(*)

SELECT COUNT(*)
FROM   emp;

Index로 Table 전체 Row를 세려면 모든 Table Row가 Index Entry에 존재해야 합니다.

CREATE UNIQUE INDEX emp_pk
ON emp(empno);

empno가 NOT NULL이면 모든 Row가 Index Entry를 가지므로 다음이 후보가 될 수 있습니다.

SORT AGGREGATE
  INDEX FAST FULL SCAN EMP_PK

6.2 COUNT(nullable_col)

CREATE INDEX emp_comm_ix
ON emp(comm);

SELECT COUNT(comm)
FROM   emp;

COUNT(comm)은 NULL을 제외합니다. 단일 Nullable Column B-tree도 NULL Row를 저장하지 않으므로 Query 의미와 Index Entry 집합이 맞을 수 있습니다.

6.3 Nullable Index로 COUNT(*)

SELECT COUNT(*)
FROM   emp;

EMP_COMM_IX에는 comm IS NULL Row가 없으므로 이 Index만으로 전체 Row 수를 정확히 만들 수 없습니다.

6.4 복합 Index

CREATE INDEX emp_comm_empno_ix
ON emp(comm, empno);

empno가 NOT NULL이면 (NULL,empno) Entry가 저장되어 모든 Row를 표현할 수 있습니다.

COUNT(*) Index 후보
  → 필요한 모든 Row가 Index에 존재하는지 검증

7. Segment Block과 Leaf Block을 구분한다

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

LEAF_BLOCKS는 Leaf Block 통계입니다. Fast Full Scan은 Index Segment를 넓게 읽으므로 Root·Branch·Leaf를 포함한 실제 Segment·Runtime Buffers·Reads를 확인합니다.

SELECT segment_name,
       blocks,
       bytes
FROM   user_segments
WHERE  segment_name IN ('EMP','EMP_DEPT_EMPNO_IX');

Partitioned·Compressed Index라면 전체와 대상 Partition의 크기를 구분합니다.

통계와 실제 Segment 상태가 다를 수 있으므로 마지막 분석 시점도 확인합니다.

8. Partition·압축·병렬

8.1 Partitioned Index

Partitioned Index에서는 필요한 Index Partition만 Full·Fast Full Scan할 수 있습니다.

PARTITION RANGE SINGLE
  INDEX FAST FULL SCAN

전체 Index 크기가 아니라 Pruning된 Partition의 Block과 Starts를 기준으로 비교합니다.

8.2 Compression

Index Compression은 Segment Block 수와 I/O를 줄일 수 있지만 CPU와 DML 특성이 달라질 수 있습니다.

작은 압축 Index
  → Fast Full Scan 이점 가능

압축·해제 CPU와 DML
  → 별도 검증

8.3 Parallel

Fast Full Scan과 Table Full Scan은 병렬 후보가 될 수 있습니다. Elapsed가 줄더라도 전체 CPU·I/O·PX Server 사용량은 증가할 수 있습니다.

9. Partial Fetch와 전체 Fetch

첫 페이지 20행만 필요한 Query와 전체 결과를 모두 처리하는 Query는 비교 계약이 다릅니다.

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

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

Index Full Scan은 정렬된 앞부분을 빨리 제공할 수 있지만 Fast Full Scan·Table Full Scan + Sort는 전체 작업 후 Row를 반환할 수 있습니다.

다음을 확인합니다.

• Fetch Calls
• Rows Processed
• End of Fetch Count
• Sort·TEMP
• Application Row Limit

서로 다른 Fetch 범위의 Elapsed를 직접 비교하지 않습니다.

10. 실행계획 비교

Index Full Scan 후보

SELECT /*+ GATHER_PLAN_STATISTICS INDEX(e emp_dept_empno_ix) */
       deptno, empno
FROM   emp e
WHERE  deptno IS NOT NULL
ORDER  BY deptno, empno;

Index Fast Full Scan 후보

SELECT /*+ GATHER_PLAN_STATISTICS INDEX_FFS(e emp_dept_empno_ix) */
       deptno, empno
FROM   emp e
WHERE  deptno IS NOT NULL;

Table Full Scan 후보

SELECT /*+ GATHER_PLAN_STATISTICS FULL(e) */
       deptno, empno
FROM   emp e
WHERE  deptno IS NOT NULL;

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

확인합니다.

• Scan Operation과 대상 Object·Partition
• Starts·E-Rows·A-Rows
• Buffers·Reads
• Sort·Memory·TEMP
• Table Access 여부
• Parallel Operation·DOP
• 실제 Fetch 범위
• CPU·Elapsed·P95
• Index·Table Block 수
• DML·Redo·공간과 다른 SQL 회귀

10.1 상위 Buffers 합산 주의

상위 Operation Buffer에는 하위 Row Source 작업이 포함될 수 있습니다. 모든 Line의 Buffers를 단순 합산하지 않고 비용이 집중된 Branch와 Statement 총량을 구분합니다.

11. 선택 예제

Table·Index 통계입니다.

EMP Table Blocks                100,000
EMP_DEPT_EMPNO_IX Segment Blocks 15,000

정렬 없는 Index-only 집계

Index Fast Full Scan
  Buffers 15,000
  Sort 없음
  Table Access 없음

Table Full Scan
  Buffers 100,000

Fast Full Scan이 유리할 수 있습니다.

ORDER BY 포함

Index Full Scan
  Buffers 12,000
  Sort 없음

Index Fast Full Scan
  Buffers 15,000
  Sort·TEMP 8,000

Ordered Full Scan이 유리할 수 있습니다.

Non-Covering Projection

Index Full Scan
  Index Buffers 12,000
  Table Buffers 90,000

Table Full Scan
  Buffers 100,000

Index 순서 이득보다 Table Random Access가 크면 Full Scan이 유리할 수 있습니다.

12. 자주 혼동하는 판단