详解ORACLE簇表堆表IOT表分区表.docx

详解ORACLE簇表堆表IOT表分区表.docx

《详解ORACLE簇表堆表IOT表分区表.docx》由会员分享，可在线阅读，更多相关《详解ORACLE簇表堆表IOT表分区表.docx（25页珍藏版）》请在冰点文库上搜索。

详解ORACLE簇表堆表IOT表分区表

详解ORACLE簇表、堆表、IOT表、分区表

簇和簇表

   簇其实就是一组表，是一组共享相同数据块的多个表组成。

将经常一起使用的表组合在一起成簇可以提高处理效率。

   在一个簇中的表就叫做簇表。

建立顺序是：

簇→簇表→数据→簇索引

   1、创建簇的格式

   CREATECLUSTERcluster_name

   （columndate_type[,columndatatype]...）

   [PCTUSED40|integer][PCTFREE10|integer]

   [SIZEinteger]

   [INITRANS1|integer][MAXTRANS255|integer]

   [TABLESPACEtablespace]

   [STORAGEstorage]

   SIZE：

指定估计平均簇键，以及与其相关的行所需的字节数。

   2、创建簇

   createclustermy_clu（deptnonumber）

   pctused60

   pctfree10

   size1024

   tablespaceusers

   storage（

initial128k

next128k

minextents2

maxextents20

   ）;

   3、创建簇表

   createtablet1_dept（

deptnonumber,

dnamevarchar2（20）

   ）

   clustermy_clu（deptno）;

   createtablet1_emp（

empnonumber,

enamevarchar2（20）,

birth_datedate,

deptnonumber

   ）

   clustermy_clu（deptno）;

   4、为簇创建索引

   createindexclu_indexonclustermy_clu;

   注：

若不创建索引，则在插入数据时报错：

ORA-02032:

clusteredtablescannotbeusedbeforetheclusterindexisbuilt

管理簇

   使用ALTER修改簇属性（必须拥有ALTERANYCLUSTER的权限）

   1、修改簇属性

   可以修改的簇属性包括：

   *PCTFREE、PCTUSED、INITRANS、MAXTRANS、STORAGE

   *为了存储簇键值所有行所需空间的平均值SIZE

   *默认并行度

   注：

   *不能修改INITIAL和MINEXTENTS的值

   *PCTFREE、PCTUSED、SIZE参数修改后适用于所有数据块

   *INITRANS、MAXTRANS仅适用于以后分配的数据块

   *STORAGE参数修改后仅影响以后分配给簇的盘区

   格式：

   alterclustermy_clu

   pctused40

   2、删除簇

   dropclustermy_clu;--仅适用于删除空簇

   dropclustermy_cluincludingtables;--删除簇和簇表

   dropclustermy_cluincludingtablescascadeconstraints;

   --同时删除外键约束

   注：

簇表可以像普通表一样删除。

散列聚簇表

   在簇表中，Oracle使用存储在索引中的键值来定位表中的行，而在散列聚簇表中，使用了散列函数代替了簇索引，先通过内部函数或者自定义的函数进行散列计算，然后再将计算得到的码值用于定位表中的行。

创建散列簇需要用到HASHKEYS子句。

   1、创建散列簇

   createclustermy_clu_two（empnonumber（10））

   pctused70

   pctfree10

   tablespaceusers

   hashisempno

   hashkeys150;

   说明：

   *hashis子句指明了进行散列的列，如果列是唯一的标示行，就可以将列指定为散列值

   *hashkeys指定和限制散列函数可以产生的唯一的散列值的数量

   2、创建散列表

   createtablet2_emp（

empnonumber（10）,

enamevarchar2（20）,

birth_datedate,

deptnonumber）

   clustermy_clu_two（empno）;

   注意：

   *必须设置数值的精度（具体原因不详）

   *散列簇不能也不用创建索引

   *散列簇不能ALTER:

size、hashkeys、hashis参数

堆表

1.基本概念

执行CREATETABLE语句时，默认得到的表类型就是堆组织表。

其他类型的表结构需要在CREATETABLE语句本身中指定它。

堆组织表中，数据以堆的方式管理。

增加数据时，会使用段中找到的第一个能放下此数据的自由空间。

从表中删除数据后，允许以后的INSERT和UPDATE重用这部分空间。

堆（heap）是一组空间，以一种随机的方式使用。

因此，无法保证按照放入表中的顺序取得数据。

有1个简单的技巧，来查看对于给定类型的表，CREATETABLE语句中主要有哪些可用的选项。

首先，尽可能简单地创建表，然后使用DBMS_METADATA来查询这个表的定义。

1.> create table t（x int primary key, y clob）;  

2.  

3.Table created.  

4.  

5.> select dbms_metadata.get_ddl（ 'TABLE', 'T' ） from dual;  

6.  

7.DBMS_METADATA.GET_DDL（'TABLE','T'）  

8.------------------------------------------------------------------------  

9.  

10.  CREATE TABLE "TONY"."T"  

11.   （    "X" NUMBER（*,0）,  

12.        "Y" CLOB,  

13.         PRIMARY KEY （"X"）  

14.  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 NOCOMPRESS LOGGING  

15.  TABLESPACE "USERS"  ENABLE  

16.   ） SEGMENT CREATION DEFERRED  

17.  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING  

18.  TABLESPACE "USERS"  

19. LOB （"Y"） STORE AS BASICFILE （  

20.  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192 RETENTION  

21.  NOCACHE LOGGING ）  

现在可以根据需要，修改某些参数。

对于ASSM有3个重要选项，对于MSSM有5个重要选项。

随着本地管理表空间的引入（推荐做法），其余的参数已经没什么意义了。

·FREELIST：

仅适用于MSSM。

·PCTFREE：

ASSM和MSSM都适用。

·PCTUSED：

仅适用于MSSM。

·INITRANS：

ASSM和MSSM都适合。

为块初始分配的事务槽数。

如果会对同样的块完成多个并发更新，就应该考虑增大这个值。

·COMPRESS/NOCOMPRESS：

ASSM和MSSM都适合。

 11g之前，选项是COMPRESS或者NOCOMPRESS，只有直接路径操作（例如CREATETABLEASSELECT,INSERT/*+APPEND*/,ALTERTABLETMOVE以及SQL*Loader直接路径加载）才能利用压缩。

 11g之后，选项是COMPRESSFOROLTP，COMPRESSBASIC或者NOCOMPRESS。

COMPRESSFOROLTP启用所有操作的压缩（包括直接路径和常规路径），COMPRESSBASIC则只针对直接路径操作。

注意：

单独存储在LOB段中的LOB数据并不使用表的PCTFREE/PCTUSED参数设置。

这些LOB块以不同的方式管理：

它们总是会填入，直至达到最大容量，而且仅当完全为空时才返回FREELIST。

2.堆表总结

  堆表具有的唯一优点是插入数据不需要采取任何措施，只需要顺其自然地安装插入的顺序存储，减少了插入大量数据的代价。

索引组织表IOT

1.基本概念

索引组织表（indexorganizedtable）简称IOT。

IOT中，数据要根据主键有序地存储。

适合使用IOT的几种情况：

·表完全由主键组成或者只通过主键来访问一个表。

使用IOT，表就是索引，可以节约空间，提高效率。

·通过外键访问子表，子表使用IOT。

通过IOT将相同外键的子表数据物理的存储在同一个位置，查询所需要的物理I/O更少，因为数据都在同一个（几个）块上。

·经常在主键或者或惟一键上使用BETWEEN查询。

数据以某种特定的顺序物理存储，所以获取这些数据时所需的物理I/O更少。

查看创建IOT时候的参数选项。

1.> create table t（x int primary key, y clob） organization index;  

2.  

3.Table created.  

4.  

5.> select dbms_metadata.get_ddl（ 'TABLE', 'T' ） from dual;  

6.  

7.DBMS_METADATA.GET_DDL（'TABLE','T'）  

8.--------------------------------------------------------------------------------  

9.  

10.  CREATE TABLE "TONY"."T"  

11.   （    "X" NUMBER（*,0）,  

12.        "Y" CLOB,  

13.         PRIMARY KEY （"X"） ENABLE  

14.   ） ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING  

15.  STORAGE（INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  

16.  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DE  

17.FAULT CELL_FLASH_CACHE DEFAULT）  

18.  TABLESPACE "USERS"  

19. PCTTHRESHOLD 50  

20. LOB （"Y"） STORE AS BASICFILE （  

21.  TABLESPACE "USERS" DISABLE STORAGE IN ROW CHUNK 8192 RETENTION  

22.  NOCACHE LOGGING  

23.  STORAGE（INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645  

24.  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC  

25.HE DEFAULT））  

IOT没有PCTUSED子句，但是有PCTFREE。

这是因为IOT中数据放在哪个块上不是根据未用空间大小，而是根据主键索引决定的。

但是对于溢出段（下面会提到），PCTFREE和PCTUSED对于IOT的意义将与对于堆表一样。

索引压缩

选项NOCOMPRESS对索引一般都可用，它和表压缩不同，它告诉Oracle把构成主键的每个值分别存储在各个索引条目中（也就是不压缩）。

例如，主键在A、B和C列上，则A、B和C列中每一次出现的值都会物理地存储。

NOCOMPRESS反过来就是COMPRESSN，在此N是一个整数，表示要压缩的列数。

这样可以避免重复值，并在块级提取“公因子”（factorout）。

例如如果使用COMPRESS2，那么A和B列上如果有重复的值，就只会存储1次。

使用索引压缩会减少物理I/O，但是占用更多的CPU时间来处理索引，需要在两者之间权衡。

可以使用ANALYZEINDEXVALIDATESTRUCTURE命令来得到最优的压缩方案。

这个命令会填写一个名为INDEX_STATS的动态性能视图，其中最多只包含一行，即这个ANALYZE命令最后一次执行的信息。

例如：

1.create table iot （owner, object_type, object_name,  

2.  constraint iot_pk primary key（owner, object_type, object_name））  

3.  organization index nocompress  

4.  as select distinct owner, object_type, object_name from all_objects;  

5.  

6.analyze index iot_pk validate structure;  

7.  

8.select lf_blks, br_blks, used_space, opt_cmpr_count,  

9.opt_cmpr_pctsave from index_stats;  

得到结果：

1.LF_BLKS    BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE  

2.------- ---------- ---------- -------------- ----------------  

3.    348          3    2496647              2               33  

LF_BLKS表示索引使用了348个叶子块（即数据所在的块）；

BR_BLKS表示索引使用了3个分支块（在索引结构中导航所用的块）来找到这些叶子块；

USED_SPACE表示使用的空间大约是2496647字节；

OPT_CMPR_COUNT（最优压缩数）表示最优的压缩设置为COMPRESS2；

OPT_CMPR_PCTSAVE（最优的节省压缩百分比）表示如果设置为COMPRESS2，就能节省大约33%的存储空间。

可以用altertableiotmovecompress2;来重建IOT，之后ANALYZEINDEX，可以看到压缩后的效果如下：

1.LF_BLKS    BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE  

2.---------- ---------- ---------- -------------- ----------------  

3.       233          1    1671914              2                0  

溢出段（OVERFLOW子句）

索引一般在表的一个列子集上。

通常索引块上的行数比表块上的行数会多很多，这对索引是有利的，否则Oracle需要花费大量的时间来维护索引，因为每个INSERT或UPDATE都可能导致索引块分解，以容纳新数据。

OVERFLOW子句允许你建立另一个段（这就使得IOT成为一个多段对象），根据我们的设定，IOT的行数据的一部分可以溢出到这个段中，这样可以让索引叶子块（包含具体索引数据的块）能够高效地存储数据。

需要注意，构成主键的列不能溢出，它们必须直接放在叶子块上。

建立IOT时，最关键的是适当地分配数据，即哪些数据存储在索引块上，哪些数据存储在溢出段上。

使用溢出段的条件可以采用两种方式来指定：

·PCTTHRESHOLD

行中的数据量超过块的这个百分比时，行中余下的列将存储在溢出段中。

例如，如果PCTTHRESHOLD是10%，而块大小是8KB，长度大于800字节的行就会把其中一部分存储在溢出段中。

Oracle会从最后一列开始向前查找，直到不包括主键的最后一列，得出哪些列需要存储在溢出段中。

例如：

createtableiot1（xintprimarykey,ydate,zvarchar2（2000））

organizationindexpctthreshold10overflow;

·INCLUDING

行中从第一列直到INCLUDING子句所指定列（也包括这一列）的所有列都存储在索引块上，余下的列存储在溢出段中。

例如：

createtableiot2（xintprimarykey,ydate,zvarchar2（2000））

organizationindexincludingyoverflow;

IOT上建立索引

IOT本身可以再建索引，就像在索引之上再加索引，这称为二次索引（secondaryindex）。

IOT上的索引可以使用非主键列提供IOT数据的快速、高效访问。

正常情况下，索引包含了所指向的行的物理地址，即rowid。

而IOT二次索引无法做到这一点。

因为IOT中的行可以大量移动，而且它不像堆组织表中的行那样“迁移”。

IOT中的行肯定在索引结构中的每个位置上，这取决于它的主键值；索引本身的大小和形状发生改变时行就会移动。

因此，Oracle引入了一个逻辑rowid（logicalrowid），这些逻辑rowid根据IOT主键建立。

因此，与常规表相比，IOT上的索引效率稍低。

分区表

一、分区表：

随着表的不断增大，对于新纪录的增加、查找、删除等（DML）的维护也更加困难。

对于数据库中的超大型表，可通过把它的数据分成若干个小表，从而简化数据库的管理活动。

对于每一个简化后的小表，我们称为一个单个的分区。

对于分区的访问，我们不需要使用特殊的SQL查询语句或特定的DML语句，而且可以单独的操作单个分区，而不是整个表。

同时可以将不同分区的数据放置到不同的表空间，比如将不同年份的销售数据，存放在不同的表空间，即年的销售数据存放到TBS_2001,2002年的销售数据存放到TBS_2002,依次类推，从而实现了分散存储，这将大大的简化大容量表的管理，提高查询性能及I/O并发等。

对于外部应用程序来说，虽然存在不同的分区，且数据位于不同的表空间，但逻辑上仍然是一张表

可以使用SQL*Loader，IMPDP，EXPDP，Import，Export等工具来装载或卸载分区表中的数据

关于分区表的功能实际上同SQLserver中的分区表是同样的概念，只不过SQLserver中的数据存放到了文件组，相当于Oracle概念中的表空间，

二、何时分区

当表达到GB大小且继续增长

需要将历史数据和当前的数据分开单独处理，比如历史数据仅仅需要只读，而当前数据则实现DML

三、分区的条件及特性

共性：

不同的分区之间必须有相同的逻辑属性，比如表名，列名，数据类型，约束等，

个性：

各个分区可以有不同的物理属性，比如pctfree,pctused,andtablespaces.

　　分区独立性：

即使某些分区不可用，其他分区仍然可用。

　　特殊性：

含有LONG、LONGRAW数据类型的表不能进行分区

四、分区的优点

1、提高查询性能：

只需要搜索特定分区，而非整张表，提高查询速度

2、节约维护时间：

单个分区的数据装载，索引重建，备份，维护等将远小于整张表的维护时间。

3、节约维护成本：

可以单独备份和恢复每个分区

4、均衡I/O：

将不同的分区映射到不同的磁盘以平衡I/O，提高并发

五、ORACLE分区类型：

范围分区、散列分区、列表分区、组合分区

可以对索引和表分区，全局索引只能按范围分区，可以将其定义在任何类型的分区或非分区表上。

通常全局索引比局部索引需要更多的维护

局部索引与基础表是等同分区的，用于反映其基础表的结构

1.Range分区：

行映射到基于列值范围的分区

Range分区，又成为范围分区，基于分区键值的范围将数据映射到所建立的分区上。

这是最通用的分区类型。

创建范围分区时，必须指定以下内容

分区方法：

range

分区列

标识分区边界的分区描述

使用Range分区的时候，要记住几条规则：

每个分区都包含VALUESLESSTHAN字名，定义了分区的上层边界。

任何等于和大于分区键值的二进制值都被添加到下一个高层分区中。

所有的分区，除了第一个，如果低于VALUESLESSTHAN所定义的下层边界，都放在前面的分区中。

MAXVALUE可以用来定义最高层的分区。

MAXVALUE表示了虚拟的无限值

示例：

createtablesal_range

（salesman_idnumber（5）,

salesman_namevarchar2（30）,

sales_amountnumber（10）,

sales_datedate）

partitionbyrange（sales_date）--创