Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Date Fields
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
--
"Some days you're the pigeon, and some days you're the statue." Received on Mon Oct 11 1999 - 05:52:26 CDT
![]() |
![]() |