In the following thread, @alex03113 (Alex R) made some claims about relative performance of PL/SQL and the SQL clause MATCH_RECOGNIZE:
https://forums.oracle.com/ords/apexds/post/friday-s-quiz-min-path-3747
He first stated: “Generally speaking about SQL solutions…If some problem can be solved in SQL with advanced Oracle SQL features (model, recursive CTE, pattern matching) then in absolute majority of cases that means there is still better procedural approach. Not always though.”
[Note: Alex clearly meant row pattern matching, which is implemented using the MATCH_RECOGNIZE clause.]
He later repeated his claim specifically for MATCH_RECOGNIZE: “I hope it is fairly obvious that MR cannot perform better than PL/SQL here in general case.”
For my part, I have found MATCH_RECOGNIZE to perform well, more like analytic functions than MODEL or recursive CTE.
During our discussion, good points were raised about performance using this clause:
- The AFTER MATCH SKIP TO NEXT ROW option may be slower;
- Backtracking can lead to slow, even catastrophic performance;
- If the requested pattern allows it, the execution plan will include a step with the words "DETERMINISTIC FINITE AUTO". This means that the state machine is deterministic and thus when running the sorted rows through the state machine, there can be no backtracking.
See https://blogs.oracle.com/datawarehousing/post/match_recognize-and-the-optimizer for an explanation by the Oracle Product Manager for this feature.
Finally, Alex made a more precise claim: “if there is no “deterministic” in the plan then very likely there is better PL/SQL solution.”
When I asked him for an example of a case where a procedural approach runs faster than a correctly coded MATCH_RECOGNIZE solution, he replied: “I can give you example where PL/SQL runs several orders of magnitude times faster. Would you like to start a separate thread for that?”
So here is that thread. @alex03113 , please post your example.
Thanks in advance,
Stew
P.S. as of 2025-07-2025, Alex has not met my challenge. He posted a MATCH_RECOGNIZE example designed to provoke backtracking, but provided no PL/SQL solution at all, much less one that runs much faster.