page118
100
FROM comment_list
UNION ALL
SELECT n_name
FROM name_list;
Not only is the refactored query easier to read, the query profiler is also simpler, as
shown in Figure 3-20 where the predicate pushdown is highlighted.
Figure 3-20.
CTE attribute pushdown enabled
CTE Costs
I recommend the use of CTEs to abstract complex logic and simplify code, but not in all
situations. As you have seen, nested CTEs can lead to predicates not being pushed down.
Elegant code is both readable and readily understood. CTEs in general aid
readability, but if poorly structured and implemented, they can increase code
complexity and maintenance overheads. You should also appreciate the lost opportunity
cost of developers understanding before they begin to refactor or remediate code. In
other words, Keep It Simple, Stupid (KISS). Use CTEs judiciously to reduce complexity
and layers of code, not forgetting to comment your code.
ChapTer 3
The Query profiler