怎么避免使用特定(错误)的索引

有的时候，使用错误的索引会导致Oracle数据库的效率明显下降，通过一些方法或者是技巧可以有效的避免这个问题:
这个例子中,如果我想使用idx_a而不是idx_b.

SQL> create table test
  2  (a int,b int,c int,d int);
Table created.

SQL> begin
  2     for i in 1..50000
  3     loop
  4       insert into mytest values(i,i,i,i);
  5     end loop;
  6     commit;
  7  end;
  8  /

PL/SQL procedure successfully completed.

SQL> create index idx_a on mytest(a,b,c);

Index created.

SQL> create index idx_b on mytest(b);

Index created.

如表mytest,有字段a,b,c,d，在a,b,c上建立联合索引idx_a(a,b,c)，在b上单独建立了一个索引idx_b(b)。

在正常情况下，where a=? and b=? and c=?会用到索引idx_a，where b=?会用到索引idx_b
比如：
SQL> analyze table mytest compute statistics;

Table analyzed.

SQL> select num_Rows from user_tables where table_name='MYTEST';

  NUM_ROWS
----------
     50000

SQL> select distinct_keys from user_indexes where index_name='IDX_A';

DISTINCT_KEYS
-------------
        50000

SQL> set autotrace traceonly
SQL> select d from mytest
  2  where a=10 and b=10 and c=10;


Execution Plan
----------------------------------------------------------
Plan hash value: 1542625214

--------------------------------------------------------------------------------
------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |

--------------------------------------------------------------------------------
------

|   0 | SELECT STATEMENT            |        |     1 |    16 |     2   (0)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MYTEST |     1 |    16 |     2   (0)| 00:0
0:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_A  |     1 |       |     1   (0)| 00:0
0:01 |

--------------------------------------------------------------------------------
------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("A"=10 AND "B"=10 AND "C"=10)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        508  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 d from mytest
  2  where b=500;


Execution Plan
----------------------------------------------------------
Plan hash value: 530004086

--------------------------------------------------------------------------------
------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time
     |

--------------------------------------------------------------------------------
------

|   0 | SELECT STATEMENT            |        |     1 |     8 |     2   (0)| 00:0
0:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| MYTEST |     1 |     8 |     2   (0)| 00:0
0:01 |

|*  2 |   INDEX RANGE SCAN          | IDX_B  |     1 |       |     1   (0)| 00:0
0:01 |

--------------------------------------------------------------------------------
------


Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("B"=500)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          4  consistent gets
          0  physical reads
          0  redo size
        508  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

    但是在这样一个条件下：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_b，而使用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

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/12361284/viewspace-246526/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/12361284/viewspace-246526/