Re: Performance of REGEXP_LIKE vs LIKE?

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 20 Apr 2007 08:56:33 +0200
Message-ID: <46286421.8050404@gmail.com>

Rene Nyffenegger schrieb:
> On 2007-04-19, Thomas Kellerer <JUAXQOSZFGQQ_at_spammotel.com> wrote:

>> On 18.04.2007 22:52 zstringer999_at_gmail.com wrote:
>>> 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?
>>>
>> I thought you should be able to create a function based index to support 
>> the regex match but I'm not sure (could have been Postgres where I did 
>> that the last time ;) )

>
> You can't, at least on 10g XE:
>
>
> SQL> create table foo_bar_t (a number, b varchar2(20));
>
> Table created.
>
> SQL> create index foo_bar_i1 on foo_bar_t (upper(b));
>
> Index created.
>
> SQL> create index foo_bar_i2 on foo_bar_t(regexp_like(b, '^[[:digit:]]+$'));
> create index foo_bar_i2 on foo_bar_t(regexp_like(b, '^[[:digit:]]+$'))
> *
> ERROR at line 1:
> ORA-00904: "REGEXP_LIKE": invalid identifier
>
>
> Maybe, I am missing something, then please let me know.
>
> Cheers, RenÚ
>

Because regexp_like is not a function but condition. Here (on 10.2.0.3):

SQL> create index foo_bar_i2 on foo_bar_t(regexp_replace(b, '^([[:digit:]]+)$','\1'));

Index created.

Best regards

Maxim Received on Fri Apr 20 2007 - 01:56:33 CDT