4. 친절한 SQL 튜닝

인덱스 확장기능 사용법

Index Range Scan : 인덱스 루트에서 리프 블록까지 수직적으로 탐색한 후에 '필요한 범위'만 스캔하는 방식이다.

SET AUTOTRACE TRACEONLY EXP

SELECT *
  FROM EMP
  WHERE SAL > 2000
  ORDER BY ENAME ;

• 인덱스를 RANGE SCAN 하려면 선두 컬럼을 가공하지 않은 상태로 조건절에 사용해야 한다.
• 반대로, 선두 컬럼을 가공하지 않은 상태로 조건절에 사용하면 INDEX RANGE SCAN은 무조건 가능하다.
• INDEX RANGE SCAN을 한다고 성능이 무조건 우수한 것은 아니다. 성능은 인덱스 스캔 범위, 테이블 액세스 횟수를 얼마나 줄일 수 있느냐에 결정된다.

 

Table Full Scan : 수직적 탐색없이 인덱스 리프 블록을 처음부터 끝까지 수평적으로 탐색하는 방식

CREATE INDEX EMP_ENAME_SAL_IDX ON EMP (ENAME, SAL) ;

SET AUTOTRACE TRACEONLY EXP

SELECT *
  FROM EMP
  WHERE SAL > 2000
  ORDER BY ENAME ;

• 위 SQL 처럼 인덱스 선두 컬럼인 ENAME이 조건절에 없으므로 INDEX RANGE SCAN은 불가하고 DBMS의 옵티마이저는 Table Full Scan을 고려한다.
• 그런데 대용량 테이블이어서 Table Full Scan에 대한 부담감이 따르면 옵티마이저는 인덱스 활용을 다시 고려한다.

 

Index Range Scan VS Table Full Scan

• SELECT 해오는 데이터의 수가 적으면 Index Range Scan이 유리하다.
• SELECT 해오는 데이터의 수가 많으면 Table Full Scan이 유리하다.

Index Unique Scan : 수직적 탐색으로 데이터를 찾는 방식으로, Unique 인덱스를 '=' 조건으로 탐색하는 경우에 작동

create unique index pk_emp on emp(empno) ;

alter table emp add
constraint pk_emp primary key(empno) using index pk_emp ;

set autotrace traceonly explain
select empno, ename from emp where empno = 7788 ;

• Unique 인덱스 컬럼은 중복 값이 입력되지 않게 DBMS가 데이터 정합성 관리를 도와준다.
• Unique 인덱스라고 해도 범위검색 조건(Between, Like, 부등호)으로 검색할 때는 Index Range Scan으로 처리된다.

Index Skip Scan : 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 '가능성 있는' 리프 블록만 골라서 액세스하는 스캔 방식

• 인덱스 선두 컬럼의 Distinct Value 개수가 적고 후행 컬럼의 Distinct Value 개수가 많을 때 유용하다.