[20181227]简单探究cluster table(补充)2.txt
--//昨天简单探究cluster table.链接如下:http://blog.itpub.net/267265/viewspace-2286463/
--//昨天我导入数据按照cluster key来导入的,这样看到每块一个键值.并且我没有设置size参数.这样在我探究C_OBJ#时,遇到一些问题.
--//重新导入测试看看来说明问题.
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),
job 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> 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> @ 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> alter system checkpoint ;
System altered.
--//通过bbed观察:
BBED> set dba 4,556
DBA 0x0100022c (16777772 4,556)
BBED> p /d kdbt
struct kdbt[0], 4 bytes @114
sb2 kdbtoffs @114 0
sb2 kdbtnrow @116 4
struct kdbt[1], 4 bytes @118
sb2 kdbtoffs @118 4
sb2 kdbtnrow @120 4
struct kdbt[2], 4 bytes @122
sb2 kdbtoffs @122 8
sb2 kdbtnrow @124 14
--//说明有3个表,cluster表deptx_cluster以及表deptx,empx.行数分别是4,4,14,后面kdbtoffs表示偏移量.
BBED> p kdbr
sb2 kdbr[0] @126 8066
sb2 kdbr[1] @128 8009
sb2 kdbr[2] @130 7786
sb2 kdbr[3] @132 7409
--//kdbr[3]的偏移在kdbr[21]的前面,这个是因为导入时empx表没有deptno=40的记录.
--//感觉oracle维护这个行目录代价蛮大的,最后插入deptno=40,插入点后面的数组都向后移动.而且这里记录的是相对偏移.
--//如果itl槽增加,这里偏移量都要修改.
sb2 kdbr[4] @134 7469
sb2 kdbr[5] @136 7449
sb2 kdbr[6] @138 7431
sb2 kdbr[7] @140 7387
sb2 kdbr[8] @142 8031
sb2 kdbr[9] @144 7968
sb2 kdbr[10] @146 7927
sb2 kdbr[11] @148 7889
sb2 kdbr[12] @150 7846
sb2 kdbr[13] @152 7808
sb2 kdbr[14] @154 7748
sb2 kdbr[15] @156 7711
sb2 kdbr[16] @158 7676
sb2 kdbr[17] @160 7635
sb2 kdbr[18] @162 7600
sb2 kdbr[19] @164 7565
sb2 kdbr[20] @166 7529
sb2 kdbr[21] @168 7493
BBED> x /rn *kdbr[0]
rowdata[679] @8166
------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
--//与前面看到的相差100.偏移从kdbh计算.一般2个ITL槽这里的偏移是100.
lock@8167: 0x00
cols@8168: 1
kref@8169: 6
mref@8171: 6
hrid@8173:0x0100022c.0
nrid@8179:0x0100022c.0
col 0[2] @8185: 20
BBED> x /rnccntnnn *kdbr[8]
rowdata[644] @8131
------------
flag@8131: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8132: 0x02
cols@8133: 6
col 0[3] @8135: 7369
col 1[5] @8139: SMITH
col 2[5] @8145: CLERK
col 3[3] @8151: 7902
col 4[7] @8155: 1980-12-17 00:00:00
col 5[2] @8163: 800
--//这条对应deptno=20.
BBED> x /rnccntnnn *kdbr[9]
rowdata[581] @8068
------------
flag@8068: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8069: 0x02
cols@8070: 7
ckix@8071: 1
--//多了1个ckix.应该表示指向*kdbr[1]的记录.
col 0[3] @8072: 7499
col 1[5] @8076: ALLEN
col 2[8] @8082: SALESMAN
col 3[3] @8091: 7698
col 4[7] @8095: 1981-02-20 00:00:00
col 5[2] @8103: 1600
col 6[2] @8106: 300
BBED> x /rn *kdbr[1]
rowdata[622] @8109
------------
flag@8109: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8110: 0x00
cols@8111: 1
kref@8112: 7
mref@8114: 7
hrid@8116:0x0100022c.1
nrid@8122:0x0100022c.1
col 0[2] @8128: 30
SCOTT@book> select * from empx where empno=7499;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
--//在观察1条记录empno=7782的情况.
SCOTT@book> select rownum,empx.* from empx;
ROWNUM EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
1 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
2 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
3 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
4 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
5 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
6 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
8 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
9 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
11 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
12 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
13 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
14 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
BBED> x /rnccntnnn *kdbr[14]
rowdata[361] @7848
------------
flag@7848: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@7849: 0x02
cols@7850: 6
ckix@7851: 2
col 0[3] @7852: 7782
col 1[5] @7856: CLARK
col 2[7] @7862: MANAGER
col 3[3] @7870: 7839
col 4[7] @7874: 1981-06-09 00:00:00
col 5[3] @7882: 2450
--//ckix=2.指向*kdbr[2].
BBED> dump /v offset 7848
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 556 Offsets: 7848 to 7911 Dba:0x0100022c
-----------------------------------------------------------------------------------------------------------
6c020602 03c24e53 05434c41 524b074d 414e4147 455203c2 4f280777 b5060901 l l.....NS.CLARK.MANAGER..O(.w....
010103c2 1933ac00 01040004 00010002 2c000201 00022c00 0202c10b 6c020601 l .....3..........,.....,.....l...
<32 bytes per line>
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
--//再看empno=7566的情况(deptno=20)
BBED> x /rnccntnnn *kdbr[11]
rowdata[502] @7989
------------
flag@7989: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@7990: 0x02
cols@7991: 6
col 0[3] @7993: 7566
col 1[5] @7997: JONES
col 2[7] @8003: MANAGER
col 3[3] @8011: 7839
col 4[7] @8015: 1981-04-02 00:00:00
col 5[3] @8023: 2975
--//没有ckix信息.
总结:
--//也就是1个块有多个cluster key的情况下.通过ckix信息定位cluster key,占用1个字节.没有的情况下指向*kdbr[0]的信息.
--//我个人还是有点疑问,oracle如何知道下面1个字节是表示ckix,还是字段的长度.
--//明白了:
BBED> dump /v offset 7989 count 200
File: /mnt/ramdisk/book/users01.dbf (4)
Block: 556 Offsets: 7989 to 8188 Dba:0x0100022c
-----------------------------------------------------------------------------------------------------------
6c020600 03c24c43 054a4f4e 4553074d 414e4147 455203c2 4f280777 b5040201 l l.....LC.JONES.MANAGER..O(.w....
~~~~~~~~
010103c2 1e4c6c02 070103c2 4c160457 41524408 53414c45 534d414e 03c24d63 l .....Ll.....L..WARD.SALESMAN..Mc
0777b502 16010101 03c20d33 02c2066c 02070103 c24b6405 414c4c45 4e085341 l .w.........3...l.....Kd.ALLEN.SA
4c45534d 414e03c2 4d630777 b5021401 010102c2 1102c204 ac000107 00070001 l LESMAN..Mc.w....................
00022c00 01010002 2c000102 c11f6c02 060003c2 4a460553 4d495448 05434c45 l ..,.....,.....l.....JF.SMITH.CLE
524b03c2 50030777 b40c1101 010102c2 09ac0001 06000600 0100022c 00000100 l RK..P..w...................,....
022c0000 02c11501 l .,......
<32 bytes per line>
--//注意0x06与0x03之间有一个00,这个就是ckix,只不过bbed不显示就是ckix=0罢了.
BBED> x /rnccntnnn *kdbr[11]
rowdata[502] @7989
------------
flag@7989: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@7990: 0x02
cols@7991: 6
col 0[3] @7993: 7566
col 1[5] @7997: JONES
col 2[7] @8003: MANAGER
col 3[3] @8011: 7839
col 4[7] @8015: 1981-04-02 00:00:00
col 5[3] @8023: 2975
--//cols在偏移7991,col 0 在7993,中间的就是0x00.oracle的bbed不显示这个ckix=0.
--//看一下转储明白了,转储就明显标识cki的信息:
SCOTT@book> alter system dump datafile 4 block 556 ;
System altered.
Block header dump: 0x0100022c
Object id on Block? Y
seg/obj: 0x1613a csc: 0x03.175ff02d itc: 2 flg: E typ: 1 - DATA
brn: 0 bdba: 0x1000228 ver: 0x01 opc: 0
inc: 0 exflg: 0
Itl Xid Uba Flag Lck Scn/Fsc
0x01 0x0009.01a.00000a4a 0x00c00520.0395.11 C--- 0 scn 0x0003.175ff02d
0x02 0x000a.019.00004d53 0x00c001f4.0f01.04 --U- 18 fsc 0x0000.175ff02f
bdba: 0x0100022c
data_block_dump,data header at 0x7f4f3844a064
===============
tsiz: 0x1f98
hsiz: 0x46
pbl: 0x7f4f3844a064
76543210
flag=-------K
ntab=3
nrow=22
frre=-1
fsbo=0x46
fseo=0x1cdb
avsp=0x1c95
tosp=0x1c95
0xe:pti[0] nrow=4 offs=0
0x12:pti[1] nrow=4 offs=4
0x16:pti[2] nrow=14 offs=8
0x1a:pri[0] offs=0x1f82
0x1c:pri[1] offs=0x1f49
0x1e:pri[2] offs=0x1e6a
0x20:pri[3] offs=0x1cf1
0x22:pri[4] offs=0x1d2d
0x24:pri[5] offs=0x1d19
0x26:pri[6] offs=0x1d07
0x28:pri[7] offs=0x1cdb
0x2a:pri[8] offs=0x1f5f
0x2c:pri[9] offs=0x1f20
0x2e:pri[10] offs=0x1ef7
0x30:pri[11] offs=0x1ed1
0x32:pri[12] offs=0x1ea6
0x34:pri[13] offs=0x1e80
0x36:pri[14] offs=0x1e44
0x38:pri[15] offs=0x1e1f
0x3a:pri[16] offs=0x1dfc
0x3c:pri[17] offs=0x1dd3
0x3e:pri[18] offs=0x1db0
0x40:pri[19] offs=0x1d8d
0x42:pri[20] offs=0x1d69
0x44:pri[21] offs=0x1d45
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 6 comc: 6 pk: 0x0100022c.0 nk: 0x0100022c.0
col 0: [ 2] c1 15
tab 0, row 1, @0x1f49
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 7 comc: 7 pk: 0x0100022c.1 nk: 0x0100022c.1
col 0: [ 2] c1 1f
tab 0, row 2, @0x1e6a
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 4 comc: 4 pk: 0x0100022c.2 nk: 0x0100022c.2
col 0: [ 2] c1 0b
tab 0, row 3, @0x1cf1
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 1 comc: 1 pk: 0x0100022c.3 nk: 0x0100022c.3
col 0: [ 2] c1 29
tab 1, row 0, @0x1d2d
tl: 24 fb: -CH-FL-- lb: 0x2 cc: 2 cki: 2
col 0: [10] 41 43 43 4f 55 4e 54 49 4e 47
col 1: [ 8] 4e 45 57 20 59 4f 52 4b
tab 1, row 1, @0x1d19
tl: 20 fb: -CH-FL-- lb: 0x2 cc: 2 cki: 0
col 0: [ 8] 52 45 53 45 41 52 43 48
col 1: [ 6] 44 41 4c 4c 41 53
tab 1, row 2, @0x1d07
tl: 18 fb: -CH-FL-- lb: 0x2 cc: 2 cki: 1
col 0: [ 5] 53 41 4c 45 53
col 1: [ 7] 43 48 49 43 41 47 4f
tab 1, row 3, @0x1cdb
tl: 22 fb: -CH-FL-- lb: 0x2 cc: 2 cki: 3
col 0: [10] 4f 50 45 52 41 54 49 4f 4e 53
col 1: [ 6] 42 4f 53 54 4f 4e
tab 2, row 0, @0x1f5f
tl: 35 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0
col 0: [ 3] c2 4a 46
col 1: [ 5] 53 4d 49 54 48
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 50 03
col 4: [ 7] 77 b4 0c 11 01 01 01
col 5: [ 2] c2 09
tab 2, row 1, @0x1f20
tl: 41 fb: -CH-FL-- lb: 0x2 cc: 7 cki: 1
col 0: [ 3] c2 4b 64
col 1: [ 5] 41 4c 4c 45 4e
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 14 01 01 01
col 5: [ 2] c2 11
col 6: [ 2] c2 04
tab 2, row 2, @0x1ef7
tl: 41 fb: -CH-FL-- lb: 0x2 cc: 7 cki: 1
col 0: [ 3] c2 4c 16
col 1: [ 4] 57 41 52 44
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 02 16 01 01 01
col 5: [ 3] c2 0d 33
col 6: [ 2] c2 06
tab 2, row 3, @0x1ed1
tl: 38 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0
col 0: [ 3] c2 4c 43
col 1: [ 5] 4a 4f 4e 45 53
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 04 02 01 01 01
col 5: [ 3] c2 1e 4c
tab 2, row 4, @0x1ea6
tl: 43 fb: -CH-FL-- lb: 0x2 cc: 7 cki: 1
col 0: [ 3] c2 4d 37
col 1: [ 6] 4d 41 52 54 49 4e
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 09 1c 01 01 01
col 5: [ 3] c2 0d 33
col 6: [ 2] c2 0f
tab 2, row 5, @0x1e80
tl: 38 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 1
col 0: [ 3] c2 4d 63
col 1: [ 5] 42 4c 41 4b 45
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 05 01 01 01 01
col 5: [ 3] c2 1d 33
tab 2, row 6, @0x1e44
tl: 38 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 2
col 0: [ 3] c2 4e 53
col 1: [ 5] 43 4c 41 52 4b
col 2: [ 7] 4d 41 4e 41 47 45 52
col 3: [ 3] c2 4f 28
col 4: [ 7] 77 b5 06 09 01 01 01
col 5: [ 3] c2 19 33
tab 2, row 7, @0x1e1f
tl: 37 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0
col 0: [ 3] c2 4e 59
col 1: [ 5] 53 43 4f 54 54
col 2: [ 7] 41 4e 41 4c 59 53 54
col 3: [ 3] c2 4c 43
col 4: [ 7] 77 bb 04 13 01 01 01
col 5: [ 2] c2 1f
tab 2, row 8, @0x1dfc
tl: 35 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 2
col 0: [ 3] c2 4f 28
col 1: [ 4] 4b 49 4e 47
col 2: [ 9] 50 52 45 53 49 44 45 4e 54
col 3: *NULL*
col 4: [ 7] 77 b5 0b 11 01 01 01
col 5: [ 2] c2 33
tab 2, row 9, @0x1dd3
tl: 41 fb: -CH-FL-- lb: 0x2 cc: 7 cki: 1
col 0: [ 3] c2 4f 2d
col 1: [ 6] 54 55 52 4e 45 52
col 2: [ 8] 53 41 4c 45 53 4d 41 4e
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 09 08 01 01 01
col 5: [ 2] c2 10
col 6: [ 1] 80
tab 2, row 10, @0x1db0
tl: 35 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0
col 0: [ 3] c2 4f 4d
col 1: [ 5] 41 44 41 4d 53
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4e 59
col 4: [ 7] 77 bb 05 17 01 01 01
col 5: [ 2] c2 0c
tab 2, row 11, @0x1d8d
tl: 35 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 1
col 0: [ 2] c2 50
col 1: [ 5] 4a 41 4d 45 53
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4d 63
col 4: [ 7] 77 b5 0c 03 01 01 01
col 5: [ 3] c2 0a 33
tab 2, row 12, @0x1d69
tl: 36 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0
col 0: [ 3] c2 50 03
col 1: [ 4] 46 4f 52 44
col 2: [ 7] 41 4e 41 4c 59 53 54
col 3: [ 3] c2 4c 43
col 4: [ 7] 77 b5 0c 03 01 01 01
col 5: [ 2] c2 1f
tab 2, row 13, @0x1d45
tl: 36 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 2
col 0: [ 3] c2 50 23
col 1: [ 6] 4d 49 4c 4c 45 52
col 2: [ 5] 43 4c 45 52 4b
col 3: [ 3] c2 4e 53
col 4: [ 7] 77 b6 01 17 01 01 01
col 5: [ 2] c2 0e
end_of_block_dump
End dump data blocks tsn: 4 file#: 4 minblk 556 maxblk 556
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-2286618/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/267265/viewspace-2286618/
2065
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
