mysql堆表和索引组织_堆组织表，索引组织表和索引聚簇表

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秒。差别显著！也许是普通表占的空间大的缘故吧!

索引聚簇表clusterindex

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

不使用聚簇的情况：

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

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

3．聚簇中的表不能TRUNCATE。

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

Although a normal index does not store null key values, cluster indexes storenull 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 onlyonce for all the rows of the same key value; it therefore uses less storagespace.

散列聚簇表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值访问数据。