Oracle性能分析8：使用索引

这一节主要讲述索引的使用，首先介绍怎么在查询中避免使用索引，然后介绍优化器怎么判断是否使用索引，并介绍了强制使用索引的方法，最后介绍了Oracle的并行处理方法。

避免使用索引

虽然你创建了索引，但有些查询你可能需要避免使用这些索引，或者你为了做一些测试，希望看看各种情况下查询的情况，也希望能够避免使用一些索引或者索引扫描方式。Oracle提供了方式来达到这些目地，就是在查询中使用hint信息，具体情况如下。

避免使用某个索引

如果索引的选择性很差，那么也许使用其它索引或者使用全表扫描的效率会更加高效，这时你可以考虑避免使用该索引，方法如下：

/*+ no_index(table_name index_name)*/

表示查询不使用table_name的index_name索引，下面是一个实例：

select /*+ no_index(historyalarm idx_historyalarm$clear)*/
 position1
  from historyalarm

优化器不使用指定索引后，仍然可以使用表上的其它索引，你也可以指定忽略一组索引：

select /*+ no_index(historyalarm idx_historyalarm$clear,idx_historyalarm$cleargmt)*/
 position1
  from historyalarm

如果你只是指定了no_index提示，而没有列出任何索引，优化器将忽略指定表的所有索引：

select /*+ no_index(historyalarm)*/
 position1
  from historyalarm

避免快速扫描

语法如下：

/*+ no_index_ffs(table_name index_name)*/

表示查询不使用table_name的index_name索引的索引快速扫描，下面是一个实例：

select /*+ no_index_ffs(historyalarm idx_historyalarm$clear)*/
 position1
  from historyalarm

强制表扫描

强制表扫描也是避免使用索引的一个方法，如下：

/*+ full(table_name)*/

表示查询表table_name时强制使用全表扫描，下面是一个实例：

select /*+ full(historyalarm)*/
 position1
  from historyalarm

使用索引

查询时优化器会计算使用索引的成本信息，以决定是否使用索引，表的选择性（通过过滤）乘以索引聚蔟因子（clustering factor），决定了通过索引访问表的成本。下面我们从聚蔟因子开始。

聚蔟因子

聚蔟因子对应user_indexes视图的CLUSTERING_FACTOR列，用于表示索引的叶子项怎么和表格的行对应关系，下面是聚蔟因子的计算方法：
 为在索引中的每一个条目，Oracle比较条目所在表的数据块和前一个的索引条目数据块，如果是不同的数据块，则聚蔟因子加1，否则继续。
因此，聚蔟因子的最小值等于索引上的所有条目对应的数据块数量（由于在单列或多列上的索引都不包含空数据，因此这将等于在表中包含了数据的数据块的数量），而最大值等于索引上的条目的数量（每两个相邻的条目都在不同的数据块块上）。
下面看一个例子，我们使用表T1和T2（请看“Oracle性能分析4：数据访问方法之全扫描”），下面是查找表T1上的索引IDX_T1_ID和T2上的索引IDX_T2_ID的聚蔟因子：

select i.INDEX_NAME, i.CLUSTERING_FACTOR
  from user_indexes i
 where index_name = 'IDX_T1_ID'
    or index_name = 'IDX_T2_ID'
	
INDEX_NAME	CLUSTERING_FACTOR
IDX_T1_ID	17
IDX_T2_ID	1700

由于表T1中相同的数据都按顺序存储，因此索引条目的顺序和数据的存储顺序基本一致，因此T1表的聚蔟因子达到了最低值；而T2表中的数据相同值都存放在不同的数据块上，因此索引条目中任意两个相邻的条目对应的数据都在不同的块上，因此聚蔟因子达到了最大值。因此T1表的IDX_T1_ID索引使用代价很低，而T2表的IDX_T2_ID索引使用的代价则很高。
需要注意的是聚蔟因子与表中数据而不是与索引有关，因此重建索引对它没有任何影响。

强制使用索引

当Oracle的优化器无法选择正确的索引时（优化器并不是完美的，它并不总能准确地了解数据的性质和分布），我们可以考虑强制使用索引，下面介绍使用hint信息来强制使用索引的方法。
需要注意的是在使用提示之前，应先查看是否有对象的正确的统计信息，并检查SQL语句的构造方法，提示应该是无路可走时的选择。

使用hint信息

index提示指示优化器对特定的表使用索引扫描，例如：

select /*+ index(historyalarm idx_historyalarm$clear)*/
 position1
  from historyalarm

这里相当于强制优化器使用idx_historyalarm$clear，也可以不指定索引，这时优化器就会选择它认为最优的索引：

select /*+ index(historyalarm)*/
 position1
  from historyalarm

这样做的缺陷在于优化器可能会选择错误的索引，因此通常这并不是好的选择。
如果你需要在index提示中指定多个索引，可以使用index_combine提示，如下；

select /*+ index_combine(historyalarm idx_historyalarm$01 idx_historyalarm$02)*/
 position1
  from historyalarm

这时，优化器会使用两个指定索引成本最低的组合，如果不提供索引列表，则优化器将根据其成本估计使用索引的最佳组合。
除了上面介绍的hint信息，还有下面的常用的跟索引相关的hint信息：
 1）index_asc：指定按升序做索引范围扫描；
 2）index_desc：指定按降序顺序扫描升序索引，或者按升序扫描降序索引；
 3）index_join：当两个索引包含返回查询结果所需的所有列时，使用该提示连接索引，从而从索引直接获取所有数据，语法类似index_combine；
 4）index_ss：执行索引跳跃式扫描；
 5）index_ffs：以索引快速全扫描的方式访问数据。

hint失效

导致hint失效的主要原因如下：
 1）优化器认为使用hint会导致错误的结果。这往往由于数据中包含许多空值，因此有一些不会出现在索引中的行，导致索引扫描最后可能会导致错误的结果；
 2）SQL语句中的表指定了别名，这时hint中也必须使用别名，否则hint会被忽略，如：

select /*+ index(h idx_historyalarm$raise)*/
 position1
  from historyalarm h

3）查询涉及表连接，基于优化器选额的连接方法，无法使用索引。这时往往可以通过添加更多的提示信息来解决（如指定表的连接方式等）。

并行

在Oracle中支持并行处理操作，下面介绍和并行处理相关的概念。

parallel

指定SQL执行的并行度，这个值会覆盖表自身设定的并行度，如：

select /*+ parallel(h 4)*/ * from historyalarm h

parallel有以下几种级别：
 1）parallel：总是并行执行，数据库计算并行度，值大于等于2；
 2）parallel(default)：和parallel相同；
 3）parallel(auto)：数据库计算并行度，值大于等于1，当并行度为1时表示串行执行；
 4）parallel(manual)：优化器使用表的并行度；
 5）parallel(integer)：优化器使用指定的并行度。

并行度

在创建表时指定并行度

create table t1 parallel 4 as 
select trunc((rownum - 1) / 100) id, rownum value
  from dba_source
 where rownum <= 10000

查看表的并行度

select degree from user_tables where table_name = 'T1'

修改表的并行度

alter table t1 parallel(degree 3)

取消表的并行度

alter table t1 noparallel

上面的方法也同样适用于索引。

no_parallel

在SQL中禁止使用并行。

select /*+ no_parallel(h)*/ * from historyalarm h

parallel_index

在处理分区索引时，可以指示优化器使用多个并发的并行服务器，来并行化剂中类型的索引操作。可以并行化的操作包括索引范围扫描、全索引扫描和索引快速全扫描。
下面是通过parallel_index提示来指定分区索引的并行扫描操作：

select /*+ parallel_index(h idx_historyalarm$06 4)*/
 alarmclearedtime, alarmkey
  from historyalarm h

整数4表示索引扫描的并行度。

no_parallel_index

禁止使用索引并行扫描。