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
数据库(database)
数据库连接(database link)
函数(function)
索引(table)
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
数据库(database)
数据库连接(database link)
函数(function)
索引(table)