Re: Why would an index not be used if specified as a hint in a query?

In article <1108386945.152677.126800_at_f14g2000cwb.googlegroups.com>, G Quesnel says...
>
>Does this imply that it is 'safe' to take the following shortcut ?
>Using Oracle 8i on HPUX, with statistics on tables and indexes.
>We have a large table which has a regular index based on (col_status,
>col_foreign_key). A very small percentage of rows will end up
>with col_status='D' (a few thousands). In order to process these
>records we want to identify blocks of 100 rows with the lowest
>col_foreign_key where col_status='D'.
>
>We currently use a statement similar to ...
>Select col_foreign_key
> from (Select col_foreign_key from tab
> where col_status='D' order by col_foreign_key)
> where rownum < 101;
>
>Since we have that ALT_KEY_STATUS_FKID index on tab (col_status,
>col_foreign_key),
>would we always get the lowest col_foreign_key with a statement like
>Select /* +INDEX(TAB ALT_KEY_STATUS_FKID) */ col_foreign_key
> from tab
>where col_status='D' and rownum < 100;
>

Unless your SQL has an order by, you cannot have any expectation that the data is returned sorted -- EVER.

Unless you use order by, the data is not sorted and you cannot have any expectation that it comes out in any order.

IF the order by can be skipped, that is the job of the optimizer and it will do that. FIRST_ROWS might be an appropriate hint, combined with the order by -- but using INDEX does not

1. assure you that the index will be used
2. assure you that someone doesn't come and rename the index tomorrow for some reason
3. assure you that data is sorted

Unless you have order by, data is not sorted. No matter how many times you run the query and observe "it appears sorted", that is no promise that tomorrow it will be sorted (just like flipping a coin and observing "heads" 1,000 times in a row does not assure you that the 1,001 flip will be heads)

-- 
Thomas Kyte
Oracle Public Sector
http://asktom.oracle.com/
opinions are my own and may not reflect those of Oracle Corporation