Re: Performance of trunc(date_column) and indexes...

As the TRUNC function is not on the indexable column the optimizer should be able to use the index. You may need to collect stats on the column.

What wouldn't work would be

TRUNC(datastamp) = TRUNC(sysdate)

Jonathan Waland wrote:

> 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:41:51 CST