SQL调优之九:堆组织表,索引组织表以及表聚簇
说在最前头,一切快慢都是相对来说的
一,堆组织表(heap-organized table)
默认情况下,我们用Create table创建出来的表就是堆组织表,在往这种表里面插入/导入数据的时候不会按某种特殊排列方式的。
表中的每一行都有一个独一的ROWID,当行移动的时候ROWID也会跟着变化,即便是在同一个heap里面移动。
举例来说,你从汉语词典撕了几页出来,然后把它们堆到你面前的桌子上,这个时候你就得到一个堆"表"。
1,你不用进行排序,这就是堆表的无序
2,你拿到一张纸就往上扔,所以插入很快
3,你要改一张纸的页码,所以你要从这堆纸里面找到你要的那一张,因为无序,所以查找很慢
4,改完你又把它往前面一扔就好了,所以存入很快
5,你更改的纸的位置在”堆”里面变了,所以ROWID会随着位置改变
6,为了快点找到纸的位置,你要写一个目录,就跟词典的目录一样,这就是索引
7,索引写着纸的位置,你通过索引找数据的时候,就是先看索引确定位置“ROWID”,然后再去表里找数据
8,你要找到其中的几页纸,因为位置随机,所以通过全表扫描获取到的数据的先后顺序也是随机的
总结:随机I/O,插入快,查找慢,这也就是为什么我们平时建议要走索引的原因(一般情况)
二,索引组织表 Index Organized Tables (IOT)
索引组织表是以类似于B树索引结构存储的一种表结构。
1,在索引组织表中,行数据是和主键索引存放在一起的。因此该表适用于经常检索主键的情况
2,每一个B数索引里面的记录含有其他非索引列的数据,因此可以减少存储,同时也可以减少I/O
3,对索引组织表做DML操作的时候,等同于对索引做操作,它在插入的过程需要进行定位,找到指定的叶块,然后再插入数据,某些极端情况下,它可能还要进行分块,分枝干的操作,所以DML操作相对于堆表来说要慢,不适合用于频繁更新的表
4,索引组织表适用于文献检索,空间数据以及OLAP应用:
- 不常更新
- 主要对主键查询
- 适合使用索引扫描的查询,既选择性好的范围扫描,唯一扫描
- 特定的数据结构需求,排序
创建语法:
CREATE TABLE locations
(id NUMBER(10) NOT NULL,
description VARCHAR2(50) NOT NULL,
map BLOB,
CONSTRAINT pk_locations PRIMARY KEY (id)
)
ORGANIZATION INDEX
TABLESPACE iot_tablespace
PCTTHRESHOLD 20
INCLUDING description
OVERFLOW TABLESPACE overflow_tablespace;
Row Overflow Area:
因为IOT表的索引和数据同时存在,所以有时候行数据可能出现溢出的情况,也就是太大了的情况,这个时候可以配置一个row overflow area。
如果指定了行溢出空间,数据库会把IOT表分为两部分:
- 索引条目:
这一部分包含了所有主键列的值,以及一个指向溢出部分(在另一个位置)的物理ROWID,以及一部分非主键列的值。这一部分存储在索引段上
- overflow部分:
这一部分则存储剩下的非主键列的值,存储在创建表时指定的溢出空间:OVERFLOW TABLESPACE overflow_tablespace;
碎片化处理:
对于IOT表变得碎片化的情况,可以用以下命令移动表:
没有启用overflow:
ALTER TABLE table_name MOVE INITRANS 10;
ALTER TABLE table_name MOVE ONLINE INITRANS 10;
启用了overflow:
ALTER TABLE table_name MOVE TABLESPACE iot_tablespace OVERFLOW TABLESPACE overflow_tablespace;
堆组织表和索引组织表的比较:
堆组织表 | 索引组织表 |
rowid唯一识别行数据, 主键约束可以选择性创建或者不 |
主键唯一识别行数据, 主键约束必须创建 |
ROWID列里的物理rowid允许创建第二个索引 | ROWID列里的逻辑rowid允许创建第二个索引 |
单独的行可以通过rowid直接访问 | 单独的行可以通过主键非直接访问 |
顺序全表扫描可以以某种顺序返回所有行 | 全索引扫描或者快速全索引扫描可以以某种顺序返回所有行 |
可以和其他表一起存在一个表聚簇里面 | 不可以 |
可以有一个LONG类型的列以及多个LOB数据类型的列 | 可以有LOB数据类型,但不能有LONG类型 |
可以包含虚拟列(仅有关系型堆组织表支持) | 不可以含有虚拟列 |
三,表集群/表聚簇 Table Cluster
一个表聚簇是指共享同一/一些列的几个表的组合,并且相关的数据会存储在相同的数据块里面。
即几个table,以相同的一个或多个列作为cluster key,来创建一个table cluster。
例子:
一个HR管理着两个书柜,
其中一个书柜上的盒子放着雇员的相关文件夹,--employee表
另一个书柜上的盒子则放着部门的相关文件夹,--department表
用户经常要找某个部门里面所有雇员的信息,--经常按某种关联关系来查询数据
为了能更容易拿到想要的数据,HR决定把所有的盒子放到一个书柜上。--创建Table Cluster
这一次是按部门编号来划分盒子,--Cluster Key, department_id列
比如,编号20的部门的所有相关文件,比如说雇员的相关信息以及部门本身的信息,都会被放到同一个盒子里面;编号100的部门的所有相关文件夹,则会放到另一个盒子。--相关数据会放到相同的数据块里面,并且department_id只存放一次
优点:
- 以Cluster Key作为连接条件的查询,磁盘I/O减少
- 获取数据的时间变快
- 使用空间变少,因为Cluster Key的值不会会单独每一行存放
不适合使用的情况:
- 频繁更新的表
- 经常需要做全表扫描的表
- 需要做truncate的表
索引化的聚簇
索引化的聚簇,是指在cluster key上的建立索引的聚簇,其必须在数据插入之前创建。
这种索引跟非聚簇的表上的索引是一样的,是单独管理的,同时也可以指定一个单独的表空间。
哈希聚簇
哈希聚簇跟索引聚簇是一样的,只不过索引键替换成了一个哈希函数。
这种情况下没有单独的索引,数据即索引。
关于聚簇可以看下这个链接做的实验:http://blog.itpub.net/17203031/viewspace-774405/