Re: Sort Behavior in 8i and 9i

This won't use any index on the table.

It will do a full table scan into a temporary segment for sorting and throw the results back.

"Jamie Pearson" <jpearson_at_nspr.com> wrote in message news:b5pupq$jkj$1_at_slb6.atl.mindspring.net...
> 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 limited
> usefulness.
>
> Thanks for the help,
> Jamie
>
>
Received on Thu Mar 27 2003 - 19:04:19 CST