[Oracle] rowid详解

一. rowid 

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

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

二. 举例说明

[sql]  view plain  copy

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

[plain]  view plain  copy

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

[plain]  view plain  copy

1. ROWID              FILE_NAME  
2.   
3.                                                                                                              BLOCK_NUMBER ROW_NUMBER  
4. ------------------ -------------------------------------------------------------------------------------------------------------------  
5. --------------------------------------------------------------------------------------------------------------------------------------  
6. ------------------------------------------------------------------------------------------------------------------------------------ -  
7. AAASRnAAEAAAALjAAA F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
8.   
9.                                                                                                                       739          0  
10. AAASRnAAEAAAALjAAB F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
11.   
12.                                                                                                                       739          1  
13. AAASRnAAEAAAALjAAC F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
14.   
15.                                                                                                                       739          2  
16. AAASRnAAEAAAALjAAD F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
17.   
18.                                                                                                                       739          3  
19. AAASRnAAEAAAALjAAE F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
20.   
21.                                                                                                                       739          4  
22. AAASRnAAEAAAALjAAF F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
23.   
24.                                                                                                                       739          5  
25. AAASRnAAEAAAALjAAG F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
26.   
27.                                                                                                                       739          6  
28. AAASRnAAEAAAALjAAH F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
29.   
30.                                                                                                                       739          7  
31. AAASRnAAEAAAALjAAI F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
32.   
33.                                                                                                                       739          8  
34. AAASRnAAEAAAALjAAJ F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
35.   
36.                                                                                                                       739          9  
37. AAASRnAAEAAAALjAAK F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
38.   
39.                                                                                                                       739         10  
40. AAASRnAAEAAAALjAAL F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
41.   
42.                                                                                                                       739         11  
43. AAASRnAAEAAAALjAAM F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
44.   
45.                                                                                                                       739         12  
46. AAASRnAAEAAAALjAAN F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
47.   
48.                                                                                                                       739         13  
49. AAASRnAAEAAAALjAAO F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
50.   
51.                                                                                                                       739         14  
52. AAASRnAAEAAAALjAAP F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
53.   
54.                                                                                                                       739         15  
55. AAASRnAAEAAAALjAAQ F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
56.   
57.                                                                                                                       739         16  
58. AAASRnAAEAAAALjAAR F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
59.   
60.                                                                                                                       739         17  
61. AAASRnAAEAAAALjAAS F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
62.   
63.                                                                                                                       739         18  
64. AAASRnAAEAAAALjAAT F:\APP\ADMINISTRATOR\ORADATA\ORCL\USERS01.DBF  
65.   
66.                                                                                                                       739         19  

三.结论

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