Re: HELP: SQL query using condition LIKE

From: Yves Noel <noel_at_omega.univ-lille1.fr>
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

Original text of this message