Re: Date Fields

From: Connor McDonald <connor_mcdonald_at_yahoo.com>
Date: Mon, 11 Oct 1999 18:52:26 +0800
Message-ID: <3801C16A.3456@yahoo.com>

amerar_at_ci.chi.il.us wrote:
>
> Hello,
>
> I have a question about date fields. We have a report that uses the
> date fields in the where clause. So, we created an index on this field
> to speed up the query. This works fine if we use a TO_DATE parameter.
> But if we use the TRUNC verb, then it seems like the index is not used
> because it takes forever......
>
> Why is this?
>
> Thanks,
>
> Arthur
> amerar_at_ci.chi.il.us
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

You can't put an expresion around an index column and hope for the index to work...Think of the phone book - if you wanted to look for the third letter of each surname being 'X' (ie use of SUBSTR) you would have to start top to bottom...

If you are using trunc to remove the time part then replace

where trunc(dte) = '01-jan-99'

with

where dte between '01-jan-99' and '01-jan-99'+0.99999

HTH
--

---

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 Mon Oct 11 1999 - 05:52:26 CDT