Re: Index on Year of date field

Stephan Schaefer wrote:
>
> I have a table with an index on the date field.
> And I have a view created by:
> create view myview
> as select col1,to_char(date_col,'YYYY') year
> from mytable ;
>
> The explain plan of the statement
> select *
> from myview
> where year = '1997';
> shows me a full table scan.
>
> Can I create an index on the date field so that selecting
> by year that index is used?
>
> Stephan Schaefer
> stesch_at_camline.com

Hi,
an index on a column is only effective, if there are, say, more than 20 distinct values in the column. Probably this isn't the case with you, so the optimizer prefers to do the full table scan.

Anne-Marie Ternes

anne-marie.ternes_at_cie.etat.lu Received on Wed Mar 04 1998 - 00:00:00 CST