SQL튜닝이론 - INDEX RANGE SCAN 과 INDEX FULL SCAN 비교

SQL튜닝이론 - INDEX RANGE SCAN 과 INDEX FULL SCAN 비교

 

 

인덱스를 스캔하는 방법은 기본적인 INDEX RANGE SCAN 이외에도 여러가지 방법이 있습니다. INDEX FULL SCAN, INDEX UNIQUE SCAN, INDEX SKIP SCAN, INDEX FAST FULL SCAN이 있습니다. 각 스캔 방식 별로 주요한 특징이 있습니다.

Index Range Scan

인덱스 루트에서 리프블록까지 수직적으로 탐색한 후에 필요한 범위만 수평적으로 탐색합니다. 이를 위한 필수선행조건으로 인덱스의 선두컬럼을 가공하지 않은 상태로 조건절에 사용해야합니다.

 

 select * from emp where deptno = 20;
--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |     1 |    87 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP    |     1 |    87 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN         | EMP_DEPTNO_IDX |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=20)

 

Index Full Scan

 

수직적 탐색 없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식입니다.

 

create index emp_ename_sal_idx on emp(ename, sal);

set autotrace traceonly exp

EXPLAIN plan FOR
select * 
from emp
where sal > 2000
order by ename;


SELECT *
  FROM TABLE(dbms_xplan.display);


-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     5 |   435 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP               |     5 |   435 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | EMP_ENAME_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SAL">2000)
       filter("SAL">2000)

 

 

데이커 검색을 위한 최적의 인덱스가 없을 때 차선으로 선택됩니다. 인덱스 선두 컬럼인 ename이 조건절에 없으므로 Index Range Scan이 불가능합니다. Index Full Scan을 통해 SAL 조건에 맞는 레코드를 스캔합니다.

 

Index Full Scan이 효율을 발휘하는 경우

 

인덱스 선두컬럼이 조건절에 없으면 옵티마이저는 우선적으로 Table Full Scan을 고려합니다. 하지만 대용량 테이블인 경우 인덱스가 차지하는 면적은 테이블 보다 훨씬 적습니다. 만약 인덱스 스캔 단계에서 대부분의 레코드를 필터링하고 일부만 테이블에서 엑세스 해야하는 경우라면 면적이 큰 테이블보다 인덱스를 스캔하는 것이 유리합니다.

 

create index emp_ename_sal_idx on emp (ename, sal);

EXPLAIN plan FOR
select *
from emp
where sal > 9000
order by ename;

SELECT *
  FROM TABLE(dbms_xplan.display);

  -------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |     1 |    87 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP               |     1 |    87 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | EMP_ENAME_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SAL">9000)
       filter("SAL">9000)

 

인덱스의 구성컬럼인 조건절의 범위가 매우 적은경우 INDEX FULL SCAN 방식이 효과적입니다.하지만 이방식은 조건절에 쓰인 적절한 선두컬럼을 가진 인덱스가 없는 차선책입니다. 수행빈도가 낮은 sql이라면 상관없지만 그렇지 않다면 SAL 컬럼을 선두로 갖는 인덱스를 생성해주는 것이 좋습니다.

 

Index Full Scan를 이용한 소트연산 생략

 

Index Full Scan을 진행하면 Range Scan 과 마찬가지로 결과집합이 인덱스 컬럼 순으로 정렬됩니다. 따라서 SORT ORDER BY 연산을 생략할 목적으로 사용할 수 있습니다.

 

EXPLAIN plan FOR
select /*+FIRST_ROWS*/
*
from emp
where sal > 1000
order by ename;

SELECT *
  FROM TABLE(dbms_xplan.display);

  -------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                   |    10 |   870 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP               |    10 |   870 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | EMP_ENAME_SAL_IDX |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

 

위 쿼리처럼 스캔해야하는 범위가 넓은 경우 직접 Table Full Scan 을 하는 것이 더 유리합니다. Index Full Scan 은 거의 모든 인덱스 레코드에 대해 테이블 엑세스가 발생하기 때문입니다. 그럼에도 옵티마이저가 인덱스를 활용하는 이유는 사용자가 first_rows 힌트를 사용했기 때문입니다.

 

first_rows 힌트

소트 연산을 생략할 수 있는 경우에 한하여 전체 인덱스 레코드 중집합 중 일부를 빠르게 엑세스합니다

 

만약 조건에 해당하는 전체 인덱스 레코드에 대해 엑세스 해야하는데 INDEX FULL SCAN을 사용한다면 훨씬 더 많은 시간이 소요됩니다.