【DB笔试面试571】在Oracle中，如何提高DML语句的效率？

♣

题目部分

在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程序员面试笔试宝典》，作者：李华荣。