인덱스를 사용하지 못하는 경우, 분포도
테이블의 데이터 조회를 빠르게 하기 위해서 만든 오브젝트가 인덱스이다.
개발자가 이런 인덱스를 사용하고자 하는데 인덱스를 사용하지 못할 수가 있다.
NOT 연산자 사용
select * from emp where empno != 7654;
select * from emp where empno <> 7654;
NOT 연산자의 경우에는 다음의 조건으로 FULL TABLE SCAN이 훨씬 유리하다.
테이블이
5블록 이하일 때에는 FULL TABLE SCAN이 무조건 유리하므로 인덱스를 사용하지 않은것이 좋다.
왜냐하면 I/O의 최소단위는 블록인데, 전체 5블록중에 한블록을 읽게되면 20%가 되어 인덱스 활용의 손익분기점인 20%가 되어 인덱스 활용의 손익분기점인 10~15%를 넘어가므로 FTS가 성능이 더 좋다.
6블록 이상의 테이블일 경우 같지않다( != ) 는 테이블의 '단일 값과 같지 않다' 는 의미 이므로 대부분의 경우에는 결과가 15%이상일 가능성이 높다. 예를들어 10블록인 경우 데이터를 읽어들이는 양은 조건에서 같을 경우 한 블록을 제외하면 되므로 (10 - 1) / 10 = 90%에 이르기 때문에 손익분기점을 넘어가므로 FTS가 더 유리하다.
IS NULL, IS NOT NULL 사용
select * from emp where empno is null;
앞의 질의문의 경우에는 당연히 인덱스를 사용하지 않는다. B*Tree 구조 인덱스는 NULL 칼럼 값을 인덱스에 저장하지 않기 때문이다. 따라서 IS NULL, IS NOT NULL 조건은 인덱스를 통해 찾을 수 없고, FTS를 통해 찾는다.
옵티마이저의 취사 선택
옵티마이저의 목표에 따라 인덱스를 사용하지 않을 수도 있다. 옵티마이저가 테이블의 데이터가 적은 경우 인덱스를 경유하는 것보다 FTS가 유리하다고 판단하면 인덱스를 사용하지 않을 수 있다. 이런 옵티마이저의 자의적인 판단을 제어하기 위해서 오라클에서는 힌트를 주로 사용한다.
외부적인 변형
데이터베이스 옵티마이저가 질의문을 수행하는 실행계획을 수립한다. 그러나 사용자가 질의문을 틀리게 작성하거나, 내부적인 처리에 오류가 있다든지, 여러가지 이유로 인해 개발자가 의도하지 않은 방식으로 실행계획이 수립될 수 있다.
예를들어 , 한 테이블에 인덱스가 여러 개 생성되어 있는 경우, 개발자가 의도하지 않은 인덱스가 사용되는 실행계획이 수립되어 질의문의 수행 속도가 기대에 못미치는 경우도 발생한다. 이런저런 이유로 개발자는 질의문의 특정 부분에 변형을 가함으로써 자신이 의도한 바대로 옵티마이저가 실행되도록 할 수 있다.
칼럼을 변형함으로써 인덱스를 사용하지 못하게 하는 것을 서프레싱이라고 한다.
서프레싱은 사용자가 의도적으로 칼럼 값에 변경을 가해 인덱스를 사용하지 못하도록 한다.
SQL 작성의 오류
많은 개발자들이 질의문을 작성할 때 컬럼 자체를 변경하는 질의문을 작성한다. 인덱스로 사용되지 않는 칼럼이라면 상관없지만, 그런 습관은 좋지 못하며 의도하지 않는 오류를 발생시킬 수 있다.
1. 불필요한 함수를 사용한 경우
개발자가 사원정보 테이블에서 이름이 'M'자로 시작하는 모든 사원의 정보를 조회하려고 질의문을 작성한 경우다.
다음과 같이 인덱스 칼럼에 SUBSTR 함수를 사용하여 질의문을 작성하면 개발자가 의도한 인덱스를 사용하지 않고 FTS가 일어나게 된다. 데이터가 적은 경우라면 큰 차이를 못느끼지만 , 대용량이라면 큰 차이가 난다.
* 인덱스를 사용하지 못하는 경우
select * from emp where SUBSTR(ename, 1, 1) = 'M';
* 인덱스를 사용하는 경우
select * from emp where ename LIKE 'M%';
2. 문자열 결합
* 인덱스를 사용하지 못하는 경우
select * from emp where JOB || DEPTNO = 'manager10';
* 인덱스를 사용하는 경우
select * from emp where JOB = 'manager' and DEPTNO = '10';
3. DATE 변수의 가공
* 인덱스를 사용하지 못하는 경우
select * from emp where to_char(hiredate , 'YYMMDD') = '021016'; // 컬럼에 변경이 가해짐 -> 인덱스 사용x
to_char : 숫자나 날짜를 문자로 변환해 주는 함수가 바로 to_char로, 매개변수로는 숫자나 날짜가 올 수 있고 반환 결과로 특정 형식에 맞춰 출력할 수 있다.
* 인덱스를 사용하는 경우
select * from emp where hiredate >= TO_DATE('20021016', 'YYMMDD') AND hiredate < TO_DATE('20021016','YYMMDD') + 1(0.99999);
4. 불필요한 NULL 처리
* 인덱스를 사용하지 못하는 경우
select * from emp where NVL(JOB, 'NOJOB') = 'manager';
* 인덱스를 사용하는 경우
select * from emp where job = 'manager';
job칼럼으로 만들어진 싱글 인덱스의 경우는 NULL값을 가지고 있지 않기 때문에 NVL함수는 필요가 없다.
job이 null인 경우를 조회하고자 할때는 IS NULL 조건을 사용해야 한다.
5. 산술식의 적용
* 인덱스를 사용하지 못하는 경우
select * from emp where sal * 12 > 40000;
* 인덱스를 사용하는 경우
select * from emp where sal > 40000/12;
분포도
개발자의 의도에 의한 서프레싱 예
사례1)
개발자는 정확한 정보에 근거하여 특정 질의문의 실행계획을 자신이 의도한 방향으로 수립되도록 유도할 수 있다.
예를들어 사원(insa) 테이블의 재직상태(status) 칼럼을 퇴직자, 파견, 휴직자를 조회할 목적으로 단일 칼럼 인덱스를 구성했다. 재직자 조회를 제외한 질의문의 경우에는 양호한 분포도에 의해 좋은 응답성능을 기대할 수 있다. 아래의 질의문은 퇴직자(DD) 를 조회하는 질의문이다.
select resno, empno, deptnm from insa where status = 'DD';
그러나 현재 재직 중인 사원(코드 AA)을 조회할 경우를 생각해보면, 사원 테이블의 대부분의 정보가 현재 재직중인 사원에 대한 정보이므로 재직 상태 칼럼의 분포도는 좋지 않게 된다. 따라서 개발자는 의도적으로 인덱스 칼럼에 변형을 가함으로써 인덱스를 사용하지 않고 FTS을 하는 실행계획 수립을 유도할 수 있다.
select resno, empno, deptnm from insa where RTRIM(status) = 'AA';
위의 예문을 보면 인덱스 칼럼인 재직상태 칼럼에 변형을 가한것을 알 수 있다. 기존에는 문자형 칼럼 유형에 대해서는 결합연산자( || ), 숫자형의 칼럼 유형에 대해서는 +0 을 하였다. 그러나 문자형의 경우에는 varchar , char에 따라 원하는 결과가 나오지 않을수도 있으므로 RTRIM함수를 사용하는 것이 적합하다고 할 수 있다.
사례2)
아래는 하나의 질의문에 두 개의 단일 칼럼 인덱스가 존재하여 인덱스 머지하는 실행 계획이 수립된 경우이다.
개발자가 현재 재직중인 사원 중에서 74년도에 태어난 사원을 조회하고자 하는 경우 각각의 칼럼에 인덱스가 생성되어 있어 옵티마이저의 판단에 의해 인덱스 머지가 일어났다.
select resno, empno, deptnm
from insa
where status = 'AA' and resno LIKE '74%';
개발자는 주민번호 칼럼의 인덱스는 분포도가 양호하고, 재직상태 칼럼의 분포도는 양호하지 못하다는 정보를 알 수 있을 것이다. 그래서 개발자가 재직상태 인덱스를 사용하지 않고 주민번호 인덱스만을 사용하기로 결정했다면 아래와 같이 질의문을 작성할 수 있다.
select resno, empno, deptnm
from insa
where RTRIM(status) = 'AA' and resno LIKE '74%';
자주 활용하는 또 하나의 형태는 조인 시 테이블의 엑세스 순서를 바꾸거나 고정시키기 위해 사용한다. 사원 기본정보 테이블과 사원 부가정보 테이블을 조인하여 1990년대 입사한 사원 중 결혼을 2002년 10월에 결혼한 사원을 조회하고자 한다. 개발자는 1990년대 입사한 사원 보다는 특정 년월에 결혼한 사원이 더 적을 것이라는 사실을 미리 알고 있다면, 다음과 같이 질의문을 작성하여 실행계획을 결혼일에 대한 인덱스를 사용하도록 유도할 수 있다.
select a.resno, a.grentdt, b.weddt
from emp_master a, emp_detail b
where a.resno = b.resno
and RTRIM(a.grentdt) LIKE '1990%'
and b.weddt LIKE '200210%';
이처럼 개발자가 데이터의 정보를 정확히 알고 특화된 경우에는 보편적인 최적화를 추구하는 옵티마이저보다 더 좋은 실행계획을 유도할수 있습니다.
내부적인 변형
개발자가 작성한 질의문에 명시적인 오류가 있거나, 정확한 정보에 근거하여 실행계획을 자신이 원하는 방식으로 유도하고자 하는 경우에 외부적인 변형이 사용되는 방법을 알아보았다. 이와는 다르게 개발자의 의도와는 데이터베이스 내부적으로 컬럼에 대한 변형이 일어나는 것을 내부적인 변형이라고 한다.
내부적인 변형은 서로 다른 자료형을 비교나 할당하는 경우에 발생하며 오라클의 경우 내부적인 규칙에 의해 이러한 변형이 일어나게 된다. 이러한 내부적인 변형에 의해 개발자의 의도와는 다른 실행계획이 수립되는 경우 질의문의 수행 속도에 막대한 영향을 줄 수 있으므로 주의해야 한다.
1) 인덱스를 사용할 수 있는 내부적인 변형
select * from emp where empno = '7788';
emp 테이블의 empno 컬럼은 NUMBER(4)의 자료형인데 위의 쿼리가 실행되었다. 이 경우는 다음과 같이 문자타입에서 숫자타입으로 변환이 자동으로 일어난다.
내부적인 변형
select * from emp where empno = TO_NUMBER('7788');
위의 경우는 상수쪽에 변형이 일어나고 인덱스 칼럼 쪽에는 변형이 없으므로 인덱스 활용에는 문제가 없다.
character는 char type와 varchar2 type 모두를 포함한다.
2) 인덱스를 사용할 수 없는 내부적인 변형
create table dept_temp (
deptno char(2)
constraint pk_dept_temp primary key,
dname varchar2(14),
loc varchar2(13)
);
insert into dept_temp select * from dept;
select * from dept_temp where deptno = 10;
dept_temp 테이블의 deptno 컬럼은 char(2)의 자료형인데 위의 쿼리가 실행되었다.
이 경우는 다음과 같이 문자 타입에서 숫자 타입으로의 변환이 자동으로 일어난다.
select * from dept_temp where to_number(deptno) = 10;
개발자는 where deptno = 10; 조건 절을 작성하고 이렇게 생각할 것입니다. 위의 상수값인 10이 자동으로 to_char 함수가 호출되어 where deptno = to_char(10); 과 같은 방식으로 처리될 것이므로 인덱스를 사용하여 빠른 응답이 나올것이라고 생각할 것이다. 그러나 그렇지않다. 값을 할당하는 경우가 아닌 연산식을 처리할 때에는 다음과 같은 두 가지의 경우에만 내부적으로 처리된다. 그렇기 때문에 오히려 where to_number(deptno) = 10 으로 변환이 일어나 인덱스를 사용하지 못하고 FULL TABLE SCAN을 사용하는 실행계획이 수립된다. 데이터의 건수가 많다면 응답 속도는 많은 시간이 소요될 것이다.
3) 연산식의 유형
p. 89