Using rownum in the where condition.

[anandkl]

Hi All,
I am experiencing a strange thing, please let me know if this is normal.

I am using oracle 8.1.7.4 and solaris 2.8.

I am using this query.

select * from (select * from cemp order by cstatus) where rownum < 10;

Will the above query give the same records every time i execute the query. Will rownum give the first 9 records. If no ..why?

what do i do to get the first 9 records.

regards
anandkl

[TimHall]

Hi.

Depending on your data, it is not guaranteed to give you the same data in the same order each time. Imagine these situations:

1) The "cemp" table contains more than 9 rows with the same "smallest" status. The order of the rows within this status will be totally dependant on what is already in cache and which blocks get read first from disk etc. As a result your ordering is not specific enough.

2) You only have nine rows of the same "smallest" status. The same rows would be returned each time, but the order of the rows is not guaranteed.

These are two very specific cases, but the problem can obviously be much bigger.

Unless you specify an order that guarantees the order of each row, you CAN NOT assume Oracle will return the data in the same order each time.

Cheers

Tim...

[pando]

I wasnt aware that the order of returning the rows depends on what data blocks are cached in the buffer.

Is this documented somewhere in the docs or some metalink note?

[TimHall]

Sorry.

Just an off the cuff statement used as an extrapolation of the "whatever the DB can get access to first" idea. It wouldn't surprise me if it has some truth in it, but I have no evidence and I will try and keep my unproved theories to myself in future.

Thanks for picking me up on that.

Cheers

Tim...

[anandkl]

Hi Tim,
Thanks for the reply.
So do you mean, the block read by oracle would change each time or is it

that which ever block corresponding to this table comes first that would be

read by oracle.

Also will the position of the blocks keep changing.

regards
anandkl

[TimHall]

Unless you specify an order that differentiates between each row, Oracle will give you the data back in "no specific order". If all your data is in a single block then it will appear that the order is fixed, until a time when the data is no loger contained in a single block.

The thing to remember here is, "If you want it in a specific order use an ORDER BY clause that *guarantees* that order, rather than relying on guesswork."

Cheers

Tim...