Re: How do you aggregate values into predefined ranges?

Thomas J. Kyte <tkyte_at_us.oracle.com> writes:

> Decode might do the trick for you. for example:
> ops$tkyte_at_8i> select decode( trunc(usage/100), 0, '0-99',
> 2 1, '100-199',
> 3 2, '200-299',
> 4 3, '300-499',
> 5 4, '300-499',
> 6 5, '500-700',
> 7 6, '500-700',
> 8 decode( usage, 700, '500-
> 700', 'Over 700' ) ),

Decode is the only solution I know of, but it's obviously incredibly awkward. It's also inefficient as oracle is not capable of using any indexes on the underlying table even though the aggregates he wants could easily be built from bitmap or regular indexes in order.

It also means Oracle has to do a possibly large nlog(n) sort to generate these numbers. One way to avoid the sort is to do

select sum(decode(field,x,1,0)) "x",

       sum(decode(field,y,1,0)) "y", 
       ...

instead of a group by on the decode. But this is only feasible for situations where you can enumerate every case. That's not always useful.

I've often wanted to do "group by trunc(foo/100)" for example, which is just a special case of this need to group on predefined ranges. It would be extremely useful if Oracle provided an access path to do the obvious thing -- use an index on a field to do a group by nosort on the field even when using less than the full precision of the field.

This could apply for trunc, round, and ideally for user defined ranges like this user. functional indexes aren't really enough, often you want to be able to group on arbitrary levels of precision, and there's really no reason to need duplicate indexes on exactly the same data to different levels of precision.

-- 
greg