Oracle 颠覆认知的无函数处理限定条件字段也可以用上函数索引
Oracle 颠覆认知的无函数处理限定条件字段也可以用上函数索引
前言
之前搞SQL优化过程中，遇到一个限定条件为where create_time >= sysdate - 7之后用上函数索引的情况，这个索引创建之初使用了trunc(create_time)做了处理。
这个就有点颠覆我之前的认知了--函数索引一定只能被同样函数处理过的字段才能使用（前提是需要回表），结果其实不是100%一定的。
现在记录一下这个情况。
现象
对SQL内容涉及保密因此会做适当处理，不影响内容阅读理解。
SQL文本如下：
SELECT T.CREATE_TIME, L.IDEN_SEQ_NO, L.ABCD_TOTAL, L.OPERA_CURR, L.AAA_TOTAL, L.BBB_TOTAL, FROM ID_PRE_HEAD T INNER JOIN ID_PRE_LIST L ON T.IDEN_SEQ_NO = L.IDEN_SEQ_NO WHERE L.BBB_TOTAL IS NULL AND L.AAA_TOTAL IS NULL AND T.CREATE_TIME IS NOT NULL AND T.CREATE_TIME >= SYSDATE - 7 AND LENGTH(L.OPERA_CURR) = 3 AND T.IS_REAL = '0' AND ROWNUM <= 5000;
通过设置statistics_level=all后得到运行完成后的执行计划为：
Plan hash value: 2382519203 ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:02.58 | 643K| |* 1 | COUNT STOPKEY | | 1 | | 1 |00:00:02.58 | 643K| | 2 | NESTED LOOPS | | 1 | 1 | 1 |00:00:02.58 | 643K| | 3 | NESTED LOOPS | | 1 | 5 | 1126K|00:00:01.48 | 551K| |* 4 | TABLE ACCESS BY INDEX ROWID| ID_PRE_HEAD | 1 | 1 | 216K|00:00:00.49 | 57729 | |* 5 | INDEX RANGE SCAN | IDX_F_CREATE_TIME | 1 | 26639 | 235K|00:00:00.05 | 1659 | |* 6 | INDEX RANGE SCAN | IDX_ID_PRE_LIST_01 | 216K| 5 | 1126K|00:00:00.81 | 494K| |* 7 | TABLE ACCESS BY INDEX ROWID | ID_PRE_LIST | 1126K| 1 | 1 |00:00:00.87 | 91481 | ------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(ROWNUM<=5000) 4 - filter(("T"."IS_REAL"='0' AND "T"."CREATE_TIME">=SYSDATE@!-7)) 5 - access("T"."SYS_NC00136$">=TRUNC(SYSDATE@!-7)) 6 - access("T"."IDEN_SEQ_NO"="L"."IDEN_SEQ_NO") 7 - filter(("L"."BBB_TOTAL" IS NULL AND "L"."AAA_TOTAL" IS NULL AND LENGTH("L"."OPERA_CURR")=3))
其中，函数索引IDX_F_CREATE_TIME的创建语句为：
create index IDX_F_CREATE_TIME on ID_PRE_HEAD(trunc(create_time)) online;
由此可见，条件
T.CREATE_TIME >= SYSDATE - 7
用上了函数索引
IDX_F_CREATE_TIME
从执行计划的ID=5步骤和对应的谓词信息（5 - access("T"."SYS_NC00136$">=TRUNC(SYSDATE@!-7))）可以看出，先是通过函数索引拿出TRUNC(SYSDATE@!-7)的数据，然后在这部分的数据中再过滤T.CREATE_TIME >= SYSDATE - 7的数据。
这么看的话，CBO还是非常聪明的，巧妙的使用上了索引避免了全表扫描。
猜想
基于以上事实，由于函数索引被trunc做了处理，而实际上trunc(sysdate-7)确实包含了sysdate -7的数据，因此CBO才可以利用上索引。
因此，“无函数处理限定条件字段可以用上函数索引”这原因应该就是即便用上该索引也不会导致最后SQL结果数据有问题。
可以试试反过来的情况，对限定条件做函数处理，但是限定条件的字段的索引是普通索引。
生产刚好有这样环境，如下（做脱敏处理）：
select /*+ index(t IDX_CREATE_TIME_00) */ distinct IS_CHECK from ID_PRE_LIST t where trunc(CREATE_TIME) >=sysdate - 1/2400;
对应执行计划： Plan hash value: 1103666447 ------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem | ------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 0 |00:02:25.52 | 206K| 114K| | | | | 1 | HASH UNIQUE | | 1 | 2 | 0 |00:02:25.52 | 206K| 114K| 1394K| 1394K| | | 2 | TABLE ACCESS BY INDEX ROWID| ID_PRE_LIST | 1 | 2173K| 0 |00:02:25.52 | 206K| 114K| | | | |* 3 | INDEX FULL SCAN | IDX_CREATE_TIME_00 | 1 | 2173K| 0 |00:02:25.52 | 206K| 114K| | | | ------------------------------------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 3 - filter(TRUNC(INTERNAL_FUNCTION("CREATE_TIME"))>=SYSDATE@!-.000416666666666666666666666666666666666667)
从结果看，果然即便是trunc(create_Time)，也可以用上普通索引IDX_CREATE_TIME_00 。
（不要纠结用hint强制走索引，能够强制成功说明有能够使用的前提，不使用hint不走索引仅仅因为cost判断问题而已）
那么，可以猜想什么时候会出现这类情况，就是能够通过索引获得的数据范围要比原来的大。
比如第一种情况用了函数索引过滤出TRUNC(SYSDATE@!-7)的数据，肯定要包含SQL中的T.CREATE_TIME >= SYSDATE - 7的数据，
比如第二种情况，ID=3以及对应的谓词信息可以知道，访问索引IDX_CREATE_TIME_00的时候是全索引扫描而不是索引范围扫，并且谓词信息出是filter而不是access。
当然，显然并不适用于大部分的情况，类似函数索引：create index idx_name on xxx(length(name));这类会完全丢失原本字段内容的情况（length之后变为数字长度的值了）那是肯定无法出现前边的两种情况的。
至此。
