Archives of the TeradataForum
Message Posted: Wed, 12 Mar 2008 @ 21:05:41 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 28 Jun 2020|