Archives of the TeradataForum

Message Posted: Thu, 13 Mar 2008 @ 17:03:28 GMT

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

Hello Bob:

Since you have control of the dev table and you don't want a product join, here are a few suggestions to make this join as fast as possible on Teradata:

1. Instead of a range of beg_serial_num and end_serial_num, place one row use 3rd normal form and have one row per serial number. I have no idea how many you are talking about here (50M?), but it does eliminate the need for a product join

2. Make serial_num on the dev table primary index so that rows from both tables are already co-located on the same AMP (eliminates redistribution in order to do the join)

3. Change your join to: ON a.serial_num = b.serial_num /* results in merge join vs product join */

4. Potentially add codes to the dev table rows that allow you to use a WHERE to retrieve only the dev rows needed for the operation

Hopes this provides some food for thought.

Michael Larkins
Certified Teradata Master
Certified Teradata SQL Instructor

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