好久没写blog了，确实刚来南京才一个多月，新的工作也需要慢慢适应，学习脚步因此也确实放慢了很多，虽然不见得以后一直做技术，但是如果能做一天就当认真对待，言归正传，有个sql语句因为走全表扫描的执行计划需要优化，具体如下： SELECT OID, SUBSID FROM
好久没写blog了，确实刚来南京才一个多月，新的工作也需要慢慢适应，学习脚步因此也确实放慢了很多，虽然不见得以后一直做技术，但是如果能做一天就当认真对待，言归正传，有个sql语句因为走全表扫描的执行计划需要优化，具体如下：
SELECT OID, SUBSID
FROM SUBS_SERVICE A
WHERE SERVICEID IN (:SERVICEID1, :SERVICEID2, :SERVICEID3)
AND ENDDATE
AND ENDDATE >= SYSDATE - 395
AND REGION = 23
AND STATUS 8
AND STATUS 9
AND NOT EXISTS (SELECT 1
FROM SUBS_SERVICE B
WHERE B.REGION = A.REGION
AND B.SUBSID = A.SUBSID
AND B.SERVICEID IN (:SERVICEID1, :SERVICEID2, :SERVICEID3)
AND NVL(ENDDATE, SYSDATE) > SYSDATE - 365)
AND ROWNUM
Plan hash value: 591110695
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 263K(100)| | | |
|* 1 | COUNT STOPKEY | | | | | | | |
|* 2 | FILTER | | | | | | | |
|* 3 | FILTER | | | | | | | |
| 4 | PARTITION RANGE SINGLE | | 90 | 3060 | 263K (2)| 00:52:44 | 4 | 4 |
|* 5 | TABLE ACCESS FULL | SUBS_SERVICE | 90 | 3060 | 263K (2)| 00:52:44 | 4 | 4 |
| 6 | PARTITION RANGE SINGLE | | 1 | 22 | 6 (0)| 00:00:01 | KEY | KEY |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID| SUBS_SERVICE | 1 | 22 | 6 (0)| 00:00:01 | KEY | KEY |
|* 8 | INDEX RANGE SCAN | IDX_SUBS_SERVICE_SUBSID | 1 | | 4 (0)| 00:00:01 | KEY | KEY |
--------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM
2 - filter( IS NULL)
3 - filter(SYSDATE@!-395
5 - filter((INTERNAL_FUNCTION("SERVICEID") AND "STATUS"8 AND "STATUS"9 AND "ENDDATE"
"ENDDATE">=SYSDATE@!-395 AND "REGION"=23))
7 - filter(("B"."REGION"=:B1 AND NVL("ENDDATE",SYSDATE@!)>SYSDATE@!-365))
8 - access("B"."SUBSID"=:B1)
filter(("B"."SERVICEID"=:SERVICEID1 OR "B"."SERVICEID"=:SERVICEID2 OR "B"."SERVICEID"=:SERVICEID3))
这里很明显有个bad的执行计划table access full SUBS_SERVICE的全表扫描，通过谓词条件基本可以得知正式因为这个bad的执行计划导致，而优化这个sql其实很简单就是建立合适的索引。
那么接下来如何建立索引了，我们看执行计划ID：5对应的谓词条件是enddate和serviceid的两个列的条件，而serviceid的不同值较少，enddate的不同值相对较多。
Table Number Empty Chain Average Global Sample Date
Name of Rows Blocks Blocks Count Row Len Stats Size MM-DD-YYYY
------------------------------ -------------- --------------- ------------ -------- ------- ------ -------------- ----------
SUBS_SERVICE 322,621,100 56,409,85 0 0 109 YES 16,131,055 09-28-2014
Column Distinct Number Number Sample Date
Name Values Density Buckets Nulls Size MM-DD-YYYY
------------------------------ ------------ ----------- ------- ------------ -------------- ----------
SUBSID 18,668,054 .00000005 1 0 16,131,055 09-28-2014
REGION 4 .25000000 1 0 16,131,055 09-28-2014
SERVICEID 402 .00248756 1 0 16,131,055 09-28-2014
ENDDATE 1,628,520 .00000061 1 258,160,160 3,223,047 09-28-2014
STATUS 7 .14285714 1 0 16,131,055 09-28-2014
...
这种情况下，我们一般可能都是选择的enddate作为前导列，serviceid作为后导列的组合索引，也是为了能够有效的利用enddate作为前导列去驱动别的sql语句来利用这个索引。
挖掘shared pool和sql历史的执行信息：
SQL> select sql_id
2 from gv$sql_plan
3 where options = 'FULL'
4 and object_owner = 'TBCS'
5 and object_name like 'SUBS_SERVICE'
6 and instr(filter_predicates, 'ENDDATE') > 0
7 and instr(filter_predicates, 'SERVICEID')
no rows selected
SQL> select sql_id
2 from dba_hist_sql_plan
3 where object_owner = 'TBCS'
4 and object_name like 'SUBS_SERVICE'
5 and instr(filter_predicates, 'ENDDATE') > 0
6 and instr(filter_predicates, 'SERVICEID')
挖掘shared pool中发觉没有单独出现enddate的sql语句，那么是没有别的sql语句能够利用enddate为前缀的索引来索引扫描的。
虽然这里enddate的前缀索引可能没有办法作用于别的sql语句，但是对于这个sql而言，建立索引是必然的，首先建立enddate的前缀的复合索引ind_enddate_serviceid
SQL> create index tbcs.ind_enddate_serviceid on tbcs.subs_service(enddate,serviceid)
Index created.
执行上述sql语句，查看每个步骤产生的逻辑读和资源消耗
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID f1cmqc5sag5s0, child number 0
-------------------------------------
SELECT /*+gather_plan_statistics*/OID, SUBSID
FROM tbcs.SUBS_SERVICE A
WHERE SERVICEID IN (:SERVICEID1, :SERVICEID2, :SERVICEID3)
AND ENDDATE
AND ENDDATE >= SYSDATE - 395
AND REGION = 23
AND STATUS 8
AND STATUS 9
AND NOT EXISTS (SELECT 1
FROM tbcs.SUBS_SERVICE B
WHERE B.REGION = A.REGION
AND B.SUBSID = A.SUBSID
AND B.SERVICEID IN (:SERVICEID1, :SERVICEID2, :SERVICEID3)
AND NVL(ENDDATE, SYSDATE) > SYSDATE - 365)
AND ROWNUM
Plan hash value: 2714263820
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200 |00:00:16.94 | 2619 | 1797 |
|* 1 | COUNT STOPKEY | | 1 | | 200 |00:00:16.94 | 2619 | 1797 |
|* 2 | FILTER | | 1 | | 200 |00:00:16.94 | 2619 | 1797 |
|* 3 | FILTER | | 1 | | 261 |00:00:44.31 | 1237 | 1218 |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| SUBS_SERVICE | 1 | 90 | 261 |00:00:44.31 | 1237 | 1218 |
|* 5 | INDEX RANGE SCAN | IND_ENDDATE_SERVICEID | 1 | 20 | 2078 |00:00:02.73 | 873 | 859 |
| 6 | PARTITION RANGE SINGLE | | 257 | 1 | 58 |00:00:04.86 | 1382 | 579 |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID | SUBS_SERVICE | 257 | 1 | 58 |00:00:04.86 | 1382 | 579 |
|* 8 | INDEX RANGE SCAN | IDX_SUBS_SERVICE_SUBSID | 257 | 1 | 325 |00:00:04.22 | 1057 | 510 |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM
2 - filter( IS NULL)
3 - filter(SYSDATE@!-395
4 - filter(("STATUS"8 AND "STATUS"9 AND "REGION"=23))
5 - access("ENDDATE">=SYSDATE@!-395 AND "ENDDATE"
filter(("SERVICEID"=:SERVICEID1 OR "SERVICEID"=:SERVICEID2 OR "SERVICEID"=:SERVICEID3))
7 - filter(("B"."REGION"=:B1 AND NVL("ENDDATE",SYSDATE@!)>SYSDATE@!-365))
8 - access("B"."SUBSID"=:B1)
filter(("B"."SERVICEID"=:SERVICEID1 OR "B"."SERVICEID"=:SERVICEID2 OR "B"."SERVICEID"=:SERVICEID3))
sql语句已经走了ind_enddate_serviceid的索引范围扫描，执行计划id：5索引范围扫描消耗的逻辑读为873，而后回表达到了1237的逻辑读
而我们看ID:5 oracle通过(enddate,serviceid)组合索引IND_ENDDATE_SERVICEID只能用access过滤满足enddate的条件，需要filter再次对serviceid的条件进行过滤
这种情况下index range scan只需要扫描满足enddate的谓词条件，会扫描更多的叶块节点，产生更多的逻辑读。
那么既然ind_enddate_serviceid的索引其实在index range scan部分索引的范围扫描只针对了enddate条件的，那么是否我们可以直接建立enddate的单列索引了。
SQL> create index tbcs.ind_enddate on tbcs.subs_service(enddate);
Index created.
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID c12gvu0qs792j, child number 0
-------------------------------------
SELECT /*+gather_plan_statistics index(A,ind_enddate)*/OID, SUBSID
FROM tbcs.SUBS_SERVICE A
WHERE SERVICEID IN (:SERVICEID1, :SERVICEID2, :SERVICEID3)
AND ENDDATE
AND ENDDATE >= SYSDATE - 395
AND REGION = 23
AND STATUS 8
AND STATUS 9
AND NOT EXISTS (SELECT 1
FROM tbcs.SUBS_SERVICE B
WHERE B.REGION = A.REGION
AND B.SUBSID = A.SUBSID
AND B.SERVICEID IN (:SERVICEID1, :SERVICEID2, :SERVICEID3)
AND NVL(ENDDATE, SYSDATE) > SYSDATE - 365)
AND ROWNUM
Plan hash value: 439697064
------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200 |00:10:41.48 | 37372 | 32157 |
|* 1 | COUNT STOPKEY | | 1 | | 200 |00:10:41.48 | 37372 | 32157 |
|* 2 | FILTER | | 1 | | 200 |00:10:41.48 | 37372 | 32157 |
|* 3 | FILTER | | 1 | | 261 |00:06:07.29 | 35990 | 32095 |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| SUBS_SERVICE | 1 | 90 | 261 |00:06:07.29 | 35990 | 32095 |
|* 5 | INDEX RANGE SCAN | IND_ENDDATE | 1 | 2810 | 199K|00:00:03.08 | 660 | 646 |
| 6 | PARTITION RANGE SINGLE | | 257 | 1 | 58 |00:00:00.11 | 1382 | 62 |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID | SUBS_SERVICE | 257 | 1 | 58 |00:00:00.11 | 1382 | 62 |
|* 8 | INDEX RANGE SCAN | IDX_SUBS_SERVICE_SUBSID | 257 | 1 | 325 |00:00:00.09 | 1057 | 50 |
------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM
2 - filter( IS NULL)
3 - filter(SYSDATE@!-395
4 - filter((INTERNAL_FUNCTION("SERVICEID") AND "STATUS"8 AND "STATUS"9 AND "REGION"=23))
5 - access("ENDDATE">=SYSDATE@!-395 AND "ENDDATE"
7 - filter(("B"."REGION"=:B1 AND NVL("ENDDATE",SYSDATE@!)>SYSDATE@!-365))
8 - access("B"."SUBSID"=:B1)
filter(("B"."SERVICEID"=:SERVICEID1 OR "B"."SERVICEID"=:SERVICEID2 OR "B"."SERVICEID"=:SERVICEID3))
由于ind_enddate是单列索引，每个叶块存储的键值会多些，那么index range scan部分消耗应该会更小，其实果然也是如我们推断的，但是我们发觉在通过ind_enddate回表时逻辑读增加到了35990
这个是由于虽然扫描的索引叶块更少了，但是扫描完后不能做任何进一步的过滤，导致需要回表的rowid非常多，导致回表成本增加，而在回表后再进行serviceid谓词条件的过滤。
那么除了上述的两种索引的创建方式，是否还有一种更优秀的：
如果我们创建serviceid作为前导列，enddate作为后导列的索引ind_serviceid_enddate
SQL> create index tbcs.ind_serviceid_enddateon tbcs.subs_service(serviceid,enddate);
SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 48bfmd32ag3nk, child number 0
-------------------------------------
SELECT /*+gather_plan_statistics*/OID, SUBSID
FROM tbcs.SUBS_SERVICE A
WHERE SERVICEID IN (:SERVICEID1, :SERVICEID2, :SERVICEID3)
AND ENDDATE
AND ENDDATE >= SYSDATE - 395
AND REGION = 23
AND STATUS 8
AND STATUS 9
AND NOT EXISTS (SELECT 1
FROM tbcs.SUBS_SERVICE B
WHERE B.REGION = A.REGION
AND B.SUBSID = A.SUBSID
AND B.SERVICEID IN (:SERVICEID1, :SERVICEID2, :SERVICEID3)
AND NVL(ENDDATE, SYSDATE) > SYSDATE - 365)
AND ROWNUM
Plan hash value: 771671576
----------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
----------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 200 |00:00:00.01 | 1745 |
|* 1 | COUNT STOPKEY | | 1 | | 200 |00:00:00.01 | 1745 |
|* 2 | FILTER | | 1 | | 200 |00:00:00.01 | 1745 |
|* 3 | FILTER | | 1 | | 256 |00:00:00.01 | 373 |
| 4 | INLIST ITERATOR | | 1 | | 256 |00:00:00.01 | 373 |
|* 5 | TABLE ACCESS BY GLOBAL INDEX ROWID| SUBS_SERVICE | 1 | 90 | 256 |00:00:00.01 | 373 |
|* 6 | INDEX RANGE SCAN | IND_SERVICEID_ENDDATE | 1 | 20 | 3186 |00:00:00.01 | 33 |
| 7 | PARTITION RANGE SINGLE | | 256 | 1 | 56 |00:00:00.01 | 1372 |
|* 8 | TABLE ACCESS BY LOCAL INDEX ROWID | SUBS_SERVICE | 256 | 1 | 56 |00:00:00.01 | 1372 |
|* 9 | INDEX RANGE SCAN | IDX_SUBS_SERVICE_SUBSID | 256 | 1 | 328 |00:00:00.01 | 1044 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM
2 - filter( IS NULL)
3 - filter(SYSDATE@!-395
5 - filter(("STATUS"8 AND "STATUS"9 AND "REGION"=23))
6 - access((("SERVICEID"=:SERVICEID1 OR "SERVICEID"=:SERVICEID2 OR "SERVICEID"=:SERVICEID3)) AND
"ENDDATE">=SYSDATE@!-395 AND "ENDDATE"
8 - filter(("B"."REGION"=:B1 AND NVL("ENDDATE",SYSDATE@!)>SYSDATE@!-365))
9 - access("B"."SUBSID"=:B1)
filter(("B"."SERVICEID"=:SERVICEID1 OR "B"."SERVICEID"=:SERVICEID2 OR "B"."SERVICEID"=:SERVICEID3))
以serviceid前导列索引前提下，执行计划ID:6 oracle通过(serviceid,enddate)组合索引IND_SERVICEID_ENDDATE可以全部用access的方式完成index range scan,
此时index range scan只会扫描同时满足serviceid和enddate的谓词条件的索引叶块,相对索引ind_enddate_serviceid而言，serviceid作为前导列的索引在index range scan扫描更少索引叶块，相应的逻辑读也会更低。
而如果我们细心观察发现索引ind_enddate_serviceid和ind_serviceid_enddate主要区别是在是否在index range scan阶段能够直接access方式读取数据，而在回表阶段其实消耗的逻辑读大体相同。
ind_enddate_serviceid回表的逻辑读=1237-873=364
ind_serviceid_enddate回表的逻辑读=373-33=340
区别主要在于index range scan部分的区别，两个复合索引扫描的叶块是完全不同的，索引能够全部走access的索引必然成本要低很多，而先走access然后走filter的索引，虽然回表成本不变，但是index range scan部分会扫描很多不满足的条件的leaf block，导致index range scan部分逻辑读增加。
这里我们再看一个sql语句，如果是两个范围的索引如何去创建索引:
SQL> SELECT OID, SUBSID
2 FROM tbcs.SUBS_SERVICE A
3 WHERE SERVICEID between :serviceid1 and :serviceid3
4 AND ENDDATE
5 AND ENDDATE >= SYSDATE - 395
6 AND REGION = 23
7 AND STATUS 8
8 AND STATUS 9
9 AND NOT EXISTS (SELECT 1
10 FROM tbcs.SUBS_SERVICE B
11 WHERE B.REGION = A.REGION
12 AND B.SUBSID = A.SUBSID
13 AND B.SERVICEID IN (:SERVICEID1, :SERVICEID2, :SERVICEID3)
14 AND NVL(ENDDATE, SYSDATE) > SYSDATE - 365)
15 AND ROWNUM
200 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 928699648
---------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 30 | 1020 | 4813 (1)| 00:00:58 | | |
|* 1 | COUNT STOPKEY | | | | | | | |
|* 2 | FILTER | | | | | | | |
|* 3 | FILTER | | | | | | | |
|* 4 | TABLE ACCESS BY GLOBAL INDEX ROWID| SUBS_SERVICE | 30 | 1020 | 4807 (1)| 00:00:58 | 4 | 4 |
|* 5 | INDEX RANGE SCAN | IND_SERVICEID_ENDDATE | 12 | | 4776 (1)| 00:00:58 | | |
| 6 | PARTITION RANGE SINGLE | | 1 | 22 | 6 (0)| 00:00:01 | KEY | KEY |
|* 7 | TABLE ACCESS BY LOCAL INDEX ROWID | SUBS_SERVICE | 1 | 22 | 6 (0)| 00:00:01 | KEY | KEY |
|* 8 | INDEX RANGE SCAN | IDX_SUBS_SERVICE_SUBSID | 1 | | 4 (0)| 00:00:01 | KEY | KEY |
---------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM
2 - filter( NOT EXISTS (SELECT 0 FROM "TBCS"."SUBS_SERVICE" "B" WHERE "B"."SUBSID"=:B1 AND "B"."REGION"=:B2 AND
NVL("ENDDATE",SYSDATE@!)>SYSDATE@!-365 AND ("B"."SERVICEID"=:SERVICEID1 OR "B"."SERVICEID"=:SERVICEID2 OR
"B"."SERVICEID"=:SERVICEID3)))
3 - filter(SYSDATE@!-395=:SERVICEID1)
4 - filter("STATUS"8 AND "STATUS"9 AND "REGION"=23)
5 - access("SERVICEID">=:SERVICEID1 AND "ENDDATE">=SYSDATE@!-395 AND "SERVICEID"
"ENDDATE"
filter("ENDDATE"=SYSDATE@!-395)
7 - filter("B"."REGION"=:B1 AND NVL("ENDDATE",SYSDATE@!)>SYSDATE@!-365)
8 - access("B"."SUBSID"=:B1)
filter("B"."SERVICEID"=:SERVICEID1 OR "B"."SERVICEID"=:SERVICEID2 OR "B"."SERVICEID"=:SERVICEID3)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
1732 consistent gets
2 physical reads
0 redo size
7107 bytes sent via SQL*Net to client
663 bytes received via SQL*Net from client
15 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
200 rows processed
这里看见如果是两个范围的谓词，在执行计划ID：5中对应的谓词信息中显示同时有access和filter，而比较奇怪的是access阶段其实已经有了enddate的过滤条件，但是filter中又包含了同样的access的过滤条件，那么这个index range scan究竟是怎么完成扫描的。
这里oracle是先找到了”SERVICEID”>=:SERVICEID1 AND “ENDDATE”>=SYSDATE@!-395索引入口，然后通过索引的双向指针左右滑动来查找数据，但是由于range scan的过程中，没办法保证每个leaf block都是满足enddate的条件的，事实也是确实如此，指针滑动过程中肯定有可能出现不满足enddate的条件的数据，比如这里出现了(serviceid2,enddate-10000)的键值，而且serviceid2是大于serviceid1的，这个键值也会出现在（serviceid1,enddate-365)的右边，所以在access完成后还需要filter满足enddate的leaf block。
创建复合索引时:如果单纯为了调整某类sql语句，不考虑别的sql是否能够最大程度的使用该索引，一般将等值条件的列作为索引的前导列，这样cbo能够尽可能的在index range scan部分只扫描满足条件的leaf block。
可以参考下兔子的一篇大作： http://blog.chinaunix.net/uid-7655508-id-3639188.html
本文出自：http://www.dbaxiaoyu.com, 原文地址：http://www.dbaxiaoyu.com/archives/2354, 感谢原作者分享。
