Oracle ROWID

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
For each row in the database, there is a call ROWID pseudo columns (Pseudocolumn, pseudo column that is not stored in the table, but like a table column, the column cannot be insert, update, delete, ID mark) for a record's physical location, so the effect of ROWID is the biggest rapid positioning record.


The composition of ROWID
The ROWID field consists of 18 characters, such as: OOOOOOFFFBBBBBBRRR
OOOOOO database object number
The FFF tablespace data file number
BBBBBB stores the recorded data block number
RRR marked the recording of the same data blocks in different
The sample: AAAAVJAAEAAAABEAAA


SELECT ROWID, ENAME
FROM EMP
WHERE EMPNO = 7369;
Output:
ROWID ENAME
AAAR3sAAEAAAACXAAA SMITH


Whether ROWID can be used in Where conditions
ROWID WHERE can be used as conditions to the query log, but because the record is the physical position may change, so in the process of using ROWID to query data is not a reliable.
SELECT * FROM EMP WHERE ROWID = 'AAAR3sAAEAAAACXAAA'


What ROWID will change



Row Piece in a Database Block


ROWID in B-Tree Index


In the leaf nodes of B-Tree index, not only for the indexed column value, but also save the column value corresponding to the ROWID, so that rapid positioning. A query process such as:


Find the location_id above SQL statements into 1800 rows. First find 1800 corresponding physical ROWID in the mapping table, and then through the physical ROWID to find the corresponding logical ROWID, and then find the corresponding rows in the IOT table by logical ROWID.


ROWID vs ROWNUM
ROWID is the unique physical address of every row of a table maintained by database automatically. ROWNUM is the sequential number allocated to each row in the result set object during query execution.

ROWID is permanent whereas ROWNUM is temporary. ROWID is 16-bit hexadecimal whereas ROWNUM is numeric.
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by natascha at December 10, 2013 - 3:22 PM