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 diff ranges) to each one of those values, so we know now many rows there are in the range 0-25 for example. Then we can report on number of rows and sum of usage that occur in each range. the ranges are 0-25, 25-50, ...., 50-500, 500-10000, 10000-100000, 1000000-999999999 the result set would be

usage value		range value
22			0-25
243			50-500
12			0-25
5647			500-10000
34234565		100000-999999999

We have divised several methods:

1. Decode: using decode to assign to block. Works fine (except very long), but the business logic of assiging blocks is now in the code and any change requires code to be modified, and retested, and remigrated, etc
2. create table RANGE with 2 cloumns and 100,000 rows with the usage value in left cloumn and range it belongs to in right column. Then do a = join between usage value in first table and left column in RANGE table to get value of right columns range.
3. Create table betw_Range that has the two columns min_value and max_value and has the range endpoints as min and max values (50 rows then) Then do select a.usage, b.min_value||'-||b.max_vaue from TB1 a, betw_range b where a.usage between b.min_val and b.max_val

which gives

usage		min_val||'-'||max_val
22			0-25
243			50-500
12			0-25
5647			500-10000
34234565		100000-999999999

Is using between this way bad design, are there pitfalls or problems that can occur?
Is there a better solution other than 1-3???

POINTS: No, we cannot put it add column to TB1. We also want table so as to allow easy modification of ranges. We need to report rows and sum of usage that occur in each range. With 140 million rows speed is very important. This is going to be used in a Cognos Pwoerplay cube, so any CUBE gurus out there any ideas?

any help would be great!! Received on Sun Jun 25 2000 - 00:00:00 CDT