ROWID pseudocolumn is a base 64 string representing the unique address of a row in its table. Oracle Database rowid values contain information necessary to locate a row:：
- The data object number of the object
- The datafile in which the row resides (first file is 1). The file number is relative to the tablespace
- The data block in the datafile in which the row resides
- The position of the row in the data block (first row is 0)
ROWID base64 format:OOOOOO FFF BBBBBB RRR
- OOOOOO is the object ID（32 bits,6 base64 chars , 2^32=4G)
- FFF is the file number (10 bits, 3 base64 chars, 2^10 =1024, except 0 ,left 1023)
- BBBBBB is the block number (22 bits, 6 base64 chars , 2^22 =4M)
- RRR is the row number (16bit, 3 base64 chars , 2^16 = 65536)
Get all parts of a ROWID:
select dbms_rowid.rowid_object(‘AAASOeAAEAAFCL/CcQ’) object_id
OBJECT_ID FILE_ID BLOCK_ID NUM
74654 4 1319679 10000
AAASOe = SOe = 186464 + 14*64 + 30 = 74654
AAE = E = 4
AAFCL/ =FCL/ = 1319679
CcQ = 10000
Get object details: select owner, object_name from dba_objects where data_object_id = 74654;
- They are the fastest way to access a single row.
- They can show you how the rows in a table are stored.
- They are unique identifiers for rows in a table.
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.
Although you can use the ROWID pseudocolumn in the SELECT and WHERE clause of a query, these pseudocolumn values are not actually stored in the database. You cannot insert, update, or delete a value of the ROWID pseudocolumn.
eg: select * from TABLE_NAME where ROWID<=chartorowid(‘AAASOeAAEAAFCL/CcQ’);
A query like “select from table” will default query by ROWID if there isn’t index column. Otherwise, it will query throgh the indexs and return records order by the indexs.
Use Oracle HINT to tell oracle to query by ROWID explicitly: select /+rowid(TABLE_NAME)/ ROWID, t. from TABLE_NAME t