Oracle的rowid是数据的物理地址,一个rowid中包含数据文件中的数据对象编号,数据记录的文件号,数据文件的数据块号,数据文件数据记录的行号。
1.rowid的显示形式:
SQL> create table t(id number,name varchar2(30));
Table created.
SQL> insert into t values(1,'a');
1 row created.
SQL> commit;
Commit complete.
SQL> select rowid from t;
ROWID
------------------
AAASSFAABAAAVURAAA
------------------
AAASSFAABAAAVURAAA
该字符串的解释为:
AAASSF:数据对象编号
AAB:数据文件编号
AAAVUR:数据文件块编号
AAA:数据文件记录的行编号
Oracle中通过特定的包计算出该记录对应的上述记录:
SQL> select dbms_rowid.rowid_object(rowid) obj#,
2 dbms_rowid.rowid_relative_fno(rowid) rfile#,
3 dbms_rowid.rowid_block_number(rowid) block#,
4 dbms_rowid.rowid_row_number(rowid) row#,
5 dbms_rowid.rowid_to_absolute_fno(rowid,'SYS','T') file#
6 from t;
2 dbms_rowid.rowid_relative_fno(rowid) rfile#,
3 dbms_rowid.rowid_block_number(rowid) block#,
4 dbms_rowid.rowid_row_number(rowid) row#,
5 dbms_rowid.rowid_to_absolute_fno(rowid,'SYS','T') file#
6 from t;
OBJ# RFILE# BLOCK# ROW# FILE#
---------- ---------- ---------- ---------- ----------
74885 1 87313 0 1
---------- ---------- ---------- ---------- ----------
74885 1 87313 0 1
2.通过rowid计算出obj#,rfile#,block#,row#,file#:
rowid是base64编码的,用A~Z表示0-25,a~z表示26-51,0~9表示52-61,+表示62,/ 表示63.
obj#=AAASSF=74885
rfile#=AAB=1
block#=AAAVUR=87313
row#=AAA=0
3.通过obj#,rfile#,block#,row#计算rowid:
实际是将10进制数据转换成64进制数,可以先将其转换成2进制。将2进制数从右向左6bit为一组,然后将这6个bit组转换成10进制数。
obj#=74885=010010 010010 000101=18 18 5=S S F
将左边补齐成6bit的base64的编码。即为AAASSF。
rfile#=1=000000 000000 ...000001=B
将左边补齐成6bit的base64的编码。即为AAB。
block#=87313=010101 010101 010001=21 21 17= V V R
将左边补齐成6bit的base64的编码。即为AAAVVR。
row#=0=000000 000000 000000=A
将左边补齐成6bit的base64的编码。即为AAA。
合起来就是AAASSFAABAAAVURAAA.
4.rowid的内部存储格式
我们从rowid伪列中select出来的rowid是base64字符显示的,但oracle内部存储还是以2进制表示的。rowid采用10byte总80bit来存储,obj#使用32bit,rfile#使用10bit,block#使用22bit,row#使用16bit.即一个数据库内的object不能超过2^64=4G个,一个表空间的数据文件不能超过2^10-1=1023个(文件号不能为0),一个数据文件中的block不能超过2^22=4M个,一个block中的数据行数不能超过2^16=64k行。
将t表的rowid转换成16进制:
SQL> select dump(rowid,16) from t;
DUMP(ROWID,16)
--------------------------------------------------------------------------------
Typ=69 Len=10: 0,1,24,85,0,41,55,11,0,0
--------------------------------------------------------------------------------
Typ=69 Len=10: 0,1,24,85,0,41,55,11,0,0
00000000 00000001 00100100 10000101 00000000 01000001 01010101 00010001 00000000 00000000
最右边16bit为row#=00000000 00000000=0
接着的22bit为block#=000001 01010101 00010001=87313
接着的10bit为rfile#=00000000 01=1
接着的32bit为obj#=00000000 00000001 00100100 10000101=74855
5.索引中的rowid
(1)B-tree索引
SQL> create index idx_t on t(id) tablespace users;
Index created.
SQL> select object_id,data_object_id,object_name from dba_objects where object_name='IDX_T';
OBJECT_ID DATA_OBJECT_ID OBJECT_NAME
---------- -------------- ------------------------------
74905 74905 IDX_T
---------- -------------- ------------------------------
74905 74905 IDX_T
SQL> select file_id,block_id from dba_extents where segment_name='IDX_T' and wner='SYS';
FILE_ID BLOCK_ID
---------- ----------
4 544
---------- ----------
4 544
由于是assm表空间,去掉了3个block的头。通过如下计算:
转储该索引:
SQL> alter system set events 'immediate trace name treedump level 74905';
System altered.
查看trace文件信息:
----- begin tree dump
leaf: 0x1000223 16777763 (0: nrow: 1 rrow: 1)
----- end tree dump
leaf: 0x1000223 16777763 (0: nrow: 1 rrow: 1)
----- end tree dump
leaf节点后的字符串0x1000223为16进制数,16777763为10进制数。
将其进行转换:
SQL> select to_number('1000223','xxxxxxxx') from dual;
TO_NUMBER('1000223','XXXXXXXX')
-------------------------------
16777763
-------------------------------
16777763
查找文件编号:
SQL> select dbms_utility.data_block_address_file(16777763) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(16777763)
----------------------------------------------
4
----------------------------------------------
4
查找block_id:
SQL> select dbms_utility.data_block_address_block(16777763) from dual;
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(16777763)
-----------------------------------------------
547
-----------------------------------------------
547
查看块存放的数据:
SQL> alter system dump datafile 4 block 547;
查看trace文件信息:
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 55 11 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 547 maxblk 547
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 55 11 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 547 maxblk 547
将16进制数00 41 55 11 00 00转换成2进制为:
00000000 01000001 01010101 00010001 00000000 00000000
最右边16bit为row#=0
接着的22bit为block=000001 01010101 00010001=87313
接着的10bit为rfile#=00000000 01=1
B-tree索引中保存的rowid是不包含obj#的,但是分区表的global index是包含obj#的,因为分区表包含多个segment,每个segment可能在不通的datafile中,根据表的obj#就无法确定该索引键对应的rowid(rfile#确定不了)。
(2)唯一索引
SQL> drop index idx_t;
Index dropped.
SQL> create unique index idx_t on t(id) tablespace users;
Index created.
SQL> select file_id,block_id from dba_extents where segment_name='IDX_T' and wner='SYS';
FILE_ID BLOCK_ID
---------- ----------
4 544
---------- ----------
4 544
查看块存放的数据:
SQL> alter system dump datafile 4 block 547;
System altered.
查看trace文件:
row#0[8020] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 55 11 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 547 maxblk 547
col 0; len 2; (2): c1 02
col 1; len 6; (6): 00 41 55 11 00 00
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 547 maxblk 547
具体计算和前面一样,就不重复了。
6.简单查看t表的执行计划:
SQL> set autotrace on;
SQL> begin
2 for i in 1..1000
3 loop
4 insert into t values(i,'a');
5 end loop;
6 commit;
7 end;
8 /
2 for i in 1..1000
3 loop
4 insert into t values(i,'a');
5 end loop;
6 commit;
7 end;
8 /
PL/SQL procedure successfully completed.
SQL> select * from t where id=10;
ID NAME
---------- ------------------------------
10 a
---------- ------------------------------
10 a
Execution Plan
----------------------------------------------------------
Plan hash value: 1769725460
--------------------------------------------------------------------------------
-----
-----
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
|
|
--------------------------------------------------------------------------------
-----
-----
| 0 | SELECT STATEMENT | | 1 | 5 | 1 (0)| 00:00
:01 |
:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 5 | 1 (0)| 00:00
:01 |
:01 |
|* 2 | INDEX UNIQUE SCAN | IDX_T | 1 | | 0 (0)| 00:00
:01 |
:01 |
--------------------------------------------------------------------------------
-----
-----
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("ID"=10)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
384 bytes sent via SQL*Net to client
408 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
从Id=2处开始执行该查询语句。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/20523441/viewspace-759395/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/20523441/viewspace-759395/