Indexes & Dates

[Medhatithi]

Hi All,
I need to find out all entries which are done in the month of January. I can easily do so by using the to_char function and appropiate formats on my date field. But, the problem is that there is an index created on the date field. When I use to_char(create_ date,'mon') on that field, the index is not used(naturally) , and so a full table scan occurs. How can I override this. (Creating a function-based index would definitely not be a good solution for a single query)

[frozenmist]

Hi All,
I need to find out all entries which are done in the month of January. I can easily do so by using the to_char function and appropiate formats on my date field. But, the problem is that there is an index created on the date field. When I use to_char(create_ date,'mon') on that field, the index is not used(naturally) , and so a full table scan occurs. How can I override this. (Creating a function-based index would definitely not be a good solution for a single query)

Hi,
Please take more care while posting threads. You had posted a duplicate thread, which I have deleted.
Thank you...

Moderator

[Medhatithi]

Hi All,
I need to find out all entries which are done in the month of January. I can easily do so by using the to_char function and appropiate formats on my date field. But, the problem is that there is an index created on the date field. When I use to_char(create_ date,'mon') on that field, the index is not used(naturally) , and so a full table scan occurs. How can I override this. (Creating a function-based index would definitely not be a good solution for a single query)

Hi All,
I found out a solution. It seems to be very simple, but seems to be very effective.
If I like to found out records entered in the month of January 2007, I can easily do so by finding out records which are after 31st Dec 2006, but before 1st Feb 2007. In this way, I won't have to use a function on the date field, thereby enabling the compiler to use the index on that field

[debasisdas]

Try using the operator BETWEEN for the purpose.

[Medhatithi]

Yes, that is alright. Actually, my main concern was to use indexes, which is not possible if I operate to_char on the date column .....