> With difference comparison, no index can be used anyway. But if
> the case
> is like Helen wrote:
>
> where extract (month from aTimestamp)
> = extract (month from CURRENT_TIMESTAMP)
> and extract (year from aTimestamp)
> = extract (year from CURRENT_TIMESTAMP)
>
> then why not simply do this:
>
> where extract(month from aTimestamp)
> = extract(month from CURRENT_TIMESTAMP)
> and aTimestamp between CURRENT_TIMESTAMP-32 and
> CURRENT_TIMESTAMP+32
>
> BETWEEN can use an index and limits the result set (no month is
> longer
> than 32 days - even if one happen to use a strange form for
> summer
> time), and EXTRACT ascertains that only records from the current
> month
> is included.

Thanks to everyone for the advice on this. If I understand your approach
correctly, because of the large number of rows I have to deal with, clearly
using an index to speed up the search is mandatory. Your query suggestion
appears to force the optimizer to use the index for the BETWEEN part of the
select query (thereby quickly limiting the number of rows that has to be
dealt with) and then I suspect sequentially searches through all the rows in
that set using the EXTRACT part of the query. Is that ultimately what you
are suggesting would be the fastest way to do this?

Regards,
Myles

===============================
Myles Wakeham
Director of Engineering
Tech Solutions US, Inc.
Phone (480) 451-7440
www.techsol.org