Oracle中如何避免使用特定错误索引（二）-Oracle认证-计算机-珠峰网 www.my8848.com

Oracle中如何避免使用特定错误索引（二）

发布时间：2010/11/12 14:22:40 来源：www.xue.net 编辑：城市总裁吧

但是在这样一个条件下：where a=? and b=? and c=? group by b会用到哪个索引呢?在索引的分析数据不正确(很长时间没有分析)或根本没有分析数据的情况下，oracle往往会使用索引idx_b。通过执行计划的分析，这个索引的使用，将大大耗费查询时间。

　　比如在索引有统计信息，分析数据正确的情况下：

　SQL> select max(d) from mytest
　　2 where a=50 and b=50 and c=50
　　3 group by b;
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 422688974
　　--------------------------------------------------------------------------------
　　-------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
　　e |
　　--------------------------------------------------------------------------------
　　-------
　　| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:
　　00:01 |
　　| 1 | SORT GROUP BY NOSORT | | 1 | 16 | 2 (0)| 00:
　　00:01 |
　　| 2 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 16 | 2 (0)| 00:
　　00:01 |
　　|* 3 | INDEX RANGE SCAN | IDX_A | 1 | | 1 (0)| 00:
　　00:01 |
　　--------------------------------------------------------------------------------
　　-------
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　3 - access("A"=50 AND "B"=50 AND "C"=50)
　　Statistics
　　----------------------------------------------------------
　　1 recursive calls
　　0 db block gets
　　3 consistent gets
　　0 physical reads
　　0 redo size
　　513 bytes sent via SQL*Net to client
　　492 bytes received via SQL*Net from client
　　2 SQL*Net roundtrips to/from client
　　0 sorts (memory)
　　0 sorts (disk)
　　1 rows processed
　　但如果索引分析数据不正确:
　　SQL> select num_rows from user_tables
　　2 where table_name='MYTEST';
　　NUM_ROWS
　　----------
　　50000
　　SQL> analyze index idx_a delete statistics;
　　Index analyzed.
　　SQL> analyze index idx_b delete statistics;
　　Index analyzed.
　　SQL> select distinct_keys from user_indexes
　　2 where index_name in ('IDX_A','IDX_B');
　　DISTINCT_KEYS
　　-------------
　　SQL> select max(d) from mytest where a=50 and b=50 and c=50 group by b;
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 3925507835
　　--------------------------------------------------------------------------------
　　-------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
　　e |
　　--------------------------------------------------------------------------------
　　-------
　　| 0 | SELECT STATEMENT | | 1 | 16 | 2 (0)| 00:
　　00:01 |
　　| 1 | SORT GROUP BY NOSORT | | 1 | 16 | 2 (0)| 00:
　　00:01 |
　　|* 2 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 16 | 2 (0)| 00:
　　00:01 |
　　|* 3 | INDEX RANGE SCAN | IDX_B | 1 | | 1 (0)| 00:
　　00:01 |
　　--------------------------------------------------------------------------------
　　-------
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　2 - filter("A"=50 AND "C"=50)
　　3 - access("B"=50)
　　Statistics
　　----------------------------------------------------------
　　0 recursive calls
　　0 db block gets
　　3 consistent gets
　　0 physical reads
　　0 redo size
　　513 bytes sent via SQL*Net to client
　　492 bytes received via SQL*Net from client
　　2 SQL*Net roundtrips to/from client
　　0 sorts (memory)
　　0 sorts (disk)
　　1 rows processed

idx_a。

where a=? and b=? and c=? group by b||'' --如果b是字符类型
　　where a=? and b=? and c=? group by b+0 --如果b是数字类型

　　通过这样简单的改变，往往可以是查询时间提交很多倍

　　当然，我们也可以使用no_index提示，相信很多人没有用过，也是一个不错的方法：

SQL> select /*+ no_index(mytest,idx_b) */ max(d) from mytest where a=50 and b=50 and c=50 group by b;
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 422688974
　　--------------------------------------------------------------------------------
　　-------
　　| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
　　e |
　　--------------------------------------------------------------------------------
　　-------
　　| 0 | SELECT STATEMENT | | 1 | 16 | 9 (0)| 00:
　　00:01 |
　　| 1 | SORT GROUP BY NOSORT | | 1 | 16 | 9 (0)| 00:
　　00:01 |
　　| 2 | TABLE ACCESS BY INDEX ROWID| MYTEST | 1 | 16 | 9 (0)| 00:
　　00:01 |
　　|* 3 | INDEX RANGE SCAN | IDX_A | 500 | | 1 (0)| 00:
　　00:01 |
　　--------------------------------------------------------------------------------
　　-------
　　Predicate Information (identified by operation id):
　　---------------------------------------------------
　　3 - access("A"=50 AND "B"=50 AND "C"=50)
　　Statistics
　　----------------------------------------------------------
　　1 recursive calls
　　0 db block gets
　　3 consistent gets
　　0 physical reads
　　0 redo size
　　513 bytes sent via SQL*Net to client
　　492 bytes received via SQL*Net from client
　　2 SQL*Net roundtrips to/from client
　　0 sorts (memory)
　　0 sorts (disk)
　　1 rows processed