ITPub博客

首页 > 数据库 > Oracle > [20181229]简单探究cluster table(补充)3.txt

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

原创 Oracle 作者:lfree 时间:2018-12-29 17:11:39 0 删除 编辑

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2286968/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2859
  • 访问量
    6646475