Re: dates and indexes

In <4l93vf$pdr_at_dfw-ixnews3.ix.netcom.com>, yeazel_at_ix.netcom.com (Steve Yeazel) writes:
>
>If a date column has an index on it, does referencing that column with
>a to_char within the where clause cause the index not to be used?
>
>For instance,
>
>select * from tbl where to_char(datecol,'YYYY') = '1996';
>
>
>If datecol has an index on it, will it be used? I believe the answer
>is no, but want to confirm.
>
>Steve

Applying any function to any column would prevent use of an index on that column, because the column's actual value, not the result of some function on the column's value, is what is indexed. If I asked you to look up my name in the phone book (where last_name = 'FEIBUSCH'), you could do it easily. If I asked you to look up everyone with eight letters in their last name (where len(last_name) = 8), you would have to scan the whole book.

If this query is really what you want to do:
>select * from tbl where to_char(datecol,'YYYY') = '1996';
then all is not lost. This query should allow use of the index:  select * from tbl where datecol >= to_date('1-jan-96') and    datecol < to_date('1-jan-1997');

That way, the type conversions are done only once, when the query is parsed, and the index provides a starting and ending point. Note, however, that if you are retrieving a significant percentage of your table, it may not be worthwhile to use the index anyway. Received on Mon Apr 22 1996 - 00:00:00 CEST