oracle 索引介绍（一）

索引广义上可以分为3类：B-树索引、位图索引以及索引组织表。

 

     B-树索引在各类应用中得到了广泛的使用。有很多种索引类型如分区索引、压缩索引、基于函数的索引都实现为B-索引。特殊的索引类型，如索引组织表以及索引组织表上的次级索引同样也实现B-树索引。

     位图索引的实现适用于不经常进行更新、插入和删除的列。它们更适合于具有较少唯一值的静态列。一个典型的例子就是在数据仓库应用中。在一张包含人口统计信息的表中的性别列是一个很好的例子，因为对于这一列只有很少的唯一值。

 

     全表扫描访问路径就一定是不好的吗?不一定。一种访问路径的效率对于不同的SQL语句构造、应用数据、数据的分布以及环境都是不同的。没有一种访问路径适用于所有的执行计划。在某些情况下，全表扫描访问路径要好过基于索引的访问。全表扫描和快速全扫描进行多块读取调用，而索引范围扫描或索引唯一扫描进行单块读取。多块读取的效率要比逐块进行的单块读取高很多。优化器的计算将这一区别也考虑了进去，从而能够恰当地选用基于索引的访问路径或全表访问路径。一般来说，OLTP应用将会主要使用基于索引的扫描路径而数据仓库将主要用全表扫描。最后要考虑的一点是并行，如果查询的谓语选择性并不是特别强，就可以使用并行来对查询进行调优，使其执行更快。一个使用并行全表扫描的执行计划的成本可能比串行索引范围扫描在本更低，从而优化器会选择更优的并行执行计划。

 

    选择进行索引的最佳列对于提高SQL访问性是非常关键的。对于索引列的选择应该与SQL语句中使用的谓语相匹配，下面是选择最优索引列时需要考虑的内容：

 

    1.如果应用代码访问某张表的时候，在某一列上使用等式或范围谓语，考虑对这一列进行索引就是一个很好的策略。对于多列索引，引导列应该是在大多数谓语中被使用。

 

    2.考虑谓语的基数以及列的选择度也是很重要的。例如，如果某个列只有两个唯一值并且是均匀分布的，那么这一列可能就不适合建立B-树索引，因为在这一列上使用等式谓语将会获取50%的数据行。另一方面，如果这个列有两个唯一值但不是均匀分布的，也就是说一个值仅在很少的数据行中出现且应用使用这个不常出现的列值来访问表，这种情况下就最好在这一列上建立索引。例如，TEST表processed列具有3个唯一值（P、N、E）。应通过谓语processed='N'来访问这张表，在processed列中仅有几行状态为'N'的未处理数据，因此通过索引来访问是最优的。但谓语为processed='Y'的查询就不应该使用索引，因为使用这个谓语几乎所有行都将被取出。可以使用直方图信息来使用优化器可以根据使用的是常量或绑定变量来选择最优执行计划。

 

     3.考虑列的排序，并安排好索引中列值的顺序以使其与应用访问模式相适应。例如，SALES表，PROD_ID列的选择度为1/72,而CUST_ID列的选择度为1/7059.看上去似乎CUST_ID列是进行索引更好的候选，因为该列的选择度较低。但是，如果应用声明了PROD_ID列上的等式谓语，而没有在谓语中声明CUST_ID列，那么CUST_ID列就不必进行索引，即使CUST_ID列具有更好的选择度。如果应用在PROD_ID和CUST_ID列都应用了谓语，那么最好在这两列上都建立索引并将CUST_ID列作为引导列。需要考虑的是列，是否在谓语中使用，而不是完全依赖于列的选择度。

 

    4.你还需要考虑索引的成本。插入、删除以及更新都需要维护索引，意味着如果在SALES表插入了一行，那么就需要在索引中加入一对与这一行数据相匹配的新值。如果索引列需要进行大量更新的话，这个索引的维护成本就更高。

 

      5.考虑列的长度。建有索引的列越长，索引也就越大。索引的成本可能会超过由索引带来的全部好处。较大的索引尺寸会增加UNDO和REDO区的大小。

 

     6.在多列索引中，如果引导列只有很少的唯一值，考虑将该索引建立为压缩索引。这些索引的尺寸会变得更小，因为压缩索引中不保存重复值。

 

    7.如果谓语在索引列上使用函数，这一列上的索引就不会被选用。例如，谓语TO_CHAR（PROD_ID） = :B1 ,需要建立索引函数

 

    8.不要在需要大幅修改的列上建立位图索引。位图索引的内部实现更适合于只有很少唯一值的只读列。如果索引进行了更新，位图索引的大小可能会迅速增大。对一个位图索引的过多修改，还可能会导致大量的锁资源争夺。位图索引在数据仓库中使用更普遍。

 

     9.在SQL语句中经常会声明IS NULL谓语，空值不存储在某个单独列的索引中，因此谓语IS NULL将不会使用索引。但空值是存储在多列索引中。通过使用另一个虚拟列来创建多列索引，就可以在IS NULL子句中启用索引。具体如下：

create table t1(n1 number ,n2 varchar2(100));
insert into t1 select object_id,object_name from all_objects where rownum < 101;
select * from t1;
create index t1_n1 on t1(n1);
select * from t1 where n1 is null;

 

重新建立索引：

create index t1_n10 on t1(n1,0);
select * from t1 where n1 is null;

 

• B-树索引

 

     B-树索引实现类似于倒置的树型结构，包括根节点、分枝节点和叶子节点，并且使用树遍历算法来搜索列值。叶子节点中包含一对（值，ROWID）值，值对应于索引键列，ROWID则表示行在数据块中的物理位置。分支节点包含叶子节点目录以及存储在其中的叶子节点的值范围。根节点包含分支节点目录以及这些分支节点所包括的值范围。B-树索引适合于具有较低选择度的列，如果列的选择度不够低，索引扫描就会较慢。并且，选择度不够的列将会从叶子块中取出大量的ROWID，从而导致对表进行过多的单块访问。

 

• 位图索引

 

    位图索引的组织结构和实现方式与B-树索引不同，使用位图来表示列值的行编号。位图索引不适合需要大量更新的列或具有较多DML操作的表。位图索适合于数据仓库。

 

create bitmap index t1_n1_bitmap on t1(n1) local;

 

• 索引组织表

 

     常规的数据表都是按照堆表的形式来组织的，因为表数据行能够存储在任何表数据块中。使用主键从常规的数据表中获取一行将会进行主键索引遍历，然后使用行编号来进行表数据块访问。在索引组织表（index organized tables,IOTs）中，表本身被组织为一个索引，所有列存储在索引树自身中，使用主键来访问数据行将只会包含索引访问。这种使用IOT进行访问的方法更好，因为所有列都可以通过访问索引结构来获取，从而避免了表访问。这是一种高效的访问模式，因为实现了访问次数的最小化。在常规表中，每一行都有一个行编号，一旦在表中建立了一行数据，它们就不再移动（可能会有行链接或行迁移，但行的头部不会移动）。不同的是，IOT数据行存储在索引结构自身中。因此，数据行可能由DML运算而迁移到不同的叶子块中，从而引起索引叶子块结构的分裂与合并。简单来说，IOT中的数据行没有物理行编号，而位于堆表中的数据行都会有一个固定的行编号。

 

IOT适合于具有下面特点的表:

    1.数据行长度较短的表。 数据列较少并且很短的表适合于IOT。如果数据行长度更长，索引结构就会过大，导致比堆表使用更多的资源。

 

    2.大多使用主键列进行访问的表 。尽管可以在IOT上建立次级索引，如果主键列较长则次级索引也可能会耗占大量资源。

create table t2(n1 number ,n2 varchar2(100),primary key (n1)) organization index;
insert into t2 select object_id,object_name from all_objects where rownum < 101;
select * from t2;

 

索引组织表是一种能够有效减少数据行较短且需要进行大量DML和SELECT活动的表中额外索引的特殊结构。但如果IOT的主键列较长，在其中加入次级索引可能会增大索引大小、UNDO、REDO区的大小。