Oracle数据库效率技巧：避免错误的索引(1)(2)

但是在这样一个条件下：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