ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAASNnAAEAAAAMrAAA 10 ACCOUNTING NEW YORK
AAASNnAAEAAAAMrAAB 20 RESEARCH DALLAS
AAASNnAAEAAAAMrAAC 30 SALES CHICAGO
AAASNnAAEAAAAMrAAD 40 OPERATIONS BOSTON
AAASNnAAEAAAAMvAAA 10 ACCOUNTING NEW YORK
AAASNnAAEAAAAMvAAB 20 RESEARCH DALLAS
AAASNnAAEAAAAMvAAC 30 SALES CHICAGO
AAASNnAAEAAAAMvAAD 40 OPERATIONS BOSTON
AAASNnAAEAAAAMvAAE 10 ACCOUNTING NEW YORK
AAASNnAAEAAAAMvAAF 20 RESEARCH DALLAS
已选择10行。
SQL>
SQL> select length('AAAPecAAFAAAABSAAA') from dual;
LENGTH('AAAPECAAFAAAABSAAA')
----------------------------
18
SQL>
可见rowid长度有18位。ROWID是什么?
联机文档Concepts上449页有对其的一个定义:
rowid,A globally unique address for a row in a database. 在一个数据库中全局标示一行记录的唯一地址。
ROWID的格式,在概念文档254页:
以下面的rowid记录为例:
ROWID
------------------
AAAPecAAFAAAABSAAA
AAAPec,代表对象id,就是表的object_id。很明显一个表里的记录,这个值都是一样的(分区表也一样?);
AAF,代表相对文件编号,就是该记录所在数据块的数据文件的编号;
AAAABS,代表块编号,就是数据块在数据文件内的编号;
AAA,代表行编号。
数据库通过如上的rowid形式可以精确地定位到行记录,索引技术就是依靠rowid来最终访问行记录的。
Oracle提供的方法通过dbms_rowid这个包来翻译rowid:
SQL> desc dbms_rowid;
FUNCTION ROWID_BLOCK_NUMBER RETURNS NUMBER
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
TS_TYPE_IN VARCHAR2 IN DEFAULT
FUNCTION ROWID_CREATE RETURNS ROWID
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
ROWID_TYPE NUMBER IN
OBJECT_NUMBER NUMBER IN ----得到段对象编号
RELATIVE_FNO NUMBER IN ---得到文件编号
BLOCK_NUMBER NUMBER IN ---得到块编号
ROW_NUMBER NUMBER IN ---得到行记录在块的行编号
PROCEDURE ROWID_INFO
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
ROWID_TYPE NUMBER OUT
OBJECT_NUMBER NUMBER OUT
RELATIVE_FNO NUMBER OUT
BLOCK_NUMBER NUMBER OUT
ROW_NUMBER NUMBER OUT
TS_TYPE_IN VARCHAR2 IN DEFAULT
FUNCTION ROWID_OBJECT RETURNS NUMBER
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_RELATIVE_FNO RETURNS NUMBER
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
TS_TYPE_IN VARCHAR2 IN DEFAULT
FUNCTION ROWID_ROW_NUMBER RETURNS NUMBER
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_TO_ABSOLUTE_FNO RETURNS NUMBER
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
FUNCTION ROWID_TO_EXTENDED RETURNS ROWID
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OLD_ROWID ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
CONVERSION_TYPE NUMBER(38) IN
FUNCTION ROWID_TO_RESTRICTED RETURNS ROWID
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
OLD_ROWID ROWID IN
CONVERSION_TYPE NUMBER(38) IN
FUNCTION ROWID_TYPE RETURNS NUMBER
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
ROW_ID ROWID IN
FUNCTION ROWID_VERIFY RETURNS NUMBER
参数名称 类型 输入/输出默认值?
------------------------------ ----------------------- ------ --------
ROWID_IN ROWID IN
SCHEMA_NAME VARCHAR2 IN
OBJECT_NAME VARCHAR2 IN
CONVERSION_TYPE NUMBER(38) IN
SQL>
SQL> select dbms_rowid.rowid_object(rowid) object_id, ----段对象的id
2 dbms_rowid.rowid_relative_fno(rowid) file_id, ---文件编号
3 dbms_rowid.rowid_block_number(rowid) block_id, ---块号
4 dbms_rowid.rowid_row_number(rowid) row_num ---行号
5 from t1 where rownum<=10;
OBJECT_ID FILE_ID BLOCK_ID ROW_NUM
---------- ---------- ---------- ----------
74599 4 811 0
74599 4 811 1
74599 4 811 2
74599 4 811 3
74599 4 815 0
74599 4 815 1
74599 4 815 2
74599 4 815 3
74599 4 815 4
74599 4 815 5
已选择10行。
SQL>
SQL> select object_id,object_name from dba_objects where owner='SCOTT' and object_name='T1';
OBJECT_ID OBJECT_NAME
---------- --------------------------------------------------------------------------------------------------------------------------------
74599 T1
SQL>
SQL> select file_id,file_name,tablespace_name from dba_data_files where tablespace_name='USERS';
FILE_ID FILE_NAME TABLESPACE_NAME
---------- -------------------------------------------------- ------------------------------
4 D:\ORADATA\DENVER\USERS01.DBF USERS
SQL>
可以通过直接rowid来检索数据,索引rowid回表就是这个原理,这个方式访问表,无意效率是最高的(但是应用代码里不可能这样写):
SQL> set autot on
SQL> select a.* from t1 a where a.rowid='AAASNnAAEAAAAMrAAA';
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
执行计划
----------------------------------------------------------
Plan hash value: 487051824
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 42 | 1 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| T1 | 1 | 42 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
557 bytes sent via SQL*Net to client
416 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>