Take the 2-minute tour ×

How safe is it to assume that ROWID is sequential or ascending when a table is only inserted to? and why?

I had a situation where I had to verify whether new records were added to table which has no sequence or date columns to use as reference. I found that ROWID of a recently added record was higher than all the records in question. Therefore I assumed that they were older than the reference record. However looking around the net there seems to be some mixed information concerning ROWID, some even claim that ROWID is not even guaranteed to be constant!

I know that relying on the ROWID for order is probably not a good idea, but that got me thinking : what are the guarantees provided concerning the order and variation of ROWID values, both when DELETE statements are used and when only inserting. Is there an official document by oracle detailing these points? Also, is ROWID unique at table level, or database level?

share|improve this question

2 Answers 2

You have essentially no guarantees about the sequence of ROWIDs.

From the ROWID Pseudocolumn docs:


So the delete scenario has a potential for not being sequential. The ROWID encodes a relative file number and block number. There is no guarantee that these will "increase" when a new extent is allocated to your table, so you might see a jump back if you get allocated to a different file or a block somewhere nearer the beginning of a file (e.g. if something else freed up blocks).

Also note that anything like a move (on a table or partition) will change ROWIDs (since rows are very likely to change blocks at least). Row migration between partitions on update will change ROWIDs too. (ROWIDs probably aren't stable in IOTs either because of block splits. Not entirely certain how that is handled though.)

For your last question, from the same page in the Oracle docs:


share|improve this answer

No. It would be true if all the rows were of exact the same size in single user mode. Imagine situation when row wont' fit into particular block because it does not fit in it. So this row will be put into the "next" block. Then you insert some smaller row, this one will be put into the previous block.

In most cases when doing full table scans the rows are read in the same order as they were written but there are exceptions and no database guaranties that.

PS: ROWID contains file number, block id, and row offset within the block.

share|improve this answer

Your Answer


