[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

相關文章