Performance of trunc(date_column) and indexes...

From: Jonathan Waland <jonathan.waland_at_vf.vodafone.co.uk>
Date: Thu, 21 Jan 1999 15:12:43 +0000
Message-ID: <36A743EB.FD1333DB@vf.vodafone.co.uk>

I have a procedure that takes datestamped data, and summaries it based on hour.

There is an index on the datestamp column, but of course the use of a function on the column stops the use of the index:

insert into summary
select trunc(datestamp,'HH24') newdatestamp,

       sum(somecounterA),
       sum(somecounterB),
       max(somecounterC)

  where datestamp >= trunc(sysdate -1) and datestamp < trunc(sysdate)   group by trunc(datestamp,'HH24');

[about 22Million records in the table, going back 58 days, average 13,000 records per hour]

Anyone any suggestions on how to speed it up. Currently using Oracle 7.3.3.5, and the cost based optimiser.

Jon Waland
Vodafone Ltd. Received on Thu Jan 21 1999 - 09:12:43 CST