Home Page for the TeradataForum

Archives of the TeradataForum

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

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

Subj:   Re: Can I avoid a product join?
From:   Michael Larkins

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>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 23 Jun 2019