Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 27 Oct 2003 @ 19:49:54 GMT


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


Subj:   Problem while doing large to small table JOIN
 
From:   Rick Davis

I was hoping somebody could shed some light on a problem I see with large table \ small table joins.

Table A (< 100 million rows)

ColA
ColB
ColC
ColD
ColE
Cust_cd
Order_cd
ColF
ColG
...

-Primary Index (ColA, ColB,ColC,ColD,Cust_cd,Order_cd,ColE)

-Collect Stats on(cust_cd,order_cd)

Table B (small...< 1000 records)

Col1
Cust_Cd
Order_Cd
Col2
..

- UNIQUE PRIMARY INDEX(COL1)

- NUSI (Cust_cd,order_cd)

- Collect stats on Col2

When I joined Table A to Table B on (Cust_Cd/Order_Cd) and with a residual condition TableB.col2 = A, the optimizer duplicating Table B to all AMPs then performing a merge join with Table B .. Works like a gem

But when I colelcted stats on NUSI for TableB , the pobitimizer is redistributing Table B and Table A and permoforming a Product Join and the SQL hangs

Any help would be highly appreciated


Thanks,

Rick



     
  <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: 27 Dec 2016