Re: when doing join is using between instead of equijoin bad design?

>> We have a table TB1 with 140,000,000 rows, with a usage value
(number datatype) that ranges from 0-89000000. We need to assign a 'range' value (around 50 different ranges) to each one of those values, so we know now many rows there are in the range 0-25 for example. <<

I would go with the three column table and the BETWEEN predicate. If you have an open end on the last range, use a NULL, then a predicate like this:

   WHERE T1.usage BETWEEN R1.min_val AND COALESCE(R1.max_val, T1.usage)

You can do the same thing for an open left end in a range. Put this query in a VIEW, and add a CHECK() constraint to the Ranges table to assure that (min_val < max_val)

--CELKO--
Joe Celko, SQL and Database Consultant
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc) which can be cut and pasted into Query Analyzer is appreciated.

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sun Jun 25 2000 - 00:00:00 CDT