Oracle 中使用 fetch bulk collect into 批量效率的讀取

 http://www.jzxue.com/shujuku/oracle/201109/21-8976.html

通常我們擷取遊标資料是用 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) 取出字段值來執行你的業務邏輯　　　　　

　　　　　　　　　　　 null; --這裡隻放置一個空操作，隻為測試循環取數的效率　　　　

　　　　　　　 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) 取出字段值來執行你的業務邏輯　　

　　　　　　　　　　　 null; --這裡隻放置一個空操作，隻為測試循環取數的效率　

　　　　　　　 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

　　 --聲明變量，參照字段的 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;

　　執行性能比較

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

　　當 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　　

　　　　　　　　　　　 --的形式來取出各字段值來執行你的業務邏輯　　

　　　　　　　　　　　 null; --這裡隻放置一個空操作，隻為測試循環取數的效率　　

　　　　　　　 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

　　　　　　　　　　　 --的形式來取出各字段值來執行你的業務邏輯

　　　　　　　　　　　 null; --這裡隻放置一個空操作，隻為測試循環取數的效率

　　　　　　　 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 操作