업무에 바로 쓰는 SQL튜닝 입문 - 5교시(인덱스활용불가)

사전 퀴즈

1. 모든 쿼리문은 인덱스를 사용해야만 빠른 조회가 가능한가?
X : 많은 양의 데이터를 가지는 테이블이면 전체의 15%이하를 처리할때만 인덱스가 의미있음
2. 인덱스로 구성된 컬럼에 변형을 가했을 때에도 해당 인덱스를 사용할 수 있나?
X : 인덱스로 구성된 컬럼에 변형을 가하게 되면 옵테마이저는 인덱스를 사용 못하게 된다.

 

인덱스 사용이 불가능한 경우

1. NOT 연산자 사용
2. IS NULL, IS NOT NULL 사용
3. 옵티마이저의 취사 선택
4. External suppresing
5. Internal suppressing

1. NOT 연산자 사용

WHERE A != 1;

WHERE A <> 1;

이면 A가 1이 아닐때, 즉 1이 아닌 모든 데이터를 찾게 되므로 15%이상의 데이터를 찾게 되므로 사용이 안되는 경우임.

2. IS NULL, IS NOT NULL 사용

인덱스는 NULL을 저장하지 않음. 따라서 NULL 조건을 넣을 시 인덱스를 사용하지 못하게 됨.

3. 옵타미아저의 취사선택

옵티마이저가 판단해서 인덱스를 사용하거나 사용하지 않는 경우. 옵티마이저의 판단을 취사라 함.

그래서 우리는 잘못된 옵티마이저의 선택을 수정하기 위해 Hint를 사용함

4. External suppressing(드러나는, 즉 쿼리에 나타나는 변형)

ex)
1. WHERE SUBSTR(A, 1, 1) = 'M'; 과 같이 A 컬럼에 변형이 일어나면 인덱스를 사용하지 못함
-> WHERE A LIKE 'M%';로 변형해야함

2. WHERE JOB||DEPTNO = 'MANAGER10'; 결합하는 경우에도 아래와 같이 나열한다.
-> WHERE JOB = 'MANAGER' AND DEPTNO = 10;

3. WHERE TO_CHAR(HIREDATE, 'YYYYMMDD') = '20221016'; TO_CHAR에 의해 변형이 일어나서 인덱스 사용 불가
-> WHERE HIREDATE BETWEEN TO_DATE('20221016', 'YYYYMMDD') AND TO_DATE('20161016', 'YYYYMMDD'); BETWEEN 으로 DATE변수로 가공해서 사용해야 INDEX 사용이 가능하다

4. 산술식의 적용
WHERE SAL*12 > 40000;
-> WHERE SAL > 40000/12;

 

5. Internal suppressing(DB내부에서 일어나는!)

[VARCHAR2] OR [CHAR to NUMBER]

[VARCHAR2] OR [CHAR to DATE]

WHERE HIREDATE = '2003-01-01'; -- 인덱스 사용 가능 우측이 자동변환
WHERE TO_NUMBER(RESNO) = 12341234; --  인덱스 사용 불가능. FULL SCAN.

옵티마이저가 인덱스를 선택하는 절차임. 이걸 안다면 대략 어떤 방식으로 인덱스가 선택될 지 유추할 수 있음.

1. 매칭률

2. 컬럼의 개수

3. 최근!

그걸 알고 나서 아래 그림을 보면

아래 쿼리에 대해 인덱스 변경 전에는 IX1이 매칭률이 2/2 였는데 순번 컬럼이 추가되면서 2/3으로 바뀌어 버렸다.

따라서 RBO 옵티마이저는 매칭률이 더 높은 IX2를 선택해버리게 된다!