[ SQL ] 003. INDEX가 동작을 제대로 안하는 경우 #1

 

 

실무를 하다보면 요즘에는 NoSQL, QueryDSL 등의 좋은 기술들로 인해 직접 복잡한 쿼리를 짤 경우는 많지는 않지만

쿼리를 만들고 나서 실행계획을 보면 내가 의도했던 인덱스를 타지 않은 경우들이 종종있다.

 

실제 쿼리를 보면 잘 짠거같은데 인덱스를 제대로 타지않는 경우에 대해서 공부를 하다보니 내가 미처 몰랐던 부분들이 많았던 것 같다. 

 

 

인덱스가 제대로 동작하지 않는 케이스

 

 

1. 조건문에서 인덱스의 컬럼을 변형시킨 경우

 

이 경우는 정말 실수하기 좋은 케이스이다. 

만약 내가 생년월일(birth_date) 라는 char형 컬럼을 인덱스로 만들었다고 가정했을 때,

아래와 같이 쿼리문을 짜면 조건문의 값이 int형이기 때문에 Stirng으로 만든 인덱스가 타지 않는다. 

select *
  from test_table
 where birth_date = 20000101

 

물론,  항상 이 말이 맞는건 아니다.

Why? 예외적인 케이스가 항상 존재하기 때문이다. 

예외적인 케이스는 Oracle,Mysql 에서는 묵시적인 형변환이 이뤄진다. 

그런데 또, 이런 묵시적인 형변환( char -> int, int -> char 등등의 변환)은 항상 작동하는건 아니다!

묵시적인 형변환은 데이터 형식의 우선순위에 따라 우선순위가 낮은 데이터 형식이 먼저 우선순위가 높은 데이터 형식으로 변환된다.

 

묵시적인 형변환관련 함수

TO_NUMBER(문자열)
TO_CHAR(숫자 혹은 날짜, [FORMAT])
TO_DATE(문자열, FORMAT)

 

그래서 이번에는 나이(age) 라는 int형 컬럼을 인덱스로 만들었다고 가정했을때,

아래와 같은 쿼리문은 조건문에서 형변환이 이루어졌기때문에 인덱스가 타지 않을거라고 생각이 되지만

막상 실행계획을 보면 인덱스가 의도한대로 잘 탄다.

그 이유는 앞서 말한이유로 char가 int 보다 우선순위가 낮기때문에 묵시적 형변환이 이루어지면서 인덱스를 탄 케이스이다.

select *
  from test_table
 where age='99';

 

2. 인덱스 컬럼 자체를 변형한 경우

 

위의 케이스와는 조금 비슷하면서 다른 케이스이다. 이 케이스는 조건문에서 사용된 컬럼 자체를 변형시킬 경우 인덱스가 작동하지 않는다.

이유는 인덱스 컬럼을 가공하게 되면 가공되는 값에 맞는 새로운 인덱스를 생성해서 사용하기 때문이다. 
인덱스가 생성되는 원리를 알면 이해가 더 쉽다. ( https://jeong-pro.tistory.com/242 참고 )

# 케이스1
select *
  from test_table
 where substr(birth_date,1,4) = '2000';

# 케이스2
select *
  from test_table
 where age+99 = 100;

# 케이스3  
select *
  from test_table
 where LOWER('name') = 'petric';