| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: Accessing tuples sequentially
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). That is because null values are not indexed so the index entries are not one for one with the rows in the table (there are more rows in the table then are in the index). So, either make sure C is not nullable OR do a query such as:
select * from T where C > 0 order by C;
the predicate on C makes NULLs not possible in the result set and the predication will help the optimizer pick the index as well. (and by all means, use the index hint as well to help it along).
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
NOTICE: In order to cut down on the amount of time I spend applying silly logic to email addresses like "In order to cut down on spam, remove THIS from my email" or "In order to cut down on spam, reverse my email address", etc, I will simply hit delete when that mail bounces back to me, which is what I do with spam myself.
I haven't figured out whats more time consuming for me, deleting spam or resending emails after re-reading the whole thing to figure out the algorithm to apply. Received on Sun Oct 19 1997 - 00:00:00 CDT
 |
 |