I have a recursive view that uses a CTE in both the recursive and non-recursive operands to the union operator. This
CTEis quite complex --- it calls various others CTEs and does some aggregations, etc. Looking at the explain plan for
theview I can see that a "CTE Scan" with a Filter is being performed for the recursive call. As one would expect, due
tothe recursion, it is scanned many many times and is killing the performance of the view.
My question is this: are there any ways to make a CTE aware of the underlying indexes of its contributing tables? Given
thecomplexity of the CTE, and that it is referenced on both sides of the union, I am reluctant to simply substitue the
definitionof the CTE into the FROM clause.
It seems my only option is to create the CTE as a materialized view and create appropriate indexes to allow the
recursiveunion to perform acceptably.
Does anyone have other suggestions for how best to achieve a performant query?
Thanks,
-Joe