Archives of the TeradataForum

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

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

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>>