[Oracle] rowid详解

一. rowid 

rowid实际上和rownum一样，都是oracle里的虚列，并不真正存在，但却具一个重要功能：

说明数据存在的数据文件\块\行，以便快速查找

二. 举例说明

-- 创建表test，插入20条数据
CREATE TABLE test as select ROWNUM AS rn FROM dual connect BY rownum <=20;
-- 通过dbms_rowid包查询各个rowid表示的文件\块\行
  SELECT tb.ROWID ,ddf.file_name ,dbms_rowid.rowid_block_number(tb.rowid) block_number,
  dbms_rowid.rowid_row_number(tb.rowid) row_number
    FROM test tb,dba_data_files ddf
  WHERE ddf.file_id=dbms_rowid.rowid_to_absolute_fno(tb.rowid,user,'TEST')

</pre><pre name="code" class="plain">

ROWID              FILE_NAME

                                                                                                             BLOCK_NUMBER ROW_NUMBER
------------------ -------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------------------------------------------ -
AAASRnAAEAAAALjAAA F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739          0
AAASRnAAEAAAALjAAB F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739          1
AAASRnAAEAAAALjAAC F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739          2
AAASRnAAEAAAALjAAD F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739          3
AAASRnAAEAAAALjAAE F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739          4
AAASRnAAEAAAALjAAF F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739          5
AAASRnAAEAAAALjAAG F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739          6
AAASRnAAEAAAALjAAH F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739          7
AAASRnAAEAAAALjAAI F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739          8
AAASRnAAEAAAALjAAJ F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739          9
AAASRnAAEAAAALjAAK F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739         10
AAASRnAAEAAAALjAAL F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739         11
AAASRnAAEAAAALjAAM F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739         12
AAASRnAAEAAAALjAAN F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739         13
AAASRnAAEAAAALjAAO F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739         14
AAASRnAAEAAAALjAAP F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739         15
AAASRnAAEAAAALjAAQ F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739         16
AAASRnAAEAAAALjAAR F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739         17
AAASRnAAEAAAALjAAS F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739         18
AAASRnAAEAAAALjAAT F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF

                                                                                                                      739         19

三.结论

试验得出：oracle的rowid存储了每一行数据的准确位置，除非对数据做了行迁移操作，否则这rowid不会随便变化。