Archives of the TeradataForum

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

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

Hi,

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.

Thanks,

Bob

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