1

As title, I'm using the Regexp_like in my oracle SQL queries, but the performance very bad. I have the following code:

SELECT ID, Name, Department, PhoneNumber, Address
FROM DPT.DP_vEmployee --vEmployee is a view
WHERE ID = :p_ID
AND REGEXP_LIKE(upper(Address), upper(:p_Address));

I filled and run this query, it returned nearly 6s for 484 records. Then, I tried use LIKE operation for this case:

SELECT ID, Name, Department, PhoneNumber, Address
FROM DPT.DP_vEmployee --vEmployee is a view
WHERE ID = :p_ID
AND (upper(address) LIKE upper('%' || :p_address || '%'));

In this case, the result returned 1.5s for 484 records. Althought It reduce a lot but it does not make me satisfied.

Is there any other solution to make it faster?

Thank you!

Mr. Falcon
  • 33
  • 1
  • 4
  • 1
    The solution is to perhaps store your address information as uppercase. And then, maybe look into using full text search, which might perform better than both your current options. – Tim Biegeleisen Oct 22 '18 at 04:10
  • It sounds to me like there's a misunderstanding here... If the value that you are a passing in for `:value~ in both your queries is the same, then it probably isn't a regular expression pattern, and so the regexp_like() call is probably returning "wrong" answers. – moilejter Oct 22 '18 at 04:15
  • 2
    Tim is right, above - but if you _must_ store your address column in mixed case, a function index on `upper(Address)` might be worth it. (It will help with both = matches and with `like 'value%'` matches). – moilejter Oct 22 '18 at 04:17
  • @TimBiegeleisen Now, my DB contain 4925881 records. At Address column, it contain include lowercase and uppercase because of my national text, so I can not uppercase Address column as you say – Mr. Falcon Oct 22 '18 at 04:29
  • 1
    Then maybe research full text search options. Using `LIKE` with wildcards on both ends or using regex is costly, because the whole string has to potentially be scanned. – Tim Biegeleisen Oct 22 '18 at 04:30

1 Answers1

1

instead of using "upper" on condition column and pattern, have you try using the regexp_like with "i" case-insensitive matching.

TismiiTipIns
  • 11
  • 1