Oracle 使用错索引,Oracle数据库效率技巧：避免错误的索引

导读：有的时候，使用错误的索引会导致Oracle数据库的效率明显下降，通过一些方法或者是技巧可以有效的避免这个问题，下文中就为大家带来避免使用错误的数据库索引的，以提高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

上文中主要是以代码的形式为大家讲解的，看起来可能是不太容易理解，大家要深入其中去学习，这个技巧是非常实用的，希望大家能够从中收获。

【编辑推荐】

【责任编辑：迎迎 TEL：(010)68476606】