autotrace得到了虚假执行计划

    一天朋友问我一个很基础的问题：函数索引是不是在sql中要使用了建立索引的函数才能被使用？我都没思考就回答是！那么问题来了，接下来朋友把一条sql的执行计划发给了我，说他的一条sql语句，语句中没有使用函数，但是执行计划中走了函数索引。sql如下：

1. SQL> select count(1)
   
2.     from (SELECT CALLER caller,
   
3.                  callee callee,
   
4.                  customer_name customerName,
   
5.                  CALL_STATE callState,
   
6.                  CALL_RESULT callResult,
   
7.                  START_TIME startTime,
   
8.                  CONTACT_LENGTH contactLength,
   
9.                  HANGUP_FLAG hangupFlag,
   
10.                  c.agent_code || '[' ||
    
11.                  (select u.user_name
    
12.                     from tbl_sys_users u
    
13.                    where u.user_code = c.user_code) || ']' userCode,
    
14.                  AGENT_CODE agentCode,
    
15.                  ACCEPT_AREA acceptArea,
    
16.                  satisfication satisfication,
    
17.                  CUST_LEVEL custLevel,
    
18.                  DIRECTION direction,
    
19.                  c.remark remark,
    
20.                  d.department_name distArea,
    
21.                  d.department_id distAreaId,
    
22.                  CONTACT_CHANNEL contactChannel,
    
23.                  RECORD_FILE recordFile
    
24.             from TBL_CONTACT_INFO c, tbl_sys_users u, tbl_sys_departments d
    
25.            where 1 = 1
    
26.              and c.user_code = u.user_code
    
27.              and u.department_id = d.department_id
    
28.              and c.CONTACT_LENGTH >= 15
    
29.              and c.user_code = 'abMA13'
    
30.              and c.START_TIME >= '2015-04-01 00:00:01'
    
31.              and c.START_TIME <= '2015-04-17 23:59:59') c__
    
32.   ;

     在TBL_CONTACT_INFO表上有如下索引：create index IDX_CONTACTINFO_FST on  TBL_CONTACT_INFO (SUBSTR("START_TIME",1,13))，执行计划如下（使用autotrace获得）：
                         
                        
    这里发现ID=8的步骤，的确走了函数索引，但这与oracle索引理论相违背了，而且这里的ID=7的谓词信息很奇怪，这里的谓词信息才是原sql的where条件，那这里就只能这样理解，oracle先走了函数索引进行过滤数据，但是这个函数索引过滤的数据与sql的where条件是不等价的，然后在回表后再进行一次过滤，得到正确的结果集。但为什么这里能走函数索引呢？
     为了解开迷惑，朋友又建立一个普通索引， create index IDX_CONTACTINFO_ST on  TBL_CONTACT_INFO (START_TIME)，然后使用hint强制走普通索引，我们再观察执行计划（使用autotrace获得）：
                        
                       

     这次的执行计划更奇怪，ID=8的步骤的谓词信息中有filter(SUBSTR("START_TIME",1,13)>='2015-04-01 00' AND SUBSTR("START_TIME",1,13)<='2015-04-17 23')，前面走函数索引的执行计划还可以按上面的分析勉强理解，但这里的谓词信息就无法解释了，因为使用普通索引之后就已经得到正确的结果集了，为什么还要有 filter(SUBSTR("START_TIME",1,13)>='2015-04-01 00' AND  SUBSTR("START_TIME",1,13)<='2015-04-17 23')操作。到此，我开始怀疑上面两个执行计划时假的，不是oracle真正的执行计划。那就从最不能理解的走普通索引的这个执行计划开始研究，我让朋友对走普通索引的语句进行10053 trace，如下是trace文件的关键内容：

******************************************* Peeked values of the binds in SQL statement ******************************************* Final query after transformations:******* UNPARSED QUERY IS ******* SELECT /*+ INDEX ("C" "IDX_CONTACTINFO_ST") */ COUNT(*) "COUNT(1)" FROM "XJ_10018VIP"."TBL_CONTACT_INFO" "C","XJ_10018VIP"."TBL_SYS_USERS" "U","XJ_10018VIP"."TBL_SYS_DEPARTMENTS" "D" WHERE "C"."USER_CODE"="U"."USER_CODE" AND "U"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "C"."CONTACT_LENGTH">=15 AND "C"."USER_CODE"='abMA13' AND "C"."START_TIME">='2015-04-01 00:00:01' AND SUBSTR("C"."START_TIME",1,13)>='2015-04-01 00' AND "C"."START_TIME"<='2015-04-17 18:30:59' AND SUBSTR("C"."START_TIME",1,13)<='2015-04-17 18' AND "U"."USER_CODE"='abMA13' AND '2015-04-01 00:00:01'<='2015-04-17 18:30:59' kkoqbc: optimizing query block SEL$F5BB74E1 (#0)                  :     call(in-use=13536, alloc=32712), compile(in-use=108712, alloc=111720), execution(in-use=5928, alloc=8088)

    注意上面标注的部分，oracle最终把语句的谓词信息转换成上面的形式了，再看10053中的执行计划：
                                     
     注意看这里ID=8的谓词信息，与之前使用autotrace的执行计划不一样，这里的谓词信息就很好理解了，这就是我们想要的。所有，上面使用autotrace获得的执行计划是假的，并不是oracle真实采用的执行计划，进一步用 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID',SQL_CHILD_NUMBER))获取执行计划，结果与10053的一致。
     再对使用函数索引的语句进行10053 trace，得到的最终转化后的sql依然是：

******************************************* Peeked values of the binds in SQL statement ******************************************* Final query after transformations:******* UNPARSED QUERY IS ******* SELECT /*+ INDEX ("C" "IDX_CONTACTINFO_ST") */ COUNT(*) "COUNT(1)" FROM "XJ_10018VIP"."TBL_CONTACT_INFO" "C","XJ_10018VIP"."TBL_SYS_USERS" "U","XJ_10018VIP"."TBL_SYS_DEPARTMENTS" "D" WHERE "C"."USER_CODE"="U"."USER_CODE" AND "U"."DEPARTMENT_ID"="D"."DEPARTMENT_ID" AND "C"."CONTACT_LENGTH">=15 AND "C"."USER_CODE"='abMA13' AND "C"."START_TIME">='2015-04-01 00:00:01' AND SUBSTR("C"."START_TIME",1,13)>='2015-04-01 00' AND "C"."START_TIME"<='2015-04-17 18:30:59' AND SUBSTR("C"."START_TIME",1,13)<='2015-04-17 18' AND "U"."USER_CODE"='abMA13' AND '2015-04-01 00:00:01'<='2015-04-17 18:30:59' kkoqbc: optimizing query block SEL$F5BB74E1 (#0)                  :     call(in-use=13536, alloc=32712), compile(in-use=108712, alloc=111720), execution(in-use=5928, alloc=8088)

   
     这样就可以理解一开的执行计划为什么走了函数索引，就是因为最终转化的sql里面有SUBSTR("C"."START_TIME",1,13)>='2015-04-01 00' AND "C"."START_TIME"<='2015-04-17 18:30:59' AND SUBSTR("C"."START_TIME",1,13)<='2015-04-17 18'。
    所以当发现使用autotrace得到的执行计划与oracle原理相违背的时候，请警惕，这时你看到的执行计划很可能是虚假的执行计划。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/29821678/viewspace-1585578/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/29821678/viewspace-1585578/