Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 05 Nov 2009 @ 19:49:44 GMT


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


Subj:   Re: UPI not behaving as expected.
 
From:   Victor Sokovin

  I have a query which have inner joins on many tables, one of the inner join is quite a mystry to me so I just thought to share with you and know if we can resolve this mystry.  


  A table is have a single-column UPI and this table is being joined with other table on UPI when I see the explain plan, it tells that this particular join be being performed using merge join. Even though there is UPI on this table still this table is being duplicated on all the 400 AMPs and then merge join is performed..due to this duplication this step is taking over 19 min. Here I can not see the advantage of UPI with uptodate stats. Can some one please explain as to why this is happening and can I avoid it? Below is exact copy of explain plan step....  


Strange indeed. I can only guess that the decision to replicate this table comes from the analysis of *other* joins that you mentioned. The optimizer is trying to think a few steps ahead and seems to be willing to "sacrifice" the "easy" UPI because it probably sees other uses for the replicated data anyway.

Try to experiment with reducing the number of tables and joins in the query to see for which join the plan changes.


Victor



     
  <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