CODEWITHSUNDEEP
sqlregexoracle-database
user1933888
user1933888

Reputation: 3017

Performance issue with usage of regex_substr

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.

Upvotes: 0

Views: 175

Answers (1)

Slava
Slava

Reputation: 79

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>%'

Upvotes: 2

Related Questions