Re: How can I use Index on a DATE Field

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Wed, 17 Nov 1999 17:44:52 +0800
Message-ID: <38327914.3737@yahoo.com>

Allen wrote:
>
> 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

Any expression around an indexed column disables the use of the index (except for 8i in certain circumstances)...

Easy enough to avoid:

where datecol > to_date('01-jan-1900','dd-mon-yyyy') for greater/less than

where datecol between sysdate and (sysdate + 0.99999) for between --

---

Connor McDonald
"These views mine, no-one elses etc etc" connor_mcdonald_at_yahoo.com

"Some days you're the pigeon, and some days you're the statue." Received on Wed Nov 17 1999 - 03:44:52 CST