|
|
|
|
|
|
| Author |
Message |
Thomas Kyte
*nix forums addict
Joined: 03 May 2005
Posts: 62
|
 Posted: Wed Feb 16, 2005 12:55 pm Post subject:
Re: Index access vs Ignored hints
|
|
|
In article <1108488927.071320.99080@z14g2000cwz.googlegroups.com>, G Quesnel
says...
| Quote: |
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.
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 |
|
| Back to top |
|
 |
G Quesnel
*nix forums addict
Joined: 29 Apr 2005
Posts: 77
|
| Posted: Tue Feb 15, 2005 7:04 pm Post subject:
Re: Index access vs Ignored hints
|
|
|
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? |
|
| Back to top |
|
|
David Aldridge
*nix forums beginner
Joined: 03 Feb 2005
Posts: 46
|
| Posted: Tue Feb 15, 2005 5:09 pm Post subject:
Re: Index access vs Ignored hints
|
|
|
No, I wouldn't rely on the hint. If Oracle is configured correctly then
the first query will use the index where appropriate anyway. |
|
| Back to top |
|
|
G Quesnel
*nix forums addict
Joined: 29 Apr 2005
Posts: 77
|
| Posted: Tue Feb 15, 2005 4:35 pm Post subject:
Index access vs Ignored hints
|
|
|
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 |
|
| Back to top |
|
|
Google
|
|
| Back to top |
|
|
|
|
The time now is Thu Jan 08, 2009 3:48 am | All times are GMT
|
|
Credit Cards | Credit Cards | Debt Consolidation | Credit Cards | Credit Card Offers
|
|
Copyright © 2004-2005 DeniX Solutions SRL
|
|
|
|
Other DeniX Solutions sites:
Unix/Linux blog |
electronics forum |
medicine forum |
science forum |
|
|
Privacy Policy
|
Powered by phpBB © 2001, 2005 phpBB Group
|
|
FAQ
Search
Memberlist
Usergroups
Register
Profile
Log in to check your private messages
Log in
Forum index
»
Databases
»
Oracle
