Posted by Gert-Jan Strik on 03/01/06 22:32

If the starting and ending bigints ranges are not overlapping, then I
would classify this as the "zipcode problem".

If this is the case, you could try the following approach:

SELECT R1.*,T1.bigint
FROM transactiontable T1
CROSS JOIN referencetable R1
WHERE R1.starting_bigint = (
SELECT MAX(starting_bigint)
FROM referencetable R2
WHERE R2.starting_bigint <= T1.bigint
)
AND R1.ending_bigint >= T1.bigint

Hope this helps,
Gert-Jan


"rkershberg@gmail.com" wrote:
>
> I have two tables.
>
> One has approx 90,000 rows with a field .. let's call in BigInt (and it
> is defined as a bigint data type).
>
> I have a reference table, with approx 10,000,000 rows. In this
> reference table, I have starting_bigint and ending_bigint fields. I
> want to pull out all of the reference data from the reference table for
> all 90,000 rows in the transaction table where the BigInt from the
> transaction table is between the starting_bigint and ending_bigint in
> the reference table.
>
> I have the join working now, but it is not as optimized as I would
> like. It appears no matter what I do, the query does a full table scan
> on the 10,000,000 rows in the reference table.
>
> Sample code
>
> SELECT ref.*, tran.bigint
> from transactiontable tran
> INNER JOIN referencetable ref on tran.bigint between
> ref.starting_bigint and ending_bigint
>
> Yes, all 3 of the fields are indexed. I even have a composite index on
> the reference table with the starting_bigint and ending_bigint fields
> selected as the composite.
>
> Any help would be appreciated.
>
> Robert H. Kershberg
> IT Director
> Tax Credit Company
> RKershberg@taxcc.com or RKershberg@pobox.com or RKershberg@gmail.com

[Back to original message]