[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