Menu
My understanding is that the ROWID is a unique value for each row in the result returned by a query.
Why do we need this ROWID? There is already the ROWNUM in ORACLE.
Have any one used ROWID in a SQL query?
454
You should not use ROWID as the primary key of a table. If you delete and reinsert a row with the Import and Export utilities, for example, then its rowid may change. If you delete a row, then Oracle may reassign its rowid to a new row inserted later.
A row ID is a value that uniquely identifies a row in a table. A column or a host variable can have a row ID data type. A ROWID column enables queries to be written that navigate directly to a row in the table because the column implicitly contains the location of the row. Each value in a ROWID column must be unique.
ROWID helps to retrieve data from a row. ROWNUM allows retrieving a row containing data. ROWID comprises of the position of the row, data object number, the data block in the data file, as well as data file in which row resides. ROWNUM comprises of sequence numbers of the rows.
It would not be safe if you intended to use the ROWID a long period of time after you SELECT it-- for example, if you allow users to edit data locally and then synchronize with the master database some arbitrary length of time later.
ROWID is the physical location of a row. Consequently it is the fastest way of locating a row, faster even than a primary key lookup. So it can be useful in certain types of transaction where we select some rows, store their ROWIDs and then later on use the ROWIDs in where clauses for DML against those same rows.
The Oracle SELECT ... FOR UPDATE syntax implicitly uses ROWID, when we update the locked row using WHERE CURRENT OF. Also the EXCEPTIONS table (referenced when applying constraints with the EXCEPTIONS INTO clause) has a column ROW_ID. This allows us to quickly identify the rows which are breaking our constraint.
That latter example points to another general usage: when we are writing some generic piece of code and need a mechanism for storing UIDs without concerns regarding data type, composite keys, etc.
ROWNUM on the other hand is a pseudo-column which tags a row in a given result set. It has no permanent significance.
edit
The ROWID for a given record can change over the lifetime of a system, for instance through a table rebuild. Also if one record is deleted a new record could be given that ROWID. Consequently ROWIDs are not suitable for use as UIDs in the long term. But they are good enough for use within a transaction.
156
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
