分類： Oracle 其它特性 2010-10-07 17:04 167人閱讀評論(0) 舉報

--========================================

-- 簇表及簇表管理(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

簇表及簇表管理(Index clustered tables)

相關文章