Strange behaviour of a query with order by and rownum on Oracle’s Exadata server

oracle-11g-r2

I am executing the following query on two different database servers: 

SELECT start_date,
  end_date,
  resort_id
FROM employee_activity
WHERE employee_id = 27
  AND start_date < to_date('2012-11-09', 'YYYY-MM-DD')
  AND is_overridden = 1
  AND ROWNUM = 1 
ORDER BY start_date DESC;

This query returns different records on Exadata than on the other server.
On Exadata, it first selects the first row and then does the ordering.
On the other server, it first does the ordering and then returns the first row.

We are using Oracle 11g on both servers.

Now the issue we are facing is that we have upgraded our database servers to Exadata and we have used such queries in a lot of places in our codebase. So can we overcome this issue?

Best Answer

This answer is wrong, because the value for rownum is assigned before the order by is executed.

Below is the correct one,

SELECT start_date,  
       end_date,  
       resort_id,  
       ROWNUM  
FROM   (SELECT   start_date,  
                end_date,  
                resort_id  
       FROM     employee_activity  
       WHERE    employee_id = 27  
       AND      start_date < TO_DATE ('2012-11-09', 'YYYY-MM-DD')  
       AND      is_overridden = 1  
       ORDER BY start_date DESC
      )  
WHERE  ROWNUM = 1
Related Question