表索引字段嵌套函数引起的性能问题

先看这句SQL

---

SELECT agentalias, COUNT (DISTINCT (callin))
FROM (SELECT agentalias, ani callin
FROM table_a
WHERE TO_DATE (starttime, 'yyyymmddhh24miss') BETWEEN TRUNC
(SYSDATE)
AND SYSDATE
AND connectlength > 0
AND agentalias = '4003'
UNION ALL
SELECT agentalias, connectno callout
FROM table_b
WHERE TO_DATE (starttime, 'yyyymmddhh24miss') BETWEEN TRUNC
(SYSDATE)
AND SYSDATE
AND connectlength > 0
AND LENGTH (TRIM (connectno)) > 5
AND agentalias = '4003')
GROUP BY agentalias

---

再看同样功能的SQL

---

SELECT agentalias, COUNT (DISTINCT (callin))
FROM (SELECT agentalias, ani callin
FROM table_a
WHERE starttime BETWEEN TO_CHAR (TRUNC (SYSDATE), 'yyyymmdd')
AND TO_CHAR (SYSDATE, 'yyyymmddhh24miss')
AND connectlength > 0
AND agentalias = '4003'
UNION ALL
SELECT agentalias, connectno callout
FROM table_b
WHERE starttime BETWEEN TO_CHAR (TRUNC (SYSDATE), 'yyyymmdd')
AND TO_CHAR (SYSDATE, 'yyyymmddhh24miss')
AND connectlength > 0
AND LENGTH (TRIM (connectno)) > 5
AND agentalias = '4003')
GROUP BY agentalias

---

table_a、table_b 表中 starttime 都是 index

---

执行结果中，第一个SQL很慢，查看查询计划走的是全表扫描，第二个SQL很快，因为走的索引

 

结论：通过SQL比较，发现在当在查询字段starttime，亦即index字段上加上函数后就会出现不走index而走全表扫描的情况。这里是不是可以得出如果在当index字段作为搜索条件时，如果在index上使用了函数那么该index将不会起到作用？