Oracle DB 인덱스 활용법

 

1. 인덱스 컬럼을 변형하여 비교할 때

• BAD - WHERE TO_CHAR(HIREDATE,'YYYYMMDD') = '19980518';
• NOT BAD - WHERE HIREDATE = TO_DATE('19980518')

비교하는 인덱스 컬럼의 형이나 값을 변경하면 발생합니다.이 경우에는 비교값을 변경해 주어야 인덱스를 사용하게 됩니다.

 

인덱스를 HIREDATE로 했을 때 인덱스를 타기위해서는 INDEX를 생성한것에 변형을 주어서는 안된다.

 

위 처럼 결과도 빠르게 나온다 그러나 중요한 점이 있다 결과가 같지 않을 수도 있다는 것이다

날짜 타입은 날짜와 시분초의 정보도 가지고 있다. 따라서 TO_DATE(‘19980518’)라는 말은 정확히 1998년5월18일 0시0분0초라는뜻이다.

그래서 우리가 원하는 1998년5월18일자와는 차이가 있다.

따라서 1998년5월18일 0시0분1초 ~ 23시59분59초까지의 데이터는 나오지 않게되는것이다.

이것은 튜닝할 때 유의할 점이다. 결과를 같게 유지해야하는것이다. 이 상황을 알고있다면 방법은 간단하다.

아래아 같이 고치면 빠른시간에 원하는 결과를 얻을 수 있을 것이다.

 

• VERY GOOD
• WHERE HIREDATE BETWEEN TO_DATE('19980518'||'00:00:00','YYYYMMDD HH24:MI:SS') AND TO_DATE('19980518'||'23:59:59','YYYYMMDD HH24:MI:SS')

• BAD - WHERE SALARY + 1000 > 100000;
• GOOD - WHERE SALARY > 100000 - 1000;

함수의한 변형이 아닌 간단한 연산에의한 변형의 경우도 마찬가지이다.

---

2. 비교 대상의 형이 달라서 내부적으로 형변환을 하는 경우

• BAD - WHERE EMP_ID = 200383;
• GOOD - WHERE EMP_ID = ‘200383’;

EMP_ID가 varchar라고 할 경우에 비교값이 숫자인 경우에 DB에서 자동으로 이를 숫자로 변경하고 비교하게 됩니다.

이 경우에 인덱스 컬럼에 변형이 일어났기 때문에 인덱스를 사용하지 못하게 됩니다.

---

3. NULL을 비교하는 경우

• BAD - WHERE JOB IS NULL;

일반적으로 Oracle을 기준으로 NULL은 인덱스 대상이 아니라고 합니다.

따라서, 이를 해결하기 위해서는 NULL을 쓰지 말고 다른 정해진 값을 이용해서 비교해야 합니다. 

 

따라서 위와 같은 경우 반드시 index를 타려거든

job컬럼을 NOT NULL로 설정하고 NULL대신 특정값 (예를 들면 : ‘NOT ASSIGN’ ) 으로 설정하고 

QUERY를 아래와 같이 수정한다면 인덱스를 탈수 있을 것이다.

 

• GOOD 
• SELECT LAST_NAME,FIRST_NAME FROM EMPLOYEES WHERE JOB = ‘NOT ASSIGN’;

---

4. 부정형 조건인 경우

• BAD - WHERE JOB NOT IN ( 'INSTRUCTOR', 'STAFF');

부정형 역시 인덱스를 사용하지 못하는 대표적인 조건 쿼리 입니다.

일반적인 INDEX가 =이나 <, > , BETWEEN조건에 만 인덱스를 탈수 있고 부정형으로 비교했을때는 인덱스를 탈수 없기때문이다

아닌 놈을 찾으려면 전체를 뒤지는 수 밖에요. 이를 피하기 위한 근본적인 DB 모델링이 중요합니다.

 

---