ROWID
Before the release of Oracle8, ROWID datatype was used to store the physical address of each row of each table, as a hexadecimal number. The ROWID contained the physical address of the row and allowed you to retrieve the row in a single efficient block access.
With Oracle8, the logical ROWID was introduced. Rows in Index-Organized tables do not have permanent physical addresses. The logical ROWID is accessed using the same syntax as the physical ROWID. For this reason, the physical ROWID was expanded in size to include a data object number (schema objects in the same segment).
To support both logical and physical ROWIDs (as well as ROWIDs of non-Oracle tables) the universal ROWID was defined.
You can use character host variables to store rowids in a readable format. When you SELECT or FETCH a rowid into a character host variable, Oracle converts the binary value to an 18-byte character string and returns it in the format
BBBBBBBB.RRRR.FFFF
where BBBBBBBB is the block in the database file, RRRR is the row in the block (the first row is 0), and FFFF is the database file. These numbers are hexadecimal. For example, the rowid
0000000E.000A.0007
points to the 11th row in the 15th block in the 7th database file.
ROWID各列信息格式如下:
数据对象编号 文件编号 块编号 行编号
OOOOOO FFF BBBBBB RRR
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的空间
select ROWIDTOCHAR(rowid) vrowid ,id from t_rover_alert where rowid=chartorowid('AAA5eEAAFAAABS2AAk');
select ROWIDTOCHAR(rowid) vrowid ,id from t_rover_alert where rowid='AAA5eEAAFAAABS2AAk';
select ROWIDTOCHAR(rowid) vrowid,id from t_rover_alert where ROWIDTOCHAR(rowid)=trim('AAA5eEAAFAAABS2AAk');
select ROWIDTOCHAR(rowid) vrowid ,id from t_rover_alert where rowid in (chartorowid('AAA5eEAAFAAABS2AAk'));
select ROWIDTOCHAR(rowid) vrowid ,id from t_rover_alert where rowid in ('AAA5eEAAFAAABS2AAk');
select ROWIDTOCHAR(rowid) vrowid,id from t_rover_alert where ROWIDTOCHAR(rowid) in (trim('AAA5eEAAFAAABS2AAk'));
其实object_id和data_object_id同样是表示数据库对象的一个唯一标志,但是object_id表示的是逻辑id,data_object_id表示的是物理id。如果一些object没有物理属性的话那它就不存在data_object_id,例如procedure,function,package,data type,db link,mv定义,view定义,临时表,分区表定义等等这些object都是没有对应着某个segment,因此它们的data_object_id都为空。当表刚创建的时候它的object_id和data_object_id都是相等的,但是如果表经过move或truncate等,涉及到segment发生改变后data_object_id将会有变化。即object_id是唯一且不能为空的,而data_object_id是可以为空,且可变。
详细参考
http://www.xifenfei.com/1553.html
从下面结果可以知道,根据rowid算出来的是data_object_id,不是object_id。
SQL>
SQL> select rowid,
2 ROWIDTOCHAR(rowid) vrowid,
3 chartorowid(ROWIDTOCHAR(rowid)) vvrowid,
4 get_rowid(ROWIDTOCHAR(rowid)) msg,
5 dbms_rowid.rowid_object(rowid) object_id,
6 dbms_rowid.rowid_relative_fno(rowid) file_id,
7 dbms_rowid.rowid_block_number(rowid) block_id,
8 dbms_rowid.rowid_row_number(rowid) num
9 from t_rover_alert
10 where rownum < 2;
ROWID VROWID VVROWID MSG OBJECT_ID FILE_ID BLOCK_ID NUM
------------------ ------------------ ------------------ -------------------------------------------------------------------------------- ---------- ---------- ---------- ----------
AAA5eEAAFAAABS6AAp AAA5eEAAFAAABS6AAp AAA5eEAAFAAABS6AAp Object# is :235396 235396 5 5306 41
Relative_fno is :5
Block number is :5306
Row number is :41
SQL>
SQL> select * from user_objects where object_name=upper('t_rover_alert')
2 ;
OBJECT_NAME SUBOBJECT_NAME OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE CREATED LAST_DDL_TIME TIMESTAMP STATUS TEMPORARY GENERATED SECONDARY
-------------------------------------------------------------------------------- ------------------------------ ---------- -------------- ------------------- ----------- ------------- ------------------- ------- --------- --------- ---------
T_ROVER_ALERT 53241 235396 TABLE 2012/10/31 2013/6/13 15: 2012-10-31:19:59:47 VALID N N N
SQL>
从下面两段执行计划来看,
rowid='AAA5eEAAFAAABS2AAk'
比
ROWIDTOCHAR(rowid)=trim('AAA5eEAAFAAABS2AAk')
的效率高。根据这个可以试着写批量delete和批量update。
SQL> select ROWIDTOCHAR(rowid) vrowid ,id from t_rover_alert where rowid=chartorowid('AAA5eEAAFAAABS2AAk');
VROWID ID
------------------------------------------------------ ----------
AAA5eEAAFAAABS2AAk 268042
Execution Plan
----------------------------------------------------------
select ROWIDTOCHAR(rowid) vrowid ,id from t_rover_alert where rowid='AAA5eEAAFAAABS2AAk';
Plan hash value: 3520792594
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)
| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| T_ROVER_ALERT | 1 | 17 | 1 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
594 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SQL>
VROWID ID
------------------------------------------------------ ----------
AAA5eEAAFAAABS2AAk 268042
Execution Plan
----------------------------------------------------------
Plan hash value: 3520792594
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)
| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| T_ROVER_ALERT | 1 | 17 | 1 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
594 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SQL>
SQL> select ROWIDTOCHAR(rowid) vrowid,id from t_rover_alert where ROWIDTOCHAR(rowid)=trim('AAA5eEAAFAAABS2AAk');
VROWID ID
------------------------------------------------------ ----------
AAA5eEAAFAAABS2AAk 268042
Execution Plan
----------------------------------------------------------
Plan hash value: 1380109762
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 2 | 34 | 1 (0)| 00:00
:01 |
|* 1 | INDEX FULL SCAN | T_ROVER_ALERT_PK | 2 | 34 | 1 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWIDTOCHAR(ROWID)='AAA5eEAAFAAABS2AAk')
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
594 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
使用in关键字
SQL>
SQL> select ROWIDTOCHAR(rowid) vrowid ,id from t_rover_alert where rowid in (chartorowid('AAA5eEAAFAAABS2AAk'));
VROWID ID
------------------------------------------------------ ----------
AAA5eEAAFAAABS2AAk 268042
Execution Plan
----------------------------------------------------------
select ROWIDTOCHAR(rowid) vrowid ,id from t_rover_alert where rowid in ('AAA5eEAAFAAABS2AAk');
Plan hash value: 3520792594
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)
| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| T_ROVER_ALERT | 1 | 17 | 1 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
Statistics
----------------------------------------------------------
173 recursive calls
0 db block gets
42 consistent gets
0 physical reads
0 redo size
594 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
5 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SQL>
VROWID ID
------------------------------------------------------ ----------
AAA5eEAAFAAABS2AAk 268042
Execution Plan
----------------------------------------------------------
Plan hash value: 3520792594
--------------------------------------------------------------------------------
------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)
| Time |
--------------------------------------------------------------------------------
------------
| 0 | SELECT STATEMENT | | 1 | 17 | 1 (0)
| 00:00:01 |
| 1 | TABLE ACCESS BY USER ROWID| T_ROVER_ALERT | 1 | 17 | 1 (0)
| 00:00:01 |
--------------------------------------------------------------------------------
------------
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1 consistent gets
0 physical reads
0 redo size
594 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL> SQL> SQL> select ROWIDTOCHAR(rowid) vrowid,id from t_rover_alert where ROWIDTOCHAR(rowid) in (trim('AAA5eEAAFAAABS2AAk'));
VROWID ID
------------------------------------------------------ ----------
AAA5eEAAFAAABS2AAk 268042
Execution Plan
----------------------------------------------------------
Plan hash value: 1380109762
--------------------------------------------------------------------------------
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
--------------------------------------------------------------------------------
-----
| 0 | SELECT STATEMENT | | 2 | 34 | 1 (0)| 00:00
:01 |
|* 1 | INDEX FULL SCAN | T_ROVER_ALERT_PK | 2 | 34 | 1 (0)| 00:00
:01 |
--------------------------------------------------------------------------------
-----
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWIDTOCHAR(ROWID)='AAA5eEAAFAAABS2AAk')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
594 bytes sent via SQL*Net to client
469 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL>
附上解析rowid的函数
create or replace function get_rowid
(l_rowid in varchar2) return varchar2 is
ls_my_rowid varchar2(200);
rowid_type number;
object_number number;
relative_fno number;
block_number number;
row_number number;
begin
if length(trim(l_rowid))!=18 then
return 'input error';
end if;
dbms_rowid.rowid_info(l_rowid,
rowid_type,
object_number,
relative_fno,
block_number,
row_number);
ls_my_rowid := 'Object# is :' || to_char(object_number) || chr(10) ||
'Relative_fno is :' || to_char(relative_fno) || chr(10) ||
'Block number is :' || to_char(block_number) || chr(10) ||
'Row number is :' || to_char(row_number);
return ls_my_rowid;
end;