一天朋友问我一个很基础的问题:函数索引是不是在sql中要使用了建立索引的函数才能被使用?我都没思考就回答是!那么问题来了,接下来朋友把一条sql的执行计划发给了我,说他的一条sql语句,语句中没有使用函数,但是执行计划中走了函数索引。sql如下:
在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文件的关键内容:
注意上面标注的部分,oracle最终把语句的谓词信息转换成上面的形式了,再看10053中的执行计划:
注意看这里ID=8的谓词信息,与之前使用autotrace的执行计划不一样,这里的谓词信息就很好理解了,这就是我们想要的。所有,上面使用autotrace获得的执行计划是假的,并不是oracle真实采用的执行计划,进一步用 SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('SQL_ID',SQL_CHILD_NUMBER))获取执行计划,结果与10053的一致。
再对使用函数索引的语句进行10053 trace,得到的最终转化后的sql依然是:
这样就可以理解一开的执行计划为什么走了函数索引,就是因为最终转化的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原理相违背的时候,请警惕,这时你看到的执行计划很可能是虚假的执行计划。
- 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__
- ;
![](http://img.blog.itpub.net/blog/attachment/201504/19/29821678_1429411513477P.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201504/19/29821678_1429411584VtES.jpg?x-oss-process=style/bb)
这里发现ID=8的步骤,的确走了函数索引,但这与oracle索引理论相违背了,而且这里的ID=7的谓词信息很奇怪,这里的谓词信息才是原sql的where条件,那这里就只能这样理解,oracle先走了函数索引进行过滤数据,但是这个函数索引过滤的数据与sql的where条件是不等价的,然后在回表后再进行一次过滤,得到正确的结果集。但为什么这里能走函数索引呢?
为了解开迷惑,朋友又建立一个普通索引, create index IDX_CONTACTINFO_ST on TBL_CONTACT_INFO (START_TIME),然后使用hint强制走普通索引,我们再观察执行计划(使用autotrace获得):
![](http://img.blog.itpub.net/blog/attachment/201504/19/29821678_142941208023i3.jpg?x-oss-process=style/bb)
![](http://img.blog.itpub.net/blog/attachment/201504/19/29821678_1429412153r1oc.jpg?x-oss-process=style/bb)
这次的执行计划更奇怪,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中的执行计划:
![](http://img.blog.itpub.net/blog/attachment/201504/19/29821678_1429413146VPWX.jpg?x-oss-process=style/bb)
注意看这里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/