I’m trying to speed up a query that uses Contains Near with one that uses regexp_like. The initial Contains Near query takes about 45 minutes to run. Clob Column holds large “documents” and is domain indexed.
Initial query:
SELECT column1
FROM TEST
WHERE CONTAINS(column1,'{NEAR(quick,fox, lazy), 3, FALSE}')>0;
Proposed query:
SELECT column1
FROM TEST
WHERE REGEXP_LIKE(column1, '(b(quick|fox|lazy)(?:W+w+){1,6}?W(quick|fox|lazy)(?:W+w+){1,}?W(quick|fox|lazy)b)','i')
I got the original regexp syntax from here: https://www.regular-expressions.info/near.html.
Problem: I get the regexp code to work in html https://www.regextester.com, but when I put it in Oracle it doesn’t find anything. What is wrong with my syntax? I can’t figure it out. Does Oracle handle REGEXP differently?
Advertisement
Answer
Alex, you were exactly right. I don’t see how to select your answer as correct though.
My problem was apparently that I was using regexp parameters that Oracle doesn’t recognize. So, whereas it worked on https://www.regextester.com, it failed to work in Oracle because most of what I used isn’t recognized as usable with regexp in Oracle. I really think Oracle should expand their regexp codes it recognized. This was really frustrating.