LIKE '%XXX' 검색에 에 대한 오해와 진실

거의모든 튜닝책(원서포함)에 LIKE '%XXX  검색은 테이블 혹은 인덱스를 full scan 한다고 되어있다.
하지만 과연 이것이 사실일까?
필자는 이런류의 오류를 "공공의 오류"라고 표현한다.
대부분 혹은 대다수의 개발자나 심지어 튜너라고 하는사람들 까지 이 공공의 오류를 믿고있다.
물론 필자도 예외는 아니며 공공의 오류를 범하지 않기위해 꾸준히 노력 하고 있다.

아례 예제를  보면서 이런 오해에서 벗어나도록 하자.

1.테스트 환경
버젼 : 10gR2(10.2.0.3) ,
Optimizer mode :all_rows/first_row_n 상관없이 실행가능
계정 : scott/tiger


2. ENAME 에 인덱스 생성

CREATE INDEX EMP5 ON EMP (ENAME);

EXEC dbms_stats.gather_table_stats(user,'EMP',cascade=>true); --> Analyze


3. LIKE '%XXX' 실행 

select *
from emp
where ename like '%'||:V_ENAME||'%';     --> 변수에 'A' 대입

-- 결과는 당연히 A 를 포함한 결과만 나왔으며 ENAME 으로 Sort 되어있다.

4. Trace 내용확인

Compile Time    : 2008/06/03 19:37:48
Trace File Name : /ora_dump/NBCORET/udump/nbcoret2_ora_6064.trc
Trace Version   : Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
Environment     : Array Size = 10
                  Long  Size = 80

********************************************************************************

select * from emp where ename like '%'||:V_ENAME||'%'

Call     Count CPU Time Elapsed Time       Disk      Query    Current       Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse        1    0.000        0.000          0          0          0          0
Execute      1    0.000        0.001          0          0          0          0
Fetch        2    0.000        0.000          0          4          0          7
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total        4    0.000        0.001          0          4          0          7

Elapsed Time for Client(sec.): 0.007
Misses in library cache during parse: 0
Optimizer goal: FIRST_ROWS
Parsing user: SI31041 (ID=387)

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      7   TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=0 pw=0 time=592 us)
      7    INDEX RANGE SCAN EMP5 (cr=2 pr=0 pw=0 time=872 us)OF EMP5 (NONUNIQUE)

-- 정확히 'A' 가 포함된 블럭만 READ 했음을 알수 있다.

5.결론

과거에는 LIKE '%XXX%' 류를 빠르게 수행하기 위하여 Domain Index 사용하거나 비싼 검색엔진을 도입하였다.
하지만 이제부터는 Like 를 사용해도 똑같은 효과를 낼수있음을 명심해야 할것이다.

편집후기:
어떻게 LIKE '%XXX%' 구분이 index range scan 을 할수 있는지는 필자도 현재 분석중이다.