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?


You have essentially no guarantees about the sequence of ROWIDs.

From the ROWID Pseudocolumn docs:

If you delete a row, then Oracle may reassign its rowid to a new row inserted later.

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:

Usually, a rowid value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same rowid.

| 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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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