Obviously in this case (second out of 3) it doesn't make any difference. But, if asked for top 10 out of 1000: - Without optimization: all 1000 would have been sorted; - With optimization: find top 10 and stop, no need to sort the rest 990. Igor Neyman, OCP DBA ineyman@xxxxxxxxxxxxxx -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Mladen Gogala Sent: Thursday, March 11, 2004 9:16 AM To: oracle-l@xxxxxxxxxxxxx Subject: Re: query for top 10 sql? Second out of 3? How do you sort them? On 03/11/2004 08:58:44 AM, Igor Neyman wrote: > T.Kyte explains this optimization very well in his (second) book. > > Sorting stops as soon as top-N (where N specified by ROWNUM) elements of > "sorted" array are filled. > > Igor Neyman, OCP DBA > ineyman@xxxxxxxxxxxxxx > > > > -----Original Message----- > From: oracle-l-bounce@xxxxxxxxxxxxx > [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Gorman > Sent: Wednesday, March 10, 2004 11:04 PM > To: oracle-l@xxxxxxxxxxxxx > Subject: Re: query for top 10 sql? > > A slight correction, below... > > on 3/10/04 7:07 PM, Mark Richard at mrichard@xxxxxxxxxxxxxxxxx wrote: > > > > > Originally I thought the same but then noticed it was "query for" & > "top > > ten sql" - which I interpreted as a query about SQL (similar to the > > Statspack type results). > > > > As far as the SQL Server "TOP" equivalent - I have not seen such a > graceful > > solution in Oracle. One approach I have seen includes using an ORDER > BY in > > a subquery and applying "where rownum <= 10" in the outer query - if > the > > inner query returns a large result set then a big sort is required. > > Oracle has an optimization on this operation that halts the sort after > the > first N rows (where "N" is the number specified in "rownum <= N") are > retrieved from the inner query. > > The following test case may help illustrate: > > SQL> select count(*) > 2 from dba_objects; > > COUNT(*) > ---------- > 4905 > SQL> > SQL> select count(distinct object_type) > 2 from (select object_id, object_type, object_name > 3 from dba_objects > 4 order by 1, 2, 3) > 5 where rownum <= 1000; > > COUNT(DISTINCTOBJECT_TYPE) > -------------------------- > 10 > SQL> > SQL> select s.value - &&V_VALUE sort_rows, s.value > 2 from v$mystat s, v$statname n > 3 where s.statistic# = n.statistic# and n.name = 'sorts (rows)'; > > SORT_ROWS > ---------- > 6185 > SQL> > SQL> select count(distinct object_type) > 2 from (select object_id, object_type, object_name > 3 from dba_objects > 4 order by 1, 2, 3) > 5 where rownum <= 100; > > COUNT(DISTINCTOBJECT_TYPE) > -------------------------- > 3 > SQL> > SQL> select s.value - &&V_VALUE sort_rows, s.value > 2 from v$mystat s, v$statname n > 3 where s.statistic# = n.statistic# and n.name = 'sorts (rows)'; > > SORT_ROWS > ---------- > 5285 > SQL> > SQL> select count(distinct object_type) > 2 from (select object_id, object_type, object_name > 3 from dba_objects > 4 order by 1, 2, 3) > 5 where rownum <= 10; > > COUNT(DISTINCTOBJECT_TYPE) > -------------------------- > 3 > SQL> > SQL> select s.value - &&V_VALUE sort_rows, s.value > 2 from v$mystat s, v$statname n > 3 where s.statistic# = n.statistic# and n.name = 'sorts (rows)'; > > SORT_ROWS > ---------- > 5195 > > Notice that when the query returned 1,000 rows, the total number of rows > sorted by the operation was 6,185. When the number of rows returned by > the > query was reduced by 900 to 100, then the total number of rows sorted > reduced correspondingly by 900 to 5,285. When the number of rows > returned > by the query was further reduced by 90 to 10, then the total number of > rows > sorted also reduced by 90 to 5,195. > > Mind you, I didn't see the number of logical I/Os change for any > operation, > so the initial scan of the inner query certainly did not change (which > is to > be expected). > > But the secondary operation of sorting appears to have been reduced. A > small optimization in this small test case, but if this was a huge sort > in > which significant I/O is performed from disk in the temporary > tablespace, > then perhaps this optimization would show more pronounced improvement... > > Well, you can't expect "top N" (or "bottom N") operations without a full > scan of the problem-set as well at least one sort operation on the > results. > So, the optimization isn't as trivial as it might seem. > > > > second approach might be to use an analytical function like RANK() or > > ROW_NUM() - but again a large sort is often required. A third > approach > > relies on using index ordering (with a hint typically for force index > > traversal) and "where rownum <= 10" applied on the original query - > this > > depends on a suitable index being available and is quite risky (for > > example, if the index is dropped the query won't fail but will return > a > > very incorrect result without warning). > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > > > ---------------------------------------------------------------- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > ---------------------------------------------------------------- > To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx > put 'unsubscribe' in the subject line. > -- > Archives are at //www.freelists.org/archives/oracle-l/ > FAQ is at //www.freelists.org/help/fom-serve/cache/1.html > ----------------------------------------------------------------- > ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 'unsubscribe' in the subject line. -- Archives are at //www.freelists.org/archives/oracle-l/ FAQ is at //www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------