Archives of the TeradataForum

Message Posted: Fri, 12 Sep 2003 @ 23:24:51 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

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.

	<Prev	Next>		<<First	<Prev	Next>	Last>>