oracle中的cluster表

Oracle中有一個統計資訊clustering factor，它就是用來反映索引中鍵值在表中的有序程度，clustering factor的值如果接近表的blocks的數量，表明資料在表中的是有序的，而如果這個值接近表的行數，則表明表中的資料是無序存放的。因為clustring factor對於索引查詢的影響很大，所以在CBO計算cost時，這個值非常重要。

我們可以通過建立一個單表的hash cluster，將相同鍵值的資料物理存放在一起，達到提高效能的目的。建立cluster有兩個最重要的引數：hashkeys和size，前者表示cluster中有多少個不同的鍵值，後者表示每個鍵值需要分配的空間。因為hash cluster的空間是預先分配的，這兩個值的正確設定對cluster的效能影響非常大。hashkeys設定過大，會造成空間浪費，而如果設定過小，則會產生大量的hash碰撞，極大影響效能。size也是一樣，設定過大會浪費空間，而設定過小，資料超過預先分配的空間時，會通過連結方式存放在溢位段中，影響效能。而這兩個值一旦設定，就無法更改，除非重建cluster。

hash cluster簡單的說就是通過預先分配空間的方式，將相同key的資料存放在一起，以提高查詢效能的一種手段，所以準確的設定hashkeys和size引數是使用hash cluster的關鍵，使用的前提是key的數量是可以估算的，而且每個key的資料是基本平均的。但是，在實際使用的環境中，資料量的變化往往是不可預知的，這也造成hash cluster的應用場景非常有限。

Index cluster和hash cluster類似，只不過index cluster是通過索引實現資料定位，而且index cluster的空間是動態分配的，但是同樣存在正確設定size引數的問題，設定過大過小都會產生效能問題。

個人觀點：Oracle cluster更適合相對靜態資料的儲存，對於OLTP應用來說，cluster在大部分情況下都不太適用，因為我們都無法預估到資料量的變化，根本無法合理設定cluster的引數。

任何技術都要找到合適的應用場景，有利一定有弊，有些技術確實是看上去很美，但是並不實用，而有些方案看上去很土，但是可以解決問題，找到最合適的就好。

大家對通常oracle中的cluster的理解是不準確的，經常和sql server中的cluster index混淆。Cluster是儲存一組table的一種方法，這些table共享同一資料塊中的某些相同column，並把不同table在這一共享column上值相同的data row儲存到同一block上。在sql server中的cluster index強制行根據index key按儲存順序儲存，這一點和oracle中的IOT類似。

從下圖中我們可以清楚的看到cluster 和非cluster 的一組table的物理儲存的區別。在cluster中，單個block上的資料可能來自多個table，概念上可以儲存“預連線”的資料。單個table也可以使用cluster，即根據某些column按組儲存資料。如圖中，所有部門ID為 20和 110的部門資訊和員工資訊的資料都將儲存在同一block。注意，這裡儲存的並不是排序的資料（那是IOT的任務），儲存的是按部門ID分組集合的資料，是以heap的方式儲存的。因此，部門20剛好和部門110相鄰，而部門99和部門100相距很遠（硬碟的物理位置）。

當單個block放不下時，額外的block將連結到最初的block，來包容溢位的資料，這種方式和在IOT中溢位block非常相似。

現在我們看看如何建立一個cluster。在cluster中建立一系列table是很簡單的，物件儲存定義如PCTFREE，PCTUSED，INITIAL都是和cluster相關的，而不是和table相關。這是因為在cluster中儲存了若干table，每個table在同一個block中擁有不同的PCTFREE沒有意義。

SQL> create cluster e_d_cluster

2 (deptid number(2))

3 size 1024

4 /

Cluster created

這裡首先建立了一個index cluster。這個cluster的key為 deptid，在table中這個列可以不命名為deptid，但資料型別number（2）必須匹配。Size選項是用來告訴oracle預計有1024

位元組資料和每個cluser key相關。Oracle將使用這個資訊來計算每個block能容納的最大cluster key數目。因此size太高，在每一block將得到很少的key，並且將使用比需要的更多的空間；設定容量太低，將得到過多的資料連線，這將偏離使用cluster的目的。Size是cluster的重要引數。

現在我們來建立cluster index。在把資料放入之前，需要索引cluster。Cluster index的作用是儲存一個cluster key，並且返回包含該key的block的地址。

SQL> create index e_d_cluster_idx

2 on cluster e_d_cluster

3 /

Index created

Cluster key 的index可以使用index所有的正常的儲存引數，並且可以位於另一個tablespace。它是一個正常的index，能夠索引到一個cluster，並且也包含一個完全null的條目。

我們在cluster中建立table：

SQL> create table department

2 (deptid number(2) primary key,

3 dname varchar2(14),

4 loc varchar2(13))

5 cluster e_d_cluster(deptid);

Table created

SQL> create table employee

2 (empid number primary key,

3 ename varchar2(10),

4 job varchar2(10),

5 mgr number,

6 hiredate date,

7 sal number,

8 comm number,

9 deptid number(2) references department(deptid))

10 cluster e_d_cluster(deptid);

Table created

這裡建立table與普通的table唯一的區別就是使用了cluster關鍵字，我們往table中裝載資料：

SQL> begin

2 for x in(select * from scott.dept)

3 loop

4 insert into department

5 values(x.deptno,x.dname,x.loc);

6 insert into employee

7 select * from scott.emp

8 where deptno = x.deptno;

9 end loop;

10 end;

11 /

PL/SQL procedure successfully completed

我們可以看到目前table中裝載瞭如下資料：

SQL> select * from department;

DEPTID DNAME LOC

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

10 ACCOUNTING NEW YORK

20 RESEARCH DALLAS

30 SALES CHICAGO

40 OPERATIONS BOSTON

SQL> select * from employee;

EMPID ENAME JOB MGR HIREDATE SAL COMM DEPTID

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

7782 CLARK MANAGER 7839 1981-6-9 2450 10

7839 KING PRESIDENT 1981-11-17 5000 10

7934 MILLER CLERK 7782 1982-1-23 1300 10

7369 SMITH CLERK 7902 1980-12-17 800 20

7566 JONES MANAGER 7839 1981-4-2 2975 20

7788 SCOTT ANALYST 7566 1982-12-9 3000 20

7876 ADAMS CLERK 7788 1983-1-12 1100 20

7902 FORD ANALYST 7566 1981-12-3 3000 20

7499 ALLEN SALESMAN 7698 1981-2-20 1600 300 30

7521 WARD SALESMAN 7698 1981-2-22 1250 500 30

7654 MARTIN SALESMAN 7698 1981-9-28 1250 1400 30

7698 BLAKE MANAGER 7839 1981-5-1 2850 30

7844 TURNER SALESMAN 7698 1981-9-8 1500 0 30

7900 JAMES CLERK 7698 1981-12-3 950 30

現在我們看看這兩個table資料儲存的位置：

SQL> select dbms_rowid.rowid_block_number(department.rowid) dept_rid,

2 dbms_rowid.rowid_block_number(employee.rowid) emp_rid,department.deptid

3 from department,employee

4 where employee.deptid = department.deptid;

DEPT_RID EMP_RID DEPTID

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

5587 5587 10

5587 5587 10

5587 5587 10

5587 5587 20

5587 5587 20

5587 5587 20

5587 5587 20

5587 5587 20

5587 5587 30

5587 5587 30

5587 5587 30

5587 5587 30

5587 5587 30

5587 5587 30

可以看到部門ID相同的資料儲存在一個block上。我們為什麼推薦上述方法對cluster進行最初的裝載呢？這樣能夠保證如果有些cluster key的相關資料超過了size，仍能使大部分資料聚集在一個block上。這隻適用於最初的資料裝載，在這以後，可以使用事務對cluster中的table insert資料。

由於cluster中的特殊的資料儲存方式，出現了這樣一個問題，rowid出現了重複，現在rowid只能在一個table中唯一標識一行資料了（另一種rowid重複出現在transport tablespace的操作後）。

SQL> select rowid from department

2 intersect

3 select rowid from employee;

ROWID

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

AAAGWQAADAAABXTAAA

AAAGWQAADAAABXTAAB

AAAGWQAADAAABXTAAC

AAAGWQAADAAABXTAAD

我們還發現，重複的rowid的數量和主表中的記錄數一樣，即和cluster中的記錄數一樣。我們把這個blick dump出來，看看是什麼原因：

SQL> alter system dump datafile 3 block 5587;

System altered

Dump file D:/database/oracle/admin/ora817/udump/ORA02628.TRC
Wed Dec 31 12:31:10 2003
ORACLE V8.1.7.4.1 - Production vsnsta=0
vsnsql=f vsnxtr=3
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production
Windows 2000 Version 5.0 Service Pack 4, CPU type 586
Instance name: ora817

Redo thread mounted by this instance: 1

Oracle process number: 17

Windows thread id: 2628, image: ORACLE.EXE

*** SESSION ID:(17.1471) 2003-12-31 12:31:10.159
Start dump data blocks tsn: 2 file#: 3 minblk 4275 maxblk 4275
buffer tsn: 2 rdba: 0x00c010b3 (3/4275)
scn: 0x0000.00142980 seq: 0x01 flg: 0x02 tail: 0x29800601
frmt: 0x02 chkval: 0x0000 type: 0x06=trans data

Block header dump: 0x00c010b3
Object id on Block? Y
seg/obj: 0x657d csc: 0x00.14297c itc: 2 flg: O typ: 1 - DATA
fsl: 0 fnx: 0x0 ver: 0x01

Itl Xid Uba Flag Lck Scn/Fsc
0x01 xid: 0x0003.029.000002d2 uba: 0x00800466.006e.35 C--- 0 scn 0x0000.0014297c －－－1.這兩個transaction的flag 和lck是什麼意思？0x02 xid: 0x0003.023.000002d2 uba: 0x00800467.006e.37 --U- 18 fsc 0x0000.00142980 

data_block_dump
===============
tsiz: 0x1fa0
hsiz: 0x46
pbl: 0x11ab745c
bdba: 0x00c010b3
flag=------K----ntab=3 －－這是代表5587的block上有3個table

（e_d_cluster,department,employee）

nrow=22
frre=-1
fsbo=0x46
fseo=0x1ce3
avsp=0x1c9d
tosp=0x1c9d0xe:pti[0] nrow=4 offs=0 －－這是3個table的行數及開始的偏移量
0x12:pti[1] nrow=4 offs=4
0x16:pti[2] nrow=14 offs=80x1a:pri[0] offs=0x1f8a 
0x1c:pri[1] offs=0x1eef
0x1e:pri[2] offs=0x1e10
0x20:pri[3] offs=0x1cf9
0x22:pri[4] offs=0x1f72
0x24:pri[5] offs=0x1edb
0x26:pri[6] offs=0x1dfe
0x28:pri[7] offs=0x1ce3
0x2a:pri[8] offs=0x1f4c
0x2c:pri[9] offs=0x1f29
0x2e:pri[10] offs=0x1f05
0x30:pri[11] offs=0x1eb8
0x32:pri[12] offs=0x1e92
0x34:pri[13] offs=0x1e6d
0x36:pri[14] offs=0x1e4a
0x38:pri[15] offs=0x1e26
0x3a:pri[16] offs=0x1dd5
0x3c:pri[17] offs=0x1dac
0x3e:pri[18] offs=0x1d81
0x40:pri[19] offs=0x1d5b
0x42:pri[20] offs=0x1d32
0x44:pri[21] offs=0x1d0f
block_row_dump:tab 0, row 0, @0x1f8a －－從這開始是emp_dept_clustertl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 4 comc: 4 pk: 0x00c010b3.0 nk: 0x00c010b3.0
col 0: [ 2] c1 0b
tab 0, row 1, @0x1eef
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 6 comc: 6 pk: 0x00c010b3.1 nk: 0x00c010b3.1
col 0: [ 2] c1 15
tab 0, row 2, @0x1e10
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 7 comc: 7 pk: 0x00c010b3.2 nk: 0x00c010b3.2
col 0: [ 2] c1 1f
tab 0, row 3, @0x1cf9
tl: 22 fb: K-H-FL-- lb: 0x0 cc: 1
curc: 1 comc: 1 pk: 0x00c010b3.3 nk: 0x00c010b3.3
col 0: [ 2] c1 29tab 1, row 0, @0x1f72 －－從這開始是departmenttl: 24 fb: -CH-FL-- lb: 0x2 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 1, row 1, @0x1edb
tl: 20 fb: -CH-FL-- lb: 0x2 cc: 2 cki: 1
col 0: [ 8] 52 45 53 45 41 52 43 48
col 1: [ 6] 44 41 4c 4c 41 53
tab 1, row 2, @0x1dfe
tl: 18 fb: -CH-FL-- lb: 0x2 cc: 2 cki: 2
col 0: [ 5] 53 41 4c 45 53
col 1: [ 7] 43 48 49 43 41 47 4f
tab 1, row 3, @0x1ce3
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 4etab 2, row 0, @0x1f4c －－2從這開始是employee，因為department和

employee都從row 0開始，所以rowid會出現重複

tl: 38 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0 －－3.這為什麼有tran的資訊？

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, @0x1f29
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, @0x1f05
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
tab 2, row 3, @0x1eb8
tl: 35 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 1
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 4, @0x1e92
tl: 38 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 1
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 5, @0x1e6d
tl: 37 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 1
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 b6 0c 09 01 01 01
col 5: [ 2] c2 1f
tab 2, row 6, @0x1e4a
tl: 35 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 1
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 b7 01 0c 01 01 01
col 5: [ 2] c2 0c
tab 2, row 7, @0x1e26
tl: 36 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 1col 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 1ftab 2, row 8, @0x1dd5
tl: 41 fb: -CH-FL-- lb: 0x2 cc: 7 cki: 2col 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 04tab 2, row 9, @0x1dac
tl: 41 fb: -CH-FL-- lb: 0x2 cc: 7 cki: 2
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 10, @0x1d81
tl: 43 fb: -CH-FL-- lb: 0x2 cc: 7 cki: 2
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 11, @0x1d5b
tl: 38 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 2
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 12, @0x1d32
tl: 41 fb: -CH-FL-- lb: 0x2 cc: 7 cki: 2
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 13, @0x1d0f
tl: 35 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 2
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
end_of_block_dump
End dump data blocks tsn: 2 file#: 3 minblk 5587 maxblk 5587

現在我們來說明這個block中的幾個問題：

1.這兩個transaction的flag 和lck是什麼意思？

這裡是block的ITL的資訊，這裡初始有兩個事務，事務槽在block有空間時是可以擴充套件的。

falg ------ 依次4個位置是 CBUT
C 表示該 ITL 事務已經提交

B: 當讀到這個ITL 的時候該block需要 rollback (一致讀)導致ITL 來自 rollback segment，也就是說該ITL事務被提交過多次了需要追述到最早的部分
U 表示該ITL 事務已經提交，但是提交的SCN是oracle 在資料有效性的基礎上“猜”出來的，可能並不是真實的commit scn ，這個SCN就是 scn/fsc . 
T : 事務提交後ITL涉及到的資料行上的 lock資訊也就是 lb ，在下次dml操作到該塊資料的時候，lb 會清零，表示清除lock(當然即使沒有清除該ITL若已經提交也認為鎖不存在)，在這個清理的過程中相應flag位置會設定為 T。

lck 表示該 itl 在該block上影響到的行數

2.為什麼rowid會出現重複？

我們想知道rowid為什麼會重複，首先要明白rowid的含義。ROWID 在磁碟上需要10 個位元組的儲存空間並使用18 個字元來顯示它包含下列元件

¨ 資料物件編號每個資料物件如表或索引在建立時都分配有此編號，並且此編號在資料庫中是唯一的；

¨ 相關檔案編號此編號對於一個表空間中的每個檔案是唯一的；

¨ 塊編號表示包含此行的塊在檔案中的位置；

¨ 行編號標識塊頭中行目錄位置的位置；

在內部資料物件編號需要32 位，相關檔案編號需要10 位，塊編號需要22，位行編號需要16 位，加起來總共是80位或10 個位元組，ROWID 使用以64 為基數的編碼方案來顯示該方案將六個位置用於資料物件，編號三個位置用於相關檔案編號六個位置用於塊編號三個位置用於行編號以64 為基數的編碼方案使用字元A-Z a-z 0-9 + 和/共64 個字元如下例所示

AAAGWQ AAD AAABXT AAA

在本例中

AAAGWQ 是資料物件編號

AAD 是相關檔案編號

AAABXT 是塊編號

AAA 是行編號

在block 5587中delparment和employee中行編號都是從0開始，檔案編號和塊編號相同，我們再來看一下資料物件編號：

SQL> select OBJECT_NAME,OBJECT_ID ,DATA_OBJECT_ID from user_objects

2 where OBJECT_NAME in ('DEPARTMENT','EMPLOYEE','E_D_CLUSTER');

OBJECT_NAME OBJECT_ID DATA_OBJECT_ID

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

E_D_CLUSTER 26000 26000

EMPLOYEE 26004 26000

DEPARTMENT 26002 26000

從這裡能看出物件編號雖然不一樣，但在一個cluster中資料物件編號是一樣的。既然構成rowid的元件都是一樣的，那麼rowid自然是相同的。

3．.這為什麼有tran的資訊？

tl: 38 fb: -CH-FL-- lb: 0x2 cc: 6 cki: 0

lb: 0x2 表示改行上有事務0x02，這就是oracle的行鎖，如果該行上沒有事務， lb: 0x0，即沒有鎖。但這行資料是否有鎖還倚賴於該事務是不是活動的，這裡雖然lb: 0x2 ，但我們在ITL中可以看到事務0x02狀態為--U-，表示事務已經提交，即非活動事務，所以這行資料上並沒有鎖。