Is the ROWID always assigned in an ascending order? Is it possible to determine the sequence of inserted records with the ROWID?
The ROWIDs of a table are managed by the DBMS. They are assigned in an ascending order per node and stay distinct within a table. For different tables, they could be the same.
DML statements such as UPDATE, DELETE, TRUNCATE or MERGE might internally reorder data storage, invalidating and reassigning all the ROWIDs. Contrary to that, structural table changes such as adding a column leave the ROWIDs unchanged. Alteringdistribution keysof orreorganizinga table will certainly reassign ROWID values.
Therefore the ROWID can't be used to determine the exact sequence or age of a record, it is designed to be ashort termidentifier for rows to be used for duplicate elimination.
The ROWID pseudo column can only be used ontableobjects, not on views or subselects. If you try it on a view, you will receive an appropriate error message