Index on date column
495403Apr 1 2011 — edited Apr 5 2011Hi,
I am experiencing a behavior when quering a table with an indexed date column that seems somewhat estrange to me.
I have a table with several columns and 4.8 million rows ( Oracle 11gR2) . One of its columns is of Date data type. Such column is indexed using a b-three index.
Consider the following select. It returns just 32 thousand rows (less than 1% of the number of rows in the table). Oracle is doing a full scan to execute the query.
Field2 may be any column of the table that is not part of the index (e.g. a column that is varchar2(14)).
As the filter used has high selectivity, I expected Oracle to use the index on the date_field when executing the query. Does any one have any clue on this issue? Is there any rule of thumb on indexing date columns?
select field2
FROM table
WHERE date_field >= TO_DATE('13/09/2010','DD/MM/YYYY')
and date_field < TO_DATE('14/09/2010','DD/MM/YYYY')