Re: HELP: SQL query using condition LIKE

From: Gert de Boer <deboer_at_wsl34>
Date: Thu, 4 Nov 1993 07:18:38 GMT
Message-ID: <CFyHn4.8Ht_at_bernina.ethz.ch>

Liang Lin (llin_at_uv1.med.umich.edu) wrote:
: 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

Well, it depends. How did you write your LIKE statement?

If you wrote: LIKE '%kjlk' or something, that is, the % at the beginning, the index will not be used. When you start with characters the index is used, but performance may slow down a little (p19-17 Database Administrator's Guide), but ten times?

So LIKE 'C%', the index is used as far as possible, but when the 'C' is found in the index, Oracle has to do a range scan. When there are many records starting with 'C', well, that may take some time.

Gert de Boer

---

Gert de Boer
WSL
Zurcherstrasse 111
8903 Birmensdorf
Switzerland
phone: +4117392577
fax: +4117392215
e-mail:deboer_at_wsl.ethz.ch

---

Received on Thu Nov 04 1993 - 08:18:38 CET