Archives of the TeradataForum
Message Posted: Wed, 07 May 2003 @ 16:02:20 GMT
| Subj: || || Re: Derived Table Handling in V2R5 |
| From: || || Walter, Todd A |
The Derived table optimization works exactly like views. If an aggregation or other operation (eg analytic functions) is performed in the
derived table or view, then that operation must be performed at that place in the plan to result in semantically correct results. What will
happen for those cases is, where possible, conditions will be pushed across the boundary into the inner query to be evaluated as early as
possible but the aggregation will be performed as specified.
The primary target of the optimization are the cases where the derived table only refers to a table or join with associated selection
conditions. In this case, like with views, the derived table will be unwrapped and rewritten as a standard join or selection in the outer
query. In this case, the ability to force precedence of the joins will indeed be removed.
As noted by Clay, there is another feature of V2R5 - partial/early group by - which should eliminate the need to define the location of
the grouping operation in the query. The optimizer will notice that the aggregation can be performed over one table early in the plan,
followed by joins to reference tables for instance. This is separate from the derived table optimization.