B*TREE 인덱스를 정상적으로 사용하기 위해선 우선 범위 스캔 시작지점을 찾아야 합니다. 이를 위해 루트블록 부터 리프블록 까지 수직적 탐색 과정을 거쳐야 합니다.
만약, 인덱스 선두 컬럼이 조건절에 사용되지 않으면 수직적 탐색을 할 수 없기 때문에 인데스 전체를 스캔하거나 테이블 전체를 스캔해야 합니다.
하지만 다음의 경우 인덱스 선두 컬럼이 조건절에 사용되더라도 범위 스캔이 불가능하거나 인덱스를 아예 사용하지 못하는 경우가 있습니다.
- 인덱스 선두 컬럼을 조건절에서 가공하면 INDEX RANGE SCAN이 불가능합니다.
SELECT *
FROM 업체
WHERE SUBSTR(업체명, 1, 2) = '대한'
- 부정형 비교를 사용하면 INDEX RANGE SCAN이 불가능합니다. 인덱스 구성 칼럼 값이 NULL인 경우 인덱스 블록에 저장하지 않기 때문입니다.
SELECT *
FROM 고객
Where 직업 <> '학생'
SELECT *
FROM 사원
WHERE 부서코드 is not null
위의 경우 모두 정상적인 INDEX RANGE SCAN은 할 수 없지만 INDEX FULL SCAN은 가능합니다. 반대로 긍정형 비교이지만 인덱스를 사용할 수 없는 경우도 있습니다. 인덱스 구성 컬럼의 값이 모두 NULL인 경우 인덱스를 만들지 않기 때문에 'IS NULL' 로 비교하면 인덱스를 사용할 수 없습니다. 단, 다른 인덱스 칼럼에 NULL이 아닌 값이 하나라도 있거나 NOT NULL 제약이 있으면 가능합니다. 물론 인덱스 선두 칼럼이 조건절에서 누락되지 않아야 합니다.
SELECT *
FROM 사원
WHERE 연락처 IS null
인덱스 칼럼의 가공
인덱스 칼럼을 가공하면 정상적인 INDEX RANGE SCAN이 불가능합니다.
-- 인덱스 컬럼 가공 튜닝방안 1
SELECT *
FROM DEPT
-- WHERE SUBSTR(DEPTNAME, 1, 3) = 'KOR'
WHERE DEPTNAME LIKE 'KOR%'
-- 인덱스 컬럼 가공 튜닝방안 2
SELECT *
FROM EMP
-- WHERE SALARY * 12 = 36000000
WHERE SALARY = 36000000 / 12
-- 인덱스 컬럼 가공 튜닝방안 3
SELECT *
FROM ORDER
-- WHERE TO_CAHR(ORDER_DATE, 'YYYYMMDD') = : DT
WHERE ORDER_DATE >= TO_DATE(:DT, 'YYYYMMDD')
AND ORDER_DATE < TO_DATE(:DT, 'YYYYMMDD') + 1;
-- 인덱스 칼럼 가공 튜닝방안 4
SELECT *
FROM CUS
-- WHERE AGE || JOB = '30DEV'
WHERE AGE = 30
AND JOB = 'DEV'
-- 인덱스 칼럼 가공 튜닝방안 5
SELECT *
FROM CLIENT
WHERE C_NUM = SUBSTR(:STR, 1, 2)
AND D_NO = SUBSTR(:STR 3,4)
묵시적 형변환
인덱스 칼럼을 명시적으로 가공하지 않더라도 조건절에서 비교되는 칼럼과 비교 값의 데이터 타입이 다르면 내부적으로 형변환이 발생합니다.
숫자 타입의 칼럼에 문자 타입 값을 대입하면 옵티마이저가 문자형 값을 숫자형으로 바꾸기 때문에 칼럼의 묵시적 형변환은 발생하지 않습니다. 반대의 경우, 옵티마이저가문자형 칼럼을 숫자로 형변환하기 때문에 묵시적 형변환이 발생합니다.
SELECT * FROM EMP WHERE CDEPTNO = 20
-- FIlter (TO_NUMBER("EMP"."CDEPTNO")=20)
--문자형 CDEPTNO 칼럼이 숫자형으로 변환된 것을 볼 수 있고, 이 때문에 emp 테이블을 TABLE FULL SCAN 하는 계획이 수립됐다.
-- 인덱스 컬럼을 가공했기 때문에 인덱스를 사용할 수 없게 됐다.
묵시적 형변환은 사용자의 코딩을 도울 용도로 제공되지만, 위와 같은 부작용을 피하기 위해선 명시적인 변환 함수를 사용하는 것이 좋습니다.
