| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Need expert help... a challenging query
Damon Hastings wrote:
> Quick question... how can I make the query "find all records with foo >
> x, sort the results by 'bar', and give me only the top 10" as efficient
> as possible, given that there are millions of records with foo > x?
>
Oracle8i has a feature I guess was designed with just that in mind,
called "top-n query". It goes along those lines:
select foo from ( select foo, lest from sometable /* a 10k rows table */
where foo > 100
order by bar )
call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 2 0.00 0.00 0 171 0 9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.00 0.00 0 171 0 9
Rows Row Source Operation
------- ---------------------------------------------------
9 COUNT STOPKEY
9 VIEW
9 SORT ORDER BY STOPKEY
9900 TABLE ACCESS BY INDEX ROWID SOMETABLE
9901 INDEX RANGE SCAN I_FOO (NON-UNIQUE)
The trick is to allow the inline view to be ordered, which was not
possible before 8i.
Cheers -- Fred
--
+-------------------------------------+----------------------------+ | Fred Petillot | fpetillo_at_fr.oracle.com | | Oracle France - Support Technique | +33 1 4762 8201 | | 65, rue des Trois Fontanot | +33 6 8089 5135 | | 92732 Nanterre Cedex | | +-------------------------------------+----------------------------+Received on Thu Dec 16 1999 - 14:23:36 CST
 |
 |