数据库表

1.         段空间管理

²        段空间管理有两种方法：

1）  手动段空间管理（Manual Segment Space Management）：由你设置FREELIST、FREELIST GROUPS、PCTUSED和其他参数来控制如何分配、使用和重用段中的空间。

2）  自动段空间管理（Automatic Segment Space Management,ASSM）：你只需控制与空间使用相关的一个参数：PCTFREE。其他参数都将被忽略。

²        Freelists

1）  使用手动段空间管理时，每个对象至少有一个相关的freelist，使用块时，可能会根据需要把块放在freelist上或者从freelist删除。只有位于高水位线（HWM）以下的对象块才会出现在freelist中。仅当freelist为空时才会使用HWM之上的块，此时，Oracle会推进HWM,并把这些块增加到freelist中，采用这种方式，Oracle会延迟到不得已时才增加对象的HWM.

2）  一个对象可以有多个freelist。如果预计会有多个并发用户在一个对象上执行大量的INSERT或UPDATE活动，就可以配置多个freelist，这对性能提升很有好处（但是可能要以额外的存储空间为代价）。

3）  使用多个freelist时，有一个主freelist，还有一些进程freelist。对于一个给定的会话，会根据其会话ID的散列值为之指定一个进程freelist。每个进程freelist都只有很少的块，余下的自由块都在主freelist上。使用一个进程freelist时，它会根据需要从主freelist拉出一些块，如果主freelist无法满足空间需求，Oracle就会推进HWM，并向主freelist中增加块。过一段时间后，主freelist会把其存储空间分配多个进程freelist（每个进程freelist都只有为数不多的块）。因此，每个进程会使用一个进程freelist，它不会从一个进程freelist到另一个进程freelist上寻找空间。故，一方面，使用多个freelist 可以大幅度提升性能。另一方面，有可能导致表不太必要地使用稍多的磁盘空间。

²        PCTFREE和PCTUSED

1）  PCTFREE参数用来告诉Oracle应该在块上保留多少空间来完成将来的更新，默认是10%，如果自由空间的百分比高于PCTFREE中的指定值，这个块就认为是“自由的”。PCTUSED则告诉Oracle当前不“自由”的一个块上自由空间百分比需要达到多大才能使它再次变为自由的，默认是40%。

2）  使用手动段空间管理时，这些参数控制着块何时放入freelist中，以及何时从freelist中取出。如果使用默认值：PCTFREE为10，PCTUSER为40，那么在块到达90%前（有10%的自由空间），这个块会一直在freelist上。一旦到达90%，就会从freelist中取出，而且直到块上的自由空间超过了块的60%时，才会重新回到freelist上，在此之前，这个块一直不在freelist上。

3）  使用ASSM时，PCTFREE仍然会限制能否将一个新行插入到一个块中，但它不会控制一个块是否在freelist上，因为ASSM根本不使用freelist。在ASSM中，PCTUSED将被忽略。

4）  PCTFREE设置得过高，就会浪费空间；设置得太低，更新行时就会导致行迁移。

5）  Oracle迁移一行时，不能简单的移动这一行，它必须留下一个“转发地址”，因为可能有一些索引物理地指向该行原来的地址，简单的更新不会同时修改这些索引，所以，在该行原来的地址上会留下一个指针，指示这一行实际上在什么位置。当一行需要多次迁移时，Oracle或者把这一行迁移回原来的块（如果原来的块有足够的空间），或者把这一行迁移到另外的某个块上，并修改原来块上的转发地址。因此，行迁移总是只涉及一层间接性。

6）  设置PCTFREE和PCTUSED值：

Ø         高PCTFREE,低PCTUSED：如果你插入了将要更新的大量数据，而且这些更新会频繁的增加行的大小。

Ø         低PCTFREE，高PCTUSED：如果你只想对表完成INSERT或DELETE，或者UPDATE只是缩小行的大小。

²        LOGGING和NOLOGGING

1）  通常对象都采用LOGGING方式创建，说明对象上完成的操作只要能生成redo就都会生成redo.

2）  NOLOGGING则允许该对象完成某些操作时可以不生成redo，如对象的初始创建，SQL*Loder的直接路径加载或重建。它不会完全禁用对象的重做日志生成，只是几个特定的操作不生成日志而已。

²        INITRANS和MAXTRANS

1）  段中每个块都有一个块首部，在块首部中有一个事务表，事务表中会建立一些条目来描述哪些事务将块上的哪些行/元素锁定。

2）  INITRANS设置事务表的初始大小，对于表和索引，默认值都为2.

3）  事务表会根据需要动态扩展，最大达到MAXTRANS个条目（假设块上有足够的空间）。在Oracle 10g中，MAXTRANS则被忽略，所有段的MAXTRANS都是255.

2.         堆组织表

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

²        堆以一种显然随机的方式管理。数据会放在最合适的地方，而不是以某种特定顺序来放置。一般来讲，数据库表本质上是无序的数据集合，全部扫描时，会按命中的顺序来获取数据，而不是以插入的顺序。因此，不要过分依赖查询得到的顺序，除非查询中有一个ORDER BY语句！

²        使用DBMS_METADATA，查询表的定义，该技巧显示了CREATE TABLE语句的许多选项，在定制详细的版本时可以避免去记大量的选项。

SQL> create table t

  2  (x int primary key,

  3  y date,

  4  z clob

  5  )

  6  /

表已创建。

SQL> set serveroutput on

SQL> set linesize 1000

SQL> set long 999999    --不设置会显示不完整

SQL> set pagesize 1000

SQL> select dbms_metadata.get_ddl('TABLE','T') from dual;

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

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

CREATE TABLE "SCOTT"."T"

   (    "X" NUMBER(38,0),

        "Y" DATE,

        "Z" CLOB,

         PRIMARY KEY ("X")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"  ENABLE

   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)

  TABLESPACE "USERS"

 LOB ("Z") STORE AS BASICFILE (

  TABLESPACE "USERS" ENABLE STORAGE IN ROW CHUNK 8192

  NOCACHE LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT))

²        对于堆表需要注意的选项

1）  FREELISTS：仅适用于手动段空间管理。

2）  PCTFREE：ASSM和手动段空间管理都使用。在INSERT过程中，会测量块的充满程度。这个参数用于控制能否将一行增加到一个块上，还可以控制因后续更新导致的行迁移。

3）  PCTUSED：仅适用于手动段空间管理。度量一个块必须为多空才允许再次插入行。

4）  INITRANS：ASSM和手动段空间管理都适合。为块初始分配的事务槽数。

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

3.         索引组织表（index organized table,IOT）

²        存储在堆中的表是无组织的（即：只要有可用的空间，数据可以放在任何地方）；IOT中的数据则按主键存储和排序。

²        使用堆组织表时，必须为表和表主键上的索引分别留出空间，而IOT则不存在主键的空间开销，因为索引就是数据，数据就是索引；索引是一个复杂的数据结构，需要大量的工作来管理和维护，堆组织表管理起来则很容易。

²        适合采用IOT的几种应用

1）  假设有如下的一个表

Create table keywords

(      word varchar2(50),

Position int,

Doc_id int,

Primary key(word,position,doc_id)

);

该表完全由主键组成，因此有超过100%的（主键索引）开销：因为表的大小与主键索引的大小相当（实际上，主键索引更大，因为索引物理的存储了rowid，而表中并不存储rowid，表中的行ID是推断出来的）。如果使用这个表时，WHERE子句只选择了WORD列或WORD和POSITION列，即只使用了表上的索引，而没有使用表，则此时表本身完全是开销。这个应用就非常适合采用IOT。

2）  如果你只会通过主键来访问一个表，这个表就非常适合实现为IOT。

3）  如果你想保证数据存储在某个位置上，或者希望数据以某种特定的顺序物理存储，IOT就是一种合适的结构。

4）  如果你希望某些数据能物理地共同存储在一起，则适合采用IOT，此种情况下，IOT提供了以下好处：

Ø         提供缓冲区效率，因为给定查询的缓存中需要的块更少；

Ø         减少缓冲区缓存访问，这会改善可扩缩性；

Ø         获取数据的工作总量更少，因为获取数据更快；

Ø         每个查询完成的物理I/O更少，因为对于任何给定的查询，需要的块更少，而且对地址记录的一个物理I/O很可能可以获取所有地址（而不只是其中一个地址，但堆表实现就只是获取一个地址）。

5）  如果经常在一个主键或惟一键上使用BETWEEN查询，利用IOT将数据有序地物理存储，就能提升这些查询的性能。

²        使用DMBS_METADATA显示详细选项

SQL> create table t1

  2  (x int primary key,

  3  y varchar2(25),

  4  z date

  5  )

  6  organization index;

表已创建。

SQL> create table t2

  2  (x int primary key,

  3  y varchar2(25),

  4  z date

  5  )

  6  organization index

  7  OVERFLOW

表已创建。

SQL>create table t3

  2  (x int primary key,

  3  y varchar2(25),

  4  z date

  5  )

  6  organization index

  7  overflow INCLUDING y

表已创建。

SQL> select dbms_metadata.get_ddl('TABLE','T1') from dual;

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

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

  CREATE TABLE "ORACLE"."T1"

   (    "X" NUMBER(38,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)

  TABLESPACE "USERS"

 PCTTHRESHOLD 50

注意：没有PCTUSED子句，但有一个PCTFREE。在堆中，块只能有时能插入新行，而索引则不同，块总是可以插入新的索引条目。PCTFREE会在新创建的索引上预留空间，但对于以后对索引的操作不预留空间。

²        选项NOCOMPRESS

1）  这个选项对索引一般都可用。它告诉Oracle 把每个值分别存储在各个索引条目中（也就是不压缩）。如果对象的主键在A、B 和C 列上，A、B 和C 的每一次出现都会物理地存储。NOCOMPRESS 反过来就是COMPRESS N，在此N 是一个整数，表示要压缩的列数。这样可以避免重复值，并在块级提取“公因子”（factor out）。这样在A 的值（以及B 的值）重复出现时，将不再物理地存储它们。

2）  对前面CREATE TABLE 的SELECT 分别采用NOCOMPRESS、COMPRESS 1 和COMPRESS 2 选项，来展示能节省多少空间。

Ø         先来创建IOT，但不进行压缩：

SQL> 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  /

表已创建。

使用ANALYZE INDEX VALIDATE STRUCTURE命令测量所用的空间

SQL> analyze index iot_pk validate structure;

索引已分析

SQL>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

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

404        3       2905039            2                   33

结果说明：索引目前使用了404 个叶子块（即数据所在的块），并使用了3 个分支块（Oracle在索引结构中导航所用的块）来找到这些叶子块。使用的空间大约是3MB（2,905,039字节）。OPT_CMPR_COUNT（最优压缩数）列要说的是：“如果你把这个索引置为COMPRESS 2，就会得到最佳的压缩”。OPT_CMPR_PCTSAVE（最优的节省压缩百分比）则是说，如果执行COMPRESS 2，就能节省大约1/3 的存储空间，索引只会使用现在2/3 的磁盘空间。

Ø         使用COMPRESS 1重建这个IOT

SQL> alter table iot move compress 1;

表已更改。

SQL> analyze index iot_pk validate structure;

索引已分析

查询结果如下：

 LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

351        3      2517886              2                  23

结果说明：可以看到，索引确实更小了：大约2.5MB，叶子块更少。但是，现在它说“你还能再节省另外23%的空间”，因为我们没有充分地压缩。

Ø         使用COMPRESS 2重建这个IOT

SQL> alter table iot move compress 2;

表已更改。

SQL> analyze index iot_pk validate structure;

索引已分析

查询结果如下：

 LF_BLKS BR_BLKS USED_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE

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

269        3      1934625               2                 0

结果说明：现在大小有了显著减少，不论是叶子块数还是总的使用空间都大幅下降，现在使用的空间大约是1.9MB。

²        选项PCTTHRESHOLD

1）    它与以下两个选项有关：OVERFLOW和INCLUDING

Ø         OVERFLOW 子句允许你建立另一个段（这就使得IOT 成为一个多段对象，就像有一个CLOB 列一样），如果IOT 的行数据变得太大，就可以溢出到这个段中。注意构成主键的列不能溢出，它们必须直接放在叶子块上。对于OVERFLO W段和堆表来说，PCTFREE和PCTUSED 的含义都相同。

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

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

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

²        二次索引（secondary index）

1）IOT 本身可以有一个索引，就像在索引之上再加索引，这称为二次索引。

2）Oracle 引入了一个逻辑rowid（logical rowid）。这些逻辑rowid 根据IOT 主键建立。对于行的当前位置还可以包含一个“猜测”，不过这个猜测几乎是错的，因为稍过一段时间后，IOT中的数据可能就会移动。这个猜测是行第一次置于二次索引结构中时在IOT 中的物理地址。如果IOT 中的行必须移动到另外一个块上，二次索引中的猜测就会变得“过时”。

3）与常规表相比，IOT 上的索引效率稍低。在一个常规表上，索引访问通常需要完成一个I/O 来扫描索引结构，然后需要一个读来读取表数据。对于IOT， 通常要完成两个扫描；一次扫描二次结构，另一次扫描IOT 本身。