Archives of the TeradataForum

Message Posted: Tue, 03 Apr 2007 @ 11:09:09 GMT

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

Ramu,

Since you have join from small table(200 rows) to huge table(5M rows), teradata is likely to choose a product join by duplicating B_acc_tmp on all amps.

Best method will be specifying multiple OR'ed join conditions.

Remember to collect statistics on smaller table.

     sel * from
     A_acc_sp
     Left Outer Join B_acc_tmp
     ON
     (A_acc_sp.acc_col_1= B_acc_tmp.acc_cd and
     B_acc_tmp.acc_id = '2A')

     OR
     (A_acc_sp.acc_col_2= B_acc_tmp.acc_cd and
     B_acc_tmp.acc_id = '3A')

     OR
     (A_acc_sp.acc_col_3= B_acc_tmp.acc_cd and
     B_acc_tmp.acc_id = '4A')

     OR
     (A_acc_sp.acc_col_4= B_acc_tmp.acc_cd and
     B_acc_tmp.acc_id = '5A')

     OR
     (A_acc_sp.acc_col_5= B_acc_tmp.acc_cd and
     B_acc_tmp.acc_id = '6A')

     OR
     (A_acc_sp.acc_col_6= B_acc_tmp.acc_cd and
     B_acc_tmp.acc_id = '7A');

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