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> select rowid,t.* from dept t where t.deptno=10;
-
-
ROWID DEPTNO DNAME LOC
-
-
AAAO0fAAFAAAAlmAAA 10 ACCOUNTING NEW YORK /*
-
-
AAAO0f - AAF - AAAAlm - AAA
-
对象号(6个字符) 文件号(3个字符) 块号(6个字符) 行号(3个字符) */
三、查看ROWID信息及相关演示
1、查看堆表上rowid及获取rowid信息
-
SQL> select rowid,dept.* from dept ;
-
-
ROWID DEPTNO DNAME LOC
-
-
AAAO0fAAFAAAAlmAAA 10 ACCOUNTING NEW YORK
-
AAAO0fAAFAAAAlmAAB 20 RESEARCH DALLAS
-
AAAO0fAAFAAAAlmAAC 30 SALES CHICAGO
-
AAAO0fAAFAAAAlmAAD 40 OPERATIONS BOSTON
-
/**************************************************/
-
/* Author: Robinson Cheng */
-
/* Blog: http://blog.csdn.net/robinson_0612 */
-
/* MSN: robinson_0612@hotmail.com */
-
/* QQ: 645746311 */
-
/**************************************************/
-
-
SQL> select object_name,object_id from dba_objects where object_name='DEPT' and owner='SCOTT';
-
-
OBJECT_NAME OBJECT_ID
-
-
DEPT 60703
-
-
SQL> select dbms_rowid.rowid_object(rowid) object_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) num
-
5 from dept;
-
-
OBJECT_ID FILE_ID BLOCK_ID NUM
-
-
60703 5 2406 0
-
60703 5 2406 1
-
60703 5 2406 2
-
60703 5 2406 3
-
-
SQL> col file_name format a50
-
SQL> select file_id,file_name from dba_data_files where file_id=5;
-
-
FILE_ID FILE_NAME
-
-
5 /u02/database/CNMMBO/oradata/CNMMBO_system_tbl.dbf
-
-
SQL> select rowid,
-
2 substr(rowid,1,6) "object",
-
3 substr(rowid,7,3) "file",
-
4 substr(rowid,10,6) "block",
-
5 substr(rowid,16,3) "row"
-
6 from dept;
-
-
ROWID object file block row
-
-
AAAO0fAAFAAAAlmAAA AAAO0f AAF AAAAlm AAA
-
AAAO0fAAFAAAAlmAAB AAAO0f AAF AAAAlm AAB
-
AAAO0fAAFAAAAlmAAC AAAO0f AAF AAAAlm AAC
-
AAAO0fAAFAAAAlmAAD AAAO0f AAF AAAAlm AAD
2、查看簇表上的rowid 有关簇表请参考:簇表及簇表管理(Index clustered tables)
-
SQL> select table_name,tablespace_name,cluster_name,status,pct_free from
-
2 dba_tables where owner = 'ROBINSON';
-
-
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME STATUS PCT_FREE
-
-
EMP TBS_TMP EMP_DEPT_CLUSTER VALID 0
-
DEPT TBS_TMP EMP_DEPT_CLUSTER VALID 0
-
-
SQL> select rowid dept_rowid,deptno from dept;
-
-
DEPT_ROWID DEPTNO
-
-
AAAPRAAAsAABgDgAAA 10
-
AAAPRAAAsAABgDgAAB 20
-
AAAPRAAAsAABgDgAAC 30
-
AAAPRAAAsAABgDgAAD 40
-
-
SQL> select d.deptno,e.rowid emp_rowid,e.ename
-
2 from dept d join emp e
-
3 on d.rowid=e.rowid;
-
-
DEPTNO EMP_ROWID ENAME
-
-
10 AAAPRAAAsAABgDgAAA CLARK
-
20 AAAPRAAAsAABgDgAAB KING
-
30 AAAPRAAAsAABgDgAAC MILLER
-
40 AAAPRAAAsAABgDgAAD SMITH
-
-
SQL> select * from dept where rowid='AAAPRAAAsAABgDgAAA';
-
-
DEPTNO DNAME LOC
-
-
10 ACCOUNTING NEW YORK
-
-
SQL> select * from emp where rowid='AAAPRAAAsAABgDgAAA';
-
-
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
-
-
7782 CLARK MANAGER 7839 09-JUN-81 4900 10
3、使用rowid访问数据的情形
-
SQL> set autotrace on;
-
SQL> select * from dept where rowid='AAAO0fAAFAAAAlmAAC';
-
-
DEPTNO DNAME LOC
-
-
30 SALES CHICAGO
-
-
Execution Plan
-
-
Plan hash value: 3453257278
-
-
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
-
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
-
| 1 | TABLE ACCESS BY USER ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
-
-
Statistics
-
-
0 recursive calls
-
0 db block gets
-
1 consistent gets
-
0 physical reads
-
0 redo size
-
651 bytes sent via SQL*Net to client
-
492 bytes received via SQL*Net from client
-
2 SQL*Net roundtrips to/from client
-
0 sorts (memory)
-
0 sorts (disk)
-
1 rows processed
-
-
SQL> select * from dept where deptno=30;
-
-
DEPTNO DNAME LOC
-
-
30 SALES CHICAGO
-
-
Execution Plan
-
-
Plan hash value: 2852011669
-
-
-
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-
-
| 0 | SELECT STATEMENT | | 1 | 20 | 1 (0)| 00:00:01 |
-
| 1 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| 00:00:01 |
-
|* 2 | INDEX UNIQUE SCAN | PK_DEPT | 1 | | 0 (0)| 00:00:01 |
-
-
-
Predicate Information (identified by operation id):
-
-
2 - access("DEPTNO"=30)
-
Statistics
-
-
1 recursive calls
-
0 db block gets
-
2 consistent gets
-
0 physical reads
-
0 redo size
-
651 bytes sent via SQL*Net to client
-
492 bytes received via SQL*Net from client
-
2 SQL*Net roundtrips to/from client
-
0 sorts (memory)
-
0 sorts (disk)
-
1 rows processed
4、使用rowid数据类型
-
SQL> create table t(id number,rid rowid);
-
-
Table created.
-
-
SQL> insert into t(id) values(1);
-
-
1 row created.
-
-
SQL> update t set rid=t.rowid ;
-
-
1 row updated.
-
-
SQL> select rowid,t.* from t;
-
-
ROWID ID RID
-
-
AAAPQ+AAFAAAAt4AAA 1 AAAPQ+AAFAAAAt4AAA
5、rowid变化的情形
-
SQL> alter table t move;
-
-
Table altered.
-
-
SQL> select rowid,t.* from t;
-
-
ROWID ID RID
-
-
AAAPQ/AAFAAAAt8AAA 1 AAAPQ+AAFAAAAt4AAA
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/29302187/viewspace-1837147/
，如需转载，请注明出处，否则将追究法律责任。
