Oracle数据库效率技巧:避免错误的索引

数据库 Oracle
Oracle数据库中是数据库索引在Oracle数据库中发挥着很大的作用,正是数据库索引的使用加快了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
 

#p#

 

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

#p#

 

  我们可以通过如下的技巧避免使用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

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

【编辑推荐】

  1. 详解Oracle数据库备份不同的恢复特性
  2. 由浅入深讲解MySQL数据库索引的选择性
  3. Oracle数据库11g创非集群基准测试世界纪录
  4. Oracle数据库开发技术经验浅谈
责任编辑:迎迎 来源: IT专家网论坛
相关推荐

2010-04-07 17:45:22

Oracle位图索引

2011-03-16 08:54:45

Oracle数据库索引

2010-10-27 14:15:44

Oracle数据库效率

2010-04-06 11:19:28

Oracle数据库

2011-04-02 09:23:19

MySQL数据库查询效率

2011-04-02 09:33:13

MySQL数据库查询效率

2011-04-02 09:33:08

MySQL数据库查询效率

2023-11-16 17:12:33

数据库oracle

2009-05-18 13:18:54

字符Oracle字符串

2010-04-19 13:31:42

Oracle索引

2011-04-13 16:48:56

索引数据库

2010-04-21 11:43:33

Oracle数据库

2010-03-18 09:28:14

Oracle数据库迁移

2011-09-02 10:06:51

OracleSqlLoad常用技巧

2010-05-10 18:54:12

Oracle数据库索引

2010-04-26 14:24:58

Oracle数据库索引

2011-03-01 16:30:55

Oracle

2011-03-17 14:09:25

Oracle数据库字符

2011-03-11 16:25:53

Oracle数据库

2010-04-09 13:59:48

Oracle数据库索引
点赞
收藏

51CTO技术栈公众号