Re: Accessing tuples sequentially

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

-- 
Chrysalis

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