欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

淺談PL/SQL批處理語(yǔ)句:BULK COLLECT與FORALL對(duì)優(yōu)化做出的貢獻(xiàn)

 更新時(shí)間:2013年04月10日 17:29:17   作者:  
本篇文章小編為大家介紹一下,淺談PL/SQL批處理語(yǔ)句:BULK COLLECT與FORALL對(duì)優(yōu)化做出的貢獻(xiàn)。有需要的朋友可以參考一下
我們知道PL/SQL程序中運(yùn)行SQL語(yǔ)句是存在開銷的,因?yàn)镾QL語(yǔ)句是要提交給SQL引擎處理
這種在PL/SQL引擎和SQL引擎之間的控制轉(zhuǎn)移叫做上下文卻換,每次卻換時(shí),都有額外的開銷

請(qǐng)看下圖:

但是,F(xiàn)ORALL和BULK COLLECT可以讓PL/SQL引擎把多個(gè)上下文卻換壓縮成一個(gè),這使得在PL/SQL中的要處理多行記錄的SQL語(yǔ)句執(zhí)行的花費(fèi)時(shí)間驟降
請(qǐng)?jiān)倏聪聢D:

下面詳解這爺倆

㈠ 通過BULK COLLECT 加速查詢

⑴ BULK COLLECT 的用法


采用BULK COLLECT可以將查詢結(jié)果一次性地加載到collections中,而不是通過cursor一條一條地處理
可以在select into ,fetch into , returning into語(yǔ)句使用BULK COLLECT
注意在使用BULK COLLECT時(shí),所有的INTO變量都必須是collections

舉幾個(gè)簡(jiǎn)單例子:

① 在select into語(yǔ)句中使用bulk collect

復(fù)制代碼 代碼如下:

DECLARE
TYPE sallist IS TABLE OF employees.salary%TYPE;
sals sallist;
BEGIN
SELECT salary BULK COLLECT INTO sals FROM employees where rownum<=50;
--接下來(lái)使用集合中的數(shù)據(jù)
END;
/

② 在fetch into中使用bulk collect

復(fù)制代碼 代碼如下:

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;
--接下來(lái)使用集合中的數(shù)據(jù)
END;
/

③ 在returning into中使用bulk collect

復(fù)制代碼 代碼如下:

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 對(duì)大數(shù)據(jù)DELETE UPDATE的優(yōu)化

這里舉DELETE就可以了,UPDATE同理

舉個(gè)案例:
需要在一個(gè)1億行的大表中,刪除1千萬(wàn)行數(shù)據(jù)
需求是在對(duì)數(shù)據(jù)庫(kù)其他應(yīng)用影響最小的情況下,以最快的速度完成

如果業(yè)務(wù)無(wú)法停止的話,可以參考下列思路:
根據(jù)ROWID分片、再利用Rowid排序、批量處理、回表刪除
在業(yè)務(wù)無(wú)法停止的時(shí)候,選擇這種方式,的確是最好的
一般可以控制在每一萬(wàn)行以內(nèi)提交一次,不會(huì)對(duì)回滾段造成太大壓力
我在做大DML時(shí),通常選擇一兩千行一提交
選擇業(yè)務(wù)低峰時(shí)做,對(duì)應(yīng)用也不至于有太大影響
代碼如下:

復(fù)制代碼 代碼如下:

DECLARE
--按rowid排序的cursor
--刪除條件是oo=xx,這個(gè)需根據(jù)實(shí)際情況來(lái)定
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ù)

語(yǔ)法:
FETCH cursor BULK COLLECT INTO ...[LIMIT rows];
其中,rows可以是常量,變量或者求值的結(jié)果是整數(shù)的表達(dá)式

假設(shè)你需要查詢并處理1W行數(shù)據(jù),你可以用BULK COLLECT一次取出所有行,然后填充到一個(gè)非常大的集合中
可是,這種方法會(huì)消耗該會(huì)話的大量PGA,APP可能會(huì)因?yàn)镻GA換頁(yè)而導(dǎo)致性能下降

這時(shí),LIMIT子句就非常有用,它可以幫助我們控制程序用多大內(nèi)存來(lái)處理數(shù)據(jù)

例子:

復(fù)制代碼 代碼如下:

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;

/*通過掃描集合對(duì)數(shù)據(jù)進(jìn)行處理*/
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的交通具體的全部信息
代碼如下:

復(fù)制代碼 代碼如下:

DECLARE
--聲明集合類型
TYPE vehtab IS TABLE OF transportation%ROWTYPE;
--初始化一個(gè)這個(gè)類型的集合
gas_quzzlers vehtab;
BEGIN
SELECT * BULK COLLECT INTO gas_quzzlers FROM transportation WHERE mileage < 20;
...

⑸ 對(duì)批量操作使用RETURNING子句

有了returning子句后,我們可以輕松地確定剛剛完成的DML操作的結(jié)果,無(wú)須再做額外的查詢工作
例子請(qǐng)見BULK COLLECT 的用法的第三小點(diǎn)


㈡ 通過FORALL 加速DML

FORALL告訴PL/SQL引擎要先把一個(gè)或多個(gè)集合的所有成員都綁定到SQL語(yǔ)句中,然后再把語(yǔ)句發(fā)送給SQL引擎

⑴ 語(yǔ)法

未完待續(xù)。。。

相關(guān)文章

  • oracle 12c創(chuàng)建可插拔數(shù)據(jù)庫(kù)(PDB)與用戶詳解

    oracle 12c創(chuàng)建可插拔數(shù)據(jù)庫(kù)(PDB)與用戶詳解

    Oracle12c 中,增加了可插接數(shù)據(jù)庫(kù)的概念,即PDB,允許一個(gè)數(shù)據(jù)庫(kù)容器(CDB)承載多個(gè)可插拔數(shù)據(jù)庫(kù)(PDB)。下面這篇文章主要給大家介紹了利用oracle 12c創(chuàng)建可插拔數(shù)據(jù)庫(kù)(PDB)與用戶的相關(guān)資料,文中介紹的很詳細(xì),需要的朋友可以參考借鑒,下面來(lái)一起看看吧。
    2017-02-02
  • ORACLE8的分區(qū)管理

    ORACLE8的分區(qū)管理

    ORACLE8的分區(qū)管理...
    2007-03-03
  • oracle 中 sqlplus命令大全

    oracle 中 sqlplus命令大全

    Oracle的sql*plus是與oracle數(shù)據(jù)庫(kù)進(jìn)行交互的客戶端工具,借助sql*plus可以查看、修改數(shù)據(jù)庫(kù)記錄。接下來(lái)通過本文給大家介紹oracle中sqlplus命令知識(shí),非常不錯(cuò),感興趣的朋友一起看看吧
    2016-09-09
  • Oracle導(dǎo)出文本文件的三種方法(spool,UTL_FILE,sqluldr2)

    Oracle導(dǎo)出文本文件的三種方法(spool,UTL_FILE,sqluldr2)

    這篇文章主要介紹了Oracle導(dǎo)出文本文件的三種方法(spool,UTL_FILE,sqluldr2),需要的朋友可以參考下
    2023-05-05
  • 常見數(shù)據(jù)庫(kù)系統(tǒng)比較  Oracle數(shù)據(jù)庫(kù)

    常見數(shù)據(jù)庫(kù)系統(tǒng)比較 Oracle數(shù)據(jù)庫(kù)

    常見數(shù)據(jù)庫(kù)系統(tǒng)比較 Oracle數(shù)據(jù)庫(kù)...
    2007-03-03
  • oracle 使用rownum的三種分頁(yè)方式

    oracle 使用rownum的三種分頁(yè)方式

    rownum是Oracle數(shù)據(jù)庫(kù)中的一個(gè)特有關(guān)鍵字,返回的是一個(gè)數(shù)字代表記錄的行號(hào)。這篇文章主要介紹了oracle 使用rownum的三種分頁(yè)方式,需要的朋友可以參考下
    2019-10-10
  • Oracle數(shù)據(jù)庫(kù)中如何給表賦予權(quán)限

    Oracle數(shù)據(jù)庫(kù)中如何給表賦予權(quán)限

    賦權(quán)是指將特定的權(quán)限授予用戶或用戶組,以便他們可以執(zhí)行特定的操作,如查詢、插入、更新和刪除數(shù)據(jù),創(chuàng)建和修改表結(jié)構(gòu),以及執(zhí)行其他管理任務(wù),這篇文章主要給大家介紹了關(guān)于Oracle數(shù)據(jù)庫(kù)中如何給表賦予權(quán)限的相關(guān)資料,需要的朋友可以參考下
    2024-01-01
  • 在Oracle數(shù)據(jù)庫(kù)中同時(shí)更新兩張表的簡(jiǎn)單方法

    在Oracle數(shù)據(jù)庫(kù)中同時(shí)更新兩張表的簡(jiǎn)單方法

    這篇文章主要介紹了在Oracle數(shù)據(jù)庫(kù)中同時(shí)更新兩張表的簡(jiǎn)單方法,同時(shí)介紹了一種差異性合并更新的方法,需要的朋友可以參考下
    2015-11-11
  • Excel VBA連接并操作Oracle

    Excel VBA連接并操作Oracle

    Excel通過ADO方式連接到Oracle并操作Oracle給我們這些編程能力不強(qiáng)的人帶來(lái)很大的福音,結(jié)合著Excel的數(shù)據(jù)處理與圖表制作,就能很輕松地處理一些常規(guī)工作。
    2009-08-08
  • win7下oracle 10g安裝圖文教程

    win7下oracle 10g安裝圖文教程

    這篇文章主要為大家詳細(xì)介紹了win7下oracle 10g安裝圖文教程,具有一定的參考價(jià)值,感興趣的小伙伴們可以參考一下
    2017-03-03

最新評(píng)論