-
SQL> select count(1)
-
from (SELECT CALLER caller,
-
callee callee,
-
customer_name customerName,
-
CALL_STATE callState,
-
CALL_RESULT callResult,
-
START_TIME startTime,
-
CONTACT_LENGTH contactLength,
-
HANGUP_FLAG hangupFlag,
-
c.agent_code || '[' ||
-
(select u.user_name
-
from tbl_sys_users u
-
where u.user_code = c.user_code) || ']' userCode,
-
AGENT_CODE agentCode,
-
ACCEPT_AREA acceptArea,
-
satisfication satisfication,
-
CUST_LEVEL custLevel,
-
DIRECTION direction,
-
c.remark remark,
-
d.department_name distArea,
-
d.department_id distAreaId,
-
CONTACT_CHANNEL contactChannel,
-
RECORD_FILE recordFile
-
from TBL_CONTACT_INFO c, tbl_sys_users u, tbl_sys_departments d
-
where 1 = 1
-
and 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 c.START_TIME <= '2015-04-17 23:59:59') c__
- ;
这里发现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原理相违背的时候，请警惕，这时你看到的执行计划很可能是虚假的执行计划。