Re: TRUNC or BETWEEN?

In article: <3grfnp$3ot_at_ams.amsinc.com> Edward_Hillman_at_mail.amsinc.com (Ed Hillmann) writes:
>
>
> I'm wondering if either of these functions/methods in Oracle 7 will turn off any
> indexes and perform full-table scans.
>
> I'm trying to receive rows from our database, where the effective date is between
> the start and end dates stored on the row. Currently, we're using TRUNC(),
> because we don't care about the time, just the date itself. So,
>
> ...and trunc(a.start_date) <= 'entered date'
> and trunc(a.end_date) > 'entered date'

        This will prevent the optimizer choosing an index on either a.start_date or a.end_date

        It won't prevent other indexes from being used.

>
> We had also considered using
>
> ...and 'entered date' between a.start_date and a.end_date
>

        Indexes on a.start_date and a.end_date could be chosen in this example.

> but were told that between shut off indexes. Now, I'm being told that TRUNC() turns off
> indexes. Anyone ever hear of either of these? Thanks.
>
>

-- 



---

	Tim L