Re: HELP: SQL query using condition LIKE
Date: 4 Nov 1993 08:34:03 GMT
Message-ID: <2baepr$kgd_at_netserver.univ-lille1.fr>
In article <2b8kp2$qkp_at_terminator.rs.itd.umich.edu>, Liang Lin <llin_at_uv1.med.umich.edu> writes:
|>Dear SQL experts,
|>
|>When I use SELECT...LIKE on an indexed column, it's almost 10
|>times slower than SELECT...=. Is there any way to improve
|>the performance while still using SELECT...LIKE?
|>
|>Thanks.
|>
|>Liang Lin
|>
Hello Liang !
Effectively a pattern match like 'x%' is faster than '%x%' BUT an index is used
ONLY if at least the first character at the left of the comparison string is
different of '%'
Exemples :
SELECT * FROM EMP WHERE NAME LIKE '%SCOTT'; OPERATION OPTIONS OBJECT_NAME ID PARENT ------------ ------------ ------------ --- ------ TABLE ACCESS FULL EMP 1 --> the index on name IS NOT USED ! SELECT * FROM EMP WHERE NAME LIKE 'SCOTT%'; OPERATION OPTIONS OBJECT_NAME ID PARENT ------------ ------------ ------------ --- ------ TABLE ACCESS BY ROWID EMP 1 INDEX RANGE SCAN IND_NAME 2 1 --> the index on name IS USED !
Then be attentive with these rules. I invite you to use EXPLAIN PLAN and TKPROF. See your Performance Tuning Guide and also the Database Administrator's Guide (chapter Improving Performance of Applications). Bye !
-- ------------------------------------------------------------------------------ _/_/_/_/ _/_/_/_/ _/_/_/_/ _/_/_/_/ I use _/_/ _/_/ _/_/ _/_/ ORACLE v6.0.36.5.2 _/_/ _/_/ _/_/ _/_/ on _/_/_/_/ . _/_/_/_/ . _/_/ . _/_/_/_/ . DEC RISC ULTRIX v4.3 ------------------------------------------------------------------------------ Yves NOEL - Database Administrator C.I.T.I. (batiment M4) Phone : (33) 20.43.42.70 Universite des Sciences & Technologies de Lille Fax : (33) 20.43.66.25 59655 Villeneuve d'Ascq Cedex - FRANCE Email : noel_at_univ-lille1.fr ------------------------------------------------------------------------------Received on Thu Nov 04 1993 - 09:34:03 CET