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

From: G Quesnel <dbaguy_ott_at_yahoo.com>
Date: 14 Feb 2005 05:15:45 -0800
Message-ID: <1108386945.152677.126800@f14g2000cwb.googlegroups.com>

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; Received on Mon Feb 14 2005 - 07:15:45 CST