Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Re: Will an Index be used with Derived Tables
 
From:   Michael Larkins

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>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023