[email]paulabacus.co.uk[/email] (Paul Reddin) wrote in message news:<1fd2a603.0307040747.3a8e2d45posting.google. com>...
Paul,> Any help appreciated, as it is fundamental to our use of views [common table
> expressions] that such [end user] predicates are pushed_down.
According to Don Chamberlin's Complete Guide to DB2 UDB, one of the
properties of common table expressions is that they remove the
possibility of potential inconsistencies that might arise if you
instead used identical table expressions at multiple points in a query
(without repeatable read). This is a useful property, although I
can't find reference to it in the manuals incidentally.
From this I have always understood that commmon table expressions are
evaluated once, which would tend to mitigate against pushing down
predicates into them - the optimizer could only push down a predicate
if that push-down was clearly appropriate in all cases where the CTE
In your case, does the CTE get referenced more than once in the query?
If so, I think that's all the answer you need. If not, then clearly
the optimizer could treat the CTE like a regular table expression, but
maybe the IBM bods have missed that potential tweak - after all, under
such cirstances you yourself could just rewrite the CTE to a
regular table expression.
Hope this helps.