详解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行中的数据量超过块的这个百分比时，行中余下的列将

21、存储在溢出段中。例如，如果PCTTHRESHOLD是10%，而块大小是8KB，长度大于800字节的行就会把其中一部分存储在溢出段中。Oracle会从最后一列开始向前查找，直到不包括主键 的最后一列，得出哪些列需要存储在溢出段中。例如：create table iot1 (x int primary key, y date, z varchar2(2000)organization index pctthreshold 10 overflow; INCLUDING行中从第一列直到INCLUDING子句所指定列（也包括这一列）的所有列都存储在索引块上，余下的列存储在溢出段中。例如：create t

22、able iot2 (x int primary key, y date, z varchar2(2000)organization index including y overflow;IOT上建立索引IOT本身可以再建索引，就像在索引之上再加索引，这称为二次索引（secondary index）。 IOT上的索引可以使用非主键列提供IOT数据的快速、高效访问。正常情况下，索引包含了所指向的行的物理地址，即rowid。而IOT二次索引无法做到这一点。因为IOT中的行可以大量移动，而且它不像堆组织表中的行那样“迁移”。IOT中的行肯定在索引结构中的每个位置上，这取决于它的主键值；索引本身的大小

23、和形状发生改变时行就会移动。因此，Oracle引入了一个逻辑rowid（logical rowid），这些逻辑rowid根据IOT主键建立。因此，与常规表相比，IOT上的索引效率稍低。分区表一、分区表：随着表的不断增大，对于新纪录的增加、查找、删除等(DML)的维护也更加困难。对于数据库中的超大型表，可通过把它的数据分成若干个小表，从而简化数据库的管理活动。对于每一个简化后的小表，我们称为一个单个的分区。对于分区的访问，我们不需要使用特殊的SQL查询语句或特定的DML语句，而且可以单独的操作单个分区，而不是整个表。同时可以将不同分区的数据放置到不同的表空间，比如将不同年份的销售数据，存放在不同

24、的表空间，即年的销售数据存放到TBS_2001,2002年的销售数据存放到TBS_2002,依次类推，从而实现了分散存储，这将大大的简化大容量表的管理，提高查询性能及I/O并发等。对于外部应用程序来说，虽然存在不同的分区，且数据位于不同的表空间，但逻辑上仍然是一张表 可以使用SQL*Loader，IMPDP，EXPDP，Import，Export等工具来装载或卸载分区表中的数据关于分区表的功能实际上同SQL server 中的分区表是同样的概念，只不过SQL server中的数据存放到了文件组，相当于Oracle概念中的表空间，二、何时分区 当表达到GB大小且继续增长需要将历史数据和当前的数据

25、分开单独处理，比如历史数据仅仅需要只读，而当前数据则实现DML三、分区的条件及特性 共性：不同的分区之间必须有相同的逻辑属性，比如表名，列名，数据类型，约束等，个性：各个分区可以有不同的物理属性，比如pctfree, pctused, and tablespaces.分区独立性：即使某些分区不可用，其他分区仍然可用。特殊性：含有LONG、LONGRAW数据类型的表不能进行分区四、分区的优点1、提高查询性能：只需要搜索特定分区，而非整张表，提高查询速度2、节约维护时间：单个分区的数据装载，索引重建，备份，维护等将远小于整张表的维护时间。3、节约维护成本：可以单独备份和恢复每个分区4、均衡I/O：

26、将不同的分区映射到不同的磁盘以平衡I/O，提高并发 五、ORACLE分区类型：范围分区、散列分区、列表分区、组合分区可以对索引和表分区，全局索引只能按范围分区，可以将其定义在任何类型的分区或非分区表上。通常全局索引比局部索引需要更多的维护局部索引与基础表是等同分区的，用于反映其基础表的结构1.Range分区：行映射到基于列值范围的分区Range 分区，又成为范围分区，基于分区键值的范围将数据映射到所建立的分区上。这是最通用的分区类型。创建范围分区时，必须指定以下内容分区方法：range分区列标识分区边界的分区描述使用Range 分区的时候，要记住几条规则：每个分区都包含VALUES LESS

27、THAN字名，定义了分区的上层边界。任何等于和大于分区键值的二进制值都被添加到下一个高层分区中。所有的分区，除了第一个，如果低于VALUES LESS THAN所定义的下层边界，都放在前面的分区中。MAXVALUE可以用来定义最高层的分区。MAXVALUE表示了虚拟的无限值示例：create table sal_range (salesman_id number(5),salesman_name varchar2(30),sales_amount number(10),sales_date date)partition by range (sales_date) -创建基于日期的范围分区并存储

28、到不同的表空间(partition sal_jan2000 values less than(to_date(02/01/2000,DD/MM/YYYY) tablespace sal_range_jan2000,partition sal_feb2000 values less than(to_date(03/01/2000,DD/MM/YYYY) tablespace sal_range_feb2000,partition sal_mar2000 values less than(to_date(04/01/2000,DD/MM/YYYY) tablespace sal_range_mar

29、2000,partition sal_apr2000 values less than(to_date(05/01/2000,DD/MM/YYYY) tablespace sal_range_apr2000);create table r -创建基于值范围的分区，分区子句未指定表空间时则位于缺省的表空间(a int) partition by range (a)(partition p1 values less than (10),partition p2 values less than (20),partition p3 values less than (30),partition p4

30、 values less than (maxvalue);select * from r partition (p1) -查看分区中的数据一个分区的损坏不会影响其它分区的数据：alter table r drop partiton p1select * from r select * from r partition (p4)除分区数据不见外，其它都正常partition by 用于指定分区方式range 表示分区的方式是范围划分partition pn 用于指定分区的名字values less than 指定分区的上界（上限）添加分区：ALTER TABLE r add partition

31、p5 values less than (xxx ) tablespace xx;查看分区表相关信息：SELECT table_name,partition_name,subpartition_count,tablespace_name,user_stats from user_tab_partitions;获取创建分区表的元数据： set long 10000 select dbms_metadata.get_ddl(TABLE,R,SCOTT) from dual; 表 表名 用户名 区分大小写2.Hash分区：散列分区Hash分区能够很容易对数据进行分区，因为语法很简单，很容易实现。在下

32、面这种情况下，使用hash分区比range分区更好：事先不知道需要将多少数据映射到给定范围的时候分区的范围大小很难确定，或者很难平衡的时候Range分区使数据得到不希望的聚集时性能特性，如并行DML、分区剪枝和分区连接很重要的时候创建散列分区时，必须指定以下信息分区方法：hash分区列分区数量或单独的分区描述分裂、删除和合并分区不能应用于Hash分区，但是，Hash分区能够合并和添加。创建hash分区有两种方法：一种方法是指定分区数量，另一种方法是指定分区的名字，但两者不能同时指定。方法一：指定分区数量create table dept2 (deptno number,deptname var

33、char2(32)partition by hash(deptno) partitions 4;方法二：指定分区的名字create table dept3 (deptno number,deptname varchar2(32)partition by hash(deptno) (partition p1 tablespace p1,partition p2 tablespace p2);create table sales_hash(salesman_id number(5),salesman_name varchar2(30),sales_amount number(10),week_no

34、 number(2)partition by hash (salesman_id)partitions 4store in (data1,data2,data3,data4)data1,data2,data3,data4 为表空间名。散列分区表的每个分区都被存储在单独的段中。3.List分区：列表分区List分区可以控制如何将行映射到分区中去。可以在每个分区的键上定义离散的值不同于Range分区和Hash分区，Range分区与分区相关联，为分区列假设了一个值的自然范围，故不可能将该值的范围以外的分区组织到一起。hash分区时不允许对数据的划分进行控制，因为系统使用的是散列函数来划分数据的。Li

35、st分区的优点在于按照自然的方式将无序和不相关的数据集合分组。List分区不支持多列分区，如果将表按列分区，那么分区键就只能有表的一个单独列组成。Range分区和Hash分区可以对多列进行分区。List分区时必须指定的以下内容分区方法：list分区列分区描述，每个描述指定一串文字值(值的列表),它们是分区列(它们限定将被包括在分区中的行)的离散值示例：create table sales_list(salesman_id number(5),salesman_name varchar2(30),sales_state varchar2(20),sales_amount number(10),s

36、ales_date date)partition by list (sales_state)(partition sales_west values (California,Hawaii) tablespace x,partition sales_east values (New York,Virginia) tablespace y,partition sales_central values (Texas,Illinois) tablespace z,partition sales_other values(DEFAULT) tablespace o);添加分区： alter table

37、sales3 add partition hk values (HK) tablespace xx4.Composite Partitioning：合成分区、组合分区组合分区使用range方法分区，在每个子分区中使用hash方法进行再分区。组合分区比range分区更容易管理，充分使用了hash分区的并行优势。组合分区支持历史数据和条块数据两者。如添加新的RANGE分区，同时为DML操作提供更高层的并行性。创建组合分区时，需要指定如下内容：分区方法：range分区列标识分区边界的分区描述子分区方法：hash子分区列每个分区的子分区数量，或子分区的描述create table sales_comp

38、osite(salesman_id number(5),salesman_name varchar2(30),sales_amount number(10),sales_date date)partition by range(sales_date)subpartition by hash(salesman_id)subpartitions 4store in (tbs1,tbs2,tbs3,tbs4)(partition sales_jan2000 values less than(to_date(02/01/2000,DD/MM/YYYY),partition sales_feb2000

39、values less than(to_date(03/01/2000,DD/MM/YYYY),partition sales_mar2000 values less than(to_date(04/01/2000,DD/MM/YYYY);create table T_TRACK (N_TRACK_ID NUMBER(20) NOT NULL, C_COMP_CDE VARCHAR2(6),T_TRACK_TM DATE NOT NULL,C_CAR_NO VARCHAR2(50) )partition by range(T_TRACK_TM)subpartition by list(C_CO

40、MP_CDE)(partition P_2009_11 values less than (to_date(2009-12-01,yyyy-MM-dd)(subpartition P_2009_11_P1013 values(P1013);六、表分区后的相关操作1.添加分区alter table T_TRACK add partition P_2005_04 values less than(to_date(2005-05-01,yyyy-MM-dd)( subpartition P_2005_04_P1013 values(P1013), subpartition P_2005_04_P1013 values(P1014), subpartition P_2005_04_P1013 values(P1015),