mysql堆表索引组织表_Oracle堆组织表及索引组织表（转）

执行CREATE

TABLE语句时，默认得到的表类型就是堆组织表。其他类型的表结构需要在CREATE TABLE语句本身中指定它。

堆组织表中，数据以堆的方式管理。增加数据时，会使用段中找到的第一个能放下此数据的自由空间。从表中删除数据后，允许以后的INSERT和UPDATE重用这部分空间。堆(heap)是一组空间，以一种随机的方式使用。因此，无法保证按照放入表中的顺序取得数据。

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

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

tony@ORA11GR2> create table t(x int primary key, y clob);

Table created.

tony@ORA11GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' )

from dual;

DBMS_METADATA.GET_DDL('TABLE','T')

------------------------------------------------------------------------

CREATE TABLE "TONY"."T"

(  "X" NUMBER(*,0),

"Y" CLOB,

PRIMARY KEY ("X")

USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS

255 NOCOMPRESS LOGGING

TABLESPACE "USERS"  ENABLE

) SEGMENT CREATION

DEFERRED

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS

255 NOCOMPRESS LOGGING

TABLESPACE "USERS"

LOB ("Y") STORE AS BASICFILE (

TABLESPACE "USERS" ENABLE STORAGE IN ROW

CHUNK 8192 RETENTION

NOCACHE LOGGING )

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

对于ASSM有3个重要选项，对于MSSM有5个重要选项。随着本地管理表空间的引入(推荐做法)，其余的参数已经没什么意义了。

· FREELIST： 仅适用于MSSM。

· PCTFREE：ASSM和MSSM都适用。

· PCTUSED：仅适用于MSSM。

· INITRANS：ASSM 和MSSM

都适合。为块初始分配的事务槽数。如果会对同样的块完成多个并发更新，就应该考虑增大这个值。

· COMPRESS/NOCOMPRESS：ASSM 和MSSM 都适合。

二、关于表压缩

Oracle压缩数据的处理基于数据库块，其本质上是通过消除在数据库块中的重复数据来实现空间节约.

Oracle比较数据块中包含的所有字段或记录，其中重复的数据只在位于数据块开始部分的记号表(Symbol

Table)中存储一份，在其他行或字段出现同样的数据时，只记录一个指向记号表中相关数据的指针。

表的压缩

create table Name(

......

) compress;

alter table Name compress;

alter table Name nocompress;

查看一个表是否为压缩表：

select compression from user_table where

table_name=TableName;

物化视图的压缩

create materialized view ViewName compress

as select ......;

alter materialized view ViewName compress;

分区表的压缩

create table Name (

......

) compress

partition by ......;

create table Name (

......

)

partition by ......(

partition PartName ...... compress,

partition PartName ...... compress,

partition PartName ......

);

查看分区表各分区的压缩属性：

select table_name, partition_name, compression from

user_tab_partitions where table_name=TableName;

表空间的压缩

create tablespace ...... default compress;

alter tablespace ...... compress / nocompress;

当压缩属性被定义在表空间上时，在其中创建表时，该特性将被表继承，但表级别的压缩属性会覆盖表空间的压缩属性。

查看一个表空间是否被压缩：

select def_tab_compression from dba_tablespace where

tablespace_name=TablespaceName;

11g之前，选项是COMPRESS或者NOCOMPRESS，只有直接路径操作(例如CREATE TABLE AS SELECT,

INSERT / *+ APPEND*/, ALTER TABLE T

MOVE以及SQL*Loader直接路径加载)才能利用压缩。

11g之后，选项是COMPRESS FOR OLTP，COMPRESS BASIC或者NOCOMPRESS。COMPRESS

FOR OLTP启用所有操作的压缩(包括直接路径和常规路径)，COMPRESS BASIC则只针对直接路径操作。

注意：

1) 将一个非压缩的表修改为压缩表后,

alter table xxx compress for oltp;

表中已经存在的数据需要执行alter table xxx move来进行压缩。后续插入数据会自动压缩。

2)

单独存储在LOB段中的LOB数据并不使用表的PCTFREE/PCTUSED参数设置。这些LOB块以不同的方式管理：它们总是会填入，直至达到最大容量，而且仅当完全为空时才返回FREELIST。

三、索引组织表

索引组织表(index organized table)简称IOT。

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

适合使用IOT的几种情况：

· 表完全由主键组成或者只通过主键来访问一个表。使用IOT，表就是索引，可以节约空间，提高效率。

·

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

·

经常在主键或者或惟一键上使用BETWEEN查询。数据以某种特定的顺序物理存储，所以获取这些数据时所需的物理I/O更少。

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

tony@ORA11GR2> create table t(x int primary key, y clob)

organization index;

Table created.

tony@ORA11GR2> select dbms_metadata.get_ddl( 'TABLE', 'T' )

from dual;

DBMS_METADATA.GET_DDL('TABLE','T')

--------------------------------------------------------------------------------

CREATE TABLE "TONY"."T"

(  "X" NUMBER(*,0),

"Y" CLOB,

PRIMARY KEY ("X") ENABLE

) ORGANIZATION INDEX

NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING

STORAGE(INITIAL 65536 NEXT 1048576

MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DE

FAULT CELL_FLASH_CACHE DEFAULT)

TABLESPACE "USERS"

PCTTHRESHOLD 50

LOB ("Y") STORE AS BASICFILE (

TABLESPACE "USERS" DISABLE STORAGE IN ROW

CHUNK 8192 RETENTION

NOCACHE LOGGING

STORAGE(INITIAL 65536 NEXT 1048576

MINEXTENTS 1 MAXEXTENTS 2147483645

PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CAC

HE DEFAULT))

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

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

索引压缩

选项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

STRUCTURE命令来得到最优的压缩方案。这个命令会填写一个名为INDEX_STATS的动态性能视图，其中最多只包含一行，即这个ANALYZE命令最后一次执行的信息。例如：

create table iot (owner, object_type, object_name,

constraint iot_pk primary key(owner,

object_type, object_name))

organization index nocompress

as select distinct owner, object_type,

object_name from all_objects;

analyze index iot_pk validate structure;

select lf_blks, br_blks, used_space, opt_cmpr_count,

opt_cmpr_pctsave from index_stats;

得到结果：

LF_BLKS  BR_BLKS

USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

------- ---------- ---------- -------------- ----------------

348  3  2496647

2  33

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

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

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

OPT_CMPR_COUNT(最优压缩数)表示最优的压缩设置为COMPRESS 2；

OPT_CMPR_PCTSAVE(最优的节省压缩百分比)表示如果设置为COMPRESS

2，就能节省大约33%的存储空间。

可以用alter table iot move compress 2;来重建IOT，之后ANALYZE

INDEX，可以看到压缩后的效果如下：

[plain] view plaincopy

LF_BLKS  BR_BLKS

USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

---------- ---------- ---------- --------------

----------------

233  1

1671914  2

0

溢出段(OVERFLOW子句)

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

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

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

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

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

· PCTTHRESHOLD

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

例如，如果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 table 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中的行肯定在索引结构中的每个位置上，这取决于它的主键值；索引本身的大小和形状发生改变时行就会移动。

因此，Oracle引入了一个逻辑rowid(logical

rowid)，这些逻辑rowid根据IOT主键建立。因此，与常规表相比，IOT上的索引效率稍低。