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

最新推荐文章于 2022-02-14 17:20:58 发布

chupian5847

最新推荐文章于 2022-02-14 17:20:58 发布

阅读量90

点赞数

原文链接：https://my.oschina.net/yeelee/blog/650916

版权

先看这句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将不会起到作用？

转载于:https://my.oschina.net/yeelee/blog/650916