Re: Performance of REGEXP_LIKE vs LIKE?

From: Rene Nyffenegger <rene.nyffenegger_at_gmx.ch>
Date: Fri, 20 Apr 2007 06:52:57 +0000 (UTC)
Message-ID: <f09o09$ilj$1@klatschtante.init7.net>

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Ú

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch

Received on Fri Apr 20 2007 - 01:52:57 CDT