> 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?

Well Myles, I don't force the optimizer to use any index, I just allow
it to do so if it finds it benefitial ;o)

It wouldn't be the quickest way to do things, that would probably be to
first find the lowest and highest date in the month and only then create
the select - thereby approximately halving the number of records
matching the BETWEEN part and eliminating the need for any EXTRACT. My
suggestion is just a way that can be a practical approach if it is too
time consuming to scan through the entire table, but at the same time
the requirements isn't that strict that every inch of performance has to
be utilized.

Said differently, my suggestion may reduce the retrieval time from 60
seconds to 2 seconds, but if required it may still be possible to reduce
it one further second.

Set