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'
>
>We had also considered using
>
> ...and 'entered date' between a.start_date and a.end_date
>
>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.
 

Others have responded on the usage of indexes for bounded queries and functions, and I agree with their responses.  

If your intention is to have the query run using a tablespace scan then you can use a "hint" in your Select statement (if you are using the cost based optimzer):

   e.g. Select /*+FULL tablespacename */ col1, col2 From ...    

Regards,
John F. Griffin
Stellar Systems Group
Victoria B.C. Canada Received on Mon Feb 06 1995 - 22:03:35 CET