表类型（索引组织表和普通堆表散列聚簇表hash cluster

表类型的定义
heap table 就是一般的表，获取表中的数据是按命中率来得到的。没有明确的先后之分，在进行全表扫描的时候，并不是先插入的数据就先获取。数据的存放也是随机的，当然根据可用空闲的空间来决定

将索引和表数据一起存储在一个称为“索引组织的表”（Index-Organized Table, IOT）的表中。使用IOT可以显著地减少磁盘空间的使用，因为不需要存储索引的列两次（一次存在表中，一次存在索引中）。相反，只需将它们和其他任何非索引的列存储在IOT中一次。IOT适用于基本的访问方法是通过主键进行访问的那些表，但允许在IOT的其他列上创建索引以改善通过这些列的访问性能。 由于IOT中的整个行存储为索引本身，因此没有用于每个行的ROWID。主键用来标识一个IOT中的行。与此不同，Oracle根据主键的值来创建逻辑ROWID，逻辑ROWID用于支持IOT上的二级索引。此外，还可以对IOT进行分区。对于频繁的插入操作的表。iot性能不好，在插入数据时，要寻找插入到那个块，若块的大小不足，需要放在溢出块中，这些操作都需要消耗资源。索引组织表适合于应用中更改不频繁，且访问时大多数时候按某一主键访问的字典。

而iot 就是类似一个全是索引的表，表中的所有字段都放在索引上，所以就等于是约定了数据存放的时候是按照严格规定的，在数据插入以前其实就已经确定了其位置，所以不管插入的先后顺序，它在那个物理上的那个位置与插入的先后顺序无关。这样在进行查询的时候就可以少访问很多blocks，但是插入的时候，速度就比普通的表要慢一些。
适用于信息检索、空间和OLAP程序。
因为索引组织的表数据所有字段都是根据索引组织的，因此每次插入都需要排序然后重新组织表结构，所以插入很慢，因此不适宜使用 频繁改动的表（除非你希望每次插入都表根据索引键排序），但是由于数据全再索引上，因此索引已经将表排序好，所以访问的时候比较快（比访问普通索引快的原 因是普通索引找到值后还要指向数据所在的块），并且，由于索引组织的表相同的索引键列中的内容放在一起，对于读取访问的块数也要少不少。
当然索引组织表对全表扫描不排序没什么好处。对不按照索引键访问的表也没什么好处。
索引组织表的适用情况：
1、 代码查找表。
2、 经常通过主码访问的表。
3、 构建自己的索引结构。
4、 加强数据的共同定位，要数据按特定顺序物理存储。
5、 经常用between…and…对主码或唯一码进行查询。数据物理上分类查询。如一张订单表，按日期装载数据，想查单个客户不同时期的订货和统计情况。
经常更新的表当然不适合iot，因为oracle需要不断维护索引，而且由于字段多索引成本就大。

An   index-organized   table   has   a   storage   organization   that   is   a   variant   of   a   primary   B-tree.   Unlike   an   ordinary   (heap-organized)   table   whose   data   is   stored   as   an   unordered   collection   (heap),   data   for   an   index-organized   table   is   stored   in   a   B-tree   index   structure   in   a   primary   key   sorted   manner.   Besides   storing   the   primary   key   column   values   of   an   index-organized   table   row,   each   index   entry   in   the   B-tree   stores   the   nonkey   column   values   as   well  
   
   
  索引组织表有一个可变的主B树存储组织。不象普通表(堆组织),数据是无序存储的集合。  
  索引组织表是以主键排序的方式的B树组织结构。   
   

IOT 有什么意义呢？使用堆组织表时，我们必须为表和表主键上的索引分别留出空间。而IOT不存在主键的空间开销，因为索引就是数据，数据就是索引，二者已经合 二为一。但是，IOT带来的好处并不止于节约了磁盘空间的占用，更重要的是大幅度降低了I/O,减少了访问缓冲区缓存(尽管从缓冲区缓存获取数据比从硬盘 读要快得多，但缓冲区缓存并不免费，而且也绝对不是廉价的。每个缓冲区缓存获取都需要缓冲区缓存的多个闩，而闩是串行化设备，会限制应用的扩展能力)

索引组织表属性 

   1、OVERFLOW子句(行溢出)

 

    因为所有数据都放入索引，所以当表的数据量很大时，会降低索引组织表的查询性能。此时设置溢出段将主键和溢出数据分开来存储以提高效率。溢出段的设置有两种格式：

 

     PCTTHRESHOLD n：制定一个数据块的百分比，当行数据占用大小超出时，该行的列数据放入溢出段

     INCLUDING column_name：指定列之前的列都放入索引块，之后的列都放到溢出段

 

      ● 当行中某字段的数据量无法确定时使用PCTTHRESHOLD。

      ● 若所有行均超出PCTTHRESHOLD规定大小，则考虑使用INCLUDING。

     

   Create table t88(

     ID varchar2(10),

     NAME varchar2(20),

     Constraint pk_id primary key(ID)

     )

   Organization index

     PCTTHRESHOLD 20

     Overflow tablespace users

     INCLUDING name;

 

   ● 如上例所示，name及之后的列必然被放入溢出列，而其他列根据PCTTHRESHOLD规则。

 

   2、COMPRESS子句(键压缩) 

 

    与普通的索引一样，索引组织表也可以使用COMPRESS子句进行键压缩以消除重复值。

    具体的操作是，在organization index之后加上COMPRESS n子句

  用于压缩索引列，在块级提取公因子，避免重复值。

如：create table iot(

        owner, object_type, object_name,

        constraint iot_pk primary key(owner, object_type,object_name)

        Orgnazation index

        NOCOMPRESS

)；

表示对于每个主键组合都会物理地存储。倘若使用COMPRESS N 则对于重复的列不再物理存储，

● n的意义在于：指定压缩的列数。默认为无穷大。 

    例如对于数据(1,2,3)、(1,2,4)、(1,2,5)、(1,3,4)、(1,3,5)时

    若使用COMPRESS则会将重复出现的(1,2)、(1,3)进行压缩

    若使用COMPRESS 1时，只对数据(1)进行压缩

 如NOCOMPRESS:

 owner , object_type,  object_name

 Scott    table          emp

 Scott    table          dept

 COMPRESS  1

 owner , object_type,  object_name

 Scott    table          emp

          table          dept

 COMPRESS 2

 owner , object_type,  object_name

 Scott    table          emp

          Dept

 

 

索引组织表的维护

 

    索引组织表可以和普通堆表一样进行INSERT、UPDATE、DELETE、SELECT操作。

    可使用ALTER TABLE ... OVERFLOW语句来更改溢出段的属性。

 

   Alter table t88 add overflow;--新增一个overflow

 

   ● 要ALTER任何OVERVIEW的属性，都必须先定义overflow，若建表时没有可以新增

 

   Alter table t88 pctthreshold 15 including name;--调整overflow的参数

   Alter table t88 initrans 2 overflow initrans 4;--修改数据块和溢出段的    initrans特性



下面分别就索引组织表和普通表的一些性能对比做一些试验，创建两张表，一个为普通的表，另外一个为索引组织表：
C:\>sqlplus

SQL*Plus: Release 9.2.0.1.0 - Production on 星期四 5月 19 11:09:06 2005

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

请输入用户名:  wwf/wwf

连接到:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production

create table heap_stocks
( symbol    varchar2(4),
  ticker_dt date,
  price     number,
  constraint heap_stocks_pk primary key (symbol,ticker_dt)
);

create table iot_stocks
( symbol    varchar2(4),
  ticker_dt date,
  price     number,
  constraint iot_stocks_pk primary key (symbol,ticker_dt)
)
organization index compress 1;

上面模仿股票，分别存放股票代码，日期，收盘价格三个字段。下面，我们插入分别对这两个表插入1000种股票200天的数据，看看其插入数据时的性能：

1 插入数据
SQL> set timing on
SQL> begin
  2    for i in 1..200 loop
  3       insert into heap_stocks
  4       select to_char(rownum, 'fm0009'), trunc(sysdate)+i, rownum
  5       from all_objects where rownum <= 1000;
  6    end loop;
  7    commit;
  8  end;
  9  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 18.06
SQL> set timing on
SQL> begin
  2    for i in 1..200 loop
  3       insert into iot_stocks
  4       select to_char(rownum, 'fm0009'), trunc(sysdate)+i, rownum
  5       from all_objects where rownum <= 1000;
  6    end loop;
  7    commit;
  8  end
  9  ;
10  /

PL/SQL 过程已成功完成。

已用时间:  00: 00: 31.07

可以看到，插入20万条数据，普通表用了18秒，而IOT表用了31秒，相差明显。这说明插入数据时，IOT表的速度是相当慢的。

2. 查询

我们重新启动一下数据库：
SQL> conn
请输入用户名:  sys / nolog as sysdba
已连接。
SQL> shutdown immediate
数据库已经关闭。
已经卸载数据库。
ORACLE 例程已经关闭。
SQL> startup
ORACLE 例程已经启动。

Total System Global Area  135338868 bytes
Fixed Size                   453492 bytes
Variable Size             109051904 bytes
Database Buffers           25165824 bytes
Redo Buffers                 667648 bytes
数据库装载完毕。
数据库已经打开。
SQL> exit
从Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production中断开

然后重新登录：
SQL> conn
请输入用户名:  wwf/wwf
已连接。
a.  使用autotrace测试
SQL> set autotrace traceonly
SQL> set timing on
SQL> set autotrace traceonly
SQL> select * from heap_stocks where symbol = '0001';

已选择200行。

已用时间:  00: 00: 00.08

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'HEAP_STOCKS'
   2    1     INDEX (RANGE SCAN) OF 'HEAP_STOCKS_PK' (UNIQUE)

Statistics
----------------------------------------------------------
        239  recursive calls
          0  db block gets
        259  consistent gets
        207  physical reads
          0  redo size
       5706  bytes sent via SQL*Net to client
        646  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
          4  sorts (memory)
          0  sorts (disk)
        200  rows processed

SQL> select * from iot_stocks where symbol = '0001';

已选择200行。

已用时间:  00: 00: 00.02

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=82 Bytes=2132)
   1    0   INDEX (RANGE SCAN) OF 'IOT_STOCK_PK' (UNIQUE) (Cost=2 Card=82 Bytes=2132)

Statistics
----------------------------------------------------------
        299  recursive calls
          0  db block gets
         63  consistent gets
          4  physical reads
          0  redo size
       5706  bytes sent via SQL*Net to client
        646  bytes received via SQL*Net from client
         15  SQL*Net roundtrips to/from client
          6  sorts (memory)
          0  sorts (disk)
        200  rows processed

逻辑读分别为259和63，差别显著！说明，查询时，IOT表性能要远远优越于普通的表！


b 使用sql_trace测试:
SQL> conn
请输入用户名:  wwf/wwf
已连接。
SQL> alter session set sql_trace = true;

会话已更改。

SQL> select avg(price) from heap_stocks where symbol = '0001';

AVG(PRICE)
----------
         1

SQL> select avg(price) from iot_stocks where symbol = '0001';

AVG(PRICE)
----------
         1

SQL> alter session set sql_trace = false;

会话已更改。

使用tkprof格式化输出文件，得到如下结果：

select avg(price) from heap_stocks where symbol = '0001'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.31       0.33          1          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.39        203        208          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.31       0.73        204        209          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
    200   TABLE ACCESS BY INDEX ROWID HEAP_STOCKS
    200    INDEX RANGE SCAN HEAP_STOCKS_PK (object id 30391)



select avg(price) from iot_stocks where symbol = '0001'


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.02       0.03          0          0          0           0
Execute      1      0.00       0.01          0          0          0           0
Fetch        2      0.00       0.07          3          4          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.02       0.11          3          4          0           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 61  

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT AGGREGATE
    200   INDEX RANGE SCAN IOT_STOCK_PK (object id 30393)

看看Tom怎么说：
So, we did 203 physical IO's to process the HEAP table.  What that tells me is
that our data for stock symbol 0001 is spread out on 200 blocks (200 days, 200
blocks).  In order to cache the results for this query, we need 200 block
buffers.  We needed to do that IO to get the answer initially.

Now, looking at the IOT we did 3 physical IO's -- we cached 3 blocks -- and got
the same answer!  Not only that but by using index key compression I was able to
remove he redudant 0001's from the data -- we can cache this much more
efficiently and getting it the first time takes seriously less IO.  Very nice.


3 删除数据
SQL> set autotrace off
SQL> delete from heap_stocks;

已删除200000行。

已用时间:  00: 00: 26.02
SQL> delete from iot_stocks;

已删除200000行。

已用时间:  00: 00: 08.08

可以看到，删除数据时，普通表用了26秒，而IOT表用了8秒。差别显著！也许是普通表占的空间大的缘故吧!

索引聚簇表cluster index

Oracle中聚簇是存储一组表的方法，而不是如同SQL Server、Sybase中那样（那是Oracle中的IOT）。概念上是通过聚簇码列将几张表“预连接”，尽可能将聚簇码列相同的几张表的行放入同一个块中。
不使用聚簇的情况：

1．聚簇可能消极影响DML性能；

2．全扫描表的性能会受到影响——不仅仅扫描一个表，而是对多个表全扫描；

3．聚簇中的表不能TRUNCATE。

Although a normal index does not store null key values, cluster indexes store null keys. There is only one entry for each key value in the cluster index. Therefore, a cluster index is likely to be smaller than a normal index on the same set of key values.

Although a normal index does not store null key values, cluster indexes store null keys.
虽然普通的索引不保存空的键值，但cluster index保存空的keys？
为什么cluster index保存空的keys？什么情况呢，搞不明白
请指教

 

cluster  在 存储中，是多个表集合在一起存储的，也就是说 多个表的数据 存储在同一个 block 中。 如果多个表中同时存在 null  key，是不是也需要把这些集中起来放在一起？ 你去观察一下？

想到底是放 null key 呢还是不放 null  key 好呢? 根据 cluster  的特点，结合起来考虑考虑看看。


其实，普通 b-tree index 也不是说就不能放 null  keys ，只不过可能oracle认为大多数查询是 等值 或者 范围查询，很少 用 is  null 查询，并且如果表中存在大量的 null 的时候不存储 null 有利于减小索引大小提高性能。在各种因素权衡下 选择了不存储 null 。

如果你实在觉得没有很明显的理由，大不了就先记下好了。

Each cluster key value is stored only once for all the rows of the same key value; it therefore uses less storage space.

散列聚簇表hash cluster

概念类似索引聚簇表，但用散列函数代替了聚簇码索引。Oracle采用行的码值，使用内部函数或者自定义的函数进行散列运算，从而指定数据的存放位置。这样没有在表中增加传统的索引，因此不能Range Scan散列聚簇中的表，而只能全表扫描（除非单独建立索引）。

CREATE CLUSTER hash_cluster

(hash_key NUMBER)

HASHKEYS 1000

SIZE 8192;

索引聚簇需要空间时是动态分配，而散列聚簇表在创建时确定了散列码数（HASHKEY）。Oracle采用第一个不小于HASHKEY的质数作为散列码数， 将散列码数*SIZE就得到分配的空间（字节），可容纳HASHKEYS/TRUNC(BLOCKSIZE/SIZE)字节的数据。

性能上，散列聚簇表消耗较少I/O，较多CPU，所需执行时间较少，大体取决于CPU时间（当然可能要等待I/O，取决于配置）。

下列情况下使用散列聚簇表较为合适：

1. 在一定程度上精确知道整个过程中表中记录行数或者合理的上限，以确定散列码数；

2.不大量执行DML，尤其是插入。更新不会产生显著的额外开销，除非更新HASHKEY，这样会导致行迁移；

3.总是通过HASHKEY值访问数据。