Re: How can I use Index on a DATE Field

Allen,

you cannot use an index-search is you have the to_char-statement on the left site. With Oracle 8i, you can create a function based index on the to_char function.
With all other versions you have to use the following:

...where fdate => to_date('19991001',yyyymmdd')

Bye
Uli

Allen <allen500_at_ms15.hinet.net> schrieb in im Newsbeitrag: 80tj7g$pjg_at_netnews.hinet.net...
> Hi, All:
> Need Your Help!
> I have created an index on a table, but the index field which data type
> is DATE.
> I found if I execute the sql statement like that :
> select * from table where fdate > sysdate - 10;
> oracle will use index searching,
> but if I execute the sql statement:
> select * from table where to_char(fdate,'yyyymmdd') => '19991001'
> oracle will use FULL table scan,
> Can anyone let me know How to force Oracle to use INDEX SCAN ?
>
> Thanks for your help
> Allen
>
>
>
Received on Wed Nov 17 1999 - 01:04:18 CST