oracle管理之 cluster(server.102 b14231)

cluster多用在 經常需要連立的表上
優點：減少io；節省儲存空間
cluster key column 一定要有合理的區分度，最好一個key 的row能儲存在一個data block中

CREATE CLUSTER emp_dept (deptno NUMBER(3))
SIZE 600
TABLESPACE users
STORAGE (INITIAL 200K
NEXT 300K
MINEXTENTS 2
MAXEXTENTS 20
PCTINCREASE 33);

CREATE TABLE emp (
empno NUMBER(5) PRIMARY KEY,
ename VARCHAR2(15) NOT NULL,
. . .
deptno NUMBER(3) REFERENCES dept)
CLUSTER emp_dept (deptno);
CREATE TABLE dept (
deptno NUMBER(3) PRIMARY KEY, . . . )
CLUSTER emp_dept (deptno);

CREATE INDEX emp_dept_index
ON CLUSTER emp_dept
TABLESPACE users
STORAGE (INITIAL 50K
NEXT 50K
MINEXTENTS 2
MAXEXTENTS 10
PCTINCREASE 33);

A cluster index must be created before any rows can be inserted into any clustered
table.

DROP CLUSTER emp_dept;
DROP CLUSTER emp_dept INCLUDING TABLES;
DROP CLUSTER emp_dept INCLUDING TABLES CASCADE CONSTRAINTS;

--cluster相關檢視
DBA_CLUSTERS
ALL_CLUSTERS
USER_CLUSTERS
DBA_CLU_COLUMNS
USER_CLU_COLUMNS
DBA_CLUSTER_HASH_EXPRESSIONS
ALL_CLUSTER_HASH_EXPRESSIONS
USER_CLUSTER_HASH_EXPRESSIONS[@more@]