Performance of REGEXP_LIKE vs LIKE?

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 Received on Wed Apr 18 2007 - 15:52:41 CDT