REGEXP_LIKE performance issue
684598Feb 19 2009 — edited Mar 4 2010The quesry uses REGEXP_LIKE takes 2.7 secs:
select ref.id
from user1.testref ref,user1.testedge edge
where ref.id = edge.id and REGEXP_LIKE(ref.name, '(^|\W)cr 63(\W|$)', 'i') and edge.st = '01' and edge.co = '097'
An alternative without REGEXP_LIKE takes 0.6 sec
select ref.id
from user1.testref ref,user1.testedge edge
where ref.id = edge.id and (upper(ref.name) LIKE '% CR 63' OR upper(ref.name) LIKE 'CR 63 %' OR upper(ref.name) LIKE '% CR 63 %' OR
upper(ref.name) LIKE 'CR 63') and edge.st = '01' and edge.co = '097'
Any suggestion to improve REGEXP_LIKE performance?
Thanks