Re: Index on Year of date field

In article <34FBDBEC.63A1543C_at_camline.com>, Stephan Schaefer wrote:

When there is a function against a column, Oracle will not use an index.

One way around your problem would be to:

alter session set NLS_DATE_FORMAT='YYYY'; select col1,date_col Year from mytable;
alter session set NLS_DATA_FORMAT='whatever-it-was-before';

To get that value, you need to:

select VALUE from NLS_SESSION_PARAMETERS  where PARAMETER = 'NLS_DATE_FORMAT';

regs

Neil Chandler

p.s. I don't think an index hint would work, but you could try it if you are using Cost based optimisation instead of rule based.

>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
>
>
>
Received on Tue Mar 03 1998 - 00:00:00 CST