초성 범위 검색 쿼리의 오류에 대해서..

[주정섭]

초성 범위 검색 쿼리의 오류에 대해서..

특정 범위에 드는 초성 쿼리에 대한 문제를 낸 다음, 많은 사람들이 의견을 주었다. 이글을 빌어 그분들께 감사를 드리고 싶다.

그런데, 사실, 초성 범위 검색에 대한 문제를 내면서 나는 크나큰 실수를 저질렀다. 이 글을 통해서 그 오류를 바로 잡고자 한다.

초성 범위 카운트 문제를 내면서 크게 간과한 점은, cust_name 필드에 인덱스가 걸려져 있는가에 따라서, 어떤 쿼리는 동작 방식이 전혀 달라진다는 점을 고려하지 못했다는 것이다.

모든 레코드에서 특정 필드값을 읽어서 처리해야 하는 경우라면, 쿼리 옵티마이저가 인덱스 사용을 거부하는 줄 알았으나, 이는 나의 잘못된 판단이었다. 어떤 경우 전체 검색이라도 인덱스가 사용되는 경우가 있다.

지난번 글에서 나는 다음 두 쿼리를 초성 범위 카운트 처리 쿼리로 소개한 바 있다.

(쿼리1)

select '기타1', count(*) from lottery where cust_name < '가'
union all
select 'ㄱ', count(*) from lottery where cust_name >= '가' and  cust_name < '나'
union all
select 'ㄴ', count(*) from lottery where cust_name >= '나' and  cust_name < '다'
union all
select 'ㄷ', count(*) from lottery where cust_name >= '다' and  cust_name < '라'
union all
select 'ㄹ', count(*) from lottery where cust_name >= '라' and  cust_name < '마'
union all
select 'ㅁ', count(*) from lottery where cust_name >= '마' and  cust_name < '바'
union all
select 'ㅂ', count(*) from lottery where cust_name >= '바' and  cust_name < '사'
union all
select 'ㅅ', count(*) from lottery where cust_name >= '사' and  cust_name < '아'
union all
select 'ㅇ', count(*) from lottery where cust_name >= '아' and  cust_name < '자'
union all
select 'ㅈ', count(*) from lottery where cust_name >= '자' and  cust_name < '차'
union all
select 'ㅊ', count(*) from lottery where cust_name >= '차' and  cust_name < '카'
union all
select 'ㅋ', count(*) from lottery where cust_name >= '카' and  cust_name < '타'
union all
select 'ㅌ', count(*) from lottery where cust_name >= '타' and  cust_name < '파'
union all
select 'ㅌ', count(*) from lottery where cust_name >= '파' and  cust_name < '하'
union all
select 'ㅎ', count(*) from lottery where cust_name >= '하' and  cust_name <= '힣'
union all
select '기타2', count(*) from lottery where cust_name > '힣'

(쿼리2)

select a1.aa, count(*)
from (
 select
   case
     when cust_name < '가'  then '기타1'
     when cust_name < '나'  then 'ㄱ'
     when cust_name < '다'  then 'ㄴ'
     when cust_name < '라'  then 'ㄷ'
     when cust_name < '마'  then 'ㄹ'
     when cust_name < '바'  then 'ㅁ'
     when cust_name < '사'  then 'ㅂ'
     when cust_name < '아'  then 'ㅅ'
     when cust_name < '자'  then 'ㅇ'
     when cust_name < '차'  then 'ㅈ'
     when cust_name < '카'  then 'ㅊ'
     when cust_name < '타'  then 'ㅋ'
     when cust_name < '파'  then 'ㅌ'
     when cust_name < '하'  then 'ㅍ'    
     when cust_name < '힣'  then 'ㅎ'    
     else '기타2'
 end as aa             
 from lottery
 --where chasu = '48'
) as a1
 
group by a1.aa
order by a1.aa

지난 번 글에서 나는 (쿼리2)가 더 좋은 방식이라고 했으나, 이는 잘못된 생각이었다. 쿼리 문장의 간결성으로만 친다면 (쿼리 2) 가 더 좋은 방식이긴 하나, 최적화 즉 속도를 고려하면, 경우에 따라서 쿼리1 이 좋을 수도 있고 쿼리2가 좋을 수도 있다.

만일 cust_name 필드에 인덱스가 걸려져 있지 않다면 속도면에서 우수한 것은 (쿼리2) 방식이다. 그러나 cust_name필드에 인덱스가 걸려져 있다면, (쿼리1) 방식이 월등히 빠르다.

지난번 제시한 초성 범위 카운트 쿼리 설명에서, 이름 필드에 인덱스가 있는가에 따라서 전혀 결과가 달라지는 사실을 제대로 고려하지 못했다.

쿼리 1은 인덱스가 없을 경우, 쿼리 2보다 5배 정도 느리다. 그러나, 인덱스가 있다면 완전히 상황이 바뀌어져서, 쿼리1은 즉각적으로 응답을 내는 반면에, 쿼리2는 무지 느리다.

결론인즉,

쿼리1은 인덱스가 없다면 무지 느린 방식이지만, 인덱스가 있으면 속도는 전광석화이다.
쿼리2의 좋은 점은 인덱스가 있던 없던 간에, 항상 동일한 속도를 낸다는 것이다.

http://cafe.daum.net/delphinegong