Performance issue with usage of regex_substr

Question

I have a condition in a very complex query which is something like

and regexp_substr(service.name, '\d+') = ?

When i run explain plan and check in production this simple filter is causing lot of performance problem.

Any ideas as to how I achieve the goal and yet have better performance? Heard about recursive QTE, but it sounds far fetched.

The goal being take the digits from service.name and compare with input.

Sample data of service.name field would be like
AB 12345
AB1234567
AB     12345
AB:352356

No fixed pattern, should pull digits out of them and compare with input.

Recursive CTE won't help you, it's got nothing to do with your problem. — Tomalak
– Tomalak, Commented Mar 19, 2014 at 7:06
Please be more specific about the data your column contains and what you search. Give a typical example. — Tomalak
– Tomalak, Commented Mar 19, 2014 at 7:12
Well any idea, if there is any elegant solution to this problem? I provided sample data. — user1933888
– user1933888, Commented Apr 7, 2014 at 15:49
Looks like using service.name LIKE '%' + ? + '%' would do what you need? — Tomalak
– Tomalak, Commented Apr 7, 2014 at 15:51
You won't be able to make that perform well through different SQL alone, no matter what you do. Adding a trigger that pulls out the required value into a separate column that has an index on it (or a persisted computed column with an index) might be your only chance to get query performance up. — Tomalak
– Tomalak, Commented Apr 7, 2014 at 16:52

Slava · Accepted Answer · 2014-03-19 06:48:15Z

2

when you use regexp_substr if you have index on service.name it won't be used, also regexp_substr causes switches between java and sql/pl/sql machines, what you can try to do is

option 1) create index on function 'regexp_substr(service.name, '\d+')' or option 2) try to use like it will be something like this service.name like '%<here is your number>%'

answered Mar 19, 2014 at 6:48

Slava

794 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Tomalak Over a year ago

The LIKE will be faster than the regex, but it will still not use an index and scan the entire table unless you remove the % from the start of the pattern.

Collectives™ on Stack Overflow

Performance issue with usage of regex_substr

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related