使用BULK COLLECT+FORALL加速批量提交

renjixinchina發表於2014-06-26

一、        批量提交

批量提交特點:

佔用較少undo,資源(獨佔鎖,undo)快速釋放,執行時間長

批量提交適合場景:

線上大批量插入,更新,刪除資料

二、        BULK COLLECT+FORALL效能提升

1.通過BULK COLLECT加速查詢

不管是顯示遊標還是隱式遊標，都可以通過BULK COLLECT在資料庫的單次互動中獲取多行資料。BULKCOLLECT相對Cursor Loop方式減少了PL/SQL引擎和SQL引擎之間的切換次數，因此也減少了提取資料時的額外開銷。

2 通過LIMIT rows限制提取的記錄數

這種方法會減少對PGA的消耗,避免換頁產生

3通過FORALL 加速DML

FORALL告訴PL/SQL引擎要先把一個或多個集合的所有成員都繫結到SQL語句中，然後再把語句傳送給SQL引擎, 如果for ..loop 迴圈，那麼會傳送n(迴圈的次數)次，而用Forall，一次行全部傳送過去。

三、        BULK COLLECT + FORALL 批量提交例句

insert

declare

TYPE ARRAY IS TABLE OF big_table%ROWTYPE;

l_data ARRAY;

CURSOR c IS SELECT * FROM big_table;

BEGIN

    OPEN c;

    LOOP

    FETCH c BULK COLLECT INTO l_data LIMIT 5000;

   

    FORALL i IN 1..l_data.COUNT

    INSERT /*+append*/ INTO big_table VALUES l_data(i);

    commit;

    EXIT WHEN c%NOTFOUND;

    END LOOP;

    CLOSE c;

delete

DECLARE  

 CURSOR mycursor IS SELECT rowid FROM t WHERE OO=XX ;  

 TYPE rowid_table_type IS TABLE OF rowid index  by  pls_integer;  

 v_rowid rowid_table_type;  

BEGIN  

  OPEN mycursor;  

  LOOP  

    FETCH mycursor BULK COLLECT INTO v_rowid LIMIT 5000;  

    EXIT WHEN v_rowid.count=0;  

    FORALL i IN v_rowid.FIRST..v_rowid.LAST  

      DELETE t WHERE rowid=v_rowid(i);  

    COMMIT;  

  END LOOP;  

  CLOSE mycursor;  

END;  

/  

update

DECLARE  

 CURSOR mycursor IS SELECT t_pk FROM t WHERE OO=XX ;  

 TYPE num_tab_t IS TABLE OF NUMBER(38);

 pk_tab NUM_TAB_T;

BEGIN  

  OPEN mycursor;  

  LOOP  

    FETCH mycursor BULK COLLECT INTO pk_tab LIMIT 5000;  

    EXIT WHEN pk_tab.count=0;  

    FORALL i IN pk_tab.FIRST..v_rowid.LAST  

      UPDATE t

            SET    name=name||’bulk’

            WHERE  t_pk = pk_tab(i);

    COMMIT;  

  END LOOP;  

  CLOSE mycursor;  

END;  

/  

 

 


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

相關文章