Re: LIKE operator

From: Ralf <r.geronimi_at_bigfoot.com>
Date: Tue, 27 Oct 1998 19:13:15 +0100
Message-ID: <715218$aks$1@platane.wanadoo.fr>

>No it cannot. It must inspect each and every value of Y. It will not use
the
>index to do this.
>
>consider a table with values:
>
>Y
>------
>A%
>B%
>%A%
>
>
>select * from where 'CONSTANT' like Y;
>
>has to look at each of the three rows to find that only %A% applies. No
>indexing can be used...

I think it should be possible :
for example, if CONSTANT='paris'
, and if Y is indexed by Z,
, oracle should :
- use the index Z to search for any Y item beginning by 'p', and recursively start again this algorithm on the right substring - then use the index to search for a mask beginning by '%', and recursively start again this algorithm on the right substring - then use the index to search for a mask beginning by '_', and recursively start again this algorithm on the right substring

But it seems that this algorithm is not used : i tried an EXPLAIN PLAN, oracle doesn't use the index Z.

Perhaps Oracle 9.0 ?
:)

---

Raphael Geronimi
email : raphael.geronimi(replace by @)bigfoot.com Received on Tue Oct 27 1998 - 12:13:15 CST