page115
97
Figure 3-18.
Filter push down
However, there are scenarios where CTEs may not perform as expected, which I will
discuss next.
Reusing CTEs
Where a CTE is referenced more than twice within the same SQL statement, you may
find attribute pruning is disabled.
In this example, a query used to expose the CTE reuse issue; you can declare a parent
CTE that is referenced by two child CTEs. Each child is then referenced by the main
query body on either side of a
UNION ALL
.
WITH nation_list AS
(
SELECT r.*
FROM nation_baseline r
),
ChapTer 3
The Query profiler