Oracle query returning differnet results for same set of data

Question

I am facing a wierd problem where the same query is returning different results.

My query is:

SELECT * FROM TX_HISTORY  WHERE acct = 7  AND ROWNUM 

What is happening is that I know that for this account there are more than 100 records in tx_history. I want to get the first 100 records based on the processing date. My data for this account is I have records from 2004 till 2011

The problem is sometimes it correctly shows the 100 records starting 2004 - but sometimes it shows me 100 records starting 2005

I read that this can be solved by:

SELECT * FROM (select * from TX_HISTORY  WHERE acct = 7  ORDER BY acct,processing_date)
where rownum 

so in my earlier query is it that the: 1> My understanding is that the order by is being applied after the rownum <= 100 and the results returned by oracle are in a random order on which row num is filtering

Though what is not understood why the results would vary Thanks, ~akila

Answer 1

If you do not specify any ordering (and in this case, as you already found out, you do not order the data being retrieved, you only sort afterwards), it is up to the database to return them in any order it sees fit.

It could for example just start reading the rows in the order they are stored, which changes as the data gets updated. It also does not have to start from the top of the table, it could start with the blocks already in the buffer cache.

Since you did not specify the order, the DB will choose (what it thinks to be) the least expensive way available to it at this particular moment.

Answer 2

Try this:

select top(100) from ...........

its give top 100 rows which u want.

Answer 3

If you include AND RowNum <= 100 Oracle will pull 100 records at free will. If you put it in

SELECT *
  FROM TX_HISTORY
 WHERE acct = 7
   AND ROWNUM <= 100
 ORDER BY acct,processing_date

it is performed on all records there are.

However, if you have

SELECT *
  FROM (select *
          from TX_HISTORY
         WHERE acct = 7
         ORDER BY acct,processing_date)
 where rownum <= 100

it is performed on the records returned in the sub-select (the SELECT within the ( ). In other words Oracle uses a different set of records to perform AND RowNum <= 100 on.

The ordering is performed on the records returned by the query, so it happens after the WHERE-clause. So you will probably still get varying results.

I hope I could make it clearer.