Re: Tuning queries for historical trend data... some success

Awesome!

In article <877lfxy9k8.fsf_at_HSE-Montreal-ppp33976.qc.sympatico.ca>,   Greg Stark <greg-spare-1_at_mit.edu> wrote:
>
> If you're like me you've found yourself frequently doing queries like:
>
> select count(*),trunc(timestamp,'MM') from tbl group by
trunc(timestamp,'MM')
>
> This query gives historical trend data. This type of data is useful
for
> graphing, or for detecting problems, or even for getting an idea of
when
> things started to go wrong,
>
> Unfortunately such queries are really inefficient as they always do
huge
> sorts. Oracle 8 cannot make use of an index on timestamp in this query
at all.
> Oracle 8i has functional indexes but unless you build an index on
every
> resolution you plan to use they don't really help here either.
>
> There's no way to convince any version of Oracle that the index on
timestamp
> itself is in precisely the right order for trunc(timestamp,'MM') and
for that
> matter trunc(timestamp,'HH') as well. As a consequence Oracle will
insist on
> sorting all those records all over again, which if you're processing
thousands
> of transactions per sample and hundreds or thousands of samples, is
> impractical.
>
> Well, I finally thought of a more efficient way to do this query:
>
> first you need something like this:
>
> create table numbers (n primary key) as (select rownum from
all_objects);
>
> now:
>
> select /*+ FIRST_ROWS */ count(*),n "Minutes Ago"
> from tbl,numbers
> where timestamp > sysdate-n/1440 and timestamp <= sysdate-(n-1)/1440
> group by n
>
> For this query, with sufficient prodding, Oracle can be pushed into
doing with
> a nested loops join using entirely indexed lookups and with the holy
grail of
> OLTP query optimization: a GROUP BY NOSORT.
>
> Oracle is particularly ornery about sorting and trying to convince it
to do a
> GROUP BY NOSORT is amazingly frustrating. If the table with the
numbers
> doesn't have an index it won't do it -- even if it just finished
sorting the
> driving table! That's why just joining against all_objects doesn't
work.
>
> --
> greg
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Tue Feb 22 2000 - 22:19:34 CST