oracle大量繫結 forall bulk collect用法以及測試案例

文章目錄

• 通過bulk collect減少loop處理的開銷
• oracle大量繫結forall bulk collect
• 使用Bulk Collect提高Oracle查詢效率

通過bulk collect減少loop處理的開銷採用bulk collect可以將查詢結果一次性地載入到collections中。而不是通過cursor一條一條地處理。可以在select into,fetch into,returning into語句使用bulk collect。注意在使用bulk collect時，所有的into變數都必須是collections. 
舉幾個簡單的例子： --在select into語句中使用bulk collectDECLARE
TYPE SalList IS TABLE OF emp.sal%TYPE;
sals SalList;
BEGIN
-- Limit the number of rows to 100.
SELECT sal BULK COLLECT INTO sals FROM emp
WHERE ROWNUM <= 100;
-- Retrieve 10% (approximately) of the rows in the table.
SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10;END;
/ --在fetch into中使用bulk collectDECLARE
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;
/ --在returning into中使用bulk collectCREATE TABLE emp2 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 FROM emp2 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;
/
DROP TABLE emp2;  oracle大量繫結forall bulk collect oracle大量繫結forall bulk collect http://otn.oracle.com/oramag/oracle...tech_plsql.html                                                           http://otn.oracle.com/docs/products...colls.htm#23723關於Bulk Binds中LIMIT的使用，請看TOM的解說
                                                           http://asktom.oracle.com/pls/ask/f?...5918938803188,Y

Oracle10g中對於forall的增強
                                                           http://www.itpub.net/showthread.php?s=&threadid=184794大量繫結（Bulk binds）可以通過減少在PL/SQL和SQL引擎之間的環境切換(context switches )提高了效能.大量繫結（Bulk binds）包括：
       (i) Input collections, use the FORALL statement，用來改善DML（INSERT、UPDATE和DELETE) 操作的效能
       (ii) Output collections, use BULK COLLECT clause，一般用來提高查詢（SELECT）的效能10g開始forall語句可以使用三種方式：
       i in low..up
       i in indices of collection  取得集合元素下標的值
       i in values of collection   取得集合元素的值
       forall語句還可以使用部分集合元素        sql%bulk_rowcount(i)表示forall語句第i元素所作用的行數CREATE TABLE parts1 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE parts2 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE parts3 (pnum INTEGER, pname VARCHAR2(15));
CREATE TABLE parts4 (pnum INTEGER, pname VARCHAR2(15));set   serveroutput   on   --把螢幕顯示開關置上DECLARE
     TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER;
     TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER;
     pnums NumTab;
     pnames NameTab;
     iterations CONSTANT PLS_INTEGER := 50000;
     t1 INTEGER; t2 INTEGER; t3 INTEGER; t4 INTEGER; t5 INTEGER;
     stmt_str varchar2(255);table_name varchar2(255);
BEGIN
     FOR j IN 1..iterations LOOP -- load index-by tables
          pnums(j) := j;
          pnames(j) := 'Part No. ' || TO_CHAR(j);
     END LOOP;
          t1 := dbms_utility.get_time;
     FOR i IN 1..iterations LOOP -- use FOR loop
          INSERT INTO parts1 VALUES (pnums(i), pnames(i));
     END LOOP;
          t2 := dbms_utility.get_time;
     FORALL i IN 1..iterations -- use FORALL statement
          INSERT INTO parts2 VALUES (pnums(i), pnames(i));

     t3 := dbms_utility.get_time;
     table_name:='parts3';
     stmt_str := 'INSERT INTO ' || table_name || ' values (:num, :pname)';
     FOR i IN 1..iterations LOOP -- use FORALL statement
          EXECUTE IMMEDIATE stmt_str USING pnums(i), pnames(i);
     END LOOP;

     t4 := dbms_utility.get_time;
     table_name:='parts4';
     stmt_str := 'INSERT INTO ' || table_name || ' values (:num, :pname)';
     FORALL i IN 1..iterations-- use FORALL statement
          EXECUTE IMMEDIATE stmt_str USING pnums(i), pnames(i);     t5 := dbms_utility.get_time;
     dbms_output.put_line('Execution Time (secs)');
     dbms_output.put_line('---------------------');
     dbms_output.put_line('FOR loop: ' || TO_CHAR((t2 - t1)/100));
     dbms_output.put_line('FORALL: ' || TO_CHAR((t3 - t2)/100));
     dbms_output.put_line('FOR loop: ' || TO_CHAR((t4 - t3)/100));
     dbms_output.put_line('FORALL: ' || TO_CHAR((t5 - t4)/100));
          COMMIT;
END;
/DROP TABLE parts1;
DROP TABLE parts2;
DROP TABLE parts3;DROP TABLE parts4;/*
bulk collect 語句：
     用於取得批量資料，只適用於select into ,fetch into 及DML語句的返回子句
          DECLARE
          TYPE type_emp IS TABLE OF scott.emp%ROWTYPE INDEX BY BINARY_INTEGER;
          tab_emp type_emp;          TYPE type_ename IS TABLE OF scott.emp.ename%TYPE INDEX BY BINARY_INTEGER;
          tab_ename type_ename;
          CURSOR c IS
               SELECT *
               FROM scott.emp;     BEGIN
          SELECT * BULK COLLECT
          INTO tab_emp
          FROM scott.emp;
                    FOR i IN 1 .. tab_emp.COUNT LOOP
               dbms_output.put_line(tab_emp(i).ename);
          END LOOP;          dbms_output.new_line;
          DELETE scott.emp RETURNING ename BULK COLLECT INTO tab_ename;
          FOR i IN 1 .. tab_emp.COUNT LOOP
               dbms_output.put_line(tab_emp(i).ename);
          END LOOP;
                    ROLLBACK;                    OPEN c;
          FETCH c BULK COLLECT
               INTO tab_emp;
               dbms_output.new_line;
               FOR i IN 1 .. tab_emp.COUNT LOOP
                    dbms_output.put_line(tab_emp(i).sal);
               END LOOP;          END;
     */使用Bulk Collect提高Oracle查詢效率

Oracle8i中首次引入了Bulk Collect特性，該特性可以讓我們在PL/SQL中能使用批查詢，批查詢在某些情況下能顯著提高查詢效率。現在，我們對該特性進行一些簡單的測試和分析。

1． 首先，我們建立一個表，並插入100000條記錄
在SQL/Plus中執行下列指令碼：

drop table empl_tbl
/
create table empl_tbl(last_name varchar2(20),
first_name varchar2(10),
salary number(10))
/

begin
for i in 3000..102999 loop
insert into empl_tbl(last_name,first_name,salary) values('carl'||(i-3000),'wu'||(103000-i),i);
end loop;
end;
/
commit
/
select count(*) from empl_tbl;
/

2． 使用三種方法計算表中某一欄位含有多少個不重複值

2.1 使用常規的Distinct來實現

SQL> select count(distinct last_name) "Distinct Last Name" from empl_tbl;

Distinct Last Name
------------------
            100000
 
Executed in 0.36 seconds

我們可以看到，常規方法需要0.36秒查出該表中有100000個不重複的Last_name值。

2.2 使用遊標來實現

我們執行下面語句來統計Last_name欄位的不重複值個數：

declare
  all_rows number(10);
  temp_last_name empl_tbl.last_name%type;
begin
  all_rows:=0;
  temp_last_name:=' ';
  for cur in (select last_name from empl_tbl order by last_name) loop
     
      if cur.last_name!=temp_last_name then
       all_rows:=all_rows+1;
      end if;
      temp_last_name:=cur.last_name;
     
  end loop;
  dbms_output.put_line('all_rows are '||all_rows);
end;

請注意上面代碼中的黑體部分使用了一個For Loop遊標，為了提高程式可讀性，我們沒有顯示定義遊標變數。

執行結果：
all_rows are 100000
PL/SQL procedure successfully completed
Executed in 1.402 seconds

遊標需要1.4秒才能查出該表中有100000個不重複的Last_name值，所耗時間是Distinct查詢的3倍多。

2.3 使用Bulk Collect批查詢來實現

範例程式碼如下：
declare
  all_rows number(10);
  --首先，定義一個Index-by表資料類型
  type last_name_tab is table of empl_tbl.last_name%type index by binary_integer;
  last_name_arr last_name_tab;
  --定義一個Index-by表集合變數
  temp_last_name empl_tbl.last_name%type;
 
begin
  all_rows:=0;
  temp_last_name:=' ';
  --使用Bulk Collect批查詢來充填集合變數
  select last_name bulk collect into last_name_arr from empl_tbl;
 
  for i in 1..last_name_arr.count loop
      if temp_last_name!=last_name_arr(i) then
       all_rows:=all_rows+1;
      end if;
      temp_last_name:=last_name_arr(i);
  end loop;
 dbms_output.put_line('all_rows are '||all_rows);
end;

請注意上面代碼中，我們首先定義了一個Index-by表資料類型last_name_tab，然後定義了一個該集合資料類型的變數last_name_arr，最後我們使用Bulk Collect批查詢來充填last_name_arr，請注意它的使用文法。

執行結果：
all_rows are 100000
PL/SQL procedure successfully completed
Executed in 0.28 seconds
從上面執行結果，我們可以看到，Bulk Collect批查詢只需要0.28秒就能查出該表中有100000個不重複的Last_name值，所耗時間只有遊標查詢的1/5，同時它比Distinct常規查詢的速度也要快。

3． 測試結果分析
為什麼會出現上面的結果呢？我們可以使用Oracle的SQL_Trace來分析一下結果。在SQL命令列中，使用alter session set sql_trace=true語句開啟Oracle的Trace，然後在命令列中執行上面三種查詢並使用TKPROF工具產生Trace報告。

3.1 常規Distinct查詢結果分析********************************************************************************select count(distinct last_name)
from
 empl_tbl

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.28       0.32        198        425          4           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        3      0.28       0.32        198        425          4           1

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 62 

Rows     Row Source Operation
-------  ---------------------------------------------------
      1  SORT GROUP BY
 100000   TABLE ACCESS FULL EMPL_TBL

********************************************************************************
上述查詢首先對empl_tbl進行全表掃描，然後分組排序得出結果。SQL解析、執行的時間都可忽略不計，主要時間花在讀取資料上，因為當前SQL只是一個查詢，沒有任何增、刪改操作。在資料讀取階段，需要從磁碟讀取198個Oracle資料區塊，一致性讀取（query，consistent gets）資料區塊425塊。因為磁碟物理讀是非常耗時的，所以該查詢執行起來不是特別快。

3.2 遊標查詢效率分析********************************************************************************
SELECT LAST_NAME
FROM
 EMPL_TBL ORDER BY LAST_NAME

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch   100001      0.71       0.62        198        425          4      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   100002      0.71       0.62        198        425          4      100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62     (recursive depth: 1)********************************************************************************該方法的主要耗時也是在資料讀取階段，雖然磁碟讀取資料區塊和一致性讀取資料區塊的數目同Distinct SQL查詢相等，但是，因為該方法中的遊標要迴圈100001次，所以上面的SQL的讀取會發生100001次，總共讀出了100000行資料，這就是為什麼使用遊標需要1.4秒的原因。下面我們看看使用Bulk Collect會發生什嗎？

3.3 Bulk Collect的查詢效率分析********************************************************************************
SELECT LAST_NAME
FROM
 EMPL_TBL

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        1      0.08       0.07          0        425          0      100000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.08       0.07          0        425          0      100000

Misses in library cache during parse: 0
Optimizer goal: CHOOSE
Parsing user id: 62     (recursive depth: 1)
********************************************************************************
雖然這種方法也要讀取100000行資料，但是讀取操作只發生了1次，並且Bulk Collect語句將所需資料一次性讀入記憶體，所以該方法沒有從磁碟讀取資料區塊，所以這種方法比上述兩種方法都具有優勢，所以執行效率最高。

4． 結論
通過上面的測試和分析，我們可以看到Bulk Collect批查詢在某種程度上可以提高查詢效率，它首先將所需資料讀入記憶體，然後再統計分析，這樣就可以提高查詢效率。但是，如果Oracle資料庫的記憶體較小，Shared Pool Size不足以儲存Bulk Collect批查詢結果，那麼該方法需要將Bulk Collect的集合結果儲存在磁碟上，在這種情況下，Bulk Collect方法的效率反而不如其他兩種方法，有興趣的讀者可以進一步測試。

另外，除了Bulk Collect批查詢外，我們還可以使用FORALL語句來實現批插入、刪除和更新，這在大批量資料操作時可以顯著提高執行效率。