Re: Sort Behavior in 8i and 9i

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 limited
> usefulness.
>
> Thanks for the help,
> Jamie

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

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"