Oracle ROWNUM Query – Different Rows on Each Execution

oracleoracle-12c

I have a query that is used to search for a list of sub divisions in our network. When the query is executed without any filter, it returns a different amount of rows on every execution

SELECT DISTINCT 
    t1.ZRESUBDIV,
    t1.SUBDIV_NAME
FROM M_STAGE_WP_PFUNCT_LOC t1
WHERE ROWNUM <= 200  -- normally a filter goes here
ORDER BY t1.SUBDIV_NAME

Execution 1

ZRESUBDIV    SUBDIV_NAME                                                
------------ ------------------------------------------------------------
262          ALEXANDRIA                                                  
400          ALLANWATER                                                  
671          ASHCROFT                                                    
372          BALA                                                        
277          BEACHBURG                                                   
200          BRIDGE                                                      
374          CARAMAT                                                     
543          CENTRAL BUTTE                                               
208          CHAPAIS                                                     
324          CHATHAM                                                     
340          CHICAGO                                                     
...

Execution 2

ZRESUBDIV    SUBDIV_NAME                                                
------------ ------------------------------------------------------------
670          ALBREDA                                                     
262          ALEXANDRIA                                                  
400          ALLANWATER                                                  
671          ASHCROFT                                                    
372          BALA                                                        
277          BEACHBURG                                                   
604          BLACKFOOT                                                   
200          BRIDGE                                                      
607          CAMROSE                                                     
354          CASO                                                        
543          CENTRAL BUTTE                                               
...

There is also missing data: the subdivision Baton Rouge never appears although it is in the data.

If I remove the ROWNUM, all data is correctly returned. I need to leave ROWNUM since the query support a WHERE clause with a filter on the subdivision name.

What is the cause of this?

Best Answer

ROWNUM is evaluated before ORDER BY, that is not how ROWNUM should be used. To use ROWNUM properly, use a query like this:

SELECT * FROM 
(
  SELECT DISTINCT 
      t1.ZRESUBDIV,
      t1.SUBDIV_NAME
  FROM M_STAGE_WP_PFUNCT_LOC t1
  ORDER BY t1.SUBDIV_NAME
)
WHERE ROWNUM <= 200;

But this is old. Since you are on 12c, use this:

SELECT DISTINCT 
    t1.ZRESUBDIV,
    t1.SUBDIV_NAME
FROM M_STAGE_WP_PFUNCT_LOC t1
ORDER BY t1.SUBDIV_NAME
FETCH FIRST 200 ROWS ONLY;

Related Solutions

Oracle SQL for left outer join to rownum = 1 of another query

Basically, I think you should just get the max date, if that is all you are looking for here, using your same filter, etc. You already have STATUS (=COMP) and WONUM (JOIN). If you needed the whole record from this table, and it was more complicated than this, I would recommend the oracle inline analytic functions with over/partition by logic to filter by the max date.

SELECT *
FROM   WORKORDER
       LEFT OUTER JOIN (SELECT WONUM AS STATUSWONUM 
                               , STATUS AS STATUS
                               , MAX(CHANGEDATE) AS STATUSCHANGEDATE
                        FROM   WOSTATUSHISTORY
                        WHERE  STATUS = 'COMP'
                        GROUP  BY WONUM, STATUS)LASTCOMPLETE
         ON ( WORKORDER.WONUM = LASTCOMPLETE.STATUSWONUM ) 

;

HOW does ROWNUM work with this “optimization”

The rownum pseudocolumn is assigned as rows are processed. The predicate rownum = <<x>> will evaluate to false if <<x>> is anything other than 1.

Conceptually (and I emphasize that this isn't how Oracle actually works, it's just a useful abstraction), the second query does something like

Read a row from table1
Evaluate the col2 = 'val' expression
If col2 = 'val', evaluate the extract
Assign the rownum to the row that has been returned. Since no other rows have satisfied all the criteria yet, the rownum will be 1
Evaluate the rownum = <<x>> predicate. If <<x>> is anything other than 1, the row is rejected
Go back to step 1 and read the next row

In this loop, every row will be read from table1. And every row will be rejected (assuming <<x>> is something other than 1) because of the rownum = <<x>> predicate. So you'll incur the cost of reading every row from the table, evaluating the extract for every row, and you'll never return any data.

Your first query appears to be working because it is filtering out <<x>> + 1 rows in the subquery. Of course, since there is no ORDER BY, your first query always returns an arbitrary row. It would be entirely legal for Oracle to return the same row for every value of <<x>> in your first query. It probably won't, of course, but it is entirely possible that you'd get different rows over time, that some rows would never be returned, that other rows would be returned multiple times, etc. If you want a deterministic result (prior to 12.1 which has some simpler syntax options), you'd need to do two levels of nesting

SELECT *
  FROM (SELECT b.*,
               rownum rn
          FROM (SELECT a.*
                  FROM some_table a
                 ORDER BY some_column) b
         WHERE rownum <= <<upper_limit>>) c
 WHERE rn >= <<lower_limit>>

For a pretty thorough discussion of the various issues, take a look through this askTom thread.

Best Answer

Related Solutions

Oracle SQL for left outer join to rownum = 1 of another query

HOW does ROWNUM work with this “optimization”

Related Question