1,某客户数据库系统发现如下SQL
select
M.BEGIN_DATE,
M.SERV_ID,
M.EXPIRE_DATE,
M.MAIN_REGION_CODE
M.SID,
M.SO_NBR ,
M.ROWID as MROWID___
from
I_WK_GROUP_MEMBER_852_ZXY M
where
M.SERV_ID = :1
AND M.MAIN_REGION_CODE = :2
AND M.SO_NBR = :3
AND M.SID = :4
order by
M.EXPIRE_DATE ASC
2，从执行计划看，使用全表扫描访问所需数据
3，表的统计信息
4，WHERE条件列的选择度不错
5，开始测试
5.1,数据库版本
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
5.2，构建和上述SQL引用的类似表结构
SQL> create table t_order(so_nbr int,begin_date date,sid int);
Table created.
SQL> insert into t_order select level,sysdate-level,level+2 from dual connect by level<=100000;
100000 rows created.
SQL> commit;
Commit complete.
SQL> exec dbms_stats.gather_table_stats(user,'t_order',cascade=>true);
PL/SQL procedure successfully completed.
5.3,未创建索引，使用全表扫描，逻辑读为319
SQL> set autot traceonly
SQL> select so_nbr,begin_date,sid from t_order where so_nbr=1 and begin_date=to_date('2015-04-11 00:00:00','yyyy-mm-dd hh24:mi:ss') and sid=3 order by begin_date desc;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2317089884
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 88 (2)| 00:00:02 |
|* 1 | TABLE ACCESS FULL| T_ORDER | 1 | 18 | 88 (2)| 00:00:02 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SO_NBR"=1 AND "SID"=3 AND "BEGIN_DATE"=TO_DATE('
2015-04-11 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
319 consistent gets
0 physical reads
0 redo size
471 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
5.4,创建SELECT的复合索引
SQL> set autot off
SQL> create index idx_t_order on t_order(so_nbr,begin_date,sid) online;
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t_order',cascade=>true);
PL/SQL procedure successfully completed.
5.5,构建复合索引后，执行计划为索引范围扫描，逻辑读为2（ORDER BY排序列在索引列中）
SQL> set autot traceonly
SQL> select so_nbr,begin_date,sid from t_order where so_nbr=1 and begin_date=to_date('2015-04-11 00:00:00','yyyy-mm-dd hh24:mi:ss') and sid=3 order by begin_date desc;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3942612543
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 1 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T_ORDER | 1 | 18 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SO_NBR"=1 AND "BEGIN_DATE"=TO_DATE(' 2015-04-11
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SID"=3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
471 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
5.6,构建不包含ORDER BY排序列的复合索引
SQL> set autot off
SQL> drop index idx_t_order;
Index dropped.
SQL> create index idx_t_order on t_order(so_nbr,sid) online;
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t_order',cascade=>true);
PL/SQL procedure successfully completed.
5.7，基于不包含ORDER BY 排序列的复合索引，仍会索引范围扫描，然后回表访问所需数据，逻辑读为3
SQL> select so_nbr,begin_date,sid from t_order where so_nbr=1 and begin_date=to_date('2015-04-11 00:00:00','yyyy-mm-dd hh24:mi:ss') and sid=3 order by begin_date desc;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2436638254
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_ORDER | 1 | 18 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_ORDER | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("BEGIN_DATE"=TO_DATE(' 2015-04-11 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
2 - access("SO_NBR"=1 AND "SID"=3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
471 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
5.8,仅基于ORDER BY 排序列构建索引
SQL> set autot off
SQL> drop index idx_t_order;
Index dropped.
SQL> create index idx_t_order on t_order(begin_date) online;
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t_order',cascade=>true);
PL/SQL procedure successfully completed.
5.9，仅基于ORDER BY 排序列构建索引，先是索引范围扫描，然后回表，逻辑读为2
SQL> set autot traceonly
SQL> select so_nbr,begin_date,sid from t_order where so_nbr=1 and begin_date=to_date('2015-04-11 00:00:00','yyyy-mm-dd hh24:mi:ss') and sid=3 order by begin_date desc;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2436638254
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_ORDER | 1 | 18 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_ORDER | 1 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SO_NBR"=1 AND "SID"=3)
2 - access("BEGIN_DATE"=TO_DATE(' 2015-04-11 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
2 consistent gets
0 physical reads
0 redo size
471 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
6.0,清空表记录
SQL> set autot off
SQL> truncate table t_order;
Table truncated.
6.1，增加表记录，对比仅ORDER BY 排序列的索引及包含ORDER BY 排序列的索引的性能区别
SQL> set autot off
SQL> truncate table t_order;
Table truncated.
SQL> insert into t_order select level,sysdate-level,level+2 from dual connect by level<=1000000;
1000000 rows created.
SQL> commit;
Commit complete.
SQL> drop index idx_t_order;
Index dropped.
6.2,包含order by排序列的复合索引
SQL> create index idx_t_order on t_order(so_nbr,begin_date,sid) online;
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t_order',cascade=>true);
PL/SQL procedure successfully completed.
6.3,包含order by排序列的复合索引，逻辑读为3
SQL> set autot traceonly
SQL> select so_nbr,begin_date,sid from t_order where so_nbr=1 and begin_date=to_date('2015-04-11 00:00:00','yyyy-mm-dd hh24:mi:ss') and sid=3 order by begin_date desc;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 3942612543
--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 3 (0)| 00:00:01 |
|* 1 | INDEX RANGE SCAN| IDX_T_ORDER | 1 | 18 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SO_NBR"=1 AND "BEGIN_DATE"=TO_DATE(' 2015-04-11
00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "SID"=3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
471 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
6.4,构建仅ORDER BY 排序列的索引
SQL> set autot off
SQL> drop index idx_t_order;
Index dropped.
SQL> create index idx_t_order on t_order(begin_date) online;
Index created.
SQL> exec dbms_stats.gather_table_stats(user,'t_order',cascade=>true);
PL/SQL procedure successfully completed.
6.5，仅ORDER BY 排序列的索引，逻辑读为3
SQL> select so_nbr,begin_date,sid from t_order where so_nbr=1 and begin_date=to_date('2015-04-11 00:00:00','yyyy-mm-dd hh24:mi:ss') and sid=3 order by begin_date desc;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2436638254
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 18 | 4 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID| T_ORDER | 1 | 18 | 4 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_T_ORDER | 1 | | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("SO_NBR"=1 AND "SID"=3)
2 - access("BEGIN_DATE"=TO_DATE(' 2015-04-11 00:00:00', 'syyyy-mm-dd
hh24:mi:ss'))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3 consistent gets
0 physical reads
0 redo size
471 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
小结：
1，构建包含ORDER BY 排序列的复合索引，不会回表
2，仅ORDER BY 排序列的索引，会回表，但逻辑读和包含ORDER BY 排序列的复合索引消耗的逻辑读相同，但成本不同，后者比前者低一些
3，不包含ORDER BY 排序列的索引，会回表，但这种逻辑读和成本比第1，2方案都高
个人简介
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/