--rowid和index的原理
--Table
--Index 使表查询变快,但修改受到影响变慢
Create Table t1 As Select * From dba_objects;
--Rowid 伪列 与nextval,currval一样都是伪列
Insert Into t1 Select * From t1 --增加表T1的数据
Select Count(*) From t1
Select Rowid,t1.* From t1 Order By object_id,Rowid
A-Z 0-25
a-z 26-51
0-9 52-61
+ 62
/ 63
Select object_id,data_object_id From dba_objects Where object_name = 'T1'; --object_id 10345
Rowid 如 AAAChpAABAAAHJSAAt
AAAChp对象编号 AAB数据文件编号 AAAHJS数据块编号 AAt行编号
--AAAChp对应object_id 即属于哪个对象
Select 2*power(64,2)+33*64+41 From dual; --10345 等于从dba_objects表中查询到的object_id
--AAB 对应数据文件相对编号 AAB换算成10进制为1
Select * From dba_data_files Where relative_fno = 1; --file_id绝对编号,文件在整个数据库中的唯一编号 relative_fno相对编号,数据文件在表空间内部的编号
--AAAHJS对应数据块编号
Select 7*power(64,2)+9*64+18 From dual; --29266
--AAt行编号 AAA换算成10进制是45,即放在第46行
即AAAChpAABAAAHJSAAt 属于对象10345,存放在数据文件1的第29266个块的第46行上
Select object_id,Rowid From t1 Order By object_id,Rowid
Create Index idx_t1 On t1(object_id) Tablespace users --创建t1上object_id的索引,存放在表空间users上
--索引的类型 b-tree,bitmap
b-tree:balance --平衡树(相对于binary--二叉树)
Select object_id From dba_objects Where object_name = 'IDX_T1'; --object_id = 10346
Alter Session Set Events 'immediate trace name treedump level 10346' --(内部命令)将对象id为10347的tree输出到为当前窗口服务的进程的日志中,日志位置C:\oracle\product\10.2.0\admin\testDB\udump下面最新的文件
/*
Dump file c:\oracle\product\10.2.0\admin\testdb\udump\testrid_ora_1392.trc
Wed Dec 04 18:47:34 2013
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:888M/1331M, Ph+PgF:1346M/1762M, VA:1628M/2047M
Instance name: testrid
Redo thread mounted by this instance: 1
Oracle process number: 15
Windows thread id: 1392, image: ORACLE.EXE (SHAD)
*** 2013-12-04 18:47:34.020
*** ACTION NAME:(SQL Window - 11.rowid和index的原) 2013-12-04 18:47:34.005
*** MODULE NAME:(PL/SQL Developer) 2013-12-04 18:47:34.005
*** SERVICE NAME:(testDB) 2013-12-04 18:47:34.005
*** SESSION ID:(146.162) 2013-12-04 18:47:34.005
----- begin tree dump
branch: 0x100000c 16777228 (0: nrow: 166, level: 1)
leaf: 0x100000d 16777229 (-1: nrow: 513 rrow: 513)
leaf: 0x100000e 16777230 (0: nrow: 497 rrow: 497)
leaf: 0x100000f 16777231 (1: nrow: 478 rrow: 478)
leaf: 0x1000010 16777232 (2: nrow: 479 rrow: 479)
leaf: 0x1000011 16777233 (3: nrow: 479 rrow: 479)
leaf: 0x1000012 16777234 (4: nrow: 478 rrow: 478)
leaf: 0x1000013 16777235 (5: nrow: 479 rrow: 479)
leaf: 0x1000014 16777236 (6: nrow: 479 rrow: 479)
leaf: 0x1000015 16777237 (7: nrow: 478 rrow: 478)
leaf: 0x1000016 16777238 (8: nrow: 479 rrow: 479)
leaf: 0x1000017 16777239 (9: nrow: 479 rrow: 479)
leaf: 0x1000018 16777240 (10: nrow: 478 rrow: 478)
---------------省略部分---------------
leaf: 0x10000b7 16777399 (160: nrow: 449 rrow: 449)
leaf: 0x10000b8 16777400 (161: nrow: 449 rrow: 449)
leaf: 0x10000b9 16777401 (162: nrow: 449 rrow: 449)
leaf: 0x10000ba 16777402 (163: nrow: 449 rrow: 449)
leaf: 0x10000bb 16777403 (164: nrow: 210 rrow: 210)
----- end tree dump
*/
分析日志:
leaf: 0x100000d 16777229 (-1: nrow: 485 rrow: 485)
0x100000d 十六进制转换成10进制
Select to_number('100000d','xxxxxxxx') From dual; --16777229
Select dbms_utility.data_block_address_file(16777229) From dual; --查找该id代表的具体数据文件 文件4(创建时候故意存放在users表空间中) select * from dba_data_files
Select dbms_utility.data_block_address_block(16777229) From dual; --查找该id代表的具体块 第13个块
Alter System Dump Datafile 4 Block 13;--将数据文件4的第13个块内容输出到附件,见附件11.rowid和index的原理_附件testrid_ora_1392.trc
/*
.............
.....接上面日志.....
.............
leaf: 0x10000ba 16777402 (163: nrow: 449 rrow: 449)
leaf: 0x10000bb 16777403 (164: nrow: 210 rrow: 210)
----- end tree dump
*** 2013-12-04 18:55:10.927
Start dump data blocks tsn: 4 file#: 4 minblk 13 maxblk 13
buffer tsn: 4 rdba: 0x0100000d (4/13)
scn: 0x0000.0004e7ee seq: 0x01 flg: 0x04 tail: 0xe7ee0601
frmt: 0x02 chkval: 0x3c73 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07A08400 to 0x07A0A400
7A08400 0000A206 0100000D 0004E7EE 04010000 [................]
7A08410 00003C73 00000002 0000286A 0004E7EB [s<......j> 7A08420 00000000 00320002 01000009 00000000 [......2.........]
7A08430 00000000 00000000 00000000 00000000 [................]
.............
.....省略部分.....
.............
7A0A3C0 40000603 2D005272 00000000 00000000 [...@rR.-........]
7A0A3D0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7A0A3F0 00000000 00000000 00000000 E7EE0601 [................]
Block header dump: 0x0100000d
Object id on Block? Y
seg/obj: 0x286a csc: 0x00.4e7eb itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0004e7eb
Leaf block dump
===============
header address 127960164=0x7a08464
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0x426
kdxcofeo 1880=0x758
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 16777230=0x100000e
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 72 52 00 2d
row#1[8012] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 75 4e 00 2d
row#2[8000] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 76 01 00 27
row#3[7988] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 76 7c 00 1a
row#4[7976] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 76 f7 00 10
row#5[7964] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 77 72 00 06
row#6[7952] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 77 ec 00 45
row#7[7940] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 78 67 00 3e
row#8[7928] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 40 72 52 00 05
row#9[7916] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 40 75 4e 00 05
row#10[7904] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 40 76 00 00 3b
.............
.....省略部分.....
.............
row#511[1892] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 42
col 1; len 6; (6): 00 40 78 67 00 50
row#512[1880] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 43
col 1; len 6; (6): 00 40 72 52 00 40
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 13 maxblk 13
*/
分析日志:
Start dump data blocks tsn: 4 file#: 4 minblk 13 maxblk 13
第4个数据文件的第13个块
Object id on Block? Y
seg/obj: 0x286a csc: 0x00.46a40 itc: 2 flg: E typ: 2 - Index
Select to_number('286a','xxxx') From dual --10346 即IDX_T1的对象id
row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 72 52 00 2d
--数据文件4第13块保存了513条数据
Select object_id,Rowid From t1 Order By object_id,Rowid; --第一条数据object_id 2,rowid AAAChpAABAAAHJSAAt;
--col 0; len 2; (2): c1 03
--object_id 2
Select Dump(2,16) From dual --查询2和3的16进制在存储时的值 Typ=2 Len=2: c1,3 ;
Select utl_raw.cast_to_number('c103') From dual --反向将c103,c104转成10进制,等于2和3
--col 1; len 6; (6): 00 40 72 52 00 2d
--AAAChpAABAAAHJSAAt
Select dbms_rowid.rowid_object('AAAChpAABAAAHJSAAt'), --10345 对象id Select * From dba_objects Where object_id = 10345 查处表T1
dbms_rowid.rowid_relative_fno('AAAChpAABAAAHJSAAt'), --1 数据文件1
dbms_rowid.rowid_block_number('AAAChpAABAAAHJSAAt'), --29266 第29266块
dbms_rowid.rowid_row_number('AAAChpAABAAAHJSAAt') From dual --45 第45行
--00 40 72 52 00 2d先转成2进制,再转成10进制
00000000 01000000 01110010 01010010 00000000 00101101
00000000 01 前10位表示数据文件编号 1
000000 01110010 01010010 接下来的22位表示文件快数 29266 select power(2,1)+power(2,4)+power(2,6)+power(2,9)+power(2,12)+power(2,13)+power(2,14) from dual
00000000 00101101 最后16位表示数据所在的行数 45 select 1+power(2,2)+power(2,3)+power(2,5) from dual
Set autotrace traceonly --打开执行计划
Set linesize 200 --设置列宽
Select * From t1 Where object_id = 2; --执行语句观察执行计划
Select * From t1 Where object_id+0 = 2; --执行语句观察执行计划
--Table
--Index 使表查询变快,但修改受到影响变慢
Create Table t1 As Select * From dba_objects;
--Rowid 伪列 与nextval,currval一样都是伪列
Insert Into t1 Select * From t1 --增加表T1的数据
Select Count(*) From t1
Select Rowid,t1.* From t1 Order By object_id,Rowid
A-Z 0-25
a-z 26-51
0-9 52-61
+ 62
/ 63
Select object_id,data_object_id From dba_objects Where object_name = 'T1'; --object_id 10345
Rowid 如 AAAChpAABAAAHJSAAt
AAAChp对象编号 AAB数据文件编号 AAAHJS数据块编号 AAt行编号
--AAAChp对应object_id 即属于哪个对象
Select 2*power(64,2)+33*64+41 From dual; --10345 等于从dba_objects表中查询到的object_id
--AAB 对应数据文件相对编号 AAB换算成10进制为1
Select * From dba_data_files Where relative_fno = 1; --file_id绝对编号,文件在整个数据库中的唯一编号 relative_fno相对编号,数据文件在表空间内部的编号
--AAAHJS对应数据块编号
Select 7*power(64,2)+9*64+18 From dual; --29266
--AAt行编号 AAA换算成10进制是45,即放在第46行
即AAAChpAABAAAHJSAAt 属于对象10345,存放在数据文件1的第29266个块的第46行上
Select object_id,Rowid From t1 Order By object_id,Rowid
Create Index idx_t1 On t1(object_id) Tablespace users --创建t1上object_id的索引,存放在表空间users上
--索引的类型 b-tree,bitmap
b-tree:balance --平衡树(相对于binary--二叉树)
Select object_id From dba_objects Where object_name = 'IDX_T1'; --object_id = 10346
Alter Session Set Events 'immediate trace name treedump level 10346' --(内部命令)将对象id为10347的tree输出到为当前窗口服务的进程的日志中,日志位置C:\oracle\product\10.2.0\admin\testDB\udump下面最新的文件
/*
Dump file c:\oracle\product\10.2.0\admin\testdb\udump\testrid_ora_1392.trc
Wed Dec 04 18:47:34 2013
ORACLE V10.2.0.1.0 - Production vsnsta=0
vsnsql=14 vsnxtr=3
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
Windows Server 2003 Version V5.2 Service Pack 2
CPU : 2 - type 586, 2 Physical Cores
Process Affinity : 0x00000000
Memory (Avail/Total): Ph:888M/1331M, Ph+PgF:1346M/1762M, VA:1628M/2047M
Instance name: testrid
Redo thread mounted by this instance: 1
Oracle process number: 15
Windows thread id: 1392, image: ORACLE.EXE (SHAD)
*** 2013-12-04 18:47:34.020
*** ACTION NAME:(SQL Window - 11.rowid和index的原) 2013-12-04 18:47:34.005
*** MODULE NAME:(PL/SQL Developer) 2013-12-04 18:47:34.005
*** SERVICE NAME:(testDB) 2013-12-04 18:47:34.005
*** SESSION ID:(146.162) 2013-12-04 18:47:34.005
----- begin tree dump
branch: 0x100000c 16777228 (0: nrow: 166, level: 1)
leaf: 0x100000d 16777229 (-1: nrow: 513 rrow: 513)
leaf: 0x100000e 16777230 (0: nrow: 497 rrow: 497)
leaf: 0x100000f 16777231 (1: nrow: 478 rrow: 478)
leaf: 0x1000010 16777232 (2: nrow: 479 rrow: 479)
leaf: 0x1000011 16777233 (3: nrow: 479 rrow: 479)
leaf: 0x1000012 16777234 (4: nrow: 478 rrow: 478)
leaf: 0x1000013 16777235 (5: nrow: 479 rrow: 479)
leaf: 0x1000014 16777236 (6: nrow: 479 rrow: 479)
leaf: 0x1000015 16777237 (7: nrow: 478 rrow: 478)
leaf: 0x1000016 16777238 (8: nrow: 479 rrow: 479)
leaf: 0x1000017 16777239 (9: nrow: 479 rrow: 479)
leaf: 0x1000018 16777240 (10: nrow: 478 rrow: 478)
---------------省略部分---------------
leaf: 0x10000b7 16777399 (160: nrow: 449 rrow: 449)
leaf: 0x10000b8 16777400 (161: nrow: 449 rrow: 449)
leaf: 0x10000b9 16777401 (162: nrow: 449 rrow: 449)
leaf: 0x10000ba 16777402 (163: nrow: 449 rrow: 449)
leaf: 0x10000bb 16777403 (164: nrow: 210 rrow: 210)
----- end tree dump
*/
分析日志:
leaf: 0x100000d 16777229 (-1: nrow: 485 rrow: 485)
0x100000d 十六进制转换成10进制
Select to_number('100000d','xxxxxxxx') From dual; --16777229
Select dbms_utility.data_block_address_file(16777229) From dual; --查找该id代表的具体数据文件 文件4(创建时候故意存放在users表空间中) select * from dba_data_files
Select dbms_utility.data_block_address_block(16777229) From dual; --查找该id代表的具体块 第13个块
Alter System Dump Datafile 4 Block 13;--将数据文件4的第13个块内容输出到附件,见附件11.rowid和index的原理_附件testrid_ora_1392.trc
/*
.............
.....接上面日志.....
.............
leaf: 0x10000ba 16777402 (163: nrow: 449 rrow: 449)
leaf: 0x10000bb 16777403 (164: nrow: 210 rrow: 210)
----- end tree dump
*** 2013-12-04 18:55:10.927
Start dump data blocks tsn: 4 file#: 4 minblk 13 maxblk 13
buffer tsn: 4 rdba: 0x0100000d (4/13)
scn: 0x0000.0004e7ee seq: 0x01 flg: 0x04 tail: 0xe7ee0601
frmt: 0x02 chkval: 0x3c73 type: 0x06=trans data
Hex dump of block: st=0, typ_found=1
Dump of memory from 0x07A08400 to 0x07A0A400
7A08400 0000A206 0100000D 0004E7EE 04010000 [................]
7A08410 00003C73 00000002 0000286A 0004E7EB [s<......j> 7A08420 00000000 00320002 01000009 00000000 [......2.........]
7A08430 00000000 00000000 00000000 00000000 [................]
.............
.....省略部分.....
.............
7A0A3C0 40000603 2D005272 00000000 00000000 [...@rR.-........]
7A0A3D0 00000000 00000000 00000000 00000000 [................]
Repeat 1 times
7A0A3F0 00000000 00000000 00000000 E7EE0601 [................]
Block header dump: 0x0100000d
Object id on Block? Y
seg/obj: 0x286a csc: 0x00.4e7eb itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000009 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0000.000.00000000 0x00000000.0000.00 ---- 0 fsc 0x0000.00000000
0x02 0xffff.000.00000000 0x00000000.0000.00 C--- 0 scn 0x0000.0004e7eb
Leaf block dump
===============
header address 127960164=0x7a08464
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 2
kdxcosdc 0
kdxconro 513
kdxcofbo 1062=0x426
kdxcofeo 1880=0x758
kdxcoavs 818
kdxlespl 0
kdxlende 0
kdxlenxt 16777230=0x100000e
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 72 52 00 2d
row#1[8012] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 75 4e 00 2d
row#2[8000] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 76 01 00 27
row#3[7988] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 76 7c 00 1a
row#4[7976] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 76 f7 00 10
row#5[7964] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 77 72 00 06
row#6[7952] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 77 ec 00 45
row#7[7940] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 78 67 00 3e
row#8[7928] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 40 72 52 00 05
row#9[7916] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 40 75 4e 00 05
row#10[7904] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 04
col 1; len 6; (6): 00 40 76 00 00 3b
.............
.....省略部分.....
.............
row#511[1892] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 42
col 1; len 6; (6): 00 40 78 67 00 50
row#512[1880] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 43
col 1; len 6; (6): 00 40 72 52 00 40
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 13 maxblk 13
*/
分析日志:
Start dump data blocks tsn: 4 file#: 4 minblk 13 maxblk 13
第4个数据文件的第13个块
Object id on Block? Y
seg/obj: 0x286a csc: 0x00.46a40 itc: 2 flg: E typ: 2 - Index
Select to_number('286a','xxxx') From dual --10346 即IDX_T1的对象id
row#0[8024] flag: ------, lock: 0, len=12
col 0; len 2; (2): c1 03
col 1; len 6; (6): 00 40 72 52 00 2d
--数据文件4第13块保存了513条数据
Select object_id,Rowid From t1 Order By object_id,Rowid; --第一条数据object_id 2,rowid AAAChpAABAAAHJSAAt;
--col 0; len 2; (2): c1 03
--object_id 2
Select Dump(2,16) From dual --查询2和3的16进制在存储时的值 Typ=2 Len=2: c1,3 ;
Select utl_raw.cast_to_number('c103') From dual --反向将c103,c104转成10进制,等于2和3
--col 1; len 6; (6): 00 40 72 52 00 2d
--AAAChpAABAAAHJSAAt
Select dbms_rowid.rowid_object('AAAChpAABAAAHJSAAt'), --10345 对象id Select * From dba_objects Where object_id = 10345 查处表T1
dbms_rowid.rowid_relative_fno('AAAChpAABAAAHJSAAt'), --1 数据文件1
dbms_rowid.rowid_block_number('AAAChpAABAAAHJSAAt'), --29266 第29266块
dbms_rowid.rowid_row_number('AAAChpAABAAAHJSAAt') From dual --45 第45行
--00 40 72 52 00 2d先转成2进制,再转成10进制
00000000 01000000 01110010 01010010 00000000 00101101
00000000 01 前10位表示数据文件编号 1
000000 01110010 01010010 接下来的22位表示文件快数 29266 select power(2,1)+power(2,4)+power(2,6)+power(2,9)+power(2,12)+power(2,13)+power(2,14) from dual
00000000 00101101 最后16位表示数据所在的行数 45 select 1+power(2,2)+power(2,3)+power(2,5) from dual
Set autotrace traceonly --打开执行计划
Set linesize 200 --设置列宽
Select * From t1 Where object_id = 2; --执行语句观察执行计划
Select * From t1 Where object_id+0 = 2; --执行语句观察执行计划
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/15810196/viewspace-1079757/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/15810196/viewspace-1079757/