Oracle bulk 示例

Oracle有2個引擎來執行PL/SQL blocks 和 subprograms。那麼在執行的時候，PL/SQL 引擎把DML 語句發送給SQL 引擎，然後由SQL 引擎執行，執行完畢後，SQL 引擎把結果集在發送給PL/SQL 引擎。

與bulk 相關的有2個文法：forall 和 bulk collect。

（1）FORALL. 将資料打包，一次性從PL/SQL 引擎發送給SQL 引擎。

（2）bulk collect: 将處理之後的結果集放到bulk collect裡，然後一次性把bulk collect從SQL 引擎發送給PL/SQL 引擎。

因為bulk 減少2個引擎之間的context switches，進而提升了SQL性能，當要處理的資料量越大，使用bulk 和不使用bulk 性能差別就越明顯。

一. Forall測試

1.1 delete 測試

1.1.1 使用for .. loop 循環

SYS@anqing2(rac2)> desc ta;

Name Null? Type

------------------------------------------------- ----------------------------

ID NUMBER

NAME VARCHAR2(10)

SYS@anqing2(rac2)> select count(*) fromta;

COUNT(*)

----------

999999

SYS@anqing2(rac2)> select * from tawhere rownum<5;

ID NAME

---------- ----------

1 dave

測試之前，先看下變量的聲明：

TYPE IdList IS TABLE OF ta.id%TYPE;

TYPE ta_row IS TABLE OF ta%ROWTYPE INDEX BYPLS_INTEGER;

--聲明變量

row_id_table dbms_sql.Urowid_Table;

idl IdList;

rar ta_row;

--先定一個類型，然後聲明該類型的變量，官網連結如下：

<a href="http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10766/tdddg_subprograms.htm%23TDDDG99950">http://download.oracle.com/docs/cd/E11882_01/appdev.112/e10766/tdddg_subprograms.htm#TDDDG99950</a>

/* Formatted on 2011/7/11 11:22:37(QP5 v5.163.1008.3004) */

DECLARE

TYPEIdList IS TABLE OF ta.id%TYPE;

idl IdList;

BEGIN

SELECT id BULK COLLECT INTO idl FROM ta WHERE ROWNUM <= 100000;

FOR i IN idl.FIRST .. idl.LAST

LOOP

DELETE FROM ta WHERE id = idl (i);

END LOOP;

END;

PL/SQL procedure successfully completed.

Elapsed: 00:00:33.59 -- 花了34秒

SYS@anqing2(rac2)> rollback;

Rollback complete.

Elapsed: 00:00:29.02 --rollback 花了29秒

1.1.2 forall

/* Formatted on 2011/7/11 11:25:59(QP5 v5.163.1008.3004) */

FORALL i IN idl.FIRST .. idl.LAST

DELETE FROM ta WHERE id = idl(i);

Elapsed: 00:00:29.45 --花了30秒，速度提升不是很多。才4秒

Elapsed: 00:00:39.18

1.1.3 forall + rowid

/* Formatted on 2011/7/11 11:29:33(QP5 v5.163.1008.3004) */

row_id_table DBMS_SQL.Urowid_Table;

SELECT ROWID BULK COLLECT INTOrow_id_table FROM ta WHERE ROWNUM <= 100000;

FORALL i INrow_id_table.FIRST ..row_id_table.LAST

DELETE FROM ta WHERE ROWID =row_id_table (i);

Elapsed: 00:00:29.19 --比單純的forall 塊了僅1秒

Elapsed: 00:00:31.08

注意一點：

FORALL隻能包含一條DML 語句，而FOR LOOP 可以包含多條

1.2 Insert

SYS@anqing2(rac2)>create table ta2 as select * from ta where 1=2;

Table created.

/* Formatted on 2011/7/11 14:31:11(QP5 v5.163.1008.3004) */

TYPEta_row IS TABLE OF ta%ROWTYPEINDEX BY PLS_INTEGER;

Trow ta_row;

t1 INTEGER;

t2 INTEGER;

t3 INTEGER;

SELECT * BULK COLLECT INTO Trow FROM ta WHERE ROWNUM <= 100000;

t1 := DBMS_UTILITY.get_time;

FOR i IN 1 .. Trow.COUNT

LOOP

INSERT INTO ta2 VALUES trow (i);

END LOOP;

t2 := DBMS_UTILITY.get_time;

FORALL i IN 1 .. Trow.COUNT

INSERT INTO ta2 VALUES trow(i);

t3 := DBMS_UTILITY.get_time;

DBMS_OUTPUT.PUT_LINE ('ExecutionTime (secs)');

DBMS_OUTPUT.PUT_LINE ('---------------------');

DBMS_OUTPUT.PUT_LINE ('FORLOOP: ' || TO_CHAR ( (t2 - t1) / 100));

DBMS_OUTPUT.PUT_LINE ('FORALL:' || TO_CHAR ( (t3 - t2) / 100));

COMMIT;

--結果如下：

Execution Time (secs)

---------------------

FOR LOOP: 24.52

FORALL: 2.91 -- 使用forall性能提升很明顯

二. BULK COLLECT

2.1 SELECT INTO Statement with BULK COLLECT Clause

/* Formatted on 2011/7/11 15:25:28(QP5 v5.163.1008.3004) */

TYPE idTabIS TABLE OF ta.id%TYPE;

TYPEnameTab IS TABLE OF ta.name%TYPE;

TYPE taSetIS TABLE OF ta%ROWTYPEINDEX BY PLS_INTEGER;

tmp_ta taSet;

tmp_id idTab;

tmp_name nameTab;

SELECT id, name BULK COLLECT INTOtmp_id,tmp_name FROM ta WHERE ROWNUM < 5;

SELECT id, name BULK COLLECT INTOtmp_ta FROM ta WHERE ROWNUM < 5;

FOR i INtmp_ta.FIRST ..tmp_ta.LAST

DBMS_OUTPUT.PUT_LINE(tmp_ta(i).id || ': ' ||tmp_ta (i).name);

FOR j IN 1..tmp_id.count

DBMS_OUTPUT.PUT_LINE(tmp_id(j) || '--' ||tmp_name (j));

198854: dave

198855: dave

198856: dave

198857: dave

198854--dave

198855--dave

198856--dave

198857--dave

2.2 Row Limits for SELECT BULK COLLECT INTO Statements

當Select bulk collect into 傳回的結果集很大時，可以使用rownum 或者sample 來限制他的大小。 sample 表示總記錄數的一個百分比。

TYPE idList IS TABLE OF ta.id%TYPE;

tmp_id idList;

tmp_id2 idList;

SELECT id BULK COLLECT INTO tmp_id FROM ta WHEREROWNUM <= 50;

SELECT id BULK COLLECT INTO tmp_id2 FROM taSAMPLE (1);

--這裡采樣為1%

DBMS_OUTPUT.PUT_LINE ('tmp_id count is: '||tmp_id.count);

DBMS_OUTPUT.PUT_LINE ('tmp_id2 count is:'|| tmp_id2.count);

結果為：

tmp_id count is: 50

tmp_id2 count is: 11044

2.3 FETCH Statement with BULK COLLECT Clause

FETCHBULK COLLECT 可以将整個結果集傳回到一個或者多個collectionvariables。

2.3.1 Bulk-Fetching into Two Nested Tables

TYPE nameList IS TABLE OF ta. name%TYPE;

CURSOR c1 IS SELECT id,name FROM ta WHERE rownum<20;

TYPE RecList IS TABLE OF c1%ROWTYPE;

tmp_name NameList;

recs RecList;

v_limit PLS_INTEGER := 2;

PROCEDURE print_results IS

-- Check if collections are empty:

IF tmp_name IS NULL ORtmp_name.COUNT = 0 THEN

DBMS_OUTPUT.PUT_LINE('Noresults!');

ELSE

DBMS_OUTPUT.PUT_LINE('Result:');

FOR i INtmp_name.FIRST ..tmp_name.LAST LOOP

DBMS_OUTPUT.PUT_LINE(' name:' ||tmp_name(i) || ': id' ||tmp_id(i));

END LOOP;

END IF;

DBMS_OUTPUT.PUT_LINE ('---Processing all results simultaneously ---');

OPEN c1;

FETCH c1 BULK COLLECT INTOtmp_id,tmp_name;

CLOSE c1;

print_results();

DBMS_OUTPUT.PUT_LINE ('---Processing ' ||v_limit || ' rowsat a time ---');

LOOP

FETCH c1 BULK COLLECT INTO tmp_id,tmp_nameLIMITv_limit;

EXIT WHENtmp_name.COUNT = 0;

--這種情況下，每次會處理limit的個數，知道處理完

DBMS_OUTPUT.PUT_LINE ('---Fetching records rather than columns ---');

FETCH c1 BULK COLLECT INTO recs;

FOR i IN recs.FIRST .. recs.LAST

-- Now all columns from result setcome from one record

DBMS_OUTPUT.PUT_LINE (' Nameis: ' ||recs(i).name || ': id' ||recs(i).id);

--- Processing all results simultaneously---

Result:

--- Processing 2 rows at a time ---

--- Fetching records rather than columns---

Name is: dave: id198854

Name is: dave: id198855

Name is: dave: id198856

Name is: dave: id198857

Name is: dave: id198858

Name is: dave: id198859

Name is: dave: id198860

Name is: dave: id198861

Name is: dave: id198862

Name is: dave: id198863

Name is: dave: id198864

Name is: dave: id198865

Name is: dave: id198866

Name is: dave: id198867

Name is: dave: id198868

Name is: dave: id198869

Name is: dave: id198870

Name is: dave: id198871

Name is: dave: id198872

2.3.2 Bulk-Fetchinginto Nested Table of Records

CURSOR c1 IS SELECT id, name FROM ta;

TYPE NameSet IS TABLE OF c1%ROWTYPE;

tmp_ta NameSet; -- nested table of records

TYPE cursor_var_type is REF CURSOR;

cv cursor_var_type;

-- Assign values to nested table ofrecords:

OPEN cv FOR

SELECT id,name FROM ta WHERE rownum<10;

FETCH cv BULK COLLECT INTO tmp_ta;

CLOSE cv;

-- Print nested table of records:

FOR i IN tmp_ta.FIRST .. tmp_ta.LAST LOOP

DBMS_OUTPUT.PUT_LINE (tmp_ta(i).id || ' '||tmp_ta (i). name);

結果：

198854 dave

198855 dave

198856 dave

198857 dave

198858 dave

198859 dave

198860 dave

198861 dave

198862 dave

2.4 Row Limits for FETCH BULK COLLECT Statements

在 FETCH BULK COLLECT 中使用limit 控制每個collection 的大小。如果傳回集很大，就分成多個collection 來處理。這個用法在2.3節中有使用。

TYPE numtab IS TABLE OF NUMBER INDEX BYPLS_INTEGER;

CURSOR c1 IS SELECT id FROM ta WHERE rownum<10;

empids numtab;

LOOP -- Fetch 10 rows or fewer in eachiteration

FETCH c1 BULK COLLECT INTO empids LIMIT 2;

EXIT WHEN empids.COUNT =0; -- Not: EXIT WHEN c1%NOTFOUND

DBMS_OUTPUT.PUT_LINE ('------- Results fromOne Bulk Fetch --------');

FOR i IN 1..empids.COUNT LOOP

DBMS_OUTPUT.PUT_LINE ('Employee Id: ' ||empids(i));

結果如下：

------- Results from One Bulk Fetch--------

Employee Id: 198854

Employee Id: 198855

Employee Id: 198856

Employee Id: 198857

Employee Id: 198858

Employee Id: 198859

Employee Id: 198860

Employee Id: 198861

Employee Id: 198862

2.5 RETURNING INTO Clause with BULK COLLECT Clause

RETURNINGBULK COLLECT INTO 可以顯示 INSERT, UPDATE, DELETE, or EXECUTE IMMEDIATE 的具體資料.

TYPE idList IS TABLE OF ta.id %TYPE;

TYPE NameList IS TABLE OF ta.name%TYPE;

DELETE FROM ta WHERE rownum<10

RETURNING id,name BULK COLLECT INTOtmp_id,tmp_name;

DBMS_OUTPUT.PUT_LINE ('Deleted ' ||SQL%ROWCOUNT || ' rows:');

FOR i IN tmp_id.FIRST.. tmp_id.LAST

DBMS_OUTPUT.PUT_LINE ('ID #' || tmp_id(i)|| ': ' || tmp_name(i));

傳回結果：

Deleted 9 rows:

ID #198854: dave

ID #198855: dave

ID #198856: dave

ID #198857: dave

ID #198858: dave

ID #198859: dave

ID #198860: dave

ID #198861: dave

ID #198862: dave

2.6 Using FORALL Statement and BULK COLLECT Clause Together

2.6.1 示例1

TYPE nameList IS TABLE OF ta.name%TYPE;

tmp_name nameList;

OPEN cv FOR SELECT id FROM ta WHERE rownum<10;

FETCH cv BULK COLLECT INTOtmp_id2;

FORALL i IN 1..tmp_id2.count

DELETE FROM ta WHERE id=tmp_id2(i) RETURNING id,name BULK COLLECT INTOtmp_id,tmp_name;

DBMS_OUTPUT.PUT_LINE ('Deleted' || SQL%ROWCOUNT || ' rows:');

FOR i INtmp_id.FIRST ..tmp_id.LAST

DBMS_OUTPUT.PUT_LINE ('Id #' ||tmp_id (i) || ' Name#' ||tmp_name (i));

Deleted 27 rows:

Id #198863 Name #dave

Id #198864 Name #dave

...

Id #198871 Name #dave

三. 用bulk 進行大表update 的測試

在blog：

用rowid 進行了性能提示，這裡測試下，看看使用bulk，性能能提升多少。在這裡我們使用forall+bulk collect+ rowid 進行測試。

SQL> select count(*) from dba;

3080070

SQL> select count(*) from dave;

兩張表都是300w。在使用rowid 時，全表更新用了近5分鐘。

/* Formatted on 2011/7/11 23:19:03(QP5 v5.163.1008.3004) */

maxrows NUMBER DEFAULT 45000;

row_id_table DBMS_SQL.Urowid_Table;

TYPE acList IS TABLE OF dba.area_code%TYPE;

tmp_ac acList;

CURSOR c1 IS

SELECT a.area_code, b.ROWID row_id FROM dave a, dba b

WHERE a.id = b.id ORDER BY b.ROWID;

OPEN c1;

FETCH c1 BULK COLLECT INTO tmp_ac, row_id_table LIMIT maxrows;

EXIT WHEN row_id_table.COUNT = 0;

FORALL i IN 1 .. row_id_table.COUNT

UPDATE dba SET prov_code = tmp_ac(i) WHERE ROWID = row_id_table(i);

COMMIT;

END LOOP;

CLOSE c1;

用limit 為1000 測試，用了Elapsed: 00:06:37.34

用limit 為40000 測試，用了Elapsed: 00:04:46.94

用limit 為45000 測試，用了Elapsed: 00:04:22.82

用limit 為50000 測試，用了Elapsed: 00:04:31.09

用limit 為50000 測試，用了Elapsed: 00:04:36.12

用limit 為100000 測試，用了Elapsed: 00:09:41.22

delete 和 insert 的相關文法和上面的類似，相關部分改成如下：

forall i in 1 .. row_id_table.count

insert into tb select * from ta where rowid = row_id_table(i) ;

delete from ta where rowid = row_id_table(i) ;

Oracle bulk 示例

繼續閱讀

2021-09-30三維點雲測量正方形包裹體積

阿裡雲天池龍珠計劃SQL訓練營day1

賦予使用者dba權限--解釋以上：

實驗樓sql進階之成績管理系統的資料操作(window)

DOG算子

Oracle的基本操作

為什麼要選擇UniDAC

SQL優化SQL語句優化的目的

JAVA高效程式設計指南

windows不能在本地計算機上運作oracleDbConsoleorcl

關于SQL語言

SQL語言基礎：常用的資料查詢語句

cs231n斯坦福基于卷積神經網絡的CV學習筆記（一）KNN和線性分類器/分類器損失/反向傳播一，KNN圖像分類算法二，線性分類器三，線性分類器損失四，反向傳播五，神經網絡

neo4j之cypher使用文檔

sqlServer根據經緯查距離

Oracle 批量查詢傳入List 傳回List