[20181229]简单探究cluster table(补充)3.txt
--//简单探究cluster table.链接如下:
http://blog.itpub.net/267265/viewspace-2286463/
http://blog.itpub.net/267265/viewspace-2286618/
--//今天探究cluster tablde的索引.
1.环境:
SCOTT@book> @ ver1
PORT_STRING VERSION BANNER
------------------- ---------- ----------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
2.建立测试环境:
create cluster deptx_cluster (deptno number(2)) size 800;
--//加入参数size 800
create table deptx
(
deptno number(2) ,
dname varchar2(14 byte),
loc varchar2(13 byte)
) cluster deptx_cluster (deptno);
alter table deptx add constraint pk_deptx primary key (deptno);
create table empx
(
empno number(4) ,
ename varchar2(10 byte),
:wjob varchar2(9 byte),
mgr number(4),
hiredate date,
sal number(7,2),
comm number(7,2),
deptno number(2)
) cluster deptx_cluster (deptno);
--//取消主外键约束.
alter table empx add constraint constraint_name primary key (empno);
create index i_deptx_cluster_deptno on cluster deptx_cluster;
--//注这里不能使用unique,否则报ORA-01715: UNIQUE may not be used with a cluster index
insert into empx select * from emp;
insert into deptx select * from dept;
commit;
--//先导入empx,再导入deptx.
--//分析略.
3.查看数据:
SCOTT@book> select rowid,deptx.* from deptx;
ROWID DEPTNO DNAME LOC
------------------ ---------- -------------- -------------
AAAWE6AAEAAAAIsAAA 10 ACCOUNTING NEW YORK
AAAWE6AAEAAAAIsAAB 20 RESEARCH DALLAS
AAAWE6AAEAAAAIsAAC 30 SALES CHICAGO
AAAWE6AAEAAAAIsAAD 40 OPERATIONS BOSTON
SCOTT@book> @ rowid AAAWE6AAEAAAAIsAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90426 4 556 0 0x100022C 4,556 alter system dump datafile 4 block 556 ;
SCOTT@book> select rowid,empx.* from empx where deptno=20;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAWE6AAEAAAAIsAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
AAAWE6AAEAAAAIsAAD 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
AAAWE6AAEAAAAIsAAH 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
AAAWE6AAEAAAAIsAAK 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
AAAWE6AAEAAAAIsAAM 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
SCOTT@book> column SEGMENT_NAME format a30
SCOTT@book> select SEGMENT_NAME ,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments where owner=user and
segment_name='I_DEPTX_CLUSTER_DEPTNO';
SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
------------------------------ ------------------ ----------- ------------
I_DEPTX_CLUSTER_DEPTNO INDEX 4 682
--//cluster key很少,这样索引也很小.索引的根节点在dba=4,683.
SCOTT@book> alter system dump datafile 4 block 683;
System altered.
Block header dump: 0x010002ab
Object id on Block? Y
seg/obj: 0x1613f csc: 0x03.175ff02c itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x10002a8 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 0x0009.01a.00000a4a 0x00c00520.0395.12 --U- 1 fsc 0x0000.175ff02d
Leaf block dump
===============
header address 140106510985828=0x7f6d16d16a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7980=0x1f2c
kdxcoavs 7936
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 8
kdxlebksz 8032
row#0[7993] flag: ------, lock: 0, len=13, data:(8): 01 00 02 2c 00 02 01 00
col 0; len 2; (2): c1 0b
row#1[8019] flag: ------, lock: 0, len=13, data:(8): 01 00 02 2c 00 00 01 00
col 0; len 2; (2): c1 15
row#2[8006] flag: ------, lock: 0, len=13, data:(8): 01 00 02 2c 00 01 01 00
col 0; len 2; (2): c1 1f
row#3[7980] flag: ------, lock: 2, len=13, data:(8): 01 00 02 2c 00 03 01 00
col 0; len 2; (2): c1 29
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 683 maxblk 683
--//kdxledsz=8,索引在索引键值前占8个字节,注意看4个键值.
--//0x0100022c=16777772= alter system dump datafile 4 block 556,很明显前面4位是块地址.
--//后面4位00020100表示什么呢?先看看deptx的唯一性索引.
SCOTT@book> select SEGMENT_NAME ,SEGMENT_TYPE,HEADER_FILE,HEADER_BLOCK from dba_segments where owner=user and segment_name='PK_DEPTX';
SEGMENT_NAME SEGMENT_TYPE HEADER_FILE HEADER_BLOCK
------------------------------ ------------------ ----------- ------------
PK_DEPTX INDEX 4 562
SCOTT@book> alter system dump datafile 4 block 563;
System altered.
Block header dump: 0x01000233
Object id on Block? Y
seg/obj: 0x1613c csc: 0x03.175fef51 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x1000230 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 0x000a.019.00004d53 0x00c001f4.0f01.05 --U- 4 fsc 0x0000.175ff02f
Leaf block dump
===============
header address 140106510985828=0x7f6d16d16a64
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7988=0x1f34
kdxcoavs 7944
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 6
kdxlebksz 8032
row#0[8021] flag: ------, lock: 2, len=11, data:(6): 01 00 02 2c 00 00
col 0; len 2; (2): c1 0b
row#1[8010] flag: ------, lock: 2, len=11, data:(6): 01 00 02 2c 00 01
col 0; len 2; (2): c1 15
row#2[7999] flag: ------, lock: 2, len=11, data:(6): 01 00 02 2c 00 02
col 0; len 2; (2): c1 1f
row#3[7988] flag: ------, lock: 2, len=11, data:(6): 01 00 02 2c 00 03
col 0; len 2; (2): c1 29
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 563 maxblk 563
--//kdxledsz=6,唯一性索引rowid信息(不包括data_object_id)在前.可以看到第5,6字节表示行号.
--//这样如下:
row#0[7993] flag: ------, lock: 0, len=13, data:(8): 01 00 02 2c 00 02 01 00
col 0; len 2; (2): c1 0b
--//第5,6字节也应该表示行号.通过bbed观察:
BBED> set dba 4,556
DBA 0x0100022c (16777772 4,556)
BBED> p kdbt[0]
struct kdbt[0], 4 bytes @114
sb2 kdbtoffs @114 0
sb2 kdbtnrow @116 4
BBED> x /rn *kdbr[2]
rowdata[399] @7886
------------
flag@7886: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@7887: 0x00
cols@7888: 1
kref@7889: 4
mref@7891: 4
hrid@7893:0x0100022c.2
nrid@7899:0x0100022c.2
col 0[2] @7905: 10
--//cluster key=10,行号=2.这样前面6位实际上普通索引的rowid一样.后面的第7,8字节0100表示什么呢?
3.继续测试,增加empx deptno=20的记录数量.
SCOTT@book> insert into empx select rownum empno,ename,job,mgr,hiredate,sal,comm,20 deptno from (select * from emp),(select rownum empno from dual connect by level<=70);
980 rows created.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint ;
System altered.
SCOTT@book> alter system dump datafile 4 block 683;
System altered.
--//再次转储cluster table的index看看.
Block header dump: 0x010002ab
Object id on Block? Y
seg/obj: 0x1613f csc: 0x03.1762afc8 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x10002a8 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 0x0009.004.00000a72 0x00c00549.0398.07 --U- 1 fsc 0x0000.1762afcb
Leaf block dump
===============
header address 140106505867876=0x7f6d16835264
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 4
kdxcofbo 44=0x2c
kdxcofeo 7980=0x1f2c
kdxcoavs 7936
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 8
kdxlebksz 8032
row#0[7993] flag: ------, lock: 0, len=13, data:(8): 01 00 02 2c 00 02 01 00
col 0; len 2; (2): c1 0b
row#1[8019] flag: ------, lock: 2, len=13, data:(8): 01 00 02 b3 00 00 06 00
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
col 0; len 2; (2): c1 15
row#2[8006] flag: ------, lock: 0, len=13, data:(8): 01 00 02 2c 00 01 01 00
col 0; len 2; (2): c1 1f
row#3[7980] flag: ------, lock: 0, len=13, data:(8): 01 00 02 2c 00 03 01 00
col 0; len 2; (2): c1 29
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 683 maxblk 683
--//注意看下划线. cluster key的deptno=20的data部分,第7,8位发生了变化,并且前面的块地址发生了变化.
SCOTT@book> @ conv_n c115
N20
----------
20
SCOTT@book> @ dfb16 0x010002b3
RFILE# BLOCK# TEXT
---------- ---------- -----------------------------------------
4 691 alter system dump datafile 4 block 691 ;
--//通过bbed观察.
BBED> x /rn dba 0x010002b3 *kdbr[0]
rowdata[3435] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168: 1
kref@8169: 91
mref@8171: 91
hrid@8173:0x0100022d.0
nrid@8179:0x0100022c.0
col 0[2] @8185: 20
--//注意看hrid,nrid,分别表示:
--//hrid: ROWID of Previous block for this cluster key
--//nrid: ROWID of Next block for this cluster key
--//顺着nrid往下看,(注意后面的0表示行号,cluster key在cluster table中是第一个表,行号是一致的.如果第2个表要看前面表0占用多
--//少行号):
BBED> x /rn dba 0x0100022c *kdbr[0]
rowdata[6831] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x01
cols@8168: 1
kref@8169: 171
mref@8171: 171
hrid@8173:0x010002b3.0
nrid@8179:0x0100022b.0
col 0[2] @8185: 20
BBED> x /rn dba 0x0100022b *kdbr[0]
rowdata[6852] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168: 1
kref@8169: 181
mref@8171: 181
hrid@8173:0x0100022c.0
nrid@8179:0x0100022f.0
col 0[2] @8185: 20
BBED> x /rn dba 0x0100022f *kdbr[0]
rowdata[6853] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168: 1
kref@8169: 181
mref@8171: 181
hrid@8173:0x0100022b.0
nrid@8179:0x0100022e.0
col 0[2] @8185: 20
BBED> x /rn dba 0x0100022e *kdbr[0]
rowdata[6847] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168: 1
kref@8169: 181
mref@8171: 181
hrid@8173:0x0100022f.0
nrid@8179:0x0100022d.0
col 0[2] @8185: 20
BBED> x /rn dba 0x0100022d *kdbr[0]
rowdata[6853] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x01
cols@8168: 1
kref@8169: 181
mref@8171: 181
hrid@8173:0x0100022e.0
nrid@8179:0x010002b3.0
col 0[2] @8185: 20
--//最后又回到了0x010002b3.正好形成一个环.仔细数一下正好占6个数据库,这样可以猜测后面的第7字节表示6个块.
--//看看扫描empx表中deptno=20的情况:
SCOTT@book> select rowid,empx.* from empx where deptno=20 and rownum=1;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAWE6AAEAAAAKzAAA 890 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6j6sk9pxrdt4x, child number 0
-------------------------------------
select rowid,empx.* from empx where deptno=20 and rownum=1
Plan hash value: 2763438471
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS CLUSTER| EMPX | 2 | 76 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | I_DEPTX_CLUSTER_DEPTNO | 1 | | 0 (0)| |
-------------------------------------------------------------------------------------------------
--//可以发现empx表上并没有deptno的索引,oracle可以利用cluster table的索引定位查询.
SCOTT@book> @ rowid AAAWE6AAEAAAAKzAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90426 4 691 0 0x10002B3 4,691 alter system dump datafile 4 block 691 ;
--//这个正好对应cluster table 索引的rowid部分.
SCOTT@book> select DBMS_ROWID.ROWID_RELATIVE_FNO (rowid) n10 ,DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) n10 ,empx.* from empx where deptno=20 ;
--//结果我不贴出来了,太长.可以发现扫描的块顺序如下:
4,691
4,556
4,555
4,559
4,558
4,557
--//实际上还可以看出插入的顺序是 4,556 => 4,555 => 4,559 => 4,558 => 4,557 =>4,691. 最后插入的块是dba=4,691.
SCOTT@book> select distinct (DBMS_ROWID.ROWID_RELATIVE_FNO (rowid) ||','||DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)) c10 from empx where deptno=20 ;
C10
----------
4,559
4,558
4,691
4,556
4,557
4,555
6 rows selected.
--//distinct改变了顺序输出的方式,不过可以验证正好占6块.
spool aa.txt
select TO_CHAR (dbms_utility.make_data_block_address(DBMS_ROWID.ROWID_RELATIVE_FNO (rowid),DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid)), '0xxxxxxx') from empx where deptno=20;
spool off
$ uniq -c aa.txt
91 010002b3
170 0100022c
181 0100022b
181 0100022f
181 0100022e
181 0100022d
--//对比前面nrid的顺序:
--//0x010002b3 => 0x0100022c => 0x0100022b => 0x0100022f => 0x0100022e => 0x0100022d 完成能对上.
4.继续找一个sys.C_OBJ#看看.
--//对应cluster index是I_OBJ#.
SYS@book> @ &r/treedump I_OBJ#
old 1: select object_id from user_objects where object_name = upper('&&1') and object_type = 'INDEX'
new 1: select object_id from user_objects where object_name = upper('I_OBJ#') and object_type = 'INDEX'
OBJECT_ID
----------
3
old 1: alter session set events 'immediate trace name treedump level &m_index_id'
new 1: alter session set events 'immediate trace name treedump level 3'
Session altered.
SYS@book> alter session set events 'immediate trace name treedump level 3';
Session altered.
branch: 0x4000a9 4194473 (0: nrow: 26, level: 1)
leaf: 0x4000aa 4194474 (-1: nrow: 502 rrow: 502)
leaf: 0x4000ab 4194475 (0: nrow: 500 rrow: 500)
leaf: 0x4000ac 4194476 (1: nrow: 500 rrow: 500)
leaf: 0x4000ad 4194477 (2: nrow: 500 rrow: 500)
leaf: 0x4000ae 4194478 (3: nrow: 500 rrow: 500)
leaf: 0x4000af 4194479 (4: nrow: 500 rrow: 500)
leaf: 0x403058 4206680 (5: nrow: 500 rrow: 500)
leaf: 0x403059 4206681 (6: nrow: 500 rrow: 500)
leaf: 0x40305a 4206682 (7: nrow: 500 rrow: 500)
leaf: 0x40305b 4206683 (8: nrow: 246 rrow: 246)
leaf: 0x40305d 4206685 (9: nrow: 231 rrow: 231)
leaf: 0x40305c 4206684 (10: nrow: 240 rrow: 240)
leaf: 0x40305e 4206686 (11: nrow: 470 rrow: 470)
leaf: 0x40305f 4206687 (12: nrow: 470 rrow: 470)
leaf: 0x405e20 4218400 (13: nrow: 466 rrow: 465)
leaf: 0x405e21 4218401 (14: nrow: 470 rrow: 469)
leaf: 0x405e22 4218402 (15: nrow: 470 rrow: 470)
leaf: 0x405e23 4218403 (16: nrow: 466 rrow: 466)
leaf: 0x405e24 4218404 (17: nrow: 385 rrow: 384)
leaf: 0x405e25 4218405 (18: nrow: 470 rrow: 470)
leaf: 0x405e26 4218406 (19: nrow: 470 rrow: 470)
leaf: 0x405e27 4218407 (20: nrow: 470 rrow: 470)
leaf: 0x412f98 4272024 (21: nrow: 469 rrow: 469)
leaf: 0x412f99 4272025 (22: nrow: 469 rrow: 469)
leaf: 0x412f9a 4272026 (23: nrow: 469 rrow: 469)
leaf: 0x412f9b 4272027 (24: nrow: 339 rrow: 338)
----- end tree dump
--//4194474= alter system dump datafile 1 block 170,转储1,170看看.
SYS@book> alter system dump datafile 1 block 170;
System altered.
--//检查转储:
Block header dump: 0x004000aa
Object id on Block? Y
seg/obj: 0x3 csc: 0x03.174c9b1a itc: 2 flg: - typ: 2 - INDEX
fsl: 0 fnx: 0x0 ver: 0x01
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0008.009.0000001c 0x00c00754.0012.01 CB-- 0 scn 0x0000.000040fa
0x02 0x000a.018.00004498 0x00c0ac0a.0c27.41 --U- 1 fsc 0x0000.174c9b1b
Leaf block dump
===============
header address 140197452909148=0x7f8243610a5c
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 1
kdxconro 502
kdxcofbo 1040=0x410
kdxcofeo 1051=0x41b
kdxcoavs 11
kdxlespl 0
kdxlende 0
kdxlenxt 4194475=0x4000ab
kdxleprv 0=0x0
kdxledsz 8
kdxlebksz 8032
row#0[1051] flag: ------, lock: 0, len=13, data:(8): 00 40 00 93 00 07 01 00
col 0; len 2; (2): c1 03
row#1[1064] flag: ------, lock: 0, len=13, data:(8): 00 40 7b 09 00 08 03 00
col 0; len 2; (2): c1 05
--//看看obj#=5的情况(编码c1 05),奇怪怎么没有c1 04的键值(对应数字3).
--//00 40 7b 09 00 08 03 00
--//0x00407b09=4225801= alter system dump datafile 1 block 31497.在块dba=1,31497,行号是8.
BBED> x /rn dba 1,31497 *kdbr[8]
rowdata[950] @4571
------------
flag@4571: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@4572: 0x00
cols@4573: 1
kref@4574: 1
mref@4576: 1
hrid@4578:0x00400093.8
nrid@4584:0x00400094.0
col 0[2] @4590: 4
--//顺着nrid往下查.
BBED> x /rn dba 0x00400094 *kdbr[0]
rowdata[7701] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168: 1
kref@8169: 21
mref@8171: 21
hrid@8173:0x00407b09.8
nrid@8179:0x00400093.8
col 0[2] @8185: 4
BBED> x /rn dba 0x00400093 *kdbr[8]
rowdata[7489] @7990
-------------
flag@7990: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@7991: 0x00
cols@7992: 1
kref@7993: 18
mref@7995: 18
hrid@7997:0x00400094.0
nrid@8003:0x00407b09.8
col 0[2] @8009: 4
--//正好3块,也是一个环,也验证我的判断,剩下第8位呢?
4.回到测试,第8位有表示什么呢?
--//删除dba= 4,555 4,558 的记录看看.
SCOTT@book> delete from empx where deptno=20 and DBMS_ROWID.ROWID_RELATIVE_FNO (rowid)=4 and DBMS_ROWID.ROWID_BLOCK_NUMBER (rowid) in (555,558);
362 rows deleted.
SCOTT@book> commit ;
Commit complete.
SCOTT@book> alter system checkpoint ;
System altered.
--//继续通过bbed观察:
BBED> x /rn dba 0x010002b3 *kdbr[0]
rowdata[3435] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168: 1
kref@8169: 91
mref@8171: 91
hrid@8173:0x0100022d.0
nrid@8179:0x0100022c.0
col 0[2] @8185: 20
BBED> x /rn dba 0x0100022c *kdbr[0]
rowdata[6831] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x01
cols@8168: 1
kref@8169: 171
mref@8171: 171
hrid@8173:0x010002b3.0
nrid@8179:0x0100022b.0
col 0[2] @8185: 20
BBED> x /rn dba 0x0100022b *kdbr[0]
rowdata[6852] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168: 1
kref@8169: 181
hrid@8171:0x0100022c.0
nrid@8177:0x0100022f.0
col 0[2] @8185: 20
--//对比前面有记录的情况.我paste前面的显示在下面这样对比好看一些.
--//没有删除记录前情况.
BBED> x /rn dba 0x0100022b *kdbr[0]
rowdata[6852] @8166
-------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168: 1
kref@8169: 181
mref@8171: 181
hrid@8173:0x0100022c.0
nrid@8179:0x0100022f.0
col 0[2] @8185: 20
--//注意看没有mref部分(mref=0),并且hrid的offset占了mref的原来的位置.nrid也做了移动.col 0的位置没有变动.
SCOTT@book> alter system dump datafile 4 block 683;
System altered.
--//也没看出什么变化.难道这里第7,8位是合在一起的表示链接的块数量.要测试导入数据量有点大.下个星期继续测试吧.
--//另外我在empx上deptno上建立索引,
SCOTT@book> create index i_empx_deptno on empx(deptno);
Index created.
SCOTT@book> select /*+ index(empx i_empx_deptno) */ rowid,empx.* from empx where deptno=20 and rownum=1;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAWE6AAEAAAAIsAAA 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SCOTT@book> @ dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 1m9897s96d19h, child number 0
-------------------------------------
select /*+ index(empx i_empx_deptno) */ rowid,empx.* from empx where
deptno=20 and rownum=1
Plan hash value: 2439905350
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPX | 2 | 76 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | I_EMPX_DEPTNO | 14 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
SCOTT@book> @ rowid AAAWE6AAEAAAAIsAAA
OBJECT FILE BLOCK ROW ROWID_DBA DBA TEXT
---------- ---------- ---------- ---------- -------------------- -------------------- ----------------------------------------
90426 4 556 0 0x100022C 4,556 alter system dump datafile 4 block 556 ;
--//当前最小的rowid.不过这样的索引建立是多余的.
SCOTT@book> select rowid,empx.* from empx where deptno=20 and rownum=1;
ROWID EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AAAWE6AAEAAAAKzAAA 890 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
--//总结:
1.cluster table的索引不需要指定unique.
2.有点像唯一索引,只不过data部分占8字节.包括dba地址以及行号以及链接的块数量(第7,8字节).
3.如果当前块没有关联数据.mref=0的情况下,hrid,nrid的offset上移动2个字节.col 0 不动.真搞不懂oracle为什么这样设计,设置为0不就ok了吗?
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2286968/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2286968/
5529
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
