Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: LIKE operator

Re: LIKE operator

From: <prochak_at_my-dejanews.com>
Date: Wed, 28 Oct 1998 18:47:36 GMT
Message-ID: <717os8$av$1@nnrp1.dejanews.com>


In article <715218$aks$1_at_platane.wanadoo.fr>,   "Ralf" <r.geronimi_at_bigfoot.com> wrote:
> >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
>
>

Seems to me that if using an index in this case improves performance, your patterns table is too big. Oracle isn't exactly a good pattern matching tool. (Have you considered using PERL for this part of your system??)

But speaking directly about your algorithm: do you really think that would be faster? Think about how indices work versus full scans. (yes, the correct plural of index is indices)

A full scan reads every record of the table in database order. The blocks of records are read from disc only once and are read in blocks (ie, many records at once).

An index scan reads the index blocks for the table. It then reads a smaller number of records that potentially are scattered over many blocks. Your algorithm would perform this process 3 times. That is, 6sets of disc reads.

Let's assume the patterns needed (anything beginning with 'p', '%', or '_') represent 10% of the records in the patterns table. (3 out of 28 possible first characters). SO it would involve at least 20% of the number of disc reads required for a full scan. Hmmm. It might come out ahead. It can also come out worse if the percentage of hits in the pattern table is higher or the hits are distributed evenly throughout the data blocks. So actually I would not expect a great difference in this case.

If you are really sure it will be faster, just code your solution in PL/SQL. It shouldn't be that hard. Then let us know how well it works. Could you force the index by using a view? I don't know.

Could more pattern tables be a solution? Make multiple tables which hold subsets of the patterns. Pattern table Alpha contains the patterns with leading characters like a%, b%, ... Pattern table Beta contains the wildcard '%' as the leading character and Gamma contains the single character wildcard '_' in front. Tables Beta and Gamma would presumably be much smaller, so linear searches are not a big hit, and table Alpha would have the index, which could get used when making the view.

create view patterns
as select Y from alpha
where Y like substr(CONSTANT,1,1) || '%' union all
select Y from beta
union all
select Y from gamma;

(My SQLPLUS is unavailable right now, so I didn't test this.) Maybe three tables is overkill, but you'd need at least two.

It's an interesting problem. I'll have to check this out more on my test database.

I hope this helps.

   ed

--
Ed Prochak
Magic Interface, Ltd.
440-498-3702

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Wed Oct 28 1998 - 12:47:36 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US