Re: Performance of REGEXP_LIKE vs LIKE?

On Apr 19, 12:52 am, "zstringer..._at_gmail.com" <zstringer..._at_gmail.com> wrote:
> Hello fellow netters,
>
> I'm curious if anyone has done any performance testing of REGEXP_LIKE
> vs LIKE. There are certain situations where the syntax of REGEXP_LIKE
> is cleaner and shorter than the comparable LIKE expression. A common
> search request for us involves doing a case-insensitive, wildcard
> search of a 3 million record table, for a series of text strings.
>
> For example:
>
> SELECT *
(> FROM big_table bt
> WHERE UPPER(TRIM(bt.txt)) LIKE '%ABRASION%'
> OR UPPER(TRIM(bt.txt)) LIKE '%DERMATOLOGICAL%'
> OR UPPER(TRIM(bt.txt)) LIKE '%PSORIASIS%'
>
> The equivalent regular expression search is:
>
> SELECT *
> FROM big_table bt
> WHERE REGEXP_LIKE(UPPER(TRIM(bt.txt)),
> '(ABRASION|DERMATOLOGICAL|PSORIASIS)')
>
> The regular expression syntax is cleaner, especially when you have a
> lot of strings to search for! However, the LIKE expression runs in 20
> seconds, while the REGEXP_LIKE one runs in 60 seconds. Has anyone
> else noticed this? Any way to speed it up?
>
> BTW there's some kind of limit to the string you can pass to
> REGEXP_LIKE. You'll get the error message: ORA-12733: regular
> expression too long
>
> Thanks,
> Zstringer

How about Oracle Text? It's as simple as

CREATE INDEX IX$CTX$BIG_TABLE#TXT ON BIG_TABLE (TEXT) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('FILTER NULL_FILTER SYNC ON COMMIT') /

(be aware that SYNC ON COMMIT option is unsafe in 10.1 up to 10.1.0.5 - there are a couple of bugs with it that can corrupt the index on subsequent optimization, fixed in 10.1.0.5 and, I believe, 10.2.0.2)

and then you query like this:

SELECT * FROM BIG_TABLE
 WHERE CONTAINS(TXT,'abrasion OR dermatological OR psoriasis') > 0

or

... CONTAINS(TXT,'about(abrasion) or about(psoriasis)') > 0

or ... well, I won't go into copying the Text Reference here, check it out yourself... :) Using Text should reduce your query response times dramatically while being much more flexible with regard to the way you search for the right information in unstructured data in and outside of the database. Just open the Text Reference and discover a whole new world of powerful and efficient full text indexing and searching supplied by Oracle out of the box. And it comes with all Oracle editions for no extra cost. :)

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Thu Apr 19 2007 - 11:01:31 CDT