Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 3 down vote accepted

The value for rownum is assigned before the order by, so you need to wrap this into a derived table:

select start_date,
       end_date,
       resort_id
from (
    SELECT start_date,
           end_date,
           resort_id,
           rownum as rn
    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 rn = 1

For more details on the rownum behaviour, please see the manual: http://docs.oracle.com/cd/E11882_01/server.112/e26088/pseudocolumns009.htm#i1006297

share|improve this answer
    
But how it worked since long on previous servers with same database ? –  Ram Dutt Shukla Feb 22 '13 at 7:38
2  
@RamDuttShukla: that was pure conincidence. –  a_horse_with_no_name Feb 22 '13 at 7:44
1  
Most likely the order of the rows in the tablespace blocks (rowid) determined it –  Phil Feb 22 '13 at 11:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.