oracle中的cluster

 Oracle object学习笔记 1-cluster 收藏
1 聚集(cluster)
1.1 概念：
用于将不同表中的数据存储到相同的物理数据块中，用于多表数据需要频繁进行联合查询的情况下。多表数据存储在相同数据块后，在对对表进行联合查询时，所需读取的物理块数减少，从而提高查询性能。
需要注意的是对聚集中单表的查询和对聚集中表数据进行修改或插入等事务操作时，性能将受到影响。
每一个聚集存储其中各聚集表的数据并维护该聚集索引，以便对数据进行排序。聚集索引中的索引列，称为聚集键（cluster key），其决定了数据在物理上的存储位置，这些列不应时常更新。聚集键通常是其聚集表中同某个表的主键相关联的某表的外键。
聚集分为普通聚集和哈希聚集两种，其操作方法不太相同。
1.2 建立：
1.2.1 建立普通聚集
--建立聚集personnel：
CREATE CLUSTER personnel
     ( department_number  NUMBER(2) )
     SIZE 512
     STORAGE (INITIAL 100K NEXT 50K);

--建立聚集表emp:
CREATE TABLE emp
     (empno     NUMBER        PRIMARY KEY,
      ename     VARCHAR2(10)  NOT NULL
                CHECK (ename = UPPER(ename)),
       job       VARCHAR2(9),
       mgr       NUMBER        REFERENCES scott.emp(empno),
       hiredate  DATE         
         CHECK (hiredate < TO_DATE ('08-14-1998', 'MM-DD-YYYY')),
     sal       NUMBER(10,2)  CHECK (sal > 500),
      comm      NUMBER(9,0)   DEFAULT NULL,
      deptno    NUMBER(2)      NOT NULL )
      CLUSTER personnel (deptno);
 
--建立聚集表dept:
CREATE TABLE dept
     (deptno  NUMBER(2),
       dname   VARCHAR2(9),
      loc     VARCHAR2(9))
      CLUSTER personnel (deptno);

--建立聚集索引：
CREATE INDEX idx_personnel ON CLUSTER personnel;
1.2.2 建立哈希聚集
--建立使用数据库默认hash函数的聚集personnel
CREATE CLUSTER personnel
( department_number  NUMBER )
     SIZE 512  HASHKEYS 500
     STORAGE (INITIAL 100K  NEXT 50K);

--建立使用自定hash函数的聚集:
CREATE CLUSTER personnel
     ( home_area_code  NUMBER,
       home_prefix     NUMBER )
     HASHKEYS 20
     HASH IS MOD(home_area_code + home_prefix, 101);

--建立聚集表和索引：
CREATE TABLE LOCATIONS
      (LOC_ID                   NUMBER NOT NULL,
       CAMPUS                   CHAR(1) NULL,
       BUILDING                 NUMBER NULL,
       FLOOR                    NUMBER NULL,
       CUBE                     NUMBER NULL,
       EXT                      NUMBER NULL,
       PRFX                     NUMBER NULL,
       LOC_TIME_STMP            DATE NULL,
       CONSTRAINT XPKLOCATIONS       PRIMARY KEY (LOC_ID)
       USING INDEX
TABLESPACE EMPLOYEE_INDEXES)
       PCTFREE 30
       PCTUSED 60
       STORAGE (  INITIAL 50k
               NEXT 50k
               MINEXTENTS 1
               MAXEXTENTS 121
               PCTINCREASE 0)
TABLESPACE EMPLOYEES;

CREATE TABLE EMPLOYEEE_SALARIES
      (EMP_SAL_ID               NUMBER NOT NULL,
       EMP_SAL_UPPER            DECIMAL(8,2) NOT NULL,
       EMP_SAL_LOWER            DECIMAL(8,2) NOT NULL,
       CONSTRAINT XPKEMPLOYEE_SALARIES       PRIMARY KEY (EMP_SAL_ID)
        USING INDEX
TABLESPACE EMPLOYEE_INDEXES)
       PCTFREE 20
       PCTUSED 70
       STORAGE (
              INITIAL 75k
              NEXT 75k
              MINEXTENTS 1
              MAXEXTENTS 121
              PCTINCREASE 0)
TABLESPACE EMPLOYEES;

CREATE CLUSTER PER (PER_ID NUMBER) PCTFREE 10 PCTUSED 80
STORAGE(
INITIAL 100K
NEXT 100K
PCTINCREASE 0)
  INDEX ;

CREATE TABLE PERSONNEL
      (PER_ID                   NUMBER NOT NULL,
       PER_FNM                  VARCHAR2(20) NOT NULL,
       PER_LNM                  VARCHAR2(20) NOT NULL,
       PER_MI                   VARCHAR2(3) NULL,
       PER_BDT                  DATE NULL,
       PER_EMP_ID               NUMBER NULL,
       CONSTRAINT XPKPERSONNEL       PRIMARY KEY (PER_ID)
       USING INDEX)
CLUSTER PER (PER_ID)
TABLESPACE PERSONNEL;

CREATE TABLE DEPARTMENT
      (DEPT_ID                 NUMBER  NOT NULL,
       DEPT_DESC               VARCHAR2(40) NULL,
       CONSTRAINT XPKDEPARTMENT       PRIMARY KEY (DEPT_ID)
       USING INDEX)
       PCTFREE 5
       PCTUSED 90
       STORAGE (
              INITIAL 10k
              NEXT 10K
              MINEXTENTS 1
              MAXEXTENTS 121
              PCTINCREASE 0 )
       TABLESPACE EMPLOYEES;

CREATE CLUSTER EMP (EMP_ID NUMBER) PCTFREE 10 PCTUSED 80
STORAGE( INITIAL 150K
NEXT 150K
PCTINCREASE 0)
INDEX;

CREATE TABLE EMPLOYEE
      (EMP_ID                   NUMBER NOT NULL,
       DEPT_ID                  NUMBER  NULL,
       PER_ID                   NUMBER NOT NULL,
       EMP_SAL_ID               NUMBER NOT NULL,
       EMP_HIRE_DT              DATE NULL,
       EMP_TITLE_CD            NUMBER NULL,
       EMP_SALARY_CD            NUMBER NULL,
       EMP_SALARY_MULT          DECIMAL(3,2) NULL,
       CONSTRAINT XPKEMPLOYEE       PRIMARY KEY (EMP_ID)
USING INDEX
TABLESPACE EMPLOYEE_INDEXES,
       CONSTRAINT dept_to_emp       FOREIGN KEY  (DEPT_ID)
               REFERENCES DEPARTMENT,
       CONSTRAINT emp_to_per       FOREIGN KEY  (PER_ID)
               REFERENCES PERSONNEL,
       CONSTRAINT emp_to_emp_sal       FOREIGN KEY  (EMP_SAL_ID)
               REFERENCES EMPLOYEEE_SALARIES)
CLUSTER EMP (EMP_ID)
TABLESPACE EMPLOYEES;

CREATE INDEX XFK1EMPLOYEE
  ON EMPLOYEE
( EMP_SAL_ID                     ASC)
TABLESPACE EMPLOYEE_INDEXES;

CREATE INDEX XFK2EMPLOYEE
  ON EMPLOYEE
( PER_ID                         ASC)
TABLESPACE EMPLOYEE_INDEXES;

CREATE INDEX XFK3EMPLOYEE
  ON EMPLOYEE
( DEPT_ID                        ASC)
TABLESPACE EMPLOYEE_INDEXES;

CREATE TABLE PERSONNEL_ADDRESS
      (PER_ADDR_ID              CHAR(18) NOT NULL,
       PER_ID                   NUMBER NOT NULL,
       STREET_NM                CHAR(18) NULL,
       STREET_NUM               CHAR(18) NULL,
       APT_NUM                  CHAR(18) NULL,
       PO_BOX_NUM               CHAR(18) NULL,
       SUITE_NUM                CHAR(18) NULL,
       CITY                     CHAR(18) NULL,
       STATE                    CHAR(18) NULL,
       ZIP                      CHAR(18) NULL,
       ZIP_4                    CHAR(18) NULL,
       AREA_CODE                CHAR(18) NULL,
       PHN_NUM                  CHAR(18) NULL,
       CONSTRAINT XPKPERSONNEL_ADDRESS       PRIMARY KEY (PER_ADDR_ID, PER_ID)
       USING INDEX
TABLESPACE PERSONNEL_INDEXES,
       CONSTRAINT per_to_per_addr   FOREIGN KEY  (PER_ID)   REFERENCES PERSONNEL)
CLUSTER PER (PER_ID)
TABLESPACE PERSONNEL;

CREATE INDEX XFK1PERSONNEL_ADDRESS
  ON PERSONNEL_ADDRESS
( PER_ID                         ASC)
TABLESPACE PERSONNEL_INDEXES;

CREATE TABLE EMPLOYEE_LOCATION
      (EMP_ID                   NUMBER NOT NULL,
       LOC_ID                   NUMBER NOT NULL,
       CONSTRAINT XPKEMPLOYEE_LOCATION       PRIMARY KEY (EMP_ID, LOC_ID)
       USING INDEX
TABLESPACE EMPLOYEE_INDEXES,
       CONSTRAINT loc_to_emp_loc       FOREIGN KEY  (LOC_ID)
               REFERENCES LOCATIONS,
       CONSTRAINT emp_to_emp_loc       FOREIGN KEY  (EMP_ID)
               REFERENCES EMPLOYEE)
TABLESPACE EMPLOYEES;

CREATE INDEX XFK1EMPLOYEE_LOCATION
  ON EMPLOYEE_LOCATION
(EMP_ID                         ASC)
TABLESPACE EMPLOYEE_INDEXES;

CREATE INDEX XFK2EMPLOYEE_LOCATION
  ON EMPLOYEE_LOCATION
( LOC_ID                         ASC)
TABLESPACE EMPLOYEE_INDEXES;

CREATE TABLE EMPLOYEE_W2
      (EMP_ID                   NUMBER NOT NULL,
       EMP_SSN                  VARCHAR2(11) NULL,
       EMP_DEP                  NUMBER NULL,
       CONSTRAINT XPKEMPLOYEE_W2       PRIMARY KEY (EMP_ID)
       USING INDEX
TABLESPACE EMPLOYEE_INDEXES,
CONSTRAINT emp_to_empw2       FOREIGN KEY  (EMP_ID)
               REFERENCES EMPLOYEE)
CLUSTER EMP (EMP_ID)
TABLESPACE EMPLOYEES;

CREATE INDEX XFK1EMPLOYEE_W2
  ON EMPLOYEE_W2
(EMP_ID                         ASC)
TABLESPACE EMPLOYEE_INDEXES;
1.3 修改：
 
--修改存储参数:
ALTER CLUSTER EMP
PCTUSED 5 PCTFREE 90
SIZE 256K
STORAGE (NEXT 200K PCTINCREASE 10);
1.4 删除：
--备份聚集中表数据
CREATE TABLE new table AS SELECT * FROM cluster table

--删出聚集和表
drop cluster emp including tables

--删除表和约束
drop cluster emp including tables cascade constraints

本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/Star_CHL/archive/2006/01/19/583998.aspx