[20181227]简单探究cluster table(补充)2.txt

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

[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

编辑推荐：

返回列表

[20181227]简单探究cluster table(补充)2.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推