oracle11g sql tuning

322e

oracle11g sql tuning--如何构建组合索引complex index part2.pdf
　　
前言
     如何为表构建科学有效的组合索引是一个复杂的问题，涉及众多因素；比如：不同业务场景使用的SQL，每个业务场景SQL使用WHERE条件列的构成情况，每个WHERE条件列的选择度，WHERE条件列到底是等值表达式还是非等值表达式，表的每种业务场景使用的频度如何，调用次数如何，DML及DDL的各占比例如何；是否存在原有的索引。    在之前的文章我们已经测试过where条件是等值表达式，本文我们测试下等值表达式和非等值表达式情况，又该如何构建组合索引　　
测试结论
1,cbo优化器在多个复合索引中会选择WHERE条件中等值表达式为前导列，非等值表达式为后导列的复合索引　　
测试明细
1,创建测试表　　SQL> create table t_2col(a int,b int);
　　
　　
　　Table created.
　　
　　
　　2，插入2列选择性好的测试数据
　　SQL> insert into t_2col select level,level from dual connect by level
　　
　　
　　10000 rows created.
　　
　　
　　SQL> commit;
　　
　　
　　Commit complete.
　　
　　
　　3，创建A列索引

　　SQL> create index>　　
　　
　　Index created.
　　
　　
　　4，获取执行计划
　　SQL> select count(a) from t_2col where a=1 and b>3;
　　
　　
　　
　　
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 3968822790
　　
　　
　　--------------------------------------------------------------------------------------------

　　|>　　--------------------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT             |             |     1 |    26 |     2   (0)| 00:00:01 |
　　|   1 |  SORT AGGREGATE              |             |     1 |    26 |            |          |
　　|*  2 |   TABLE ACCESS BY INDEX ROWID| T_2COL      |     1 |    26 |     2   (0)| 00:00:01 |

　　|*  3 |    INDEX RANGE SCAN          |>　　--------------------------------------------------------------------------------------------
　　
　　

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　
　　
　　2 - filter("B">3)
　　3 - access("A"=1)
　　
　　
　　Note
　　-----
　　- dynamic sampling used for this statement (level=2)
　　
　　
　　
　　
　　Statistics
　　----------------------------------------------------------
　　0  recursive calls
　　0  db block gets
　　3  consistent gets
　　0  physical reads

　　0  redo>　　525  bytes sent via SQL*Net to client
　　519  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　
　　
　　SQL> 
　　
　　
　　5，创建A,B列复合索引

　　SQL> create index>　　
　　
　　Index created.
　　
　　
　　6，获取执行计划
　　SQL> select count(a) from t_2col where a=1 and b>3;
　　
　　
　　
　　
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 2618778677
　　
　　
　　---------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT  |             |     1 |    26 |     0   (0)| 00:00:01 |
　　|   1 |  SORT AGGREGATE   |             |     1 |    26 |            |          |

　　|*  2 |   INDEX RANGE SCAN|>　　---------------------------------------------------------------------------------
　　
　　

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　
　　
　　2 - access("A"=1 AND "B">3 AND "B" IS NOT NULL)
　　
　　
　　Note
　　-----
　　- dynamic sampling used for this statement (level=2)
　　
　　
　　
　　
　　Statistics
　　----------------------------------------------------------
　　0  recursive calls
　　0  db block gets
　　2  consistent gets
　　0  physical reads

　　0  redo>　　525  bytes sent via SQL*Net to client
　　519  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　
　　
　　SQL> 
　　
　　
　　7,构建A,B反序组合索引

　　SQL> create index>　　
　　
　　Index created.
　　
　　
　　8，获取执行计划
　　SQL> select count(a) from t_2col where a=1 and b>3;
　　
　　
　　
　　
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 2618778677
　　
　　
　　---------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT  |             |     1 |    26 |     0   (0)| 00:00:01 |
　　|   1 |  SORT AGGREGATE   |             |     1 |    26 |            |          |

　　|*  2 |   INDEX RANGE SCAN|>　　---------------------------------------------------------------------------------
　　
　　

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　
　　
　　2 - access("A"=1 AND "B">3 AND "B" IS NOT NULL)
　　
　　
　　Note
　　-----
　　- dynamic sampling used for this statement (level=2)
　　
　　
　　
　　
　　Statistics
　　----------------------------------------------------------
　　0  recursive calls
　　0  db block gets
　　2  consistent gets
　　0  physical reads

　　0  redo>　　525  bytes sent via SQL*Net to client
　　519  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　
　　
　　SQL> 
　　
　　
　　9，清除表数据重新插入数据
　　SQL> set autot off
　　SQL> truncate table t_2col;
　　
　　
　　Table truncated.
　　
　　
　　SQL> insert into t_2col select level,mod(level,1000) from dual connect by level
　　
　　
　　10000 rows created.
　　
　　
　　SQL> commit;
　　
　　
　　Commit complete.
　　
　　
　　SQL> exec dbms_stats.gather_table_stats(user,'t_2col',cascade=>true);
　　
　　
　　PL/SQL procedure successfully completed.
　　
　　
　　10,获取执行计划
　　SQL> select count(a) from t_2col where a=1 and b>3;
　　
　　
　　
　　
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 2618778677
　　
　　
　　---------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT  |             |     1 |     8 |     1   (0)| 00:00:01 |
　　|   1 |  SORT AGGREGATE   |             |     1 |     8 |            |          |

　　|*  2 |   INDEX RANGE SCAN|>　　---------------------------------------------------------------------------------
　　
　　

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　
　　
　　2 - access("A"=1 AND "B">3 AND "B" IS NOT NULL)
　　
　　
　　
　　
　　Statistics
　　----------------------------------------------------------
　　0  recursive calls
　　0  db block gets
　　2  consistent gets
　　0  physical reads

　　0  redo>　　525  bytes sent via SQL*Net to client
　　519  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　
　　
　　SQL> 
　　
　　
　　11,重新插入数据
　　SQL> set autot off
　　SQL> truncate table t_2col;
　　
　　
　　Table truncated.
　　
　　
　　SQL> insert into t_2col select level,mod(level,3) from dual connect by level
　　
　　
　　10000 rows created.
　　
　　
　　SQL> commit;
　　
　　
　　Commit complete.
　　
　　
　　SQL> exec dbms_stats.gather_table_stats(user,'t_2col',cascade=>true);
　　
　　
　　PL/SQL procedure successfully completed.
　　
　　
　　12,获取执行计划
　　SQL> select count(a) from t_2col where a=1 and b>3;
　　
　　
　　
　　
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 2618778677
　　
　　
　　---------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT  |             |     1 |     7 |     1   (0)| 00:00:01 |
　　|   1 |  SORT AGGREGATE   |             |     1 |     7 |            |          |

　　|*  2 |   INDEX RANGE SCAN|>　　---------------------------------------------------------------------------------
　　
　　

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　
　　
　　2 - access("A"=1 AND "B">3 AND "B" IS NOT NULL)
　　
　　
　　
　　
　　Statistics
　　----------------------------------------------------------
　　0  recursive calls
　　0  db block gets
　　2  consistent gets
　　0  physical reads

　　0  redo>　　525  bytes sent via SQL*Net to client
　　519  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　
　　
　　13，重插表数据
　　SQL> set autot off
　　SQL> truncate table t_2col;
　　
　　
　　Table truncated.
　　
　　
　　SQL> insert into t_2col select mod(level,1000),mod(level,1000) from dual connect by level
　　
　　
　　10000 rows created.
　　
　　
　　SQL> commit;
　　
　　
　　Commit complete.
　　
　　
　　SQL> exec dbms_stats.gather_table_stats(user,'t_2col',cascade=>true);
　　
　　
　　PL/SQL procedure successfully completed.
　　
　　
　　SQL> set autot traceonly
　　
　　
　　14,获取执行计划
　　SQL> select count(a) from t_2col where a=1 and b>3;
　　
　　
　　
　　
　　Execution Plan
　　----------------------------------------------------------
　　Plan hash value: 2618778677
　　
　　
　　---------------------------------------------------------------------------------

　　|>　　---------------------------------------------------------------------------------
　　|   0 | SELECT STATEMENT  |             |     1 |     8 |     1   (0)| 00:00:01 |
　　|   1 |  SORT AGGREGATE   |             |     1 |     8 |            |          |

　　|*  2 |   INDEX RANGE SCAN|>　　---------------------------------------------------------------------------------
　　
　　

　　Predicate Information (identified by operation>　　---------------------------------------------------
　　
　　
　　2 - access("A"=1 AND "B">3 AND "B" IS NOT NULL)
　　
　　
　　
　　
　　Statistics
　　----------------------------------------------------------
　　0  recursive calls
　　0  db block gets
　　2  consistent gets
　　0  physical reads

　　0  redo>　　525  bytes sent via SQL*Net to client
　　519  bytes received via SQL*Net from client
　　2  SQL*Net roundtrips to/from client
　　0  sorts (memory)
　　0  sorts (disk)
　　1  rows processed
　　
　　
　　SQL> 
　　
8年oracle从业经验，具备丰富的oracle技能，目前在国内北京某专业oracle服务公司从事高级技术顾问。      服务过的客户：          中国电信          中国移动          中国联通          中国电通          国家电网          四川达州商业银行          湖南老百姓大药房          山西省公安厅          中国邮政          北京302医院               河北廊坊新奥集团公司  　　项目经验:
           中国电信3G项目AAA系统数据库部署及优化           中国联通CRM数据库性能优化           中国移动10086电商平台数据库部署及优化           湖南老百姓大药房ERR数据库sql优化项目           四川达州商业银行TCBS核心业务系统数据库模型设计和RAC部署及优化           四川达州商业银行TCBS核心业务系统后端批处理存储过程功能模块编写及优化           北京高铁信号监控系统RAC数据库部署及优化           河南宇通客车数据库性能优化           中国电信电商平台核心采购模块表模型设计及优化           中国邮政储蓄系统数据库性能优化及sql优化           北京302医院数据库迁移实施           河北廊坊新奥data guard部署及优化           山西公安厅身份证审计数据库系统故障评估           国家电网上海灾备项目4 node rac+adg 　　贵州移动crm及客服数据库性能优化项目
　　贵州移动crm及客服务数据库sql审核项目
　　
 联系方式：          手机：18201115468          qq   :   305076427          qq微博: wisdomone1          新浪微博：wisdomone9          qq群：275813900              itpub博客名称：wisdomone1    http://blog.itpub.net/9240380/