Oracle性能分析8：使用索引

32e2

这一节主要讲述索引的使用，首先介绍怎么在查询中避免使用索引，然后介绍优化器怎么判断是否使用索引，并介绍了强制使用索引的方法，最后介绍了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

禁止使用索引并行扫描。