淺談PL/SQL批處理語句:BULK COLLECT與FORALL對優(yōu)化做出的貢獻
這種在PL/SQL引擎和SQL引擎之間的控制轉移叫做上下文卻換,每次卻換時,都有額外的開銷
請看下圖:
但是,F(xiàn)ORALL和BULK COLLECT可以讓PL/SQL引擎把多個上下文卻換壓縮成一個,這使得在PL/SQL中的要處理多行記錄的SQL語句執(zhí)行的花費時間驟降
請再看下圖:
下面詳解這爺倆
㈠ 通過BULK COLLECT 加速查詢
⑴ BULK COLLECT 的用法
采用BULK COLLECT可以將查詢結果一次性地加載到collections中,而不是通過cursor一條一條地處理
可以在select into ,fetch into , returning into語句使用BULK COLLECT
注意在使用BULK COLLECT時,所有的INTO變量都必須是collections
舉幾個簡單例子:
① 在select into語句中使用bulk collect
DECLARE
TYPE sallist IS TABLE OF employees.salary%TYPE;
sals sallist;
BEGIN
SELECT salary BULK COLLECT INTO sals FROM employees where rownum<=50;
--接下來使用集合中的數(shù)據(jù)
END;
/
② 在fetch into中使用bulk collect
DECLARE
TYPE deptrectab IS TABLE OF departments%ROWTYPE;
dept_recs deptrectab;
CURSOR cur IS SELECT department_id,department_name FROM departments where department_id>10;
BEGIN
OPEN cur;
FETCH cur BULK COLLECT INTO dept_recs;
--接下來使用集合中的數(shù)據(jù)
END;
/
③ 在returning into中使用bulk collect
CREATE TABLE emp AS SELECT * FROM employees;
DECLARE
TYPE numlist IS TABLE OF employees.employee_id%TYPE;
enums numlist;
TYPE namelist IS TABLE OF employees.last_name%TYPE;
names namelist;
BEGIN
DELETE emp WHERE department_id=30
RETURNING employee_id,last_name BULK COLLECT INTO enums,names;
DBMS_OUTPUT.PUT_LINE('deleted'||SQL%ROWCOUNT||'rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
DBMS_OUTPUT.PUT_LINE('employee#'||enums(i)||':'||names(i));
END LOOP;
END;
/
deleted6rows:
employee#114:Raphaely
employee#115:Khoo
employee#116:Baida
employee#117:Tobias
employee#118:Himuro
employee#119:Colmenares
EATE TABLE emp AS SELECT * FROM employees;DECLARE TYPE numlist IS TABLE OF employees.employee_id%TYPE; enums numlist; TYPE namelist IS TABLE OF employees.last_name%TYPE; names namelist;BEGIN DELETE emp WHERE department_id=30 RETURNING employee_id,last_name BULK COLLECT INTO enums,names; DBMS_OUTPUT.PUT_LINE('deleted'||SQL%ROWCOUNT||'rows:'); FOR i IN enums.FIRST .. enums.LAST LOOP DBMS_OUTPUT.PUT_LINE('employee#'||enums(i)||':'||names(i)); END LOOP;END;/deleted6rows:employee#114:Raphaelyemployee#115:Khooemployee#116:Baidaemployee#117:Tobiasemployee#118:Himuroemployee#119:Colmenares
⑵ BULK COLLECT 對大數(shù)據(jù)DELETE UPDATE的優(yōu)化
這里舉DELETE就可以了,UPDATE同理
舉個案例:
需要在一個1億行的大表中,刪除1千萬行數(shù)據(jù)
需求是在對數(shù)據(jù)庫其他應用影響最小的情況下,以最快的速度完成
如果業(yè)務無法停止的話,可以參考下列思路:
根據(jù)ROWID分片、再利用Rowid排序、批量處理、回表刪除
在業(yè)務無法停止的時候,選擇這種方式,的確是最好的
一般可以控制在每一萬行以內(nèi)提交一次,不會對回滾段造成太大壓力
我在做大DML時,通常選擇一兩千行一提交
選擇業(yè)務低峰時做,對應用也不至于有太大影響
代碼如下:
DECLARE
--按rowid排序的cursor
--刪除條件是oo=xx,這個需根據(jù)實際情況來定
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 5000;--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;
/
⑶ 限制BULK COLLECT 提取的記錄數(shù)
語法:
FETCH cursor BULK COLLECT INTO ...[LIMIT rows];
其中,rows可以是常量,變量或者求值的結果是整數(shù)的表達式
假設你需要查詢并處理1W行數(shù)據(jù),你可以用BULK COLLECT一次取出所有行,然后填充到一個非常大的集合中
可是,這種方法會消耗該會話的大量PGA,APP可能會因為PGA換頁而導致性能下降
這時,LIMIT子句就非常有用,它可以幫助我們控制程序用多大內(nèi)存來處理數(shù)據(jù)
例子:
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 100;
/*通過掃描集合對數(shù)據(jù)進行處理*/
FOR i IN 1 .. v_emp.count
LOOP
upgrade_employee_status(v_emp(i).employee_id);
END LOOP;
EXIT WHEN allrows_cur%NOTFOUND;
END LOOP;
CLOSE allrows_cur;
END;
/
⑷ 批量提取多列
需求:
提取transportation表中的油耗小于 20公里/RMB的交通具體的全部信息
代碼如下:
DECLARE
--聲明集合類型
TYPE vehtab IS TABLE OF transportation%ROWTYPE;
--初始化一個這個類型的集合
gas_quzzlers vehtab;
BEGIN
SELECT * BULK COLLECT INTO gas_quzzlers FROM transportation WHERE mileage < 20;
...
⑸ 對批量操作使用RETURNING子句
有了returning子句后,我們可以輕松地確定剛剛完成的DML操作的結果,無須再做額外的查詢工作
例子請見BULK COLLECT 的用法的第三小點
㈡ 通過FORALL 加速DML
FORALL告訴PL/SQL引擎要先把一個或多個集合的所有成員都綁定到SQL語句中,然后再把語句發(fā)送給SQL引擎
⑴ 語法
未完待續(xù)。。。
- 開啟SQLSERVER數(shù)據(jù)庫緩存依賴優(yōu)化網(wǎng)站性能
- SQLServer 優(yōu)化SQL語句 in 和not in的替代方案
- SQL語句優(yōu)化方法30例(推薦)
- SQL Server數(shù)據(jù)庫的高性能優(yōu)化經(jīng)驗總結
- MySQL 大數(shù)據(jù)量快速插入方法和語句優(yōu)化分享
- MySQL性能優(yōu)化的一些技巧幫助你的數(shù)據(jù)庫
- MySQL查詢優(yōu)化:連接查詢排序limit(join、order by、limit語句)介紹
- 淺談MySQL中優(yōu)化sql語句查詢常用的30種方法
- 如何優(yōu)化SQL語句的心得淺談
- SQL語句優(yōu)化提高數(shù)據(jù)庫性能
相關文章
oracle 12c創(chuàng)建可插拔數(shù)據(jù)庫(PDB)與用戶詳解
Oracle12c 中,增加了可插接數(shù)據(jù)庫的概念,即PDB,允許一個數(shù)據(jù)庫容器(CDB)承載多個可插拔數(shù)據(jù)庫(PDB)。下面這篇文章主要給大家介紹了利用oracle 12c創(chuàng)建可插拔數(shù)據(jù)庫(PDB)與用戶的相關資料,文中介紹的很詳細,需要的朋友可以參考借鑒,下面來一起看看吧。2017-02-02Oracle導出文本文件的三種方法(spool,UTL_FILE,sqluldr2)
這篇文章主要介紹了Oracle導出文本文件的三種方法(spool,UTL_FILE,sqluldr2),需要的朋友可以參考下2023-05-05常見數(shù)據(jù)庫系統(tǒng)比較 Oracle數(shù)據(jù)庫
常見數(shù)據(jù)庫系統(tǒng)比較 Oracle數(shù)據(jù)庫...2007-03-03在Oracle數(shù)據(jù)庫中同時更新兩張表的簡單方法
這篇文章主要介紹了在Oracle數(shù)據(jù)庫中同時更新兩張表的簡單方法,同時介紹了一種差異性合并更新的方法,需要的朋友可以參考下2015-11-11