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



相关阅读:
php设计模式 Bridge (桥接模式)
iis 服务器应用程序不可用的解决方法
创建本地的PEAR
使用AJAX异步获取数据
支持php4、php5的mysql数据库操作类
调整FreeBSD的虚拟内存系统
函数式JavaScript编程指南
file_get_contents函数不能使用的解决方法
PHP, JScript 和 VBScript 函数和类的语法
vBulletin Forum 2.3.xx SQL Injection
VBS教程:VBscript属性-功能介绍
XP/2003 Server操作系统配置与管理
ext 列表页面关于多行查询的办法
如何让Win7全屏幕幻灯放映图片
快速导航
PHP MySQL HTML CSS JavaScript MSSQL AJAX .NET JSP Linux Mac ASP 服务器 SQL jQuery C# C++ java Android IOS oracle MongoDB SQLite wamp 交通频道 作文范文 一片纸的遭遇 2015中考作文范文:选择出发 学院秘书处明年工作规划 奶奶您幸苦啦! 学 滑 轮 读《小青石》有感作文400字 登白鸡峰漫记 被忽略的爱作文1000字 话题作文精彩构思80则--话题72:创新 2016保险业务员工作总结 那段开心的时刻 小学五年级作文400字:家乡变了 回眸童年作文500字 成功男士的着装礼仪 如何心疼你的另一半 想念圣诞节 小卫兵 我的未来是否还有你们 幻。听 召开党委专题民主生活会情况报告 散文诗:再见了,2016 薛法根:悄悄地长大 淡去的年味 医院护士个人述职报告最新 浅析动漫衍生品的现状和前景 游长安诸寺联句。平康坊菩萨寺。书事联句 高中入团志愿书表格填写100字 一个月的契约恋爱时光 父爱作文1400字 欢不见,美人殁 作文父母给我的爱 团队精神的作文 只有彼此拥抱才能展翅飞翔作文900字 江津市人大常委会经济工委关于我市畜牧业发展情况的调查报告_调查报告 14年最经典的中考考试祝福语 教育教学先锋岗事迹材料(优秀教师) 我的野生动物朋友读后感100字 粥事 在哪里安放飘忽的心 窃听器250字 市中医院推行总额预付制度调研报告 师德无价,师爱无声演讲稿 公司财务会计个人工作总结 『醉卧春风里,梦中有她』〖四〗 学会去爱它们 小学英语作文:我喜欢香蕉(I like banana) 我的烦恼 我想跟未来的你谈个恋爱 关爱家人的生命 我心中指导明灯科学发展观演讲

Copyright © 2016 phpStudy |