Oracle ROWID, can we assume it is sequential in a insert-only scenario

oraclerowsequence

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?

Best Answer

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.

Related Question