堆表、索引组织表、索引表簇Oracle SQL 071-2

堆表、索引组织表、索引表簇 如何实践应用

1. 堆表	单表行顺序是随机	最长用	create table ttt1(id number primary key,n varchar2(5)); insert into ttt1 values(1,'a'); insert into ttt1 values(3,'c'); insert into ttt1 values(2,'b'); select * from ttt1;     ID N ---------- ----------      1 a      3 c      2 b
   索引组织的表	单表行按PK顺序写	更好读取性能	create table tt2(id number primary key,n varchar2(5))organization index; insert into tt2 values(1,'a'); insert into tt2 values(3,'c'); insert into tt2 values(2,'b'); select * from tt2; 1 a 2 b 3 c
   索引表簇	关联表的行存相同物理block	减少关联表查询数据所需的磁盘读取	表簇create cluster CLU_INFO_DEPEMP (deptno number) size 1024;（若block为8k就可以放7个表簇，因为pct_free;大空间浪费小影响聚合） 簇表create table clu_info_dept(DEPTNO number,DNAME VARCHAR2(14),LOC VARCHAR2(13)) cluster CLU_INFO_DEPEMP(DEPTNO); create table clu_info_emp(DEPTNO number,ENAME VARCHAR2(10),JOB VARCHAR2(9)) cluster CLU_INFO_DEPEMP(DEPTNO); 表簇索引create index CLU_INFO_index on cluster CLU_INFO_DEPEMP; 索引之后才能 insert into clu_info_dept values(10,'IT','SH'); insert into clu_info_dept values(10,'IT2','SH'); insert into clu_info_dept values(5,'IT3','SH'); insert into clu_info_emp values(10,'Peter','ITManager'); select deptno,dname,ename,loc,job from clu_info_dept natural join clu_info_emp; 其中cluster table如clu_info_dept，clu_info_emp仅仅存储在cluster中，不占用segment空间。 select SEGMENT_NAME,SEGMENT_TYPE, extents, HEADER_FILE, HEADER_BLOCK, BYTES, BLOCKS from dba_segments where owner='HR' and segment_name like '%CLU_INFO%';
   哈希表簇
   分区表