Oracle索引化表,oracle索引详解

一、索引概述

如果一个数据表中存有海量的数据记录，当对表执行指定条件的查询时。常规的查询方法会将所有的记录都读取出来，然后再把读取的每一条记录与查询条件进行对比，最后返回满足条件的记录。这样进行操作的时间开销和I/O开销都很大。对于这种情况，就可以考虑通过建立索引来减小系统开销。

如果要在表中查询指定的记录，在没有索引的情况下，必须遍历整个表，而有了索引之后，只需要在索引中找到符合查询条件的索引字段值，就可以通过保存在索引中的ROWID快速找到表中对应的记录。例如，如果将表看做一本书，索引的作用类似于书中的目录。在没有目录的情况下，要在书中查找指定的内容必须阅读全文，而有了目录之后，只需要通过目录就可以快速找到包含所需内容的页码(相当于ROWID)。

用户可以在Oracle中创建多种类型的索引，以适应各种表的特点。按照索引数据的存储方式可以将索引分为B树索引、位图索引、反向键索引和基于函数的索引；按照索引列的唯一性可以分为唯一索引和非唯一索引；按照索引列的个数可以分为单列索引和复合索引。

建立和规划索引时。必须选择合适的表和列，如果选择的表和列不合适，不仅无法提高查询速度，反而会极大地降低DML操作的速度，所以建立索引必须注意以下几点：

1.索引应该建立在WHERE子句频繁引用列表上，如果在大表上频繁使用某列或某几个列作为条件执行索引操作，并且检索行数低于总行数的15%，那么应该考虑在这些列上建立索引。

2.如果经常需要基于某列或者某几个列排序操作，那么应该在这些列上建立索引可以加快数据排序速度。

3.限制表的索引个数。索引主要用于加快查询速度，但会降低DML操作的速度。索引越多，DML操作速度越慢，尤其会极大地影响INSERT和DELETE操作的速度。因此，规划索引时，必须仔细权衡查询和DML的需求。

4.指定索引块空间的使用参数。基于表建立索引时，Oracle会将相应表列数据添加到索引块。为索引块添加数据时，Oracle会按照PCTFREE参数在索引块上预留部分空间，该预留空间是为将来的INSERT操作准备的。如果将来在表上执行大量INSERT操作，那么应该在建立索引时设置较大的PCTFREE。

5.将表和索引部署到相应的表空间，可以简化表空间的管理；将表和索引部署到不同的表空间，可以提高访问性能。

6.当在大表上建立索引时，使用NOLOGGING选项可以最小化重做记录。使用NOLOGGING选项可以节省重做日志空间、降低索引建立时间、提高索引并行建立的性能。

7.不要在小表上建立索引。

8.为了提高多表连接的性能，应该在连接列上建立索引。

二、创建索引

在创建索引时，Oracle首先对将要建立索引的字段进行排序，然后将排序后的字段值和对应记录的ROWID存储在索引段中。建立索引可以使用CREATE INDEX语句，通常由表的所有者来建立索引。如果要以其他用户身份建立索引，则要求用户必须具有CREATE ANY INDEX系统权限或者相应表的INDEX对象权限。

1.建立B树索引

B树索引是Oralce数据库中最常用的索引类型(也是默认的)，它是以B树结构组织并且存放索引数据的。默认情况下，B树索引中的数据是以升序方式排序的。如果表包含的数据非常多，并且经常在WHERE字句中引用某列或某几个列，则应该基于该列或这几个列建立B树索引。B树索引由根节点块、分支节点块和叶子节点块组成。其中主要数据都集中在叶子节点块所指向的数据行。

根节点块：索引顶级块，它包含指向下一级节点的信息。

分支节点块：它包含指向下一节点的信息。

叶子节点块：通常也称为叶子，它包含索引入口数据，索引入口包含索引列的值和记录行对应的物理地址ROWID。

在B树索引中无论用户要搜索哪个分支的叶块，都可以保证所经过的索引层次是相同的。Oracel采用这种方式的索引，可以确保无论索引条目位于何处，都只需花费相同的I/O即可获取它，这就是为什么被称为B树索引。

如果在WHERE字句中要经常应用某列或者某几列，应该基于这些列建立B树索引。

2.建立位图索引

索引的作用简单地说就是能够通过给定的索引列值，快速地找到相应的记录。在B树索引中，通过在索引中保存排序的索引列的值以及记录的物理地址ROWID来实现快速查找。但是对于一些特殊的表，B树索引的效率可能会降低。

例如在某个具有性别列的表中，该列的所用取值只能是男或女。如果在性别列上创建B树索引，那么创建的B树只有两个分支。当列的基数很低时，为其建立B树索引显然不合适。“基数低”表示在索引列中，所有取值的数量比表中行的数量少。如“性别”只有两个取值；再比如某个拥有10000行的表，它的一个列包含100个不同的取值，则该列仍然满足低基数的要求，因为该列与该行数的比例为1%。Oracle推荐一个列的基数小于1%时，这些列不再适合建立B树索引，而使用于位图索引。

3.建立反向键索引

反向键索引是一个特殊类型的B树索引，在顺序递增列上建立索引时非常有用。反向键索引的工作原理非常简单，在存储结构方面它与常规的B树索引相同。然而，如果用户使用序列在表中输入记录，则反向键索引首先指向每个列键值的字节，然后在反向后的新数据上进行索引。例如，如果用户输入的索引列为2011，则反向转换后为1102；9527反向转换后为7259.需要注意，刚才提及的两个序列编号是递增的，但是当进行反向键索引时确是非递增的。这意味着如果将其添加到子叶节点，可能会在任意的子节点中进行。这样就使得新数据在值的范围上的分布通常比原来的有序数更均匀。