Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 12 Mar 2008 @ 21:05:41 GMT

  <Prev Next>  
Next> Last>>  

Subj:   Can I avoid a product join?
From:   Duell, Bob


I have a 2,000 row table (DEV_LOOKUP) based on a non-overlapping range of serial numbers (column names beg_serial_num and end_serial_num, which make up the unique primary index). The table contains attributes for equipment sold to our customers, such as make and model number.

I also have a 50 million row table (CUSTOMER) with the unique serial number of a particular device assigned to individual customers. To lookup the attributes of a particular device, I currently join these two tables using a BETWEEN expression, i.e.

     select b.model_num, count(*) as items_in_use
     from   CUSTOMER a
     join   DEV_LOOKUP b
     on     a.serial_number between b.beg_serial_num and b.end_serial_num
     group by 1

This requires a product join which is killing my query.

Can anyone suggest an alternative technique? I have lots of flexibility with the DEV_LOOKUP table (I own it), but the main CUSTOMER table is outside my control.



  <Prev Next>  
Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023