--========================================
-- 簇表及簇表管理(Index clustered tables)
--========================================
簇表是Oracle中一種可選、的儲存表資料的方法。使用簇表可以減少磁碟I/O，改善訪問簇表的聯結所帶來的資源開銷，本文講述了簇表的原理、建立以及管理簇表等。
一、什麼是簇表及簇表的特性
1.簇表
由共享相同資料塊的一組表組成。在堆表的管理過程中，對於某些表的某些列和另外的表的某些列經常被用來聯結使用，可以將這些表的聯結列作為共享的公共列而將這些表組合在一起。這就是簇表形成的原因。例如，scott模式中，有emp表，dept表，兩個表經常使用 deptno列來進行聯結，為此，我們共享deptno列，將emp和dept表組成簇表。組成簇表後，Oracle物理上將emp和dept表中有關每個部門所有行儲存到相同的資料塊中。
簇表不能等同於SQL server中的簇索引，兩者並不是一回事。SQL server中的簇索引是使得行的儲存按索引鍵來儲存，類似於IOT表。
2.簇鍵
簇鍵是列或多列的組合，為簇表所共有
在建立簇時指定簇鍵的列，以後在建立增加的簇中的每個表時，指定相同的列即可
每個簇鍵值在簇和簇索引中僅僅儲存一次，與不同表中有有多少這樣的行無關
3.使用簇表的好處。
減少磁碟I/O，減少了因使用聯結所帶來的系統開銷
節省了磁碟儲存空間，因為原來需要單獨存放多張表，現在可以將聯結的部分作為共享列的儲存。
4.何時建立簇表
對於經常查詢、當DML較少的表
表中的記錄經常使用到聯結查詢
5.建立簇表的步驟
建立簇
建立簇索引
建立簇表
6.建立簇、簇鍵、簇表時考慮的問題
哪些表適用於建立簇
對於建立簇的表哪些列用作簇列
建立簇時資料塊空間如何使用(pctfree,pctused)
平均簇鍵及相關行所需的空間大小
簇索引的位置(比如存放到不同的表空間)
預估簇的大小
二、建立簇及簇表
在建立簇時，如果未指定索引列，則預設地建立一個索引簇。
如果指定了雜湊引數，如hashkeys,hashis 或single table hashkeys，則可以建立雜湊簇
SQL> show user;
USER is "ROBINSON"
SQL> create cluster emp_dept_cluster(deptno number(2))
2 pctused 80
3 pctfree 15
4 size 1024
5 tablespace users;
Cluster created.
在上面建立的簇中，一個最重要的引數就是size，需要為size 指定合適的大小，如果size 指定的太大，則每個塊僅僅能存放
少量的簇，容易引起空間的浪費，如果指定的太小，則容易產生過多的資料鏈
建立簇索引的條件
模式中必須包含簇
必須具有create any index的許可權
簇索引的作用
用於一個簇鍵值並返回的包含該簇鍵值的地址塊
SQL> create index emp_dept_cluster_idx
2 on cluster emp_dept_cluster;
Index created.
建立簇表
SQL> create table dept
2 (deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13)
5 )
6 cluster emp_dept_cluster(deptno); --使用了cluster關鍵字後面跟簇名、簇列
Table created.
SQL> create table emp
2 (empno number primary key,
3 ename varchar2(10),
4 job varchar2(9),
5 mgr number,
6 hiredate date,
7 sal number,
8 comm number,
9 deptno number(2) references dept(deptno)
10 )
11 cluster emp_dept_cluster(deptno); --使用了cluster關鍵字後面跟簇名、簇列
Table created.
對於建立的簇表,與普通表的唯一差別是使用了cluster關鍵字，即告訴oracle 基表的哪一列將對映到簇表中
檢視剛剛建立的簇物件
SQL> select object_name,object_type,status from user_objects order by object_name ;
OBJECT_NAME OBJECT_TYPE STATUS
-------------------- ------------------- -------
DEPT TABLE VALID --簇表dept
EMP TABLE VALID --簇表emp
EMP_DEPT_CLUSTER CLUSTER VALID --簇emp_dept_cluster
EMP_DEPT_CLUSTER_IDX INDEX VALID --簇索引
SYS_C005422 INDEX VALID
SYS_C005423 INDEX VALID
SQL> select table_name,tablespace_name,cluster_name,status,pct_free from
2 dba_tables where owner = 'ROBINSON';
TABLE_NAME TABLESPACE_NAME CLUSTER_NAME STATUS PCT_FREE
--------------- --------------- ------------------ -------- ----------
EMP USERS EMP_DEPT_CLUSTER VALID 0
DEPT USERS EMP_DEPT_CLUSTER VALID 0
下面開始對簇表填充資料
SQL> begin
2 for x in ( select * from scott.dept )
3 loop
4 insert into dept
5 values ( x.deptno, x.dname, x.loc );
6 insert into emp
7 select * from scott.emp
8 where deptno = x.deptno;
9 end loop;
10 end;
11 /
PL/SQL procedure successfully completed.
三、更改簇
對於已經建立的簇，我們可以修改簇的相關屬性，比如
修改簇的物理屬性(pctfree,pctused,initrans,maxtrans等)
儲存簇鍵值的所有行所需空間的平均值(size)
預設的並行度
alter cluster emp_dpet_cluster
pctfree 20
initrans 3;
四、刪除簇、簇表
1.刪除簇
可以刪除不再需要的簇，刪除簇時，簇中對應的表及對應的簇索引都將被刪除
簇資料段佔用的盤區以及簇索引段佔用的盤區將被釋放返還給各自所在的表空間
刪除不包含表及索引的簇
drop cluster emp_dept_cluster;
對於包含簇表的簇，可以使用including talbes選項,如果簇中包含表但未使用including tables子句，將收到錯誤資訊
drop cluster emp_dept_cluster including tables;
對於包含簇之外的foreign key 約束說參照的主鍵，需要使用cascade constraints子句
drop cluster emp_dept including tables cascade constraints
2.刪除簇表
對於不再使用的簇表可以直接使用drop table table_name命令來刪除
drop table emp;
drop table dept;
3.刪除簇索引
簇索引可以被刪除而不影響簇或它的簇表
若不存在簇索引則簇表也無法使用
對於簇的訪問，則需要重建簇索引
drop index emp_dept_cluster_idx;
五、簇的相關檢視
dba_clusters
all_clusters
user_clusters
dba_clu_columns
user_clu_columns
六、演示相關操作
檢視dba_clusters檢視獲得所建立的簇
SQL> select cluster_name,tablespace_name,pct_free,pct_used,ini_trans
2 from dba_clusters where owner = 'ROBINSON';
CLUSTER_NAME TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS
-------------------- ------------------------------ ---------- ---------- ----------
EMP_DEPT_CLUSTER USERS 15 2
檢視簇列
SQL> select * from user_clu_columns;
CLUSTER_NAME CLU_COLUMN_NAME TABLE_NAME TAB_COLUMN_NAME
-------------------- -------------------- -------------------- ----------------------------------------
EMP_DEPT_CLUSTER DEPTNO DEPT DEPTNO
EMP_DEPT_CLUSTER DEPTNO EMP DEPTNO
修改簇的相關屬性
SQL> alter cluster emp_dept_cluster
2 pctfree 20
3 initrans 3;
Cluster altered.
SQL> select cluster_name,tablespace_name,pct_free,pct_used,ini_trans
2 from dba_clusters where owner = 'ROBINSON';
CLUSTER_NAME TABLESPACE_NAME PCT_FREE PCT_USED INI_TRANS
-------------------- ------------------------------ ---------- ---------- ----------
EMP_DEPT_CLUSTER USERS 20 3
從dba_segments可以看到簇產生了簇段，簇索引產生的為索引段
SQL> select segment_name,tablespace_name,segment_type from dba_segments where owner = 'ROBINSON';
SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE
-------------------- ------------------------------ ------------------
EMP_DEPT_CLUSTER USERS CLUSTER
EMP_DEPT_CLUSTER_IDX USERS INDEX
SYS_C005422 USERS INDEX
SYS_C005423 USERS INDEX
刪除簇，簇為非空時收到錯誤提示
SQL> drop cluster emp_dept_cluster;
drop cluster emp_dept_cluster
*
ERROR at line 1:
ORA-00951: cluster not empty
使用including tables 刪除簇及簇表、簇索引
SQL> drop cluster emp_dept_cluster including tables;
Cluster dropped.
SQL> select segment_name,tablespace_name from dba_segments where owner = 'ROBINSON';
no rows selected
簇由一組共享多個資料塊的多個表組成，它將這些表的相關行一起儲存到相同資料塊中，這樣可以減少查詢資料所需的磁碟讀取量。建立簇後，使用者可以在簇中建立表，這些表稱為簇表。
例如有如下兩個表：student和achievement.。其中，student表儲存學生資訊，需要使用SID欄位（儲存學生ID）；achievement表儲存學生成績資訊，也需要使用SID欄位。也就是說，student和achievement需要共享學生ID資料塊。
注意：如果使用者在自己的模式中建立簇，則必須具有create cluster許可權和unlimited tablespace系統許可權；如果想在其他模式中建立簇，則還必須具有create any cluster系統許可權
建立簇和簇表
建立簇
建立簇，需要使用create cluster語句，例如建立一個名為stu_ach的簇，如下：
-
create cluster stu_ach(sid number)
-
pctused 40
-
pctfree 10
-
size 1024
-
storage
-
(initial 128k
-
next 128k
-
minextents 2
-
maxextents 20
-
)tablespace huizhi;
上面建立簇stu_ach時，指定透過SID欄位來對簇中的表進行聚簇儲存，這個SID欄位就可以稱之為聚簇欄位。
注意：size子句用來為聚簇欄位提供指定的資料塊數量。例如，將size設定為1024，即表明簇中的聚簇欄位記錄只能儲存在1024個資料塊中。
建立簇表
建立簇表，需要使用cluster子句指定所使用的簇和簇欄位。
例如，在stu_ach簇中建立兩個簇表：student和achievement。如下：
-
create table student(
-
sid number,
-
sname varchar2(8),
-
sage number
-
)
-
cluster stu_ach(sid);
-
-
create table achievement(
-
aid number,
-
score number,
-
sid number
-
)
-
cluster stu_ach(sid);
-
上例在建立student和achievement表時，使用cluster子句指定它們所使用的簇為stu_ach，所使用的簇欄位為SID。
提示：將student和achievement兩個表組成一個簇後，在物理上oracle會將這兩個表中每個學生的學生資訊和該學生的所有成績資訊儲存到相同的資料塊中。
現在向student表中新增記錄，如下：
-
insert into student values(1,'小明',24);
發現還無法向簇表中新增記錄。
注意：為了能夠向簇表中新增記錄，還需要首先為簇建立索引。
建立簇索引
簇索引與簇表不同，它並不存在於簇中，而是與普通索引一樣需要具有獨立的儲存空間。
例如，為簇stu_ach建立一個簇索引，如下：
-
create index stu_ach_index
-
on cluster stu_ach
-
tablespace huizhi;
上例為簇stu_ach建立了一個名為stu_ach_index的簇索引。建立簇索引後，就可以向簇表中新增記錄了。
管理簇
對一個簇的管理主要是修改簇和刪除簇。如果使用者想要管理簇，則必須具有alter any cluster系統許可權。
修改簇
修改一個簇，主要是修改簇的如下屬性值:
1.物理儲存屬性，包括pctfree、pctused、initrans、maxtrans和storage。
2.為了儲存簇鍵值的所有行所需空間的平均值size。
3.預設的並行度。
刪除簇
1.刪除一個空簇：當一個簇中不包含簇表時，刪除該簇可以使用drop cluster cluster_name語句。
2.刪除一個含有簇表的簇：需要使用drop cluster...including tables語句，如下
-
drop cluster stu_ach including tables;
另外，如果某個簇含有簇表，並且有外來鍵約束，則需要使用drop cluster...including tables cascade constraints語句刪除該簇。如下：
-
drop cluster stu_ach including tables cascade constraints;
簇其實就是一組表，由一組共享相同資料塊的多個表組成，將經常一起使用的表組合在一起成簇可以提高處理效率；在一個簇中的表就叫做簇表。
建立順序是：簇→簇表→簇索引→資料
建立簇的格式
CREATE CLUSTER cluster_name
(column date_type [,column datatype]...)
[PCTUSED 40 | integer] [PCTFREE 10 | integer]
[SIZE integer]
[INITRANS 1 | integer] [MAXTRANS 255 | integer]
[TABLESPACE tablespace]
[STORAGE storage]
SIZE：指定估計平均簇鍵，以及與其相關的行所需的位元組數。
1、建立簇
create cluster my_clu (deptno number )
pctused 60
pctfree 10
size 1024
tablespace users
storage (
initial 128 k
next 128 k
minextents 2
maxextents 20
);
2、建立簇表
create table t1_dept(
deptno number ,
dname varchar2 ( 20 )
)
cluster my_clu(deptno);
create table t1_emp(
empno number ,
ename varchar2 ( 20 ),
birth_date date ,
deptno number
)
cluster my_clu(deptno);
3、為簇建立索引
create index clu_index on cluster my_clu;
注：若不建立簇索引，則在插入資料時報錯：ORA-02032: clustered tables cannot be used before the cluster index is built
管理簇
使用ALTER修改簇屬性(必須擁有ALTER ANY CLUSTER的許可權)
1、修改簇屬性
可以修改的簇屬性包括：
* PCTFREE、PCTUSED、INITRANS、MAXTRANS、STORAGE
* 為了儲存簇鍵值所有行所需空間的平均值SIZE
* 預設並行度
注：
* 不能修改INITIAL和MINEXTENTS的值
* PCTFREE、PCTUSED、SIZE引數修改後適用於所有資料塊
* INITRANS、MAXTRANS僅適用於以後分配的資料塊
* STORAGE引數修改後僅影響以後分配給簇的盤區
例：
alter cluster my_clu
pctused 40
2、刪除簇
drop cluster my_clu; -- 僅適用於刪除空簇
drop cluster my_clu including tables ; -- 刪除簇和簇表
drop cluster my_clu including tables cascade constraints ;--同時刪除外來鍵約束
注：簇表可以像普通表一樣刪除。
3、清空簇
truncate cluster my_clu;
注：所有在此簇上的表的資料全部被清空
雜湊聚簇表
在簇表中，Oracle使用儲存在索引中的鍵值來定位表中的行，而在雜湊聚簇表中，使用了雜湊函式代替了簇索引，先透過內部函式或者自定義的函式進行雜湊計算，然後再將計算得到的碼值用於定位表中的行。建立雜湊簇需要用到HASHKEYS子句。
1、建立雜湊簇
create cluster my_clu_two(empno number(10) )
pctused 70
pctfree 10
tablespace users
hash is empno
hashkeys 150 ;
說明：
* hash is 子句指明瞭進行雜湊的列，如果列是唯一的標示行，就可以將列指定為雜湊值
* hashkeys 指定和限制雜湊函式可以產生的唯一的雜湊值的數量
2、建立雜湊表
create table t2_emp (
empno number ( 10 ),
ename varchar2 ( 20 ),
birth_date date ,
deptno number )
cluster my_clu_two(empno);
注意：
* 必須設定數值的精度
* 雜湊簇不能也不用建立索引
* 雜湊簇不能ALTER:size、hashkeys、hash is引數
不宜用聚簇表的情況
1)如果預料到聚簇中的表會大量修改,聚簇表會對DML的效能產生負面影響
2)非常不適合對單表的全表掃描,因為只能引起對其它表的全表掃描
3)頻繁對錶進行TRUNCATE和載入,因為聚簇中的表是不能TRUNCATE的，只能TRUNCATE簇
4)如果表只是偶爾被連線或者它們的公共列經常被修改，則不要聚簇表
5)如果經常從所有有相同聚簇鍵值的表查詢出的結果資料超過一個或兩個Oracle塊，則不要聚簇表
6)如果空間不夠，並且不能為將要插入的新記錄分配額外的空間，那麼不要使用聚簇
