Geek Answers Handbook

Optimizing Oracle Queries with ROWNUM

This question is now controversial.

I recalculated the statistics on the table, added new indexes and reanalyzed the existing indexes. This completely changed my results and invalidates most of my results below. At this point, I found a new query that is efficient enough and doesn't need a trick ROWNUM.

In addition, I believe it is worth noting that the query below, as written, does not guarantee the results that I wanted. Adding DISTINCTto the middle query can potentially destroy the ordering that I tried to apply in the inner query itself. In practice, this did not happen, but I cannot rely on it.

Original question

I wrote a query that seems to have improved significantly when I introduced a fake check ROWNUM:

  SELECT * FROM
  (
    SELECT DISTINCT * FROM 
    ( 
      SELECT TransactionID FROM WOWDev.QueryLog WHERE UPPER(UserName)=UPPER('xyz') AND TransactionID IS NOT NULL ORDER BY TransactionID DESC
    ) 
    WHERE ROWNUM<=1e100 -- fake ROWNUM check! this gets us on the fast path
  )
  WHERE ROWNUM<=50

Here's the optimizer plan.

SELECT STATEMENT, GOAL = ALL_ROWS           38025   50  650
 COUNT STOPKEY                  
  VIEW  JSTILES     38025   801 10413
   SORT UNIQUE NOSORT           38025   801 3204
    COUNT STOPKEY                   
     VIEW   JSTILES     38024   801 3204
      TABLE ACCESS BY INDEX ROWID   WOWDEV  QUERYLOG    38024   545694  9276798
       INDEX FULL SCAN DESCENDING   WOWDEV  IX_QUERYLOG_TID 1263    212704

If I comment on a fake check ROWNUM, suddenly the request will fall apart and become much slower (and also has a 5x cost).

SELECT STATEMENT, GOAL = ALL_ROWS           204497  50  650
 COUNT STOPKEY                  
  VIEW  JSTILES     204497  34865   453245
   SORT GROUP BY STOPKEY            204497  34865   592705
    INDEX FAST FULL SCAN    WOWDEV  IX_QUERYLOG_USER_TID    204462  545694  9276798

Obviously, it falls into a completely different index (which, apparently, does not suit him).

If I simplify the query naturally and remove all redundancy, we get a similar execution plan for the poor version, as well as poor performance.

SELECT * FROM 
(
  SELECT DISTINCT TransactionID
          FROM WOWDev.QueryLog
         WHERE UPPER(UserName) = UPPER('xyz')
           AND TransactionID IS NOT NULL
         ORDER BY TransactionID DESC
)
WHERE ROWNUM <= 50

It is explained as follows:

SELECT STATEMENT, GOAL = ALL_ROWS           207527  50  650
 COUNT STOPKEY                  
  VIEW  JSTILES     207527  34865   453245
   SORT UNIQUE STOPKEY          207491  34865   592705
    INDEX FAST FULL SCAN    WOWDEV  IX_QUERYLOG_USER_TID    204462  545694  9276798

As suggested below, I tried replacing mine ROWNUM<=1e100with ROWNUM>0, and it also hit the fast path, but with a slightly different plan:

SELECT STATEMENT, GOAL = ALL_ROWS           38025   50  650
 COUNT STOPKEY                  
  VIEW  JSTILES     38025   801 10413
   SORT UNIQUE NOSORT           38025   801 3204
    COUNT                   
     FILTER                 
      VIEW  JSTILES     38024   801 3204
       TABLE ACCESS BY INDEX ROWID  WOWDEV  QUERYLOG    38024   545694  9276798
        INDEX FULL SCAN DESCENDING  WOWDEV  IX_QUERYLOG_TID 1263    212704

Can anyone explain this behavior? Is there a cleaner, less hacky way to get Oracle on the fast track?

+4
3

, ....

ROWNUM > 0 , "" .

, , .

, , , . , , . ROWNUM > 0 rownum , ROWNUM > 0,

, .

, - , , , .

, ?

, UPPER , .

+1

, , .

, , . , , .

0

, "" romwnum WHERE ROWNUM<=1e100

, ROWNUM 1e100.

The reason the first request is faster is because the choice of outper should handle a small subset.

Perhaps you can achieve a “quick” result on SELECT * FROM ( SELECT DISTINCT * FROM ( SELECT TransactionID FROM WOWDev.QueryLog WHERE UPPER(UserName)=UPPER('xyz') AND TransactionID IS NOT NULL ORDER BY TransactionID DESC ) WHERE ROWNUM<=50 -- move inside to get small result set )

-1
source

More articles:


All Articles