[20181229]简单探究cluster table(补充)3.txt

来源：这里教程网时间：2026-03-03 12:49:20 作者：

[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了吗?