Oracle query optimization with ROWNUM

Question

This question is now moot

I've recomputed statistics on the table, added new indices, and re-analyzed the existing indices. This has completely changed my results and invalidates most of my findings below. At this point I've found a new query which is sufficiently performant and doesn't need any ROWNUM trickery.

Also, I think it's worth pointing out that the query below, as written, is not guaranteed to give the results I wanted. Adding DISTINCT to the mid-query can potentially destroy the ordering I attempted to apply in the innermost query. In practice this was not occurring, but I can't rely on that.

---

Original question

I've written a query which seems to perform dramatically better when I put in a fake ROWNUM check:

  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 out the fake ROWNUM check, suddenly the query falls apart and becomes much slower (as well as having 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's hitting a completely different index as well (one which apparently doesn't suit it).

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

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

Explains like so:

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 my ROWNUM<=1e100 with ROWNUM>0 and this 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 path?

Answer 1

You have reduced the possible results adding the "fake" romwnum WHERE ROWNUM<=1e100

This instructs the database that ROWNUM must be at most 1e100.

---

The reason why the first query is faster is that the outher select needs to process a small subset.

Maybe you can achieve the "faster" result by 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 )

Answer 2

This is not necessarily the correct answer but....

I have used ROWNUM > 0 in the past to force 'materialisation' of the data.

This in turn allows the query processor to get the cardinality correct.

For example if the query planner thinks a particular predicate will only return one row it will often use a cartesian join on it. If the data is in fact not one row but lots, the cartesian joins results in lots of rows and lots of incorrect processing. Adding ROWNUM > 0 forces it to evaluate rownum for every row before it can evaluate ROWNUM > 0, effectively forcing a materialisation of data

It doesn't look like this is actually your problem though.

Perhaps because of the spread of the data it's quicker to materialise then search the table rather than trying to check the index first.

Is there a way in that plan dump to work out where the predicates are being applied?

It's a shame you have to UPPER the field because it makes it non sargeable and it won't use an index on that field.

Answer 3

As I recall, one of the effects of placing a round predicate in a subquery is that it prevents predicate pushing, and certain types of view merging.

Although they are both generally beneficial for query performance, there are some cases where they have undesirable side effects. This used to be a trick that could be used instead of an optimiser hint to prevent that query transformation from being considered.