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@]
优点:减少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博客 ” ,链接:http://blog.itpub.net/70612/viewspace-1025152/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/70612/viewspace-1025152/