[20140909]oracle cluster index (11g).txt

lfree發表於2014-09-09

[20140909]oracle cluster index (11g).txt

--應用中除了堆表,很少使用cluser表,也就僅僅在生產系統使用IOT索引組織表.
--實際上系統表中許多都是cluster表.比如SYS.TAB$,SYS.COL$等都建立在cluster中.

--沒事,簡單研究一下其儲存結構.

1.建立測試環境:

SCOTT@test> @ver
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

--create cluster cluster_dept (deptno NUMBER(2)) index ;
create cluster cluster_dept (deptno NUMBER(2)) ;
create index i_cluster_deptno on cluster cluster_dept;

create table dept1 cluster cluster_dept(deptno) as select * from dept;
create table emp1  cluster cluster_dept(deptno) as select * from emp;

2.研究:
select owner, segment_name, file_id, block_id, blocks, bytes
from dba_extents
where segment_name in ('I_CLUSTER_DEPTNO','CLUSTER_DEPT')
order by segment_name, extent_id;


OWNER  SEGMENT_NAME            FILE_ID   BLOCK_ID     BLOCKS      BYTES
------ -------------------- ---------- ---------- ---------- ----------
SCOTT  CLUSTER_DEPT                  4       1976          8      65536
SCOTT  CLUSTER_DEPT                  4        544          8      65536
SCOTT  I_CLUSTER_DEPTNO              4       1984          8      65536

SCOTT@test> select owner,object_name,object_id,data_object_id,object_type  from dba_objects where owner=user and object_name in ('DEPT1','EMP1','CLUSTER_DEPT');
OWNER  OBJECT_NAME           OBJECT_ID DATA_OBJECT_ID OBJECT_TYPE
------ -------------------- ---------- -------------- -------------------
SCOTT  CLUSTER_DEPT             291016         291016 CLUSTER
SCOTT  DEPT1                    291018         291016 TABLE
SCOTT  EMP1                     291019         291016 TABLE

--可以發現這些物件的DATA_OBJECT_ID=291016.

SCOTT@test> select owner,segment_name,segment_type,header_file,header_block,bytes  from dba_segments where owner=user and segment_name in ('DEPT1','EMP1','CLUSTER_DEPT','I_CLUSTER_DEPTNO');
OWNER  SEGMENT_NAME         SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES
------ -------------------- ------------------ ----------- ------------ ----------
SCOTT  I_CLUSTER_DEPTNO     INDEX                        4         1986      65536
SCOTT  CLUSTER_DEPT         CLUSTER                      4         1978     131072

--僅僅存在CLUSTER_DEPT,I_CLUSTER_DEPTNO段.

SCOTT@test> select rowid,dept1.* from dept1;
ROWID                  DEPTNO DNAME          LOC
------------------ ---------- -------------- -------------
AABHDIAAEAAAAe7AAA         20 RESEARCH       DALLAS1
AABHDIAAEAAAAe8AAA         30 SALES          CHICAGO
AABHDIAAEAAAAe9AAA         40 OPERATIONS     BOSTON
AABHDIAAEAAAAe+AAA         50 aaa            bbb
AABHDIAAEAAAAe/AAA         10 ACCOUNTING     NEW YORK
AABHDIAAEAAAAIgAAA         70 aaaa           BBBB
AABHDIAAEAAAAInAAA         60 cc             aaa

7 rows selected.

SCOTT@test> select rowid,emp1.* from emp1;
ROWID                   EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
------------------ ---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
AABHDIAAEAAAAe7AAA       7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
AABHDIAAEAAAAe7AAB       7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
AABHDIAAEAAAAe7AAC       7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
AABHDIAAEAAAAe7AAD       7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
AABHDIAAEAAAAe7AAE       7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
AABHDIAAEAAAAe8AAA       7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
AABHDIAAEAAAAe8AAB       7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
AABHDIAAEAAAAe8AAC       7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
AABHDIAAEAAAAe8AAD       7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
AABHDIAAEAAAAe8AAE       7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
AABHDIAAEAAAAe8AAF       7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
AABHDIAAEAAAAe/AAA       7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
AABHDIAAEAAAAe/AAB       7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
AABHDIAAEAAAAe/AAC       7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.
--注意看empno=7782行,rowid='AABHDIAAEAAAAe/AAA',與dept1.deptno=10的rowid是一樣的.

select d.deptno,
dbms_rowid.rowid_relative_fno(d.rowid)||':'||dbms_rowid.rowid_block_number(d.rowid) dept_row,
dbms_rowid.rowid_relative_fno(e.rowid)||':'||dbms_rowid.rowid_block_number(e.rowid) emp_row,
count(*)
from dept1 d, emp1 e
where d.deptno = e.deptno
group by
d.deptno,
dbms_rowid.rowid_relative_fno(d.rowid)||':'||dbms_rowid.rowid_block_number(d.rowid),
dbms_rowid.rowid_relative_fno(e.rowid)||':'||dbms_rowid.rowid_block_number(e.rowid)
order by deptno;

DEPTNO DEPT_ROW  EMP_ROW  COUNT(*)
------- --------- -------- --------
     10 4:1983    4:1983          3
     20 4:1979    4:1979          5
     30 4:1980    4:1980          6

--可以發現記錄被組織起來,相同的deptno在同一塊裡面,這樣當檢索deptno一樣的資訊時,emp對應的資訊很快查詢.
--比較適合連線查詢,實際上空間消耗也比較大.

SCOTT@test> @lookup_rowid AABHDIAAEAAAAe/AAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
    291016          4       1983          0 4,1983               alter system dump datafile 4 block 1983
 
3.做一個轉儲看看.
SCOTT@test> alter system dump datafile 4 block 1983 ;
System altered.

Block header dump:  0x010007bf
Object id on Block? Y
seg/obj: 0x470c8  csc: 0x02.a63f479c  itc: 2  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x10007b8 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0001.021.000044c0  0x00c005b5.1c5b.0d  C---    0  scn 0x0002.a63f4788
0x02   0x0005.008.0000725e  0x00c007a0.2518.1b  --U-    3  fsc 0x0000.a63f47a3
bdba: 0x010007bf
data_block_dump,data header at 0x2a9752e064
===============
tsiz: 0x1f98
hsiz: 0x24
pbl: 0x2a9752e064
     76543210
flag=--------
ntab=3
nrow=5
frre=-1
fsbo=0x24
fseo=0x1efd
avsp=0x1ed9
tosp=0x1ed9
0xe:pti[0]  nrow=1  offs=0
0x12:pti[1] nrow=1  offs=1
0x16:pti[2] nrow=3  offs=2
0x1a:pri[0] offs=0x1f82
0x1c:pri[1] offs=0x1f6a
0x1e:pri[2] offs=0x1f44
0x20:pri[3] offs=0x1f21
0x22:pri[4] offs=0x1efd
block_row_dump:
tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0  cc: 1
curc: 4 comc: 4 pk: 0x010007bf.0 nk: 0x010007bf.0
col  0: [ 2]  c1 0b
tab 1, row 0, @0x1f6a
tl: 24 fb: -CH-FL-- lb: 0x0  cc: 2 cki: 0
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 2, row 0, @0x1f44
tl: 38 fb: -CH-FL-- lb: 0x2  cc: 6 cki: 0
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 1, @0x1f21
tl: 35 fb: -CH-FL-- lb: 0x2  cc: 6 cki: 0
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 2, @0x1efd
tl: 36 fb: -CH-FL-- lb: 0x2  cc: 6 cki: 0
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 1983 maxblk 1983

--說明:
ntab=3 =>表示有3個表. nrow=5 有5條記錄.

tab 0, row 0, @0x1f82
tl: 22 fb: K-H-FL-- lb: 0x0  cc: 1
curc: 4 comc: 4 pk: 0x010007bf.0 nk: 0x010007bf.0
col  0: [ 2]  c1 0b
--tab 0 實際上cluster主鍵.

SCOTT@test01p> select dump(10,16) from dual ;
DUMP(10,16)
-----------------
Typ=2 Len=2: c1,b

--tab 1 實際上dept1表.
tab 1, row 0, @0x1f6a
tl: 24 fb: -CH-FL-- lb: 0x0  cc: 2 cki: 0
col  0: [10]  41 43 43 4f 55 4e 54 49 4e 47
col  1: [ 8]  4e 45 57 20 59 4f 52 4b

SCOTT@test01p> select dump(dname,16) c50 ,dump(loc,16) c40 from dept1 where  deptno=10 ;
C50                                                C40
-------------------------------------------------- ----------------------------------------
Typ=1 Len=10: 41,43,43,4f,55,4e,54,49,4e,47        Typ=1 Len=8: 4e,45,57,20,59,4f,52,4b

--tab 2 實際上emp1表.
tab 2, row 0, @0x1f44
tl: 38 fb: -CH-FL-- lb: 0x2  cc: 6 cki: 0
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

SCOTT@test01p> select dump(empno,16) c30 ,dump(ename,16) c30 from emp1 where empno=7782;
C30                            C30
------------------------------ ------------------------------
Typ=2 Len=3: c2,4e,53          Typ=1 Len=5: 43,4c,41,52,4b

--可以發現資訊是一致的.
--有點奇怪的是oracle如何知道tab1 對應的就是dept1.tab 2 對應的就是emp1呢?
--另外可以發現emp1(tab2)並沒有包含cluster鍵資訊。

SCOTT@test> @desc emp1;
Name                    Null?    Type
----------------------- -------- ----------------
EMPNO                            NUMBER(4)
ENAME                            VARCHAR2(10)
JOB                              VARCHAR2(9)
MGR                              NUMBER(4)
HIREDATE                NOT NULL DATE
SAL                              NUMBER(7,2)
COMM                             NUMBER(7,2)
DEPTNO                           NUMBER(2)

SCOTT@test> select col#,segcol#,intcol# from sys.col$ where obj#=291019;
      COL#    SEGCOL#    INTCOL#
---------- ---------- ----------
         1          2          1
         2          3          2
         3          4          3
         4          5          4
         5          6          5
         6          7          6
         7          8          7
         8          1          8

8 rows selected.

-- COL#可以表示該列是否在用(0為UNUSED),SEGCOL#表示各列在資料塊上儲存時的順序,INTCOL#表示建立表時各列的定義順序。

4.看看索引:
SCOTT@test> select owner,segment_name,segment_type,header_file,header_block,bytes  from dba_segments where owner=user and segment_name in ('DEPT1','EMP1','CLUSTER_DEPT','I_CLUSTER_DEPTNO');
OWNER  SEGMENT_NAME         SEGMENT_TYPE       HEADER_FILE HEADER_BLOCK      BYTES
------ -------------------- ------------------ ----------- ------------ ----------
SCOTT  I_CLUSTER_DEPTNO     INDEX                        4         1986      65536
SCOTT  CLUSTER_DEPT         CLUSTER                      4         1978     131072

SCOTT@test> alter system dump datafile 4 block 1987 ;
System altered.

Block header dump:  0x010007c3
Object id on Block? Y
seg/obj: 0x470c9  csc: 0x02.a63f4790  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x10007c0 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   0x0001.01a.000044bb  0x00c005b5.1c5b.1f  --U-    1  fsc 0x0000.a63f4791
Leaf block dump
===============
header address 182927417444=0x2a9752e064
kdxcolev 0
KDXCOLEV Flags = - - -
kdxcolok 0
kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y
kdxconco 1
kdxcosdc 0
kdxconro 7
kdxcofbo 50=0x32
kdxcofeo 7941=0x1f05
kdxcoavs 7891
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 8
kdxlebksz 8032
row#0[8019] flag: ------, lock: 0, len=13, data:(8):  01 00 07 bf 00 00 01 00
col 0; len 2; (2):  c1 0b
row#1[8006] flag: ------, lock: 0, len=13, data:(8):  01 00 07 bb 00 00 01 00
col 0; len 2; (2):  c1 15
row#2[7993] flag: ------, lock: 0, len=13, data:(8):  01 00 07 bc 00 00 01 00
col 0; len 2; (2):  c1 1f
row#3[7980] flag: ------, lock: 0, len=13, data:(8):  01 00 07 bd 00 00 01 00
col 0; len 2; (2):  c1 29
row#4[7967] flag: ------, lock: 0, len=13, data:(8):  01 00 07 be 00 00 01 00
col 0; len 2; (2):  c1 33
row#5[7954] flag: ------, lock: 0, len=13, data:(8):  01 00 02 27 00 00 01 00
col 0; len 2; (2):  c1 3d
row#6[7941] flag: ------, lock: 2, len=13, data:(8):  01 00 02 20 00 00 01 00
col 0; len 2; (2):  c1 47
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 1987 maxblk 1987


5.補充使用bbed觀察:
BBED> set dba 4,1983
        DBA             0x010007bf (16779199 4,1983)


BBED> p kdbt[0]
struct kdbt[0], 4 bytes                     @114
   sb2 kdbtoffs                             @114      0
   sb2 kdbtnrow                             @116      1

BBED> p kdbt[1]
struct kdbt[1], 4 bytes                     @118
   sb2 kdbtoffs                             @118      1
   sb2 kdbtnrow                             @120      1

BBED> p kdbt[2]
struct kdbt[2], 4 bytes                     @122
   sb2 kdbtoffs                             @122      2
   sb2 kdbtnrow                             @124      3


BBED> p *kdbr[0]
rowdata[133]
------------
ub1 rowdata[133]                            @8166     0xac

BBED> x /rn
rowdata[133]                                @8166
------------
flag@8166: 0xac (KDRHFL, KDRHFF, KDRHFH, KDRHFK)
lock@8167: 0x00
cols@8168:    1
kref@8169:    4
mref@8171:    4
hrid@8173:0x010007bf.0
nrid@8179:0x010007bf.0

col    0[2] @8185: 10


BBED> p *kdbr[1]
rowdata[109]
------------
ub1 rowdata[109]                            @8142     0x6c

BBED> x /rcc
rowdata[109]                                @8142
------------
flag@8142: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8143: 0x00
cols@8144:    2

col   0[10] @8146: ACCOUNTING
col    1[8] @8157: NEW YORK


BBED> p *kdbr[2]
rowdata[71]
-----------
ub1 rowdata[71]                             @8104     0x6c

BBED> x /rnccntnnn
rowdata[71]                                 @8104
-----------
flag@8104: 0x6c (KDRHFL, KDRHFF, KDRHFH, KDRHFC)
lock@8105: 0x02
cols@8106:    6

col    0[3] @8108: 7782
col    1[5] @8112: CLARK
col    2[7] @8118: MANAGER
col    3[3] @8126: 7839
col    4[7] @8130: 1981-06-09 00:00:00
col    5[3] @8138: 2450

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/267265/viewspace-1266411/,如需轉載,請註明出處,否則將追究法律責任。

相關文章