Oracle 中使用 fetch bulk collect into 批量效率的讀取遊標資料

通常我們擷取遊標資料是用 fetch some_cursor into var1, var2 的形式，當遊標中的記錄數不多時不打緊。然而自 Oracle 8i 起，Oracle 為我們提供了 fetch bulk collect 來批量取遊標中的資料。它能在讀取遊標中大量資料的時候提高效率，就像 SNMP 協議中，V2 版比 V1 版新加了 GET-BULK PDU 一樣，也是用來更高效的批量取裝置上的節點值。

　　fetch bulk collect into 的使用格式是：fetch some_cursor collect into col1, col2 limit xxx。col1、col2 是聲明的集合類型變數，xxx 為每次取資料區塊的大小（記錄數），相當於緩衝區的大小，可以不指定 limit xxx 大小。下面以實際的例子來說明它的使用，並與逐條取記錄的 fetch into 執行效率上進行比較。測試環境是 Oracle 10g　 10.2.1.0，查詢的連絡人表 sr_contacts 中有記錄數 1802983 條，遊標中以 rownum 限定返回的記錄數。

　　使用 fetch bulk collect into 擷取遊標資料

　　declare　　　　

　　 --聲明需要集合類型及變數，參照欄位的 type 來宣告類型　　　　　　

　 type id_type is table of sr_contacts.sr_contact_id%type;　　　　　
　 v_id id_type;　　　　　
　　　　　　　
　 type phone_type is table of sr_contacts.contact_phone%type;　　　　　
　 v_phone phone_type;　　　　　
　　　　　　　
　 type remark_type is table of sr_contacts.remark%type;　　　　　
　 v_remark remark_type;　　　

 

 

　　 cursor all_contacts_cur is --用 rownum 來限定取出的記錄數來測試

 select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000;　　　　　　

begin　　　　
　　　　　　　　　
　　　 open all_contacts_cur;　　　　　
　　　 loop　　　　　
　　　　　　　 fetch all_contacts_cur bulk collect into v_id,v_phone,v_remark limit 256;　　　　　
　　　　　　　 for i in 1..v_id.count loop --遍曆集合　　　　　
　　　　　　　　　　　 --用 v_id(i)/v_phone(i)/v_remark(i) 取出欄位值來執行你的商務邏輯　　　　　 　　
　　　　　　　 end loop;　　　　　
　　　　　　　 exit when all_contacts_cur%notfound; --exit 不能緊接 fetch 了，不然會漏記錄　　　　　
　　　 end loop;　　　　　
　　　 close all_contacts_cur;　　　　　
end;　　　
declare　　

 

　　 --聲明需要集合類型及變數，參照欄位的 type 來宣告類型　　　

　 type id_type is table of sr_contacts.sr_contact_id%type;　　
　 v_id id_type;　　
　　　　
　 type phone_type is table of sr_contacts.contact_phone%type;　　
　 v_phone phone_type;　　
　　　　
　 type remark_type is table of sr_contacts.remark%type;　　
　 v_remark remark_type;　

 

 

　　 cursor all_contacts_cur is --用 rownum 來限定取出的記錄數來測試

 select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000;　　　

begin　
　　　 open all_contacts_cur;　　
　　　 loop　　
　　　　　　　 fetch all_contacts_cur bulk collect into v_id,v_phone,v_remark limit 256;　　
　　　　　　　 for i in 1..v_id.count loop --遍曆集合　　
　　　　　　　　　　　 --用 v_id(i)/v_phone(i)/v_remark(i) 取出欄位值來執行你的商務邏輯　　
　　　　　　　 end loop;　　
　　　　　　　 exit when all_contacts_cur%notfound; --exit 不能緊接 fetch 了，不然會漏記錄　　
　　　 end loop;　　
　　　 close all_contacts_cur;　　
end;　

　　使用 fetch into 逐行擷取遊標資料

　　declare　

　　 --聲明變數，參照欄位的 type 來宣告類型　　　

　 v_id sr_contacts.sr_contact_id%type;　　
　 v_phone sr_contacts.contact_phone%type;　　
　 v_remark sr_contacts.remark%type;　　　

 

　　 cursor all_contacts_cur is　 --用 rownum 來限定取出的記錄數來測試　　　

　　　　 select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000;　　　

begin　
　　　　　　
　　　 open all_contacts_cur;　　
　　　 loop　　
　　　　　　　 fetch all_contacts_cur into v_id,v_phone,v_remark;　　
　　　　　　　 exit when all_contacts_cur%notfound;　　　　　　
　　　　　　　 --用 v_id/v_phone/v_remark 取出欄位值來執行你的商務邏輯　　
　　　　　　　 null; --這裡只放置一個空操作，只為測試迴圈取數的效率　　
　　　 end loop;　　
　　　 close all_contacts_cur;　　
end;　
declare

 v_id sr_contacts.sr_contact_id%type;
　 v_phone sr_contacts.contact_phone%type;
　 v_remark sr_contacts.remark%type;

　　 cursor all_contacts_cur is　 --用 rownum 來限定取出的記錄數來測試

　　　　 select sr_contact_id,contact_phone,remark from sr_contacts where rownum <= 100000;

begin
　　　
　　　 open all_contacts_cur;
　　　 loop
　　　　　　　 fetch all_contacts_cur into v_id,v_phone,v_remark;
　　　　　　　 exit when all_contacts_cur%notfound;　　　
　　　　　　　 --用 v_id/v_phone/v_remark 取出欄位值來執行你的商務邏輯
　　　　　　　 null; --這裡只放置一個空操作，只為測試迴圈取數的效率
　　　 end loop;
　　　 close all_contacts_cur;
end;

　　執行效能比較

　　看看測試的結果，分別執行五次所耗費的秒數：

　　當 rownum <= 100000 時：

　　fetch bulk collect into 耗時：0.125秒, 0.125秒, 0.125秒, 0.125秒, 0.141秒

　　fetch into 耗時：　　　　　　1.266秒, 1.250秒, 1.250秒, 1.250秒, 1.250秒

　　當 rownum <= 1000000 時：

　　fetch bulk collect into 耗時：1.157秒, 1.157秒, 1.156秒, 1.156秒, 1.171秒

　　fetch into 耗時：　　　　　　12.128秒, 12.125秒, 12.125秒, 12.109秒, 12.141秒

　　當 rownum <= 10000 時：

　　fetch bulk collect into 耗時：0.031秒, 0.031秒, 0.016秒, 0.015秒, 0.015秒

fetch into 耗時：　　　　　　　　　　　　　　　　 0.141秒, 0.140秒, 0.125秒, 0.141秒, 0.125秒

　　當 rownum <= 1000 時：

　　fetch bulk collect into 耗時：0.016秒, 0.015秒, 0.016秒, 0.016秒, 0.015秒

　　fetch into 耗時：　　　　　　0.016秒, 0.031秒, 0.031秒, 0.032秒, 0.015秒

　　從測試結果來看遊標的記錄數越大時，用 fetch bulk collect into 的效率很明顯示，趨於很小時就差不多了。

　　注意了沒有，前面使用 fetch bulk collect into 時前為每一個查詢列都定義了一個集合，這樣有些繁瑣。我們之前也許用過表的 %rowtype 類型，同樣的我們也可以定義表的 %rowtype 的集合類型。看下面的例子，同時在這個例子中，我們藉助於集合的 first、last 屬性來代替使用 count　 屬性來進行遍曆。

　　declare　

　　 --聲明需要集合類型及變數，參照欄位的 type 來宣告類型　　　

　 type contacts_type is table of sr_contacts%rowtype;　　
　 v_contacts contacts_type;　　　

 

　　 cursor all_contacts_cur is --用 rownum 來限定取出的記錄數來測試　　　

　　　　 select * from sr_contacts where rownum <= 10000;　　　

begin　
　　　　　　
　　　 open all_contacts_cur;　　
　　　 loop　　
　　　　　　　 fetch all_contacts_cur bulk collect into v_contacts limit 256;　　
　　　　　　　 for i in v_contacts.first .. v_contacts.last loop --遍曆集合　　
　　　　　　　　　　　 --用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark　　
　　　　　　　　　　　 --的形式來取出各欄位值來執行你的商務邏輯　　 　
　　　　　　　 end loop;　　
　　　　　　　 exit when all_contacts_cur%notfound;　　
　　　 end loop;　　
　　　 close all_contacts_cur;　　
end;　
declare

--聲明需要集合類型及變數，參照欄位的 type 來宣告類型

　 type contacts_type is table of sr_contacts%rowtype;
　 v_contacts contacts_type;

 

　　 cursor all_contacts_cur is --用 rownum 來限定取出的記錄數來測試

　　　　 select * from sr_contacts where rownum <= 10000;

begin
　　　
　　　 open all_contacts_cur;
　　　 loop
　　　　　　　 fetch all_contacts_cur bulk collect into v_contacts limit 256;
　　　　　　　 for i in v_contacts.first .. v_contacts.last loop --遍曆集合
　　　　　　　　　　　 --用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark
　　　　　　　　　　　 --的形式來取出各欄位值來執行你的商務邏輯
　　　　　　　 end loop;
　　　　　　　 exit when all_contacts_cur%notfound;
　　　 end loop;
　　　 close all_contacts_cur;
end;

　　關於 limit 參數

　　你可以根據你的實際來調整 limit 參數的大小，來達到你最優的效能。limit 參數會影響到 pga 的使用率。而且也可以在 fetch bulk 中省略 limit 參數，寫成

fetch all_contacts_cur bulk collect into v_contacts;

　　有些資料中是說，如果不寫 limit 參數，將會以資料庫的 arraysize　 參數值作為預設值。在 sqlplus 中用 show arraysize　 可以看到該值預設為 15，set arraysize 256 可以更改該值。而實際上我測試不帶 limit 參數時，外層迴圈只執行了一輪，好像不是 limit 15，所以不寫 limit 參數時，可以去除外層迴圈，begin-end 部分可寫成：

 

begin　
　　　 open all_contacts_cur;　　
　　　 fetch all_contacts_cur bulk collect into v_contacts;　　
　　　 for i in v_contacts.first .. v_contacts.last loop --遍曆集合　　
　　　　　　　 --用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark　　
　　　　　　　 --的形式來取出各欄位值來執行你的商務邏輯　　
　　　　　　　 null; --這裡只放置一個空操作，只為測試迴圈取數的效率　　
　　　　　　　 dbms_output.put_line(2000);　　
　　　 end loop;　　
　　　 close all_contacts_cur;　　
end;　
begin
　　　 open all_contacts_cur;
　　　 fetch all_contacts_cur bulk collect into v_contacts;
　　　 for i in v_contacts.first .. v_contacts.last loop --遍曆集合
　　　　　　　 --用 v_contacts(i).sr_contact_id/v_contacts(i).contact_phone/v_contacts(i).remark
　　　　　　　 --的形式來取出各欄位值來執行你的商務邏輯
　　　　　　　 null; --這裡只放置一個空操作，只為測試迴圈取數的效率
　　　　　　　 dbms_output.put_line(2000);
　　　 end loop;
　　　 close all_contacts_cur;
end;

　　bulk collect 的其他用法(總是針對集合)

　　select into 語句中，如：

SELECT sr_contact_id,contact_phone BULK COLLECT INTO v_id,v_phone
　　　　 FROM sr_contacts WHERE ROWNUM <= 100;
dbms_output.put_line('Count:'||v_id.count||', First:'||v_id(1)||'|'||v_phone(1));

 

　　returning into 語句中，如：

DELETE FROM sr_contacts WHERE sr_contact_id < 30
　　　 RETURNING sr_contact_id, contact_phone BULK COLLECT INTO v_id, v_phone;
dbms_output.put_line('Count:'||v_id.count||', First:'||v_id(1)||'|'||v_phone(1));

　　forall 的 bulk dml 操作，它大大優於 for 集合後的操作

fetch all_contacts_cur bulk collect into v_contacts;
forall i in 1 .. v_contacts.count
--forall i in v_contacts.first .. v_contacts.last　　
--forall i in indices of v_contacts --10g以上,可以是非連續的集合　　
insert into sr_contacts(sr_contact_id,contact_phone,remark)
　　　 values(v_contacts(i).sr_contact_id,v_contacts(i).contact_phone,v_contacts(i).remark);　

　　 --或者是單條的 delete/update 操作