SQL調優真實案例

改進建議：修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資源。