인덱스 Range Scan이 되기 위한 선행 조건

인덱스 Range Scan이 되기 위한 선행 조건


학교이름, 나이, 이름, 주소로 구성된 테이블이 있다고 가정해보자.



빠른 검색을 위해서 인덱스를 학교 이름, 나이, 이름으로 구성해서 만들었다고 가정해보자.


CREATE  INDEX SCOTT.student_idx

ON SCOTT.STUDENT_TEST ("SCHOOL_NAME" ASC,"AGE" ASC,"NAME" ASC);


인덱스 구성의 순서로 인해 학교순으로 정렬하고, 나이로 정렬하고, 이름으로 정렬해서 데이터를 찾는다.


그렇기 때문에 이름을 조건으로 데이터를 검색하였을 때 결국 모든 리프노드를 다 검색해야한다.





그렇기 때문에 인덱스를 Range Scan  하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야한다.



그렇다면 만약 인덱스에 사용된 컬럼이 가공 되었으면 인덱스 Range Scan이 지정이 되지 않는거가??


다음 예를 살펴보자.


SELECT * FROM student_test WHERE name = '정철' and substr(SCHOOL_NAME, 0, 1) = :SCHOOL_NAME;


위와 같이 쿼리를 수행하려고 할 때 아래와 같이 인덱스를 구성해보자.


CREATE  INDEX SCOTT.student_idx

ON SCOTT.STUDENT_TEST ("NAME" ASC,"AGE" ASC,"SCHOOL_NAME" ASC);


그리고 실행쿼리에 대해 실행계획을 확인해 보면 인덱스 Range Scan이 가능한 것을 알 수 있다.


인덱스에 사용되는 컬럼이 조작되면 인덱스 Range Scan이 되지 않는다고 알고 있었는데 의아할 수도 있다.





인덱스 Range Scan이 가능한 이유는 인덱스를 구성하는 첫 번째 컬럼이 가공되지 않았기 때문이다.


인덱스 Range Scan을 사용하기 위해서는 인덱스를 사용하는 첫 번째 컬럼이 가공되지 않으면 사용이 가능하다.





인덱스를 타기만 하면 튜닝이 종료되는건가??


대부분의 개발자가 실행계획 확인 없이 SQL 작성한다. 그리고 인덱스 Range Scan이 지정된 것만 확인하면 추가적으로 확인하지 않는다.


위의 테이블에서 인덱스를 다음과 같이 지정해보자.


CREATE  INDEX SCOTT.student_idx

ON SCOTT.STUDENT_TEST ("NAME" ASC,"SCHOOL_NAME" ASC);


그리고 학생 검색을 위해 다음 쿼리 두 개를 살펴보자.


SELECT * FROM student_test WHERE name = '정철' and substr(SCHOOL_NAME, 0, 1) = :SCHOOL_NAME;

 SELECT * FROM student_test WHERE name = '정철' and SCHOOL_NAME LIKE :SCHOOL_NAME;

두 개의 쿼리 모두 인덱스 Range 스캔을 사용하지만 조건에 사용된 컬럼이 가공되었기 때문에 성능에 문제가 있다.


이를 해결하는 방법은 추후에 공부해보자.

댓글()
  1. 동구 2018.07.08 18:41 댓글주소  수정/삭제  댓글쓰기

    실행계획을 믿었다가 프로덕트 DB에서 속도가 안나오는거 보고 예전엔 많이 의아했는데....
    통계작업으로 인한 수치와 옵티마이저에 의해 언제든지 달라질 수 있다는 것 ㅜ

인덱스 기본 사용법과 인덱스 스캔이 불가능한 경우 소

우리가 색인을 통해 단어를 찾는 순간을 생각해보자.

ㄱ.

가나

가방 장식

가시 방석

ㄴ.

나방

나방 나무

누에고치

나무 장식

누나


여기서 누에고치라는 단어를 찾을 때, 위에서 순차적으로 진행한다고 가정하였을 때 큰 어려움 없이 발견할 수있다. 이 방식을 Index Range Scan이라고 한다.

반대로 장식이 포함된 단어를 찾아보자. 찾기 어려운 건 아니여도 모든 색인을 전부 확인해봐야한다. 이렇게 모든 색인을 다 확인하고 나서 찾을 수 있는 방식을 Index Full Scan 방식이라고 한다.

그렇기 때문에 인덱스의 기준이되는 데이터 즉 컬럼을 가공하게되면 Range Scan이 불가능해진다. 정리하면 인덱스 기준이 가공되면 인덱스 스캔의 시작점을 찾는 수직적 탐색이 불가능해지기 때문이다.


몇 가지 쿼리를 예로 들어보자.


1
2
3
4
create table student (
name varchar2(255),
birth date);
cs


학생 테이블이 있을 때 생일이 1월로 시작하는 사용자를 찾기위해 다음과 같은 쿼리를 사용한다고 가정해보자.


1
select * from student where substr(birth, 52= '01';
cs



어디서 부터 스캔을 멈춰야할지 인덱스 스캔을 할 수가 없다. 또 다른 예를 들어서 확인해보자.



1
2
3
select * from student where nvl(birth, CURRENT_DATE) < '2018-08-12';
 
select * from student where name like '%edu%';
cs



어디서 인덱스 스캔을 멈추어야하는지 알 수없기 때문에 마찬가지로 인덱스 스캔을 진행할 수가 없다.





OR이나 IN절의 경우에는 내부적으로 UNION ALL으로 내부적으로 나뉘어서 각자 자신의 쿼리를 사용하여 인덱스를 사용할 수 있다.


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
where 전화번호 in ( tel_no1, tel_no2 )
 
 
 
                |
// 아래와 같이 변경
                ▽
 
 
 
select * from 고객 where 전화번호 = tel_no1
 
union all 
 
select * from 고객 where 전화번호 = tel_no2

cs


댓글()