Re: TRUNC or BETWEEN?

>
>I'm wondering if either of these functions/methods in Oracle 7 will turn off
>indexes and perform full-table scans.
>
> ...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

I think your info is 1/2 right--an index on a column will *not* be used if the the col. is an argument to an SQL func (like TRUNC.) (This makes sense if you think about it--once your date is TRUNC'd it no longer has the same format as the values in the index). However, the Oracle doc on the rule-based optimizer (which I assume you are using)-- such as Chap 13 in the "Concepts" manual-- states that indexes are used in bounded range searches (such as the one stated using the BETWEEN operator.) However, bounded ranges represent only one type of access path, and the optimizer may not use it if there is an access path with a higher rank (bounded range ranks 10th out of 15 diff access paths.)
BTW: you can use EXPLAIN PLAN to find out for sure what is happening-- see Appendix B in the "Oracle 7 Server Application Developer's Guide.)

Good luck.

-- 
   Vince Ventrone
   The MITRE Corporation       "...In my opinion, there's nothing
   Bedford, MA 01730           in this world beats a '52 Vincent
   vav_at_mitre.org               and a redheaded girl."  -- Richard Thompson