ROWID 是一个类似于rownum的伪列,用于定位数据库中一条记录的一个相对唯一地址值。通常情况下,该值在该行数据插入到数据库表时即
被确定且唯一。而对于聚簇表,由于聚簇特性,不同表上的记录由于存储在相同的簇上,因此会拥有相同的ROWID。数据库的大多数操作都是通
过ROWID来完成的,而且使用ROWID来进行单记录定位速度是最快的。下面将给出ROWID的相关描述。

 

一、ROWID的特性组成及用途
  1、特性
      相对唯一性(聚簇表上不唯一)
      一旦确定,不可随意更改
      使用10个字节存储(扩展rowid),显示为18位的字符串
      特殊情况下,ROWID会发生变化(如下列情形)
         表的导入导出操作
         alter table tab_name move
         alter table tab_name shrink space
         flashback table tab_name
         拆分分区表
         分区表上更新一个值后记录被移入到新分区
         合并两个分区
  2、组成(扩展ROWID)
      数据库对象的对象编号
      数据库对象所在文件的文件编号
      数据库对象上块的编号
      块上的行编号(起始值为0)
  3、用途
      快速定位单行记录
      展示行在表上如何存储
      表上的一行的唯一标识符  
      用作数据类型 column_name rowid
  4、限制rowid,扩展rowid
      限制rowid用于早期Oracle版本(Oracle 8 以前),rowid由file#+block#+row#组成,占用6个bytes的空间
      扩展rowid,由data_object_id#+rfile#+block#+row#组成,占用10个bytes的空间
  
二、ROWID的格式      

[sql]  view plain  copy
 
 print ?
  1. SQL> select rowid,t.* from dept t where t.deptno=10;                                  
  2.                                                                                       
  3. ROWID                  DEPTNO DNAME          LOC                                      
  4. ------------------ ---------- -------------- -------------                            
  5. AAAO0fAAFAAAAlmAAA         10 ACCOUNTING     NEW YORK        /*                       
  6.                                                                                       
  7. AAAO0f            -     AAF              -    AAAAlm         -    AAA                 
  8. 对象号(6个字符)         文件号(3个字符)       块号(6个字符)       行号(3个字符)   */  

三、查看ROWID信息及相关演示
  1、查看堆表上rowid及获取rowid信息    

[sql]  view plain  copy
 
 print ?
  1. SQL> select rowid,dept.* from dept ;      -->查看表dept中所有记录的rowid                                             
  2.                                                                                                                      
  3. ROWID                  DEPTNO DNAME          LOC                                                                     
  4. ------------------ ---------- -------------- -------------                                                           
  5. AAAO0fAAFAAAAlmAAA         10 ACCOUNTING     NEW YORK                                                                
  6. AAAO0fAAFAAAAlmAAB         20 RESEARCH       DALLAS                                                                  
  7. AAAO0fAAFAAAAlmAAC         30 SALES          CHICAGO                                                                 
  8. AAAO0fAAFAAAAlmAAD         40 OPERATIONS     BOSTON                                                                  
  9. /**************************************************/    
  10. /* Author: Robinson Cheng                         */        */    
  11. /* MSN:    robinson_0612@hotmail.com              */    
  12. /* QQ:     645746311                              */    
  13. /**************************************************/    
  14.                                                                                                                      
  15. SQL> select object_name,object_id from dba_objects where object_name='DEPT' and owner='SCOTT';-->查看对象id          
  16.                                                                                                                      
  17. OBJECT_NAME           OBJECT_ID                                                                                      
  18. -------------------- ----------                                                                                      
  19. DEPT                      60703                                                                                      
  20.                                                                                                                      
  21. SQL> select dbms_rowid.rowid_object(rowid) object_id,  -->使用dbms_rowid包获得rowid的十进制信息                      
  22.   2  dbms_rowid.rowid_relative_fno(rowid) file_id,                                                                   
  23.   3  dbms_rowid.rowid_block_number(rowid) block_id,                                                                  
  24.   4  dbms_rowid.rowid_row_number(rowid) num                                                                          
  25.   5  from dept;                                                                                                      
  26.                                                                                                                      
  27.  OBJECT_ID    FILE_ID   BLOCK_ID        NUM           -->此处可以看到对应的对象号,文件号,块号以及行号              
  28. ---------- ---------- ---------- ----------                                                                          
  29.      60703          5       2406          0                                                                          
  30.      60703          5       2406          1                                                                          
  31.      60703          5       2406          2                                                                          
  32.      60703          5       2406          3                                                                          
  33.                                                                                                                      
  34. SQL> col file_name format a50                                                                                        
  35. SQL> select file_id,file_name from dba_data_files where file_id=5;  -->通过文件id获得对象所在数据文件的位置          
  36.                                                                                                                      
  37.    FILE_ID FILE_NAME                                                                                                 
  38. ---------- --------------------------------------------------                                                        
  39.          5 /u02/database/CNMMBO/oradata/CNMMBO_system_tbl.dbf                                                        
  40.                                                                                                                      
  41. SQL> select rowid,              -->这个查询按照rowid的定义格式进行分离rowid                                          
  42.   2  substr(rowid,1,6) "object",                                                                                     
  43.   3  substr(rowid,7,3) "file",                                                                                       
  44.   4  substr(rowid,10,6) "block",                                                                                     
  45.   5  substr(rowid,16,3) "row"                                                                                        
  46.   6  from dept;                                                                                                      
  47.                                                                                                                      
  48. ROWID              object             file      block              row                                               
  49. ------------------ ------------------ --------- ------------------ ---------                                         
  50. AAAO0fAAFAAAAlmAAA AAAO0f             AAF       AAAAlm             AAA                                               
  51. AAAO0fAAFAAAAlmAAB AAAO0f             AAF       AAAAlm             AAB                                               
  52. AAAO0fAAFAAAAlmAAC AAAO0f             AAF       AAAAlm             AAC                                               
  53. AAAO0fAAFAAAAlmAAD AAAO0f             AAF       AAAAlm             AAD                                               

  2、查看簇表上的rowid 有关簇表请参考:簇表及簇表管理(Index clustered tables)    

[sql]  view plain  copy
 
 print ?
  1. SQL> select table_name,tablespace_name,cluster_name,status,pct_free from                          
  2.   2  dba_tables where owner = 'ROBINSON';     -->列cluster_name上包含簇名,这两个表为簇表         
  3.                                                                                                   
  4. TABLE_NAME       TABLESPACE_NAME   CLUSTER_NAME                   STATUS     PCT_FREE             
  5. ---------------- ----------------- ------------------------------ -------- ----------             
  6. EMP              TBS_TMP           EMP_DEPT_CLUSTER               VALID             0             
  7. DEPT             TBS_TMP           EMP_DEPT_CLUSTER               VALID             0             
  8.                                                                                                   
  9. SQL> select rowid dept_rowid,deptno from dept;  -->查看dept上的rowid                              
  10.                                                                                                   
  11. DEPT_ROWID             DEPTNO                                                                     
  12. ------------------ ----------                                                                     
  13. AAAPRAAAsAABgDgAAA         10                                                                     
  14. AAAPRAAAsAABgDgAAB         20                                                                     
  15. AAAPRAAAsAABgDgAAC         30                                                                     
  16. AAAPRAAAsAABgDgAAD         40                                                                     
  17.                                                                                                   
  18. SQL> select d.deptno,e.rowid emp_rowid,e.ename   -->查看emp上的rowid,存在与dept表相同的rowid     
  19.   2  from dept d join emp e                                                                       
  20.   3  on d.rowid=e.rowid;                                                                          
  21.                                                                                                   
  22.     DEPTNO EMP_ROWID          ENAME                                                               
  23. ---------- ------------------ ----------                                                          
  24.         10 AAAPRAAAsAABgDgAAA CLARK                                                               
  25.         20 AAAPRAAAsAABgDgAAB KING                                                                
  26.         30 AAAPRAAAsAABgDgAAC MILLER                                                              
  27.         40 AAAPRAAAsAABgDgAAD SMITH                                                               
  28.                                                                                                   
  29. SQL> select * from dept where rowid='AAAPRAAAsAABgDgAAA';   -->使用相同的rowid访问不同的表        
  30.                                                                                                   
  31.     DEPTNO DNAME          LOC                                                                     
  32. ---------- -------------- -------------                                                           
  33.         10 ACCOUNTING     NEW YORK                                                                
  34.                                                                                                   
  35. SQL> select * from emp where rowid='AAAPRAAAsAABgDgAAA';    -->使用相同的rowid访问不同的表        
  36.                                                                                                   
  37.      EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO             
  38. ---------- ---------- --------- ---------- --------- ---------- ---------- ----------             
  39.       7782 CLARK      MANAGER         7839 09-JUN-81       4900                    10                 

  3、使用rowid访问数据的情形   

[sql]  view plain  copy
 
 print ?
  1. SQL> set autotrace on;                                                                                            
  2. SQL> select * from dept where rowid='AAAO0fAAFAAAAlmAAC';  -->使用rowid访问数据行                                 
  3.                                                                                                                   
  4.     DEPTNO DNAME          LOC                                                                                     
  5. ---------- -------------- -------------                                                                           
  6.         30 SALES          CHICAGO                                                                                 
  7.                                                                                                                   
  8. Execution Plan                                                                                                    
  9. --------------------------------------------           -->执行计划中为TABLE ACCESS BY USER ROWID访问方式          
  10. Plan hash value: 3453257278                                                                                       
  11.                                                                                                                   
  12. -----------------------------------------------------------------------------------                               
  13. | Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |                               
  14. -----------------------------------------------------------------------------------                               
  15. |   0 | SELECT STATEMENT           |      |     1 |    20 |     1   (0)| 00:00:01 |                               
  16. |   1 |  TABLE ACCESS BY USER ROWID| DEPT |     1 |    20 |     1   (0)| 00:00:01 |                               
  17. -----------------------------------------------------------------------------------                               
  18. Statistics                                                                                                        
  19. ----------------------------------------------------------                                                        
  20.           0  recursive calls                                                                                      
  21.           0  db block gets                                                                                        
  22.           1  consistent gets                           -->此处的consistent gets值为1                              
  23.           0  physical reads                                                                                       
  24.           0  redo size                                                                                            
  25.         651  bytes sent via SQL*Net to client                                                                     
  26.         492  bytes received via SQL*Net from client                                                               
  27.           2  SQL*Net roundtrips to/from client                                                                    
  28.           0  sorts (memory)                                                                                       
  29.           0  sorts (disk)                                                                                         
  30.           1  rows processed                                                                                       
  31.                                                                                                                   
  32. SQL> select * from dept where deptno=30;    -->使用字面量访问行记录                                               
  33.                                                                                                                   
  34.     DEPTNO DNAME          LOC                                                                                     
  35. ---------- -------------- -------------                                                                           
  36.         30 SALES          CHICAGO                                                                                 
  37.                                                                                                                   
  38. Execution Plan                                                                                                    
  39. -------------------------------------      -->执行计划先INDEX UNIQUE SCAN,然后根据索引叶结点上的rowid访问数据    
  40. Plan hash value: 2852011669                                                                                       
  41.                                                                                                                   
  42. ---------------------------------------------------------------------------------------                           
  43. | Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |                           
  44. ---------------------------------------------------------------------------------------                           
  45. |   0 | SELECT STATEMENT            |         |     1 |    20 |     1   (0)| 00:00:01 |                           
  46. |   1 |  TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    20 |     1   (0)| 00:00:01 |                           
  47. |*  2 |   INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |                           
  48. ---------------------------------------------------------------------------------------                           
  49.                                                                                                                   
  50. Predicate Information (identified by operation id):    -->包含了谓词信息                                          
  51. ---------------------------------------------------                                                               
  52.    2 - access("DEPTNO"=30)                                                                                        
  53. Statistics                                                                                                        
  54. ----------------------------------------------------------                                                        
  55.           1  recursive calls                                                                                      
  56.           0  db block gets                                                                                        
  57.           2  consistent gets                          -->consistent gets比直接使用rowid多一次(即执行了索引扫描)   
  58.           0  physical reads                                                                                       
  59.           0  redo size                                                                                            
  60.         651  bytes sent via SQL*Net to client                                                                     
  61.         492  bytes received via SQL*Net from client                                                               
  62.           2  SQL*Net roundtrips to/from client                                                                    
  63.           0  sorts (memory)                                                                                       
  64.           0  sorts (disk)                                                                                         
  65.           1  rows processed                                                                                       

  4、使用rowid数据类型       

[sql]  view plain  copy
 
 print ?
  1. SQL> create table t(id number,rid rowid);  -->创建包含rowid类型的表          
  2.                                                                              
  3. Table created.                                                               
  4.                                                                              
  5. SQL> insert into t(id) values(1);          -->新增一条记录                   
  6.                                                                              
  7. 1 row created.                                                               
  8.                                                                              
  9. SQL> update t set rid=t.rowid ;            -->更新rowid类型的列              
  10.                                                                              
  11. 1 row updated.                                                               
  12.                                                                              
  13. SQL> select rowid,t.* from t;             -->rid列于rowid列值相同            
  14.                                                                              
  15. ROWID                      ID RID                                            
  16. ------------------ ---------- ------------------                             
  17. AAAPQ+AAFAAAAt4AAA          1 AAAPQ+AAFAAAAt4AAA                             

  5、rowid变化的情形    

[sql]  view plain  copy
 
 print ?
  1. SQL> alter table t move;                                                                            
  2.                                                                                                     
  3. Table altered.                                                                                      
  4.                                                                                                     
  5. SQL> select rowid,t.* from t;          -->使用alter table tab_name move命令后,rowid发生变化        
  6.                                        -->其他导致rowid变化的情形演示略                             
  7. ROWID                      ID RID                                                                   
  8. ------------------ ---------- ------------------                                                    
  9. AAAPQ/AAFAAAAt8AAA          1 AAAPQ+AAFAAAAt4AAA