[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

-------------------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------------

| 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了嗎?

[20181229]簡單探究cluster table(補充)3.txt

相關文章