Connor McDonald wrote:
> Jamie Pearson wrote:
>>
>> Can anyone tell me if Oracle 8i or 9i will utilize a non-unique
> index when
>> sorting a result set. Here's an example:
>>
>> TABLE_A (col1, col2, col3)
>> Primary key is col1.
>> Non-unique index on col3.
>>
>> Statement:
>> SELECT * FROM TABLE_A ORDER BY col3
>>
>> DBA says it will, but explain plan indicates a full table scan with
> no
>> reference to the non-unique index. Granted, explain plan has
[...]
>
> You will probably need col3 defined as NOT NULL because any null
> values
> would not be present in the index. Then its up to the optimizer to
> decide on what costs less - the index or the full scan & sort
>
Maybe one should add that an index on a char or varchar2 column will
most likely not be used for an order by since order by uses nls_sort to
get the right order for your current language setting and the index is
built up using binary comparison.
So you have to say alter session set nls_sort=binary; to get an index
used for order by.
Maybe there is a possibility to build a function based index on
nls_sort(col3) that will be used for sorting but this is a wild guess.
Hope that helps,
Lothar
--
Lothar Armbrüster | la_at_oktagramm.de
Hauptstr. 26 | la_at_heptagramm.de
D-65346 Eltville | lothar.armbruester_at_t-online.de
Received on Mon Mar 31 2003 - 10:46:20 CST
