I have a question on some information that has been passed on similar
threads. Specifically concerning the possibility that Oracle would
never ignore a hint, if it can be used.
(I though that a hint was never guaranteed to be followed)

We have a rather large table (millions of rows), and one of the
function typically has to work on a very small set of rows (few
hundreds), and once in a while, several thousands. The function will
select a block of rows to be processed based on a status column, where
an ID column has the lowest value. The processing of the records is
important for this business function.
To get the blocks of ids, we currently use something like;
Select col_id
from (select col_id from TAB where col_status='D'
order by col_id)
where rownum < 101;

Since we currently have an index TAB_STATUS_ID_IDX
on TAB (col_status, col_id)
could we use the following select statement instead

Select /*+ INDEX (TAB TAB_STATUS_ID_IDX) */ col_id
from TAB
where col_status='D' and rownum < 101;

and be guaranteed the same result ?
(meaning that we would always get the lowest col_id, of ALL rows in
table TAB where col_status='D')

TIA

No, I wouldn't rely on the hint. If Oracle is configured correctly then
the first query will use the index where appropriate anyway.

But in some cases, the performance would sufer, since it would need to
retreive all rows with col_status='D', figure out which are the lowest
100, pass these, and start over. If I could relly on the index being
used, then the first 100 rows read would always be the lowest ID, and
unneccessary block read would be avoided.
Is the performance hit the price to pay to guaranty consistent
execution path?

In article <1108488927.071320.99080 (AT) z14g2000cwz (DOT) googlegroups.com>, G Quesnel
says...

there is precisely ONE way to get data ordered from the database.

a) use order by


there are no other options, none, zero.

to get the


Select col_id
from (select col_id from TAB where col_status='D'
order by col_id)
where rownum < 101;

the optimizer will utilize an index on col_status,col_id if it makes sense. The
only hint you would consider for this might be FIRST_ROWS, but beyond that -- if
the optimizer can skip an order by -- it'll do it. If not, it'll sort (ensuring
you get the right answer)


but -- from the database, in order to get sorted data from ANY structure (IOT,
Heap table, whatever) you must specify order by. If the optimizer finds a path
that lets it skip sorting, it'll do so. But do not rely ever on a hint to
ensure "ordered data"


but again, if it can skip a sort and it knows "first rows fast please", it will.
consider:


ops$tkyte@ORA9IR2> create table t as select * from all_objects where 1=0;
Table created.

ops$tkyte@ORA9IR2> create index t_idx on t(status,object_id);
Index created.

ops$tkyte@ORA9IR2> exec dbms_stats.set_table_stats( user, 'T', numrows
=>1000000, numblks => 1000000 );

PL/SQL procedure successfully completed.

ops$tkyte@ORA9IR2> set autotrace traceonly explain
ops$tkyte@ORA9IR2> select *
2 from (select /*+ first_rows(100) */ *
3 from t
4 where status = 'VALID'
5 order by object_id )
6 where rownum < 101;

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=HINT: FIRST_ROWS (Cost=6 Card=100 Bytes=12800)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=6 Card=10000 Bytes=1280000)
3 2 TABLE ACCESS (BY INDEX ROWID) OF 'T' (Cost=6 Card=10000
Bytes=1000000)
4 3 INDEX (RANGE SCAN) OF 'T_IDX' (NON-UNIQUE) (Cost=2 Card=4000)





No sort there -- it "knew" it did not have to.


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