[SQL 튜닝 실습 1-4] 인덱스 풀 스캔 튜닝 개념

인덱스 풀 스캔

인덱스도 하나의 객체로 인덱스 스캔 후 구성 컬럼만으로 결과 집합을 도출할 수 없는 경우, 항상 테이블 랜덤 액세스가 일어나게 된다.
테이블 랜덤 액세스는 DBMS 성능의 큰 장애물이다. 

* 테이블 랜덤 액세스를 최소화한다
* 테이블 순차 액세스를 최대화한다

인덱스 풀 스캔은 인덱스라는 객체만을 풀 스캔해서 원하는 데이터를 가져오는 것으로, 인덱스 튜닝에서 아주 중요한 부분을 차지한다.

 

인덱스 풀 스캔의 종류

* 인덱스 풀 스캔
* 인덱스 패스트 풀 스캔

 

오라클은 데이터를 가져올 때 항상 블록 단위로 가져온다
단, 한 건의 행만 가져온다고 해도 해당 행이 속해있는 블록 전체를 가져오게 된다
(*) 오라클은 한 블록당 8KB의 디스크 공간을 차지하는데, 이게 바로 블록 단위 I/O 이다.

 

 

블록 단위 I/O

싱글 블록 I/O

한번의 읽기를 통해 한 개의 블록을 읽는 방식으로 일반적인 인덱스 스캔시 사용
실시간으로 자주 수행되는 SQL문에 아주 유리하다.

 

멀티 블록 I/O

한번의 읽기를 통해 여러 개의 블록을 읽는 방식으로 일반적으로 테이블 풀 스캔시 사용
효율적인 인덱스가 아니면 오히려 테이블 풀 스캔이 성능이 좋을 수 있는 이유가 바로 이것이다
자주 호출되는 SQL문에는 부적합한 방법이 된다.

하지만, 멀티 블록 I/O 읽기 방식을 인덱스 스캔에서도 사용할 수 있는 방법이 있다.
그것이 바로 인덱스 패스트 풀 스캔이다

 

인덱스 패스트 풀

단점은 인덱스는 정렬된 상태를 유지하는 객체지만, 
인덱스 패스트 풀 스캔을 통한 결과 집합은 인덱스에 기정렬되어있는 데이터의 정렬을 보장하지 않는다.
기정렬된 데이터를 검색하는 부분 범위 처리방식 등에는 부적합하며, 통계나 집계 도출할 경우에는 아주 유용하게 사용가능하다.

 

 

인덱스 풀 스캔

인덱스 풀 스캔 튜닝은 인덱스를 풀 스캔해서 원하는 결과를 빠르게 가져오는 튜닝 기법으로, 

다음의 상황에서 성능을 극대화할 수 있다

* 인덱스 구성 컬럼만으로 결과를 도출할 수 있는 경우
* 인덱스 구성 컬럼을 추가해서 결과 도출할 수 있는 경우 인덱스 컬럼 추가
* 부분 범위 처리 시 인덱스만 부분적으로 읽어서 성능 극대화 (인덱스 풀 스캔일 경우에만)

 

인덱스 풀 스캔 튜닝 관련 힌트

INDEX_FFS

테이블 풀 스캔 대신에 인덱스 패스트 풀 스캔 유도

SELECT /*+ INDEX_FFS(테이블 인덱스) */

INDEX_FFS 힌트를 사용함으로써 EMPNO 컬럼으로 된 인덱스를 인덱스 패스트 풀 스캔하게 된다.

(*) EMPNO 컬럼의 결과 집합의 정렬을 보장하지 않는다.

SELECT /*+ INDEX_FFS(EMP EMP_EMPNO) */
EMPNO
FROM EMP
WHERE EMPNO > 200;