Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How do you aggregate values into predefined ranges?

Re: How do you aggregate values into predefined ranges?

From: Greg Stark <greg-spare-1_at_mit.edu>
Date: 2000/04/03
Message-ID: <87vh1zak5v.fsf@HSE-MTL-ppp43091.qc.sympatico.ca>#1/1

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
Received on Mon Apr 03 2000 - 00:00:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US