Hello forum.
I have a query that uses the UTL_MATCH.JARO_WINKLER_SIMILARITY function.
SELECT count(*)
FROM <some-table>
WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(<table-column>, <matching-value>) >= <threshold-value>;
The query, when run once at the prompt, runs pretty fast. However, I have it sitting in a PL/SQL loop, executing
500K times and within each iteration multiple slightly different times:
...
for c_rec in (SELECT ... FROM ...)
loop
SELECT count(*)
INTO...
FROM <some-table>
WHERE UTL_MATCH.JARO_WINKLER_SIMILARITY(<table-column>, c_rec.<matching-value>) >= <threshold-value>;
end loop;
...
It adds up. The process takes 90 hours to run, and from our performance stats report, most of it appears to come from the UTL_MATCH.JARO_WINKLER_SIMILARITY.
Is there any way to create a function-based index or something else to speed up the processing?
How might the function-based index work if the function's second value <matching-value> is not known beforehand?
Thank you
Boris