使用Bulk Collect提高Oracle查詢效率
Oracle8i中首次引入了Bulk Collect特性，該特性可以讓我們在PL/SQL中能使用批查詢，批查詢在某些情況下能顯著提高查詢效率。現(xiàn)在，我們對該特性進(jìn)行一些簡單的測試和分析。
一、首先，我們創(chuàng)建一個表，并插入100000條記錄
在SQL/Plus中執(zhí)行下列腳本：
drop table empl_tbl
/
create table empl_tbl(last_name varchar2(20),
first_name varchar2(10),
salary number(10))
/
begin
for i in 30000..1029999 loop
insert into empl_tbl(last_name,first_name,salary) values('carl'||(i-3000),'wu'||(1003000-i),i);
end loop;
end;
/
commit
/
select count(*) from empl_tbl;
/
二、使用三種方法計算表中某一字段含有多少個不重復(fù)值
2.1 使用常規(guī)的Distinct來實現(xiàn)
SQL> select count(distinct last_name) from empl_tbl;
Distinct Last Name
------------------
1000000
Executed in 1.531 seconds
我們可以看到，常規(guī)方法需要1.531 秒查出該表中有1000000個不重復(fù)的Last_name值。
2.2 使用游標(biāo)來實現(xiàn)
我們執(zhí)行下面語句來統(tǒng)計Last_name字段的不重復(fù)值個數(shù)：
CREATE OR REPLACE PROCEDURE tttt IS
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游標(biāo)，為了提高程序可讀性，我們沒有顯示定義游標(biāo)變量。
執(zhí)行結(jié)果：
all_rows are 1000000
PL/SQL procedure successfully completed
Executed in 10.39 seconds
游標(biāo)需要10.396秒才能查出該表中有1000000個不重復(fù)的Last_name值，所耗時間是Distinct查詢的10倍多。
2.3 使用Bulk Collect批查詢來實現(xiàn)
示例代碼如下：
declare
all_rows number(10);
--首先，定義一個Index-by表數(shù)據(jù)類型
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表數(shù)據(jù)類型last_name_tab，然后定義了一個該集合數(shù)據(jù)類型的變量last_name_arr，最后我們使用Bulk Collect批查詢來充填last_name_arr，請注意它
的使用語法。
執(zhí)行結(jié)果：
all_rows are 1000000
PL/SQL procedure successfully completed
Executed in 2.343 seconds
從上面執(zhí)行結(jié)果，我們可以看到，Bulk Collect批查詢只需要2.343 秒就能查出該表中有1000000個不重復(fù)的Last_name值，所耗時間只有游標(biāo)查詢的1/2.5。
三． 測試結(jié)果分析
為什么會出現(xiàn)上面的結(jié)果呢？我們可以使用Oracle的SQL_Trace來分析一下結(jié)果。在SQL命令行中，使用alter session set sql_trace=true語句打開Oracle的Trace，然后在命令行中執(zhí)行上面三種查詢并使用TKPROF工具生成Trace報告。
3.1 常規(guī)Distinct查詢結(jié)果分析
3.2 游標(biāo)查詢效率分析
3.3 Bulk Collect的查詢效率分析
四． 結(jié)論
通過上面的測試和分析，我們可以看到Bulk Collect批查詢在某種程度上可以提高查詢效率，它首先將所需數(shù)據(jù)讀入內(nèi)存，然后再統(tǒng)計分析，這樣就可以提高查詢效率。但是，如果Oracle數(shù)據(jù)庫的內(nèi)存較小，Shared Pool Size不足以保存Bulk Collect批查詢結(jié)果，那么該方法需要將Bulk Collect的集合結(jié)果保存在磁盤上，在這種情況下，Bulk Collect方法的效率反而不如其他兩種方法，有興趣的讀者可以進(jìn)一步測試。
另外，除了Bulk Collect批查詢外，我們還可以使用FORALL語句來實現(xiàn)批插入、刪除和更新，這在大批量數(shù)據(jù)操作時可以顯著提高執(zhí)行效率。