SQL優化--函式索引

select distinct oi.customer_id, oi.order_id, oi.create_time, 1 type

  from t_to_order_info oi

  left join t_TO_Consign_Pymt_Record cpr

    on oi.order_id = cpr.order_id

 where 1 = 1

   and exists (select 1

          from t_TO_Order_Tickets

         where t_TO_Order_Tickets.Order_ID = oi.Order_ID

           and to_char(Departure_Time, 'yyyyMMdd') =

               to_char(sysdate, 'yyyyMMdd'))

   and not exists

 (select 1 from t_to_order_change oc where oc.order_id = oi.order_id)

   and ((cpr.pymt_type = 1 or

       cpr.pymt_type = 3 and oi.guest_pymt_sts_id = 2)

       or

       (cpr.pymt_type = 2 and

       cpr.partner_id in

       (select p.partner_id

            from t_tp_partner p

           where p.is_company_inner = 1) and oi.guest_pymt_sts_id = 2)

       or (cpr.pymt_type = 2 and

       cpr.partner_id in

       (select p.partner_id

               from t_tp_partner p

              where p.agent_class_id = 1

                and p.is_company_inner != 1) and

       to_char(oi.issue_time) <> ' ')

       or

       (cpr.pymt_type = 2 and

       cpr.partner_id in

       (select p.partner_id

            from t_tp_partner p

           where p.agent_class_id != 1) and to_char(oi.issue_time) <> ' '))

 

這麼一條SQL，要跑30多秒，開發讓調優

 

我第一的想法是看執行計劃，t_to_order_info和t_TO_Order_Tickets這兩張表是hash連線，但是兩表走的是全表掃描，我首先是將目標定向了t_to_order_info走全表掃描的原因，以為是exists造成的， 在這裡我並不知道是哪裡沒走索引造成的，我將SQL多執行了幾次，然後再em工具裡面的TOP SQL裡面找到了這條SQL，看了oracle給的建議

首先執行了如下命令收集了索引的統計資訊

dbms_stats.gather_index_stats(ownname => 'TEST20110217', indname => 'IDX_BT_T_TO_ORDER_HANG', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

dbms_stats.gather_index_stats(ownname => 'TEST20110217', indname => 'TO_RELATIONCHANGE_FK', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

dbms_stats.gather_index_stats(ownname => 'TEST20110217', indname => 'PK_T_TO_ORDER_CHANGE', estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE);

end;

 

做完次操作後效率沒有提高，再看到oracle給的一個建議，就是說

t_TO_Order_Tickets

         where t_TO_Order_Tickets.Order_ID = oi.Order_ID

           and to_char(Departure_Time, 'yyyyMMdd') =

               to_char(sysdate, 'yyyyMMdd'))

這裡出了問題，Departure_Time本來是有索引的，但是這裡沒有走索引，因為to_char的方式無法讓它再走索引了，這是問題的關鍵，這種問題可以通過函式索引來解決

 

SQL> create  index TEST20110217.IDX_HANSU on TEST20110217.T_TO_ORDER_TICKETS(TO_CHAR("DEPARTURE_TIME",'yyyyMMdd'));

 

SQL> exec dbms_stats.gather_index_stats('TEST20110217','IDX_HANSU');

 

上面建立了函式索引變收集了新索引的統計資訊，SQL的效率得到了大大的提高，差不多1秒之類就OK了，同時驗證了一點就是一個列上函式索引和普通索引可以並存他們各走各的，不影響效能，問題本來在這裡就已經解決了了，但是開發的後面需要在 DEPARTURE_TIME+1這將再次引起不走索引的情況，那麼必須通過改寫SQL的方式來解決

and    Departure_Time between to_date( to_char(sysdate-1,'YYYY-MM-DD HH24:MI:SS') ,'YYYY-MM-DD HH24:MI:SS')

                          and to_date( to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') ,'YYYY-MM-DD HH24:MI:SS'))

 

其實問題的關鍵就在departure_time，用上面這種方式不變departure_time，改變等號的那邊方可解決走索引的問題。

 

 

 

總結：

整個問題的關鍵地方是如何定位到問題是departure_time列不走索引而引起的效能問題，

這次是通過em工具找出來的提醒，那麼如果下次在em工具中找不到TOP SQL,自己如何分

析定位到這個點上面來。t_TO_Order_Tickets 這張表後面where 欄位有等號的地方，就

是這種欄位。這次調優沒有經驗，下次在看到如果在時間欄位上面做了手段，就應該將

問題快速的定位到這個上面來

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/10678398/viewspace-715093/，如需轉載，請註明出處，否則將追究法律責任。