Oracle索引详解

1.索引

1.1 索引

         索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据；Oracle存储索引的数据结构是 B 树，位图索引也是如此，只不过是叶子节点不同B数索引；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。

1.1.1 索引特点： 

第一.通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性。 

第二.可以大大加快数据的检索速度,这也是创建索引的最主要的原因。 

第三.可以加速表和表之间的连接,特别是在实现数据的参考完整性方面特别有意义。 

第四.在使用分组和排序子句进行数据检索时,同样可以显著减少查询中分组和排序的时间。 

第五.通过使用索引.可以在查询的过程中,使用优化隐藏器,提高系统的性能。 

1.1.2应该建索引列的特点：

• 在经常需要搜索的列上,可以加快搜索的速度； 
• 在作为主键的列上,强制该列的唯一性和组织表中数据的排列结构； 
• 在经常用在连接的列上,这些列主要是一些外键,可以加快连接的速度； 
• 在经常需要根据范围进行搜索的列上创建索引,因为索引已经排序.其指定的范围是连续的； 
• 在经常需要排序的列上创建索引,因为索引已经排序,这样查询可以利用索引的排序,加快排序查询时间； 
• 在经常使用在WHERE子句中的列上面创建索引,加快条件的判断速度。 

1.2 目的

        当查询 返回的记录数排序表 < 40%非排序表  < 7%且表的碎片较多（频繁增加、删除）时可以加快查询速度减少I/O操作消除磁盘排序

1.3 分类

1.3.1 逻辑上

Single column/Concatenated 单行索引/多行索引
Unique/NonUnique 唯一索引/非唯一索引

1.3.2、物理上

B-tree B树索引
Bitmap 位图索引
REVERSE 反向索引
HASHHASH索引
Function-based基于函数的索引
Partitioned/NonPartitioned 分区索引/非分区索引
Domain 域索引

1.4 各种索引详解

1.4.1 B树索引

Oracle中最常见的索引类型是b-tree索引，也就是B-树索引，以其同名的计算科学结构命名。CREATE INDEX语句时，默认就是在创建b-tree索引。没有特别规定可用于任何情况。

（1）特点：

1.   适合与大量的增、删、改（OLTP）
2.   不能用包含OR操作符的查询；
3.   适合高基数的列（唯一值多）(等值匹配)
4.   典型的树状结构；
5.   每个结点都是数据块；
6.   大多都是物理上一层、两层或三层不定，逻辑上三层；
7.   叶子块数据是排序的，从左向右递增；
8.   在分支块和根块中放的是索引的范围(范围查找)；

  索引列的值都存储在索引中。因此，可以建立一个组合(复合)索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了I/O量。

1.4.2、位图索引

位图索引非常适合于决策支持系统(Decision Support System，DSS)和数据仓库，它们不应该用于通过事务处理应用程序访问的表。它们可以使用较少到中等基数(不同值的数量)的列访问非常大的表。尽管位图索引最多可达30个列，但通常它们都只用于少量的列。

•         适合与决策支持系统；
•   做UPDATE代价非常高；
•   非常适合OR操作符的查询；
•   基数比较少的时候才能建位图索引；

位图对于低基数(少量的不同值)列来说非常快，这是因为索引的尺寸相对于B树索引来说小了很多

位图索引在批处理(单用户)操作中加载表(插入操作)方面通常要比B树做得好,当多个会话同时向表中插入行时不应该使用位图索引，在大多数事务处理应用程序中都会发生这种情况。在一个查询中合并多个位图索引后，可以使性能显著提高。位图索引使用固定长度的数据类型要比可变长度的数据类型好。

1.4.3 反向索引

如果在创建B树索引的时候索引列的值比较密集，这样不能够  很好的利用系统的并行操作来提高查询效率，可以讲索引列的值逆向存贮，这样就可以打破数据额度集中度。

不可以将反转键索引与位图索引或索引组织表结合使用。因为不能对位图索引和索引组织表进行反转键处理。

1.4.4HASH索引

           使用HASH索引必须要使用HASH集群。建立一个集群或HASH集群的同时，也就定义了一个集群键。这个键告诉Oracle如何在集群上存储表。在存储数据时，所有与这个集群键相关的行都被存储在一个数据库块上。如果数据都存储在同一个数据库块上，并且将HASH索引作为WHERE子句中的确切匹配，Oracle就可以通过执行一个HASH函数和I/O来访问数据——而通过使用一个二元高度为4的B树索引来访问数据，则需要在检索数据时使用4个I/O。其中的查询是一个等价查询，用于匹配HASH列和确切的值。Oracle可以快速使用该值，基于HASH函数确定行的物理存储位置。

HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。低估了集群键的不同值的数字可能会造成集群的冲突(两个集群的键值拥有相同的HASH值)。这种冲突是非常消耗资源的。冲突会造成用来存储额外行的缓冲溢出，然后造成额外的I/O。如果不同HASH值的数目已经被低估，您就必须在重建这个集群之后改变这个值。

 ALTER CLUSTER命令不能改变HASH键的数目。HASH集群还可能浪费空间。如果无法确定需要多少空间来维护某个集群键上的所有行，就可能造成空间的浪费。如果不能为集群的未来增长分配好附加的空间，HASH集群可能就不是最好的选择。如果应用程序经常在集群表上进行全表扫描，HASH集群可能也不是最好的选择。由于需要为未来的增长分配好集群的剩余空间量，全表扫描可能非常消耗资源。

1.4.5函数索引

       可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。

下面的查询就不能使用JOB列上的索引，除非它是基于函数的索引：

• select * from emp where UPPER(job) = 'MGR';

下面的查询使用JOB列上的索引，但是它将不会返回JOB列具有Mgr或mgr值的行：

• select * from emp where job = 'MGR';

可以创建这样的索引，允许索引访问支持基于函数的列或数据。可以对列表达式UPPER(job)创建索引，而不是直接在JOB列上建立索引，如：

create index EMP$UPPER_JOB on emp(UPPER(job));
尽管基于函数的索引非常有用，但在建立它们之前必须先考虑下面一些问题：

• 能限制在这个列上使用的函数吗？如果能，能限制所有在这个列上执行的所有函数吗
• 是否有足够应付额外索引的存储空间？
• 在每列上增加的索引数量会对针对该表执行的DML语句的性能带来何种影响？
• 基于函数的索引非常有用，但在实现时必须小心。在表上创建的索引越多，INSERT、UPDATE和DELETE语句的执行就会花费越多的时间。

1.4.6 分区索引和全局索引

        分区索引就是简单地把一个索引分成多个片断。通过把一个索引分成多个片断，可以访问更小的片断(也更快)，并且可以把这些片断分别存放在不同的磁盘驱动器上(避免I/O问题)。B树和位图索引都可以被分区，而HASH