oracle11g 索引簇表实验

具备的系统权限：create any cluster
1.创建索引簇表
CREATE CLUSTER emp_dept_cluster (deptno NUMBER(2))
SIZE 1024
TABLESPACE crm;

size参数 影响着一个数据块上聚簇键的多少.也是影聚簇表的空间利用率.
指定估计平均簇键，以及与其相关的行所需的字节数。


2.创建索引簇索引(必须)
CREATE INDEX idx_emp_dept_cluster
ON CLUSTER emp_dept_cluster
TABLESPACE crm；

3.创建EMPLOYEE、DEPARTMENT表，并指定索引簇表
CREATE TABLE employee (
empno number(4),ename varchar2(10),job varchar2(10),salary number(6,2),deptno number(2))
CLUSTER emp_dept_cluster (deptno);

CREATE TABLE department (deptno number(2),dname varchar2(15),location varchar2(12))
CLUSTER emp_dept_cluster (deptno);
查看相应的数据段，块等占用(其中有个参数叫freelist其查看相关的未及HWM，数据块还有多少空间可用）
select segment_name,segment_type,bytes/1024/1024 as MB,blocks,extents from user_segments
where tablespace_name='CTM';

SEGMENT_NAME SEGMENT_TYPE MB BLOCKS EXTENTS
-------------------- ------------------ ---------- ---------- ----------
E1 CLUSTER 5 640 1
IN1 INDEX 5 640 1
写相应的循环语句继续向employee表插入数据，观察相应的数据块，
declare n number;
begin
for n in 1..1000000
loop
insert into employee values('7934','miller','1300','clerk',20,'01-JUN-1998');
end loop;
end;
查看相应的数据段，证明其相应的共享簇键数据存储与相同的分区。
SQL> select segment_name,tablespace_name,segment_type,blocks,bytes from user_segments;

SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE BLOCKS
-------------------- -------------------- ------------------ ----------
BYTES
----------
DEPT USERS TABLE 8
65536

EMP2 USERS TABLE 8
65536

SALGRADE USERS TABLE 8
65536


SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE BLOCKS
-------------------- -------------------- ------------------ ----------
BYTES
----------
EMP ZHANG_DATA TABLE 8
65536

EMP4 USERS TABLE 4608
37748736

E1 CTM CLUSTER 5120
41943040


SEGMENT_NAME TABLESPACE_NAME SEGMENT_TYPE BLOCKS
-------------------- -------------------- ------------------ ----------
BYTES
----------
PK_DEPT USERS INDEX 8
65536

PK_EMP USERS INDEX 8
65536

IN1 CTM INDEX 640
5242880

SQL> select table_name,cluster_name,tablespace_name,num_rows,blocks from user_tables;

TABLE_NAME CLUSTER_NA TABLESPACE NUM_ROWS BLOCKS
-------------------- ---------- ---------- ---------- ----------
DEPT USERS 4 5
EMP2 USERS 5 5
BONUS USERS 0 0
SALGRADE USERS 5 5
EMP ZHANG_DATA 7 4
EMP4 USERS 851968 4528
EMPLOYEE E1 CTM
DEPARTMENT E1 CTM
DEPARTMENT

4.删除索引簇
DROP CLUUSTER emp_dept_cluster INCLUDING TABLES ;

drop cluster emp_dept_cluster; -- 仅适用于删除空簇
drop cluster emp_dept_cluster including tables ; -- 删除簇和簇表
drop cluster emp_dept_cluster including tables cascade constraints ; -- 同时删除外键约束

4.修改索引簇
ALTER CLUSTER emp_dept_cluster SIZE 20M
TABLESPACE crm
PARALLEL 2
STORAGE (initial 2k next 3k minextents3 maxextents 5 initrans 6 pctincrease 6);

修改簇属性
可以修改的簇属性包括：
* PCTFREE、PCTUSED、INITRANS、MAXTRANS、STORAGE
* 为了存储簇键值所有行所需空间的平均值SIZE
* 默认并行度

注：
* 不能修改INITIAL和MINEXTENTS的值
* PCTFREE、PCTUSED、SIZE参数修改后适用于所有数据块
* INITRANS、MAXTRANS仅适用于以后分配的数据块
* STORAGE参数修改后仅影响以后分配给簇的盘区


注意:
在删除cluster的时候,首先必须删除参与该cluster的表或者使用including tables子句.不能从正在使用的cluster删除表.

簇相关的视图：
DBA_CLUSTERS
ALL_CLUSTERS
USER_CLUSTERS

DBA_CLU_COLUMNS
USER_CLU_COLUMNS


INSERT INTO employee
SELECT empno,ename,job,sal,deptno FROM EMP;


COL SEGMENT_NAME FORMAT A22
SELECT SEGMENT_NAME,SEGMENT_TYPE,EXTENT_ID,BYTES,BLOCKS FROM user_extents;


检验DEPTNO列数据物理存储位置：
DEPARTMENT表的物理存储地址：
SQL> select rowid,d.* from department d;
ROWID DEPTNO DNAME LOCATION
------------------------------- ---------- --------------------- -------------------------
AAASSJAAJAAAAH7AAA 10 ACCOUNTING NEW YORK
AAASSJAAGAAAAIAAAA 30 SALES CHICAGO
AAASSJAAGAAAAIFAAA 20 RESEARCH DALLAS
AAASSJAAGAAAAIGAAA 40 OPERATIONS BOSTON


select rowid,empno,ename,job,salary,deptno from employee
where rowid='AAASSJAAJAAAAH7AAA';

索引聚簇表使用注意：
如果表只是偶尔被连接或者它们的公共列经常被修改，则不要聚簇表。
（修改记录的聚簇键值比在非聚簇的表中修改此值要花费更多的时间，因为Oracle必须将修改的记录移植到其他的块中以维护聚簇）。

如果经常需要在一个表上进行完全搜索，则不要聚簇这个表（对一个聚簇表进行完全搜索比在非聚簇表上进行完全搜索的时间长，Oracle可能要读更多的块，因为表是被一起存储的。）

如果经常从一个父表和相应的子表中查询记录，则考虑给1对多（1:*）关系创建聚簇表。（子表记录存储在与父表记录相同的数据块中，因此当检索它们时可以同时在内存中，因此需要Oracle完成较少的I/O）。

如果经常查询同一个父表中的多个子记录，则考虑单独将子表聚簇。（这样提高了从相同的父表查询子表记录的性能，而且也没有降低对父表进行完全搜索的性能）。

如果从所有有相同聚簇键值的表查询的数据超过一个或两个Oracle块，则不要聚簇表。（要访问在一个聚簇表中的记录，Oracle读取所有包含那个记录值的全部数据块，如果记录占据了多个数据块，则访问一个记录需要读的次数比一个非聚簇的表中访问相同的记录读的次数要多）。

散列簇表：
当经常使用有相同列的包含相等条件的查询子句访问表时，考虑使用哈希聚簇来存储表。使用这些列作为聚簇键。

如果可以确定存放具有给定聚簇键值的所有记录所需的空间（包括现在的和将来的），则将此表以哈希聚簇存储。

如果空间不够，并且不能为将要插入的新记录分配额外的空间，那么不要使用哈希聚簇。

如果偶尔创建一个新的、很大的哈希聚簇来保存这样的表是不切实际的，那么不要用哈希聚簇存储经常增长的表。

如果经常需要进行全表搜索，并且必须要为表的预期增长中的哈希聚簇分配足够的空间，则不要将此表以哈希聚簇存储。（这样的完全检索必须要读分配给哈希聚簇的全部块，即使有些块可能只包含很少的记录。单独地存储表将减少由完全的表检索读取的块的数量。）

如果你的应用程序经常修改聚簇键的值，则不要将表以哈希聚簇方式存储。

不管这个表是否经常与其他表连接，只要进行哈希对于基于以前的指定的表是合适的，那么在哈希聚簇中存储一个表可能是有用的。