一個SQL語句的優化

e71hao發表於2016-04-15
SQL優化
1.要優化的語句：把表的5個欄位同步到另外一個表
update bu_clear_detail_bak20160412 a
   set (a.card_ser_numer,
        a.trans_amount2,
        a.trans_type_mark,
        a.card_online_num,
        a.trans_number,
        a.issr_mark) =
       (select b.card_ser_numer,
               b.trans_amount2,
               b.trans_type_mark,
               b.card_online_num,
               b.trans_number,
               b.issr_mark
          from bu_clear_detail_bak_addcolmns b
         where a.id = b.id);
表的統計資料：bu_clear_detail_bak20160412 有23萬資料，bu_clear_detail_bak_addcolmns有30萬資料。表上沒有約束，沒有其他的鎖。

這個語句執行了30分鐘後，還是沒有結果。kill掉。
2.兩個表資料量不大。為了確定這個語句能不能更新，是否有鎖，可以加個約束條件，執行1000條。如：
update bu_clear_detail_bak20160412 a
   set (a.card_ser_numer,
        a.trans_amount2,
        a.trans_type_mark,
        a.card_online_num,
        a.trans_number,
        a.issr_mark) =
       (select b.card_ser_numer,
               b.trans_amount2,
               b.trans_type_mark,
               b.card_online_num,
               b.trans_number,
               b.issr_mark
          from bu_clear_detail_bak_addcolmns b
         where a.id = b.id)
 where rownum < 1000 ;
時間為3分18秒。這個可以證明的是：更新語句沒有被其他物件鎖住。更新速度很慢。
3.猜想這個語句的執行計劃，應該不難，從bu_clear_detail_bak20160412表裡取出一條資料，去bu_clear_detail_bak_addcolmns表中檢索相同的ID，然後把5個欄位更新如此迴圈100次。bu_clear_detail_bak_addcolmns表有30萬資料，迴圈30萬X100次。是不是想到解決辦法了，對，就是加索引。在表bu_clear_detail_bak_addcolmns的ID列加索引。測試，全部更新，30秒完成

4.問題是完美解決了，但是研究沒有停止.把表bu_clear_detail_bak_addcolmns的索引刪除，我們來看看它執行的系統指標表現：
us為20%，用10046事件跟蹤SQL都是db file scattered read等待事件，TOP SQL

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

相關文章