BULK COLLECT和FORALL優化

    我們知道PL/SQL程序中運行SQL語句是存在開銷的因為SQL語句是要提交給SQL引擎處理這種在PL/SQL引擎和SQL引擎之間的控制轉移叫做上下文卻換每次卻換時都有額外的開銷請看下圖
           
    
     
    但是FORALL和BULK COLLECT可以讓PL/SQL引擎把多個上下文卻換壓縮成一個這使得在PL/SQL中的要處理多行記錄的SQL語句執行的花費時間驟降
    請再看下圖
           
    
     
    下面詳解這爺倆
    ㈠ 通過BULK COLLECT 加速查詢
    ⑴ BULK COLLECT 的用法
    采用BULK COLLECT可以將查詢結果一次性地加載到collections中而不是通過cursor一條一條地處理
    可以在select into fetch into returning into語句使用BULK COLLECT
    注意在使用BULK COLLECT時所有的INTO變量都必須是collections
    舉幾個簡單例子
    ① 在select into語句中使用bulk collect
    [sql]
    DECLARE
    TYPE sallist IS TABLE OF employeessalary%TYPE;
    sals sallist;
    BEGIN
    SELECT salary BULK COLLECT INTO sals FROM employees where rownum<=;
    接下來使用集合中的數據
    END;
    /

 
    ② 在fetch into中使用bulk collect
    [sql]
    DECLARE
    TYPE deptrectab IS TABLE OF departments%ROWTYPE;
    dept_recs deptrectab;
    CURSOR cur IS SELECT department_iddepartment_name FROM departments where department_id>;
    BEGIN
    OPEN cur;
    FETCH cur BULK COLLECT INTO dept_recs;
    接下來使用集合中的數據
    END;
    /
    ③ 在returning into中使用bulk collect
    [sql]
    CREATE TABLE emp AS SELECT * FROM employees;
    DECLARE
    TYPE numlist IS TABLE OF employeesemployee_id%TYPE;
    enums numlist;
    TYPE namelist IS TABLE OF employeeslast_name%TYPE;
    names namelist;
    BEGIN
    DELETE emp WHERE department_id=
    RETURNING employee_idlast_name BULK COLLECT INTO enumsnames;
    DBMS_OUTPUTPUT_LINE（deleted||SQL%ROWCOUNT||rows:）
    FOR i IN enumsFIRST  enumsLAST
    LOOP
    DBMS_OUTPUTPUT_LINE（employee#||enums（i）||:||names（i））
    END LOOP;
    END;
    /
    deletedrows:
    employee#:Raphaely
    employee#:Khoo
    employee#:Baida
    employee#:Tobias
    employee#:Himuro
    employee#:Colmenares
    ⑵ BULK COLLECT 對大數據DELETE UPDATE的優化
    這裡舉DELETE就可以了UPDATE同理
    舉個案例
    需要在一個億行的大表中刪除千萬行數據
    需求是在對數據庫其他應用影響最小的情況下以最快的速度完成
    如果業務無法停止的話可以參考下列思路
    根據ROWID分片再利用Rowid排序批量處理回表刪除
    在業務無法停止的時候選擇這種方式的確是最好的
    一般可以控制在每一萬行以內提交一次不會對回滾段造成太大壓力
    我在做大DML時通常選擇一兩千行一提交
    選擇業務低峰時做對應用也不至於有太大影響
    代碼如下
    [sql]
    DECLARE
    按rowid排序的cursor
    刪除條件是oo=xx這個需根據實際情況來定
    CURSOR mycursor IS SELECT rowid FROM t WHERE OO=XX ORDER BY rowid;
    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 ;行提交一次
    EXIT WHEN unt=;
    FORALL i IN v_rowidFIRSTv_rowidLAST
    DELETE t WHERE rowid=v_rowid（i）
    COMMIT;
    END LOOP;
    CLOSE mycursor;
    END;
    /
    ⑶ 限制BULK COLLECT 提取的記錄數
    語法
    FETCH cursor BULK COLLECT INTO …[LIMIT rows];
    其中rows可以是常量變量或者求值的結果是整數的表達式
    假設你需要查詢並處理W行數據你可以用BULK COLLECT一次取出所有行然後填充到一個非常大的集合中
    可是這種方法會消耗該會話的大量PGAAPP可能會因為PGA換頁而導致性能下降
    這時LIMIT子句就非常有用它可以幫助我們控制程序用多大內存來處理數據
    例子
    [sql]
    DECLARE
    CURSOR allrows_cur IS SELECT * FROM employees;
    TYPE employee_aat IS TABLE OF allrows_cur%ROWTYPE INDEX BY BINARY_INTEGER;
    v_emp employee_aat;
    BEGIN
    OPEN allrows_cur;
    LOOP
    FETCH allrows_cur BULK FETCH INTO v_emp LIMIT ;
    /*通過掃描集合對數據進行處理*/
    FOR i IN   unt
    LOOP
    upgrade_employee_status（v_emp（i）employee_id）
    END LOOP;
    EXIT WHEN allrows_cur%NOTFOUND;
    END LOOP;
    CLOSE allrows_cur;
    END;
    /
    ⑷ 批量提取多列
    需求
    提取transportation表中的油耗小於 公裡/RMB的交通具體的全部信息
    代碼如下
    [sql]
    DECLARE
    聲明集合類型
    TYPE vehtab IS TABLE OF transportation%ROWTYPE;
    初始化一個這個類型的集合
    gas_quzzlers vehtab;
    BEGIN
    SELECT * BULK COLLECT INTO gas_quzzlers FROM transportation WHERE mileage  < ;
    …
    ⑸ 對批量操作使用RETURNING子句
    有了returning子句後我們可以輕松地確定剛剛完成的DML操作的結果無須再做額外的查詢工作
    例子請見BULK COLLECT 的用法的第三小點
    ㈡ 通過FORALL 加速DML
    FORALL告訴PL/SQL引擎要先把一個或多個集合的所有成員都綁定到SQL語句中然後再把語句發送給SQL引擎

From:http://tw.wingwit.com/Article/program/Oracle/201311/18706.html