Oracle性能优化之索引篇

Oracle性能优化之索引篇目录1.索引基本概念........................................................................................................................21.1索引的分类................................................................................................................21.2索引的扫描类型........................................................................................................41.3索引的工作原理.......................................................................................................52.索引的影响因素..................................................................................................................62.1索引的选择性............................................................................................................62.2索引的集群因子.........................................................................................................82.3二元高度..................................................................................................................102.4影响索引运行的SQL.............................................................................................123.执行计划的获取与解读......................................................................................................133.1获取解释计划..........................................................................................................133.2获取执行计划..........................................................................................................143.3执行计划的解读......................................................................................................183.4执行计划的优化策略..............................................................................................194.优化方案示例......................................................................................................................194.1问题定位..................................................................................................................194.2案例展示..................................................................................................................221.索引基本概念索引是用于加速数据存取的数据对象。合理的使用索引可以大大降低i/o次数，从而提高数据访问性能。索引存在如下几个优点：（1）通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。（2）可以大大加快数据的检索速度，这也是创建索引的最主要的原因。（3）可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。（4）在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。（5）通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。是不是索引建得越多越好呢？当然不是，索引还存在如下不足之处：（1）创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。（2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，系统要占用大约为表1.2倍的硬盘和内存空间来保存索引，如果要建立聚簇索引，那么需要的空间就会更大。（3）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。实践表明，不恰当的索引不但于事无补，反而会降低系统性能。综上所述，提高查询效率是以消耗一定的系统资源为代价的，索引不能盲目的建立，这是考验一个DBA是否优秀的很重要的指标。1.1索引的分类索引根据不同的角度分类，可以有多种不同的分类，按照数据存储方式，可以分为B*树、反向索引、位图索引，按照索引列的个数分类，可以分为单列索引、复合索引；按照索引列值的唯一性，可以分为唯一索引和非唯一索引。此外还有函数索引，全局索引，分区索引等等。如下介绍几种常见的索引分类：（1）B树索引，即二叉树索引。（NORMAL）B树索引在Oracle中是一个通用索引。在创建索引时它就是默认的索引类型。B树索引可以是一个列的(简单)索引，也可以是组合/复合(多个列)的索引。B树索引最多可以包括32列。B树索引位于雇员表的last_name列上。这个索引的二元高度为3；接下来，Oracle会穿过两个树枝块(branchblock)，到达包含有ROWID的树叶块。在每个树枝块中，树枝行包含链中下一个块的ID号。树叶块包含了索引值、ROWID，以及指向前一个和后一个树叶块的指针。Oracle可以从两个方向遍历这个二叉树。B树索引保存了在索引列上有值的每个数据行的ROWID值。Oracle不会对索引列上包含NULL值的行进行索引。如果索引是多个列的组合索引，而其中列上包含NULL值，这一行就会处于包含NULL值的索引列中，且将被处理为空(视为NULL)。CREATEINDEXIDX_MEDFAC_DAY_BYAGE_001onDW_MEDFAC_DAY_BYAGE(FBUS_CODE,BUS_DATE,HOS_CODE,DISTRICT_CITY,DISTRICT_COUNTY)tablespaceHECD_INDEXpctfree10initrans2maxtrans255storage(initial64Knext16Kminextents1maxextentsunlimited);（2）位图索引（BITMAP）位图索引非常适合于决策支持系统和数据仓库，他们不应该用于通过事务处理应用程序（多用户）访问的表。位图索引适用于基数较少的列，如sex列。如果用户频繁地根据sex列的值进行查询，这就是位图索引的基列。尽管位图索引最多可达30列，但是通常他们只用于少量的列。当一个表内包含多个位图索引时，就可以体会到位图索引的性能。oracle可以合并从每个位图索引得到的结果集，快速删除不必要的数据。在单用户模式中，在大多数条目不会向位图添加新值的前提下，位图索引的批量插入的性能比B树索引性能高。为什么不建议在事物处理应程序中使用位图索引？B树索引的索引值中包含ROWID，这样oracle就可以在行级别上锁定索引。位图索引存储为压缩的索引值，其中包含了一定范围的ROWID，因此oracle会根据一个给定的值索引所有范围内的ROWID，这种锁定类型可能在某些DML语句中造成死锁。位图索引有很多限制，如下所示：1）基于规则的优化器不会考虑位图索引。2）当执行ALTERTABLE语句并修改包含有位图索引的列时，会使位图索引失效。3）位图索引不包含任何列数据，并且不能用于任何类型的完整性检查。4）位图索引不能被声明为唯一索引。5）位图索引的最大长度为30.创建位图索引的语句如下：createbitmapindexIDX_MEDFAC_DAY_BYAGE_002onDW_MEDFAC_DAY_BYAGE(FBUS_CODE)tablespaceHECD_INDEXpctfree10initrans2maxtrans255storage(initial64Knext1Mminextents1maxextentsunlimited);（3）HASH索引使用HASH索引必须要使用HASH集群。通常，HASH对于一些包含有序值的静态数据非常有效。（4）索引组织表索引组织表会把表的存储结构改成B树结构，以表的主键进行排序。对于一些涉及精确匹配和范围搜索的语句，索引组织表提供了一种基于键的快速数据访问机制。基于主键值的UPDATE和DELETE语句的性能也同样得以提高。如果不会频繁的根据主键列查询数据，则需要在索引组织表中的其他列上创建二级索引。（5）反转键索引如果数据以反转键索引存储，这些数据的值就会与原先存储的数值相反，索引会为每次新插入的行更新不同的索引块。当载入一些有序的数据时，索引肯定会碰到与I/O相关的一些瓶颈。解决这个问题可以有两种解决方法：1）可以把索引表空间存放在能够把文件物理分割在多个磁盘上的磁盘体系结构上。2）如果磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。（6）基于函数的索引如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。对于优化器所使用的基于函数的索引来说，必须把初始参数QUERY_REWRITE_ENABLED设定为TRUE。（7）位图连接索引位图连接索引是基于两个表的连接的位图索引，在数据仓库环境中使用这种索引改进连接维度表和事实表的查询的性能。创建语法如下：createbitmapindexsales_cust_name_idxonsales(customer.customer_name)fromsales,cutomerwheresales.customer_id=customer.customer_id;位图连接的语法比较特别，其中包含from子句和where子句，并且引用两个单独的表。索引列通常都是维度表中的描述列，比如维度表（基表）customer表，并且它的主键是customer_id，则通常索引customer_name这样的描述列。如果用户接下来用customer_name列在where子句中查询sales表和customer表，优化器则会就可以使用位图连接索引快速返回结果。位图连接索引一般会有如下限制：只可以索引维度表（基表）中的列。用于连接表的列必须是维度表中的主键或唯一约束；如果是复合主键，则必须使用连接中的每一列。不可以