Re: How do you aggregate values into predefined ranges?

I tried Thomas's solution since we have similar problem, it processed 12,000,00 rows in 4:28 mnuites around ~47,000 rows a second. Pretty darn good in my book!!!

In article <87vh1zak5v.fsf_at_HSE-MTL-ppp43091.qc.sympatico.ca>, Greg Stark <greg-spare-1_at_mit.edu> wrote:
>
>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.
>
>
Received on Mon Apr 03 2000 - 00:00:00 CDT