Oracle聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点。非聚簇索引的顺序与数据物理排列顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。一个表最多只能有一个聚簇索引。
聚簇是一种存储表的方法，这些表密切相关并经常一起连接进磁盘的同一区域。例如，表 BOOKSHELF 和BOOKSHELF_AUTHOR 数据行可以一起插入到称为簇（Cluster）的单个区域中，而不是将两个表放在磁盘上的不同扇区上。
簇键（Cluster Key）可以是一列或多列，通过这些列可以将这些表在查询中连接起来（例如，BOOKSHELF表和BOOKSHELF_AUTHOR表中的 Title列）。为了将表聚集在一起，必须拥有这些将要聚集在一起的表。
对于几个表密切相关并经常一起通过某个字段连接的比较实用：
实验1：
create cluster BOOKandAUTHOR (Col1 VARCHAR2(100));
create table BOOKSHELF
(Title VARCHAR2(100) primary key,
Publisher VARCHAR2(20),
CategoryName VARCHAR2(20),
Rating VARCHAR2(2)
)
cluster BOOKandAUTHOR(Title);
在向BOOKSHELF表中插入数据行之前，必须建立一个Oracle聚簇索引：
create index BOOKandAUTHORndx on cluster BOOKandAUTHOR;
insert into BOOKSHELF values('aaa','sdf','sdfds','aa');
create table BOOKSHELF_AUTHOR
(Title VARCHAR2(100) primary key,
AuthorName VARCHAR2(50)
)
cluster BOOKandAUTHOR (Title);
insert into BOOKSHELF_AUTHOR values('sdfds','sdfds');
insert into BOOKSHELF_AUTHOR values('aaa','sdfds');
select rowid,t.* from BOOKSHELF_AUTHOR t;
AAAjbUAAEAAHsDcAAA
AAAjbUAAEAAHsDdAAA
select rowid,t.* from BOOKSHELF t;
AAAjbUAAEAAHsDdAAA
当这两个表被聚在一起时，每个唯一的Title在簇中实际只存储一次。对于每个Title，都从这两个表中附加列。
来自这两个表的数据实际上存放在一个位置上，就好像簇是一个包含两个表中的所有数据的大表一样。
select segment_name,file_id,block_id,blocks from dba_extents where
segment_name ='BOOKANDAUTHOR';
1 BOOKANDAUTHOR 4 2015448 8
select rowid,dbms_rowid.rowid_relative_fno(rowid),dbms_rowid.rowid_block_number(rowid),dbms_rowid.rowid_row_number(rowid),t.* from BOOKSHELF t;
1 AAAjbUAAEAAHsDdAAA 4 2015453 0 aaa sdf sdfds aa
select * from BOOKSHELF_AUTHOR a ,BOOKSHELF b where a.title=b.title and a.title='aaa';
select * from v$mystat where rownum<2;
select spid from v$process t where t.ADDR in (select s.PADDR from v$session s where sid='1193');
alter system dump datafile 4 block min 2015453 block max 2015453;
可以dump出来看看块内的内容。两个表的行确实存在一个块中。两个表没有单独的段，段在聚簇上。
实验2：
参考sys.TAB$ sys.IND$ 建聚簇。
create cluster clu_obj (OBJ# number);
CREATE TABLE t_obj_test
( OBJ# NUMBER NOT NULL ENABLE,
DATAOBJ# NUMBER,
TS# NUMBER NOT NULL ENABLE,
FILE# NUMBER NOT NULL ENABLE,
BLOCK# NUMBER NOT NULL ENABLE,
BOBJ# NUMBER,
TAB# NUMBER,
COLS NUMBER NOT NULL ENABLE,
CLUCOLS NUMBER,
PCTFREE$ NUMBER NOT NULL ENABLE,
PCTUSED$ NUMBER NOT NULL ENABLE,
INITRANS NUMBER NOT NULL ENABLE,
MAXTRANS NUMBER NOT NULL ENABLE,
FLAGS NUMBER NOT NULL ENABLE,
AUDIT$ VARCHAR2(38) NOT NULL ENABLE,
ROWCNT NUMBER,
BLKCNT NUMBER,
EMPCNT NUMBER,
AVGSPC NUMBER,
CHNCNT NUMBER,
AVGRLN NUMBER,
AVGSPC_FLB NUMBER,
FLBCNT NUMBER,
ANALYZETIME DATE,
SAMPLESIZE NUMBER,
DEGREE NUMBER,
INSTANCES NUMBER,
INTCOLS NUMBER NOT NULL ENABLE,
KERNELCOLS NUMBER NOT NULL ENABLE,
PROPERTY NUMBER NOT NULL ENABLE,
TRIGFLAG NUMBER,
SPARE1 NUMBER,
SPARE2 NUMBER,
SPARE3 NUMBER,
SPARE4 VARCHAR2(1000),
SPARE5 VARCHAR2(1000),
SPARE6 DATE
)cluster clu_obj(OBJ#);
CREATE TABLE t_ind_test
( OBJ# NUMBER NOT NULL ENABLE,
DATAOBJ# NUMBER,
TS# NUMBER NOT NULL ENABLE,
FILE# NUMBER NOT NULL ENABLE,
BLOCK# NUMBER NOT NULL ENABLE,
BO# NUMBER NOT NULL ENABLE,
INDMETHOD# NUMBER NOT NULL ENABLE,
COLS NUMBER NOT NULL ENABLE,
PCTFREE$ NUMBER NOT NULL ENABLE,
INITRANS NUMBER NOT NULL ENABLE,
MAXTRANS NUMBER NOT NULL ENABLE,
PCTTHRES$ NUMBER,
TYPE# NUMBER NOT NULL ENABLE,
FLAGS NUMBER NOT NULL ENABLE,
PROPERTY NUMBER NOT NULL ENABLE,
BLEVEL NUMBER,
LEAFCNT NUMBER,
DISTKEY NUMBER,
LBLKKEY NUMBER,
DBLKKEY NUMBER,
CLUFAC NUMBER,
ANALYZETIME DATE,
SAMPLESIZE NUMBER,
ROWCNT NUMBER,
INTCOLS NUMBER NOT NULL ENABLE,
DEGREE NUMBER,
INSTANCES NUMBER,
TRUNCCNT NUMBER,
SPARE1 NUMBER,
SPARE2 NUMBER,
SPARE3 NUMBER,
SPARE4 VARCHAR2(1000),
SPARE5 VARCHAR2(1000),
SPARE6 DATE
) cluster clu_obj(OBJ#);
create index clu_objidx on cluster clu_obj;
insert into t_obj_test select * from sys.TAB$;
insert into t_ind_test select * from sys.IND$;
select * from t_obj_test o where rownum<5;
select * from t_ind_test o where rownum<5 for update; 修改一些值使他们有相等的。
analyze table t_obj_test compute statistics;
analyze table t_ind_test compute statistics;
select * from t_obj_test o ,t_ind_test i where o.obj#=i.obj# and o.obj#=17;
执行计划：
SELECT STATEMENT, GOAL = ALL_ROWS 3 1 215
MERGE JOIN CARTESIAN 3 1 215
TABLE ACCESS CLUSTER SPUSER T_IND_TEST 2 1 83
INDEX UNIQUE SCAN SPUSER CLU_OBJIDX 1 1 "I"."OBJ#"=17
BUFFER SORT 1 1 132
TABLE ACCESS CLUSTER SPUSER T_OBJ_TEST 1 1 132 "O"."OBJ#"=17
select * from t_obj_test o ,t_ind_test i where o.obj#=i.obj# and o.obj#<200;
执行计划：
SELECT STATEMENT, GOAL = ALL_ROWS 9 11 2365
HASH JOIN 9 11 2365 "O"."OBJ#"="I"."OBJ#"
TABLE ACCESS CLUSTER SPUSER T_IND_TEST 4 27 2241
INDEX RANGE SCAN SPUSER CLU_OBJIDX 2 27 "I"."OBJ#"<200
TABLE ACCESS CLUSTER SPUSER T_OBJ_TEST 4 12 1584
INDEX RANGE SCAN SPUSER CLU_OBJIDX 2 12 "O"."OBJ#"<200
select * from t_obj_test o ,t_ind_test i where o.obj#=i.obj# ;
执行计划：
SELECT STATEMENT, GOAL = ALL_ROWS 9081 8647 1859105
MERGE JOIN 9081 8647 1859105
TABLE ACCESS CLUSTER SPUSER T_IND_TEST 826 19983 1658589
INDEX FULL SCAN SPUSER CLU_OBJIDX 26 19983
SORT JOIN 8255 8647 1141404 "O"."OBJ#"="I"."OBJ#" "O"."OBJ#"="I"."OBJ#"
TABLE ACCESS FULL SPUSER T_OBJ_TEST 7995 8647 1141404
select * from t_obj_test o where o.obj#=59 ;
执行计划：
SELECT STATEMENT, GOAL = ALL_ROWS 2 1 132
TABLE ACCESS CLUSTER SPUSER T_OBJ_TEST 2 1 132
INDEX UNIQUE SCAN SPUSER CLU_OBJIDX 1 1 "O"."OBJ#"=59
select * from t_obj_test o where o.dataobj#=59 ;
执行计划：
SELECT STATEMENT, GOAL = ALL_ROWS 7995 1 132
TABLE ACCESS FULL SPUSER T_OBJ_TEST 7995 1 132 "O"."DATAOBJ#"=59
create index idx_obj_test_n1 on t_obj_test(dataobj#);
select * from t_obj_test o where o.dataobj#=59 ;
执行计划：
SELECT STATEMENT, GOAL = ALL_ROWS 3 1 132
TABLE ACCESS BY INDEX ROWID SPUSER T_OBJ_TEST 3 1 132
INDEX RANGE SCAN SPUSER IDX_OBJ_TEST_N1 1 1 "O"."DATAOBJ#"=59
