Re: Index access vs Ignored hints

From: Thomas Kyte <tkyte_at_oracle.com>
Date: 16 Feb 2005 05:55:07 -0800
Message-ID: <118562107.00010924.067@drn.newsguy.com>

In article <1108488927.071320.99080_at_z14g2000cwz.googlegroups.com>, G Quesnel says...
>
>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
>

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

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_at_ORA9IR2> create table t as select * from all_objects where 1=0; Table created.

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

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

PL/SQL procedure successfully completed.

ops$tkyte_at_ORA9IR2> set autotrace traceonly explain ops$tkyte_at_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

Received on Wed Feb 16 2005 - 07:55:07 CST