oracle数据库表_笔记5:索引组织表(续)

索引组织表(续)

IOT表有哪些选项。有哪些需要告诫的方面?IOT的选项与堆组织表的选项非常相似。使用DBMS_METADATA来显示详细选项。先从IOT的3个基本变体开始：

sys@ORCL> create table t1

2 (x int primary key,

3 y varchar2(25),

4 z date

5 )

6 organization index;

Table created.

sys@ORCL> create table t2

2 (x int primary key,

3 y varchar2(25),

4 z date

5 )

6 organization index

7 overflow;

Table created.

sys@ORCL> create table t3

2 (x int primary key,

3 y varchar2(25),

4 z date

5 )

6 organization index

7 overflow INCLUDING y;

Table created.

首先来看第一个表所需的详细SQL

sys@ORCL> select dbms_metadata.get_ddl('TABLE','T1')from dual;

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

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

CREATE TABLE "SYS"."T1"

("X" NUMBER(*,0),

"Y" VARCHAR2(25),

"Z" DATE,

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 "SYSTEM"

PCTTHRESHOLD 50

这个表引入了两个新的选项：NOCOMPRESS和PCTTHRESHOLD。没有PCTUSED子句，但是这里有一个PCTFREE。这是因为，索引是一个复杂的数据结构，它不像堆那样随机组织，所以数据必须按部就班地存放到它该去的地方去。在堆中，块只是有时能插入新行，而索引则不同，块总是可以插入新的索引条目。如果某个数据(根据它的值)属于一个给定块，它总会放到那个块上，而不论这个块多满或者多空。另外，只是在索引结构中创建对象和填充数据时才会使用PCTFREE。其用法与堆组织表中的用法不同。PCTFREE会在新创建的索引上预留空间，但是对于以后对索引的操作不预留空间，这与不使用PCTUSED的原因是一样的。堆组织表上关于FREELIST的考虑同样完全适用于IOT。

现在来讨论选项NOCOMPRESS。这个选项与表压缩实现不同。它适用于索引编列表上的任何操作(而表压缩可能会影响常规路径操作)。它告诉Oracle把每个值分别存储在各个索引条目中(也就是说不压缩)。如果对象的主键在A、B和C列上，A、B和C的每一次出现都会物理地存储。NOCOMPRESS反过来就是COMPRESS N，在此N是一个整数，表示要压缩的列数。这样可以避免重复值，并在块级提

取“公因子”(factor out)。这样在A的值(以及B的值)重复出现时，将不再物理地存储它们。

例如：

sys@ORCL> create table iot

2 (owner,object_type,object_name,

3 constraint iot_pk primary key(owner,object_type,object_name)

4 )

5 organization index

6 NOCOMPRESS

7 as

8 select distinct owner,object_type,object_name

9 from all_objects

10 /

Table created.

可以想想看，每个模式(作为OWNER)都拥有大量对象，所以OWNER值可能会重复数百次。甚至OWNER、OBJECT_TYPE值也会重复多次，因为给定模式可能有数十个表、数十个包等。只是这3列合在一起不会重复。可以让Oracle压缩这些重复的值。

也就是说，值SYS和TABLE只出现一次，然后存储第三列。采用这种方式，每个索引块可以有更多的条目(否则这是不可能的)。这不会降低并发性，因为仍在行级操作;另外也不会影响功能。它可能会稍多占用一些CPU时间，因为Oracle必须做更多的工作将键合并在一起。另一方面，这可能会显著的减少I/O，并允许更多的数据在缓冲区缓存中缓存，原因是每个块上能有更多的数据。

下面做一个快速测试，对前面CREATE TABLE的SELECT 分别采用NOCOMPRESS、COMPRESS 1和COMPRESS 2选项，来展示能节省多少空间。先来创建IOT，但不进行压缩：

sys@ORCL> create table iot

2 (owner,object_type,object_name,

3 constraint iot_pk primary key(owner,object_type,object_name)

4 )

5 organization index

6 NOCOMPRESS

7 as

8 select distinct owner,object_type,object_name

9 from all_objects

10 /

Table created.

现在可以测量所用的空间。为此将使用ANALYZE INDEX VALIDATE STRUCTURE命令。这个命令会填写一个名为INDEX_STATS的动态性能视图，其中最多只包含一行，即这个ANALYZE命令最后一次执行的信息：

sys@ORCL> analyze index iot_pk validate structure;

Index analyzed.

sys@ORCL> l

1 select lf_blks,br_blks,used_space,

2 opt_cmpr_count,opt_cmpr_pctsave

3* from index_stats

sys@ORCL> /

LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

432 3 3105644 2 33

由此显示出，索引目前使用了432个叶子块(即数据所在的块)，并使用了3个分支块(Oracle在索引结构中导航所用的块)来找到这些叶子块。使用的空间大约是3MB(3105644字节)。另外两列名字有些奇，这两列是告诉我们一些信息。OPT_CMPR_COUNT(最优压缩数)列要说的是：“如果把这个索引置为COMPRESS 2，就会得到最佳的压缩”。OPT_CMPR_PCTSAVE(最优的节省压缩百分比)则是说，如果执行COMPRESS 2，就能节省大约1/3的存储空间，索引只会使用现在2/3的磁盘空间。

先用COMPRESS 1重建这个IOT：

sys@ORCL> alter table iot move compress 1;

Table altered.

sys@ORCL> analyze index iot_pk validate structure;

Index analyzed.

sys@ORCL> select lf_blks,br_blks,used_space,

2 opt_cmpr_count,opt_cmpr_pctsave

3 from index_stats;

LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

374 3 2688790 2 22

可以看到，索引确实更小了：大约2.6MB，叶子块变得更少。下面用COMPRESS 2再来重建IOT：

sys@ORCL> alter table iot move compress 2;

Table altered.

sys@ORCL> analyze index iot_pk validate structure;

Index analyzed.

sys@ORCL> select lf_blks,br_blks,used_space,opt_cmpr_count,opt_cmpr_pctsave

2 from index_stats;

LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

288 3 2070361 2 0

现在大小有了显著减少，不论是叶子块数还是总的使用空间都大幅下降，现在使用的空间大约是2MB,再来看原来的数字：

sys@ORCL> select (2/3)*3105644 from dual;

(2/3)*3105644

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

2070429.33

可以看到OPT_CMPR_PCTSAVE真是精准无比。关于IOT有一点很有意思：IOT是表，但是只是有其名而无其实。IOT段实际上是一个索引段。

如果查看以下两组表(T2和T3)的完整SQL，可以看到如下内容：

sys@ORCL> begin

2 dbms_metadata.set_transform_param

3 (DBMS_METADATA.SESSION_TRANSFORM,'STORAGE',false);

4 end;

5 /

PL/SQL procedure successfully completed.

sys@ORCL> select dbms_metadata.get_ddl('TABLE','T2') from dual;

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

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

CREATE TABLE "SYS"."T2"

("X" NUMBER(*,0),

"Y" VARCHAR2(25),

"Z" DATE,

PRIMARY KEY ("X") ENABLE

) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING

TABLESPACE "SYSTEM"

PCTTHRESHOLD 50 OVERFLOW

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING

TABLESPACE "SYSTEM"

sys@ORCL> select dbms_metadata.get_ddl('TABLE','T3') from dual;

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

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

CREATE TABLE "SYS"."T3"

("X" NUMBER(*,0),

"Y" VARCHAR2(25),

"Z" DATE,

PRIMARY KEY ("X") ENABLE

) ORGANIZATION INDEX NOCOMPRESS PCTFREE 10 INITRANS 2 MAXTRANS 255 LOGGING

TABLESPACE "SYSTEM"

PCTTHRESHOLD 50 INCLUDING "Y" OVERFLOW

PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING

TABLESPACE "SYSTEM"

所以，现在只剩下PCTTHRESHOLD、OVERFLOW和INCLUDING还没有讨论。这三个选项有点“绕”，其目标是让索引叶子块(包含具体索引数据的块)能够高效地存储数据。索引一般在一个列子集上。

通常索引块上的行数比堆表上的行数会多出几倍。索引指望着每块能得到多行。否则，Oracle会花费大量的时间来维护索引，因为每个INSERT或UPDATE都可能导致索引块分解，以容纳新数据。

OVERFLOW子句允许建立另一个段(这就使得IOT成为一个多段对象，就像有一个CLOB列一样)，如果IOT的行数据变得太大，就可以溢出到这个段中。

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

注意：使用MSSM时，OVERFLOW再次为IOT引入了PCTUSED子句。对于OVERFLOW段和堆表来说，

PCTFREE和PCTUSED的含义都相同。使用溢出段的条件可以采用以下两种方式来指定。

.PCTTHRESHOLD：行中的数据量块的这个百分比时，行中余下的列将存储在溢出段中。所以，如果PCTTHRESHOLD是10%，而块大小是8KB，长度大于800字节的行就会把其中一部分存储在别外，而不能在索引块上存储。

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

---

oracle数据库表_笔记5:索引组织表http://www.52ij.com/jishu/5285.html

本文来源 我爱IT技术网 http://www.52ij.com/jishu/5286.html 转载请保留链接。