Term: ROWID

Definition:
In Oracle PL/SQL, the ROWID can be used as a pseudocolumn and also as a Data Type.

1. As a pseudocolumn

An Oracle server assigns each row in each table with a unique ROWID to identify the row in the table. The ROWID is the address of the row which contains the data object number, the data block of the row, the row position and data file. Using the ROWID method is generally considered to be the fastest way to search for a given row in the database.

It's important to note that ROWID values are not necessarily unique within a database. It is entirely possible for two rows of two different tables stored in the same cluster to have the same ROWID.

The ROWID is intended to be immutable (that is, unchangeable) and the user should not try to change it once it is assigned.

Note that the the ROWID may change if the row is physically moved on disk, such as:

Doing an export or import of the table
Doing ALTER TABLE XXXX MOVE
Doing ALTER TABLE XXXX SHRINK SPACE
Doing FLASHBACK TABLE XXXX
When splitting a partition
When updating a value so that it moves to a new partition
When combining two partitions

Example Usage:

The SQL query below displays the ROWID for the EMPLOYEE table records

SQL> SELECT ROWID,  ENAME, DEPTNO, SAL FROM EMPLOYEE;



ROWID              ENAME      DEPTNO        SAL

------------------ ------ ---------- ----------

AAAIVuAABAAAMhCAAA JOHN          100       2300

AAAIVuAABAAAMhCAAB MILLER        110       3900

AAAIVuAABAAAMhCAAC KATE          120       4500

2. As a Data Type

As a Data Type, the ROWID is a valid data type of a column and is available in both SQL and Pl/SQL. It stores the ROWID pseudocolumn value of a row in the database.

Example Syntax:

[COLUMN] ROWID

Example Usage:

The below SQL statements create a table T_ROW with RID as ROWID column. Note that it is updated with ROWID of its own row.

CREATE TABLE T_ROW

(ID NUMBER,

 RID ROWID);



Table created.



INSERT INTO T_ROW(ID) VALUES(1)



1 row created.



UPDATE T_ROW T

SET RID = T.ROWID



1 row updated.



SQL> SELECT * FROM T_ROW;



ID                           RID

------------------ ----------

AAAIYxAABAAAMiSAAA          1