oracle聚簇和聚簇索引

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