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

- 인덱스로 구성된 칼럼에 변형을 가하게 되면 옵티마이저는 해당 인덱스를 사용하지 못하게 된다.
- 많은 양의 데이터를 갖는 테이블인 경우, 전체의 15% 이하를 처리할 때만 인덱스 사용이 빠르다.


1. 인덱스 활용이 불가능한 경우
- 인덱스 스캔이 무조건 빠른건 아니다.
- 조건에 의한 처리범위가 넓어짐으로 인해 분포도가 나빠지는 경우가 있는데, 이 경우 인덱스 스캔을 하는 것보다는 FULL TABLE SCAN을 하는 것이 바람직함.
-> FULL TABLE SCAN 시엔 한 번의 I/O 때마다 여러 개의 데이터 Blocks을 처리하기 때문에 I/O 횟수가 감소하게 됨. 

(인덱스는 한번의 I/O 발생 시, 하나의 Block만 처리가 가능함.)


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

① NOT 연산자 사용
ex)  SELECT * FROM emp WHERE empno!= 2170000;
       SELECT * FROM emp WHERE emp <> 2170000;
한 건의 데이터를 제외하고 나머지 모두를 찾는 것 이므로, FULL TABLE SCAN이 유리하다.

② IS NULL, IS NOT NULL 사용
ex) SELECT * FROM emp WHERE empno is NULL;
INDEX에는 NULL 값이 저장되지 않음. 그러므로 NULL 과의 비교연산자는 인덱스를 사용할 수 없다.

③ 옵티마이저의 취사 선택
옵티마이저의 자의적 판단에 의해서 인덱스를 사용할 수도 있고 사용하지 않을 수도 있다. 이러한 것을 '취사선택'이라 한다. Rule Base Optimizer와 Cost Base Optimizer에서는 옵티마이저의 자의적 판단으로 인한 잘못된 선택을 강제로 제어하기 위해 Hint를 사용한다.

④ External suppressing 
suppressing 이란 인덱스로 구성된 컬럼에 변형을 가했을 때, 그로 인해 해당 컬럼으로 구성된 인덱스를 사용하지 못하는 것을 의미함. External suppressing은 코드와 같이 겉으로 드러나는 suppressing을 의미함.

ex1) 불필요한 함수를 사용하는 경우
WHERE SUBSTR(ename, 1, 1) = 'M' (SUBSTR로 칼럼에 변형을 가함)
-> WHERE ename LIKE 'M%'; 로 사용해야 INDEX 사용 가능.

ex2) 문자열 결합
WHERE job || deptno = 'MANAGER10'; (|| 로 칼럼을 변형)
-> WHERE job = 'MANAGER' AND deptno = 10;  을 사용해야 인덱스를 사용가능.

ex3) DATE 변수의 가공
WHERE TO_CHAR(hiredate, 'YYYYMMDD') = '20021023'
(TO_CHAR로 칼럼이 변형됨)
-> WHERE hiredate BETWEEN TO_DATE('20021016' ,'YYYYMMDD') AND TO_DATE('20021023', 'YYYYMMDD') 와 같이 사용하는 게 좋다.

ex4) 산술식의 적용
WHERE sal*12 > 40000;
WHERE sal > 40000/12; -> 이렇게 사용

⑤ Internal suppressing
Internal suppressing은 DB 내부에서 자체적으로 존재하는 suppressing을 의미함

NUMBER, DATE, CHAR 형을 VARCHAR2로 바꾸거나, 그 반대의 경우들이 있음. 즉 서로 다른 data type끼리 비교와 연산이 발생하는 경우를 의미한다.

3. 옵티마이저에 의한 선택절차

특정 테이블에 대해서 SQL의 주어진 조건으로 인해 사용될 수 있는 인덱스가 두 개 이상인 경우, 옵티마이저는 조건에 가장 적절한 인덱스를 선택하여 사용함. 
-> 주어진 조건에 가장 적절한 인덱스를 선택하려 할 때, 일련의 절차에 따라 결정한다.

* 옵티마이저의 인덱스 선택 시 판단 절차
① 주어진 조건에 대한 각 인덱스 별로 매칭률을 계산해서 매칭률이 높은 것을 우선적으로 선택한다.
* 인덱스매칭률 =  WHERE절에서 1st 칼럼부터 연속된 칼럼에 대해 상수(값)를 '='로 비교하는 칼럼의 개수 / 인덱스를 구성하는 총 칼럼의 개수
② 인덱스 별 매칭률이 같을 경우에는 인덱스를 구성하는 칼럼의 개수가 많은 것을 우선적으로 선택한다.
③  인덱브 별 매칭률과 인덱스를 구성하는 칼럼의 개수가 같은 경우에는 가장 최근에 생성된 것을 우선적으로 선택한다.

위 사례에서는 인덱스 변경 전에는 IX1_SALES 인덱스를 타지만, 인덱스 변경 후에는 IX2_SALES 인덱스를 타게된다.

* RBO와 CBO가 선택한 인덱스 차이
ex)
EC_COURSE_SQ_PK : COURSE_CODE + YEAR + COURSE_SQ_NO

EC_COURSE_SQ_IDX_01 : YEAR (Non Unique)

 

SELECT MIN(course_sq_no) AS min_sq

               MAX(course_sq_no) AS max_sq

FROM ec_course_sq

WHERE course_code = 1960

AND year = '2002' ;

 

RBO은 인덱스 매칭률이 높은 것을 선택하므로,

EC_COURSE_SQ_PK 인덱스 매칭률 : (2/3)

EC_COURSE_SQ_IDX_01 : 인덱스 매칭률 (1/1) 에 따라 EC_COURSE_SQ_IDX_01인덱스를 선택함.

 

CBO는 YEAR 칼럼을 하나 사용하는 것 보다, COURSE_CODE + YEAR 결합 칼럼이 데이터 범위를 줄여줄 수 있다면, EC_COURSE_SQ_PK 인덱스를 선택한다.

 

-> CBO, RBO의 실행계획이 서로 달라질 수 있으므로, 좋은 인덱스는 아니다.