oracle11g sql tuning--如何构建组合索引complex index part2

  • wisdomone1

    2015-06-06 23:08:19
  • Oracle
  • 原创

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;


10000 rows created.


SQL> commit;


Commit complete.


3，创建A列索引
SQL> create index idx_t_col_1 on t_2col(a);


Index created.


4，获取执行计划
SQL> select count(a) from t_2col where a=1 and b>3;




Execution Plan
----------------------------------------------------------
Plan hash value: 3968822790


--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   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          | IDX_T_COL_1 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------


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


   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 size
        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 idx_t_col_2 on t_2col(a,b);


Index created.


6，获取执行计划
SQL> select count(a) from t_2col where a=1 and b>3;




Execution Plan
----------------------------------------------------------
Plan hash value: 2618778677


---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    26 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |    26 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T_COL_2 |     1 |    26 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------


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


   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 size
        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 idx_t_col_2_reverse on t_2col(b,a);


Index created.


8，获取执行计划
SQL> select count(a) from t_2col where a=1 and b>3;




Execution Plan
----------------------------------------------------------
Plan hash value: 2618778677


---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |    26 |     0   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |    26 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T_COL_2 |     1 |    26 |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------


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


   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 size
        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;


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


---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |     8 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |     8 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T_COL_2 |     1 |     8 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------


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


   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 size
        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;


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


---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |     7 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |     7 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T_COL_2 |     1 |     7 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------


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


   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 size
        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;


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


---------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |     1 |     8 |     1   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE   |             |     1 |     8 |            |          |
|*  2 |   INDEX RANGE SCAN| IDX_T_COL_2 |    10 |    80 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------


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


   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 size
        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/
请使用浏览器的分享功能分享到微信等