Archives of the TeradataForum
Message Posted: Fri, 12 Sep 2003 @ 23:24:51 GMT
| Subj: || || Re: Will an Index be used with Derived Tables |
| From: || || Christie, Jon |
Prior to release 5, the spool containing the materialization of a derived table is built locally. That means its distribution is
unpredictable because it depends on the plan used to materialize the derived table. If, when joining the materialized spool to other
tables, the optimizer finds it needs a particular distribution, it will put a retrieve step in the plan to achieve that distribution.
In release 5 and beyond, the simplest way to put it is to say that derived tables will be handled as if they were views. The good thing
about this is that it will make those extra retrieve steps a thing of the past and may also allow the optimizer to choose a better plan
because it can "see inside" the derived table. An unfortunate side effect is that some tricks with derived tables may no longer work. For
instance, sometimes a bad plan can be improved by using derived tables to force a particular join order. This won't work in release 5.
Todd Walter started warning people about this a long time ago.