Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

REGEXP_LIKE performance issue

684598Feb 19 2009 — edited Mar 4 2010
The 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
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Apr 1 2010
Added on Feb 19 2009
3 comments
8,898 views