【DB筆試面試571】在Oracle中，如何提高DML語句的效率？

• 2019 年 10 月 10 日
• 筆記

♣

題目部分

在Oracle中，如何提高DML語句的效率？

♣

答案部分

若是批量處理海量數據的話通常都是很複雜及緩慢的，方法也很多，但是通常的概念是：分批刪除，逐次提交。下面介紹一下提高DML語句效率的常用方法。

（一）UPDATE

可以使用以下原則：

①　多欄位更新使用一個查詢。

②　將表修改為NOLOGGING模式。

③　根據情況決定是否暫停索引，更新後恢復。避免在更新的過程中涉及到索引的維護。

④　批量更新，每更新一些記錄後及時進行提交動作，避免大量佔用回滾段和或臨時表空間。

⑤　可以創建一個臨時的大的表空間用來應對這些更新動作。

⑥　加大排序緩衝區。

⑦　如果更新的數據量接近整個表，那麼就不應該使用索引而應該採用全表掃描。

⑧　如果伺服器有多個CPU，那麼可以採用PARELLEL Hint，可以大幅度地提高效率。

⑨　建表的參數非常重要，對於更新非常頻繁的表，建議加大PCTFREE的值，以保證數據塊中有足夠的空間用於UPDATE。

⑩　通過快速游標更新法，並對ROWID進行排序更新，如下所示：

DECLARE    V_COUNTER NUMBER;  BEGIN    V_COUNTER := 0;    FOR CUR IN (SELECT A.AREA_CODE, B.ROWID ROW_ID FROM TA A, TB B WHERE A.ID = B.ID ORDER BY B.ROWID ) LOOP      UPDATE TB SET AREA_CODE = CUR.AREA_CODE WHERE ROWID = CUR.ROW_ID;      V_COUNTER := V_COUNTER + 1;      IF (V_COUNTER >= 1000) THEN        COMMIT;        V_COUNTER := 0;      END IF;    END LOOP;    COMMIT;  END;  

⑪　當需要更新的表是單個或者被更新的欄位不需要關聯其它錶帶過來中的數據（例如：外鍵約束），則選擇標準的UPDATE語句，速度最快，穩定性最好，並返回影響條數。如果WHERE條件中的欄位加上索引，那麼更新效率就更高。但若需要關聯表更新欄位時，UPDATE的效率就非常差。此時可以採用MERGE且非關聯形式高效完成表對錶的UPDATE操作。

（二）INSERT

①　將表修改為NOLOGGING模式。

②　暫停索引。

③　以APPEND模式插入。

④　加入PARALLEL，採用並行插入。

（三）DELETE

①　利用FORALL完成。

②　利用ROWID或ROW_NUMBER() OVER()高效刪除重複記錄。

③　將表修改為NOLOGGING模式。

以上這些方法都是拋磚引玉，資料庫優化沒有最好的方法，只有最合適的方法。

& 說明：

有關SQL優化的一些案例可以參考我的BLOG：http://blog.itpub.net/26736162/viewspace-1254942/、http://blog.itpub.net/26736162/viewspace-1244055/、http://blog.itpub.net/26736162/viewspace-1244050/、http://blog.itpub.net/26736162/viewspace-1244044/、http://blog.itpub.net/26736162/viewspace-1222431/、http://blog.itpub.net/26736162/viewspace-1222423/、http://blog.itpub.net/26736162/viewspace-1218671/、http://blog.itpub.net/26736162/viewspace-1209796/、http://blog.itpub.net/26736162/viewspace-1208814/、http://blog.itpub.net/26736162/viewspace-1684396/、http://blog.itpub.net/26736162/viewspace-2125815/。

本文選自《Oracle程式設計師面試筆試寶典》，作者：李華榮。