| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: query for top 10 sql?
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_at_perceptron.com
-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
Sent: Wednesday, March 10, 2004 11:04 PM
To: oracle-l_at_freelists.org
Subject: Re: query for top 10 sql?
A slight correction, below...
on 3/10/04 7:07 PM, Mark Richard at mrichard_at_transurban.com.au 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).
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://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_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Thu Mar 11 2004 - 08:09:07 CST
 |
 |