详解ORACLE簇表堆表IOT表分区表.doc
《详解ORACLE簇表堆表IOT表分区表.doc》由会员分享,可在线阅读,更多相关《详解ORACLE簇表堆表IOT表分区表.doc(19页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、.详解ORACLE簇表、堆表、IOT表、分区表簇和簇表 簇其实就是一组表,是一组共享相同数据块的多个表组成。 将经常一起使用的表组合在一起成簇可以提高处理效率。 在一个簇中的表就叫做簇表。建立顺序是:簇簇表数据簇索引 1、创建簇的格式 CREATE CLUSTER cluster_name (column date_type ,column datatype.) PCTUSED 40 | integer PCTFREE 10 | integer SIZE integer INITRANS 1 | integer MAXTRANS 255 | integer TABLESPACE tablesp
2、ace STORAGE storage SIZE:指定估计平均簇键,以及与其相关的行所需的字节数。 2、创建簇 create cluster my_clu (deptno number ) pctused 60 pctfree 10 size 1024 tablespace users storage ( initial 128 k next 128 k minextents 2 maxextents 20 ); 3、创建簇表 create table t1_dept( deptno number , dname varchar2 ( 20 ) ) cluster my_clu(deptno)
3、; create table t1_emp( empno number , ename varchar2 ( 20 ), birth_date date , deptno number ) cluster my_clu(deptno); 4、为簇创建索引 create index clu_index on cluster my_clu; 注:若不创建索引,则在插入数据时报错:ORA-02032: clustered tables cannot be used before the cluster index is built 管理簇 使用ALTER修改簇属性(必须拥有ALTER ANY CLU
4、STER的权限) 1、修改簇属性 可以修改的簇属性包括: * PCTFREE、PCTUSED、INITRANS、MAXTRANS、STORAGE * 为了存储簇键值所有行所需空间的平均值SIZE * 默认并行度 注: * 不能修改INITIAL和MINEXTENTS的值 * PCTFREE、PCTUSED、SIZE参数修改后适用于所有数据块 * INITRANS、MAXTRANS仅适用于以后分配的数据块 * STORAGE参数修改后仅影响以后分配给簇的盘区 格式: alter cluster my_clu pctused 40 2、删除簇 drop cluster my_clu; - 仅适用于
5、删除空簇 drop cluster my_clu including tables ; - 删除簇和簇表 drop cluster my_clu including tables cascade constraints ; - 同时删除外键约束 注:簇表可以像普通表一样删除。 散列聚簇表 在簇表中,Oracle使用存储在索引中的键值来定位表中的行,而在散列聚簇表中,使用了散列函数代替了簇索引,先通过内部函数或者自定义的函数进行散列计算,然后再将计算得到的码值用于定位表中的行。创建散列簇需要用到HASHKEYS子句。 1、创建散列簇 create cluster my_clu_two(empno
6、 number(10) ) pctused 70 pctfree 10 tablespace users hash is empno hashkeys 150 ; 说明: * hash is 子句指明了进行散列的列,如果列是唯一的标示行,就可以将列指定为散列值 * hashkeys 指定和限制散列函数可以产生的唯一的散列值的数量 2、创建散列表 create table t2_emp ( empno number ( 10 ), ename varchar2 ( 20 ), birth_date date , deptno number ) cluster my_clu_two(empno);
7、 注意: * 必须设置数值的精度(具体原因不详) * 散列簇不能也不用创建索引 * 散列簇不能ALTER:size、hashkeys、hash is参数 堆表 1.基本概念执行CREATE TABLE语句时,默认得到的表类型就是堆组织表。其他类型的表结构需要在CREATE TABLE语句本身中指定它。堆组织表中,数据以堆的方式管理。增加数据时,会使用段中找到的第一个能放下此数据的自由空间。从表中删除数据后,允许以后的INSERT和UPDATE重用这部分空间。堆(heap)是一组空间,以一种随机的方式使用。因此,无法保证按照放入表中的顺序取得数据。有1个简单的技巧,来查看对于给定类型的表,CRE
8、ATE TABLE语句中主要有哪些可用的选项。首先,尽可能简单地创建表,然后使用DBMS_METADATA来查询这个表的定义。1. createtablet(xintprimarykey,yclob);2. 3. Tablecreated.4. 5. selectdbms_metadata.get_ddl(TABLE,T)fromdual;6. 7. DBMS_METADATA.GET_DDL(TABLE,T)8. -9. 10. CREATETABLETONY.T11. (XNUMBER(*,0),12. YCLOB,13. PRIMARYKEY(X)14. USINGINDEXPCTFRE
9、E10INITRANS2MAXTRANS255NOCOMPRESSLOGGING15. TABLESPACEUSERSENABLE16. )SEGMENTCREATIONDEFERRED17. PCTFREE10PCTUSED40INITRANS1MAXTRANS255NOCOMPRESSLOGGING18. TABLESPACEUSERS19. LOB(Y)STOREASBASICFILE(20. TABLESPACEUSERSENABLESTORAGEINROWCHUNK8192RETENTION21. NOCACHELOGGING)现在可以根据需要,修改某些参数。对于ASSM有3个重要选
10、项,对于MSSM有5个重要选项。随着本地管理表空间的引入(推荐做法),其余的参数已经没什么意义了。 FREELIST: 仅适用于MSSM。 PCTFREE:ASSM和MSSM都适用。 PCTUSED:仅适用于MSSM。 INITRANS:ASSM 和MSSM 都适合。为块初始分配的事务槽数。如果会对同样的块完成多个并发更新,就应该考虑增大这个值。 COMPRESS/NOCOMPRESS:ASSM 和MSSM 都适合。 11g之前,选项是COMPRESS或者NOCOMPRESS,只有直接路径操作(例如CREATE TABLE AS SELECT, INSERT /*+ APPEND*/, ALT
11、ER TABLE T MOVE以及SQL*Loader直接路径加载)才能利用压缩。 11g之后,选项是COMPRESS FOR OLTP,COMPRESS BASIC或者NOCOMPRESS。COMPRESS FOR OLTP启用所有操作的压缩(包括直接路径和常规路径),COMPRESS BASIC则只针对直接路径操作。注意:单独存储在LOB段中的LOB数据并不使用表的PCTFREE/PCTUSED参数设置。这些LOB块以不同的方式管理:它们总是会填入,直至达到最大容量,而且仅当完全为空时才返回FREELIST。2. 堆表总结堆表具有的唯一优点是 插入数据不需要采取任何措施,只需要顺其自然地安
12、装插入的顺序存储,减少了插入大量数据的代价。索引组织表IOT 1. 基本概念索引组织表(index organized table)简称IOT。IOT中,数据要根据主键有序地存储。适合使用IOT的几种情况: 表完全由主键组成或者只通过主键来访问一个表。使用IOT,表就是索引,可以节约空间,提高效率。 通过外键访问子表,子表使用IOT。通过IOT将相同外键的子表数据物理的存储在同一个位置,查询所需要的物理I/O更少,因为数据都在同一个(几个)块上。 经常在主键或者或惟一键上使用BETWEEN查询。数据以某种特定的顺序物理存储,所以获取这些数据时所需的物理I/O更少。查看创建IOT时候的参数选项。
13、1. createtablet(xintprimarykey,yclob)organizationindex;2. 3. Tablecreated.4. 5. selectdbms_metadata.get_ddl(TABLE,T)fromdual;6. 7. DBMS_METADATA.GET_DDL(TABLE,T)8. -9. 10. CREATETABLETONY.T11. (XNUMBER(*,0),12. YCLOB,13. PRIMARYKEY(X)ENABLE14. )ORGANIZATIONINDEXNOCOMPRESSPCTFREE10INITRANS2MAXTRANS25
14、5LOGGING15. STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS214748364516. PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULTFLASH_CACHEDE17. FAULTCELL_FLASH_CACHEDEFAULT)18. TABLESPACEUSERS19. PCTTHRESHOLD5020. LOB(Y)STOREASBASICFILE(21. TABLESPACEUSERSDISABLESTORAGEINROWCHUNK8192RETENTION2
15、2. NOCACHELOGGING23. STORAGE(INITIAL65536NEXT1048576MINEXTENTS1MAXEXTENTS214748364524. PCTINCREASE0FREELISTS1FREELISTGROUPS1BUFFER_POOLDEFAULTFLASH_CACHEDEFAULTCELL_FLASH_CAC25. HEDEFAULT)IOT没有PCTUSED子句,但是有PCTFREE。这是因为IOT中数据放在哪个块上不是根据未用空间大小,而是根据主键索引决定的。但是对于溢出段(下面会提到),PCTFREE和PCTUSED对于IOT的意义将与对于堆表一样。
16、索引压缩选项NOCOMPRESS对索引一般都可用,它和表压缩不同,它告诉Oracle把构成主键的每个值分别存储在各个索引条目中(也就是不压缩)。例如,主键在A、B和C列上,则A、B和C列中每一次出现的值都会物理地存储。NOCOMPRESS反过来就是COMPRESS N,在此N是一个整数,表示要压缩的列数。这样可以避免重复值,并在块级提取“公因子”(factor out)。例如如果使用COMPRESS 2,那么A和B列上如果有重复的值,就只会存储1次。使用索引压缩会减少物理I/O,但是占用更多的CPU时间来处理索引,需要在两者之间权衡。可以使用ANALYZE INDEX VALIDATE STR
17、UCTURE命令来得到最优的压缩方案。这个命令会填写一个名为INDEX_STATS的动态性能视图,其中最多只包含一行,即这个ANALYZE命令最后一次执行的信息。例如:1. createtableiot(owner,object_type,object_name,2. constraintiot_pkprimarykey(owner,object_type,object_name)3. organizationindexnocompress4. asselectdistinctowner,object_type,object_namefromall_objects;5. 6. analyzei
18、ndexiot_pkvalidatestructure;7. 8. selectlf_blks,br_blks,used_space,opt_cmpr_count,9. opt_cmpr_pctsavefromindex_stats;得到结果:1. LF_BLKSBR_BLKSUSED_SPACEOPT_CMPR_COUNTOPT_CMPR_PCTSAVE2. -3. 34832496647233LF_BLKS表示索引使用了348个叶子块(即数据所在的块);BR_BLKS表示索引使用了3个分支块(在索引结构中导航所用的块)来找到这些叶子块;USED_SPACE表示使用的空间大约是2496647
19、字节;OPT_CMPR_COUNT(最优压缩数)表示最优的压缩设置为COMPRESS 2;OPT_CMPR_PCTSAVE(最优的节省压缩百分比)表示如果设置为COMPRESS 2,就能节省大约33%的存储空间。可以用alter table iot move compress 2;来重建IOT,之后ANALYZE INDEX,可以看到压缩后的效果如下:1. LF_BLKSBR_BLKSUSED_SPACEOPT_CMPR_COUNTOPT_CMPR_PCTSAVE2. -3. 2331167191420溢出段(OVERFLOW子句)索引一般在表的一个列子集上。通常索引块上的行数比表块上的行数会
20、多很多,这对索引是有利的,否则Oracle需要花费大量的时间来维护索引,因为每个INSERT或UPDATE都可能导致索引块分解,以容纳新数据。OVERFLOW子句允许你建立另一个段(这就使得IOT成为一个多段对象),根据我们的设定,IOT的行数据的一部分可以溢出到这个段中,这样可以让索引叶子块(包含具体索引数据的块)能够高效地存储数据。需要注意,构成主键的列不能溢出,它们必须直接放在叶子块上。建立IOT时,最关键的是适当地分配数据,即哪些数据存储在索引块上,哪些数据存储在溢出段上。使用溢出段的条件可以采用两种方式来指定: PCTTHRESHOLD行中的数据量超过块的这个百分比时,行中余下的列将
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 详解 oracle 簇表堆表 iot 分区表
限制150内