Oracle ROWID.doc

Oracle ROWID 我们可能对oracle的rowid的使用并不陌生，不过，如果仔细分析一下，发现其还是有些知识点。 ? ?1、rowid是一个伪列，是用来确保表中行的唯一性，它并不能指示出行的物理位置，但可以用来定位行。 2、rowid是存储在索引中的一组既定的值（当行确定后）。我们可以像表中普通的列一样将它选出来。 3、利用rowid是访问表中一行的最快方式。 4、rowid需要10个字节来存储，显示为18位的字符串。 rowid的组成结构为： data object number(6位字符串)+relative file number(3位字符串)+block number(6位字符串)+row number(3位字符串)，如：AAAADeAABAAAAZSAAA 5、我们可以借助oracle提供的包dbms_rowid，来对rowid进行解析从而获取关于行的相关信息： bossdb-SQLselect 2 rowid, 3 dbms_rowid.rowid_object(rowid) obj_id, 4 dbms_rowid.rowid_relative_fno(rowid) df#, 5 dbms_rowid.rowid_block_number(rowid) blknum, 6 dbms_rowid.rowid_row_number(rowid) rowno 7 from p_test where rownum5; ROWID OBJ_ID DF# BLKNUM ROWNO ------------------ ---------- ---------- ---------- ---------- AAAQ+tAANAAAC6SAAA 69549 13 11922 0 AAAQ+tAANAAAC6SAAB 69549 13 11922 1 AAAQ+tAANAAAC6SAAC 69549 13 11922 2 AAAQ+tAANAAAC6SAAD 69549 13 11922 3 我们可以看到，通过rowid_row_number得到的行号是从0开始的，这是和rownum伪列的一个不同之处。我猜测rowid_row_number在求行号的时候是计算首行的偏移量。 一般来说，当表中的行确定后，rowid就不会发生变化。但当如下情况发生时，rowid将发生改变： 1、对一个表做表空间的移动后 2、对一个表进行了EXP/IMP后 ? ? ? ROWID For each row in the database, the ROWID pseudocolumn returns a rows address. ROWID values contain information necessary to locate a row: * which data block in the data file * which row in the data block (first row is 0) * which data file (first file is 1) In most cases, a ROWID value uniquely identifies a row in the database. However, rows in different tables that are stored together in the same cluster can have the same ROWID. Values of the ROWID pseudocolumn have the datatype ROWID. ROWID values have several important uses: * They are the fastest means of accessing a single row. * They can show you how a tables rows are stored. * They are unique identifiers for rows in a table. A ROWID does not change during the lifetime of its row. However, you should not use ROWID as a tables primary key. If you delete and reinsert a row with the Import and Export utilities, for example, its ROWID ma