Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Fri, 27 Jul 2012 @ 14:06:41 GMT

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

Subj:   Re: PE plan for join
From:   Dieter Noeth

sughesh iyyani wrote:

  Suppose there are 2 tables, table a with 10 million records, table b has 100 million records, now we are joining both tables, when we see explain plan of PE the plan showing TD will took smaller table a and it will redistributes it among all AMPs to do join.  

  My question is: by doing this plan is the optimizer is correct job or not?  


Table B seems to have the PI on the join column, table A has a different PI, so redistributing the small table seems reasonable.

  So much spool space required for this operation.  

Explain will show you how much spool the optimizer assumes (but it will not tell if it's evenly distributed or skewed)

  If I have UPI and some SI on this table, will PE select above plan?  

A SI on the join column will not change that plan (unless you join a small number of rows), because using a SI for that join would need more resources. This is different to most other DBMSes.

But if the join column is the PI of table A you'll get a direct join without any preparation.


  <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: 28 Jun 2020