改进建议：修where条件中的trunc(t.operate_time) >= trunc(sysdate)改为t.operate_time >= to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')

select a.user_code,

       case

         when b.aa is null then

          0

         else

          b.aa

       end valuecount

  from (select cu.user_code

          from crm_user_role r, crm_user cu

         where r.user_id = cu.user_id

           and r.role_id = 'role8687e756117248a4a264ea2758769d25'

           and cu.user_code in ('1015',

                                '1017',

                                '1021',

                                '1025',

                                '1030',

                                '1031',

                                '1032',

                                '1033')) a,

       (select t.act_user_code, count(*) aa

          from crm_campaign_log t

         where t.act_user_code in ('1015',

                                   '1017',

                                   '1021',

                                   '1025',

                                   '1030',

                                   '1031',

                                   '1032',

                                   '1033')

           and t.log_property = '2'

           and t.operate_time >= to_date(to_char(sysdate,'yyyy-mm-dd'),'yyyy-mm-dd')

         group by t.act_user_code) b

where a.user_code = b.act_user_code(+)

order by valuecount

修改后的SQL语句执行计划如下：

修改后的语句由原来的执行时间5秒钟减少为0.05秒，cost 有原来的49014 减少到24.

原因是trunc(sysdate)函数把时间转成了字符串，字符串比较时是从左到右逐个字节进行比较，消耗较多CPU资源。