042-11 schema-Index Rowid

Alter Table tt Move     --讲表移动整理，让数据存放的更有序
b-tree:balance  --平衡树（相对于binary--二叉树,用于数据仓库）


--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的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        --设置列宽
Set timming on
Select * From t1 Where object_id = 2; --执行语句观察执行计划 
Select * From t1 Where object_id+0 = 2; --执行语句观察执行计划

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15810196/viewspace-1130237/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/15810196/viewspace-1130237/