Tuning queries for historical trend data... some success

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 Received on Tue Feb 22 2000 - 01:34:51 CST