Re: Performance of REGEXP_LIKE vs LIKE?

On 18 Apr 2007 13:52:41 -0700, "zstringer999_at_gmail.com" <zstringer999_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

Regular expressions are implemented using Java. You force Oracle to call Java, an interpreted language. The delay would be caused by the architecture.

-- 
Sybrand Bakker
Senior Oracle DBA