| 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?
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 -----------------------------------------------------------------Received on Wed Mar 10 2004 - 22:01:21 CST
 |
 |