Archives of the TeradataForum

Message Posted: Thu, 11 Sep 2003 @ 18:30:25 GMT

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

Hi Rick:

I will take a shot at your question, but not sure I understand all the ramifications of what you might be looking for in posing it.

Derived tables are held in SPOOL. SPOOL does not have any secondary index definitions. Therefore, it will not use a secondary INDEX in a JOIN or anywhere else. In general, Teradata does not use an index for joining, except in a nested (single AMP/single row) join.

Teradata will primarily tend to use one type of merge join or another (ie. merge join, inclusive merge, exclusive merge - even with OUTER JOIN processing). In this processing, normally one of the table's rows are spooled anyway (sometimes both tables).

The Teradata optimizer decides at run time whether or not to use an INDEX dependent on residual conditions being defined in the WHERE clause and on columns specifying equality and a value (versus column=column join).

The biggest impact that you can have on the use of your derived table is to place the join column as the first column, since that is the implied primary index. It can save potential redistribution in order to do a subsequent join.

I hope this helps.

Michael Larkins
Certified Teradata Master

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