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@]

ITPub博客

oracle管理之 cluster(server.102 b14231)

最新文章