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 http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Other related posts: