Re: Accessing tuples sequentially

Thomas Kyte wrote:
>
> On Sun, 19 Oct 1997 21:58:37 -0700, Chrysalis <cellis_at_iol.ie> wrote:
>
> >Joseph Manuel Camilleri wrote:
> >>
> >> I am developing a PL/SQL program that is required to read a huge Oracle
> >> table in a particular order, say in order of increasing values for a
> >> particular attribute.
> >>
> >> Now there exists an index on this attribute (a single attribute index).
> >>
> >> I know a can easily use the 'order by statement' at the end of the select
> >> but I think this actually does a sort on that same attribute. Since its a
> >> very huge table I would like to avoid this sort.
> >>
> >> Logically, since there is an index on the attribute, I am requesting the
> >> tuple in the order of the index.
> >>
> >> Can anyone help by explaining how I can accesses all tuples in a table in
> >> the order of an index created on that table, without having Oracle actually
> >> do the unnecessary 'order by'.
> >>
> >> Thanks,
> >>
> >> J M Camilleri
> >
> >With the ORDER BY option, if the optimiser reckons it is more efficient
> >to retrieve all rows via a full table scan and then sort the result, it
> >will do that. Otherwise it will use the index.
> >
> >If you trust the optimiser, let it choose.
> >If you don't, force the use of the index with an explicit hint:
> >/*+ index_asc (tablename/label indexname) */
> >
> >HTH
>
> Well, the hint will tend to drive the optimizer towards an index but it will not
> force it. Hints are hints, not directives...
>
> Beware, if the indexed column is defined to allow NULLS (don't care if none of
> the values are actually null but of the definition allows for nulls) then a
> query of the sort:
>
> select * from T order by C;
>
> where C is such a column (nullable) the optimizer cannot use the index (no
> matter what).

snip

Good point, Thomas.
I should have said "..., you can *try* to force the use of the index ..."

-- 
Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards"