使用Bulk Collect提高Oracle查詢效率

maojinyu發表於2011-09-06

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語句來實現批插入、刪除和更新,這在大批次資料操作時可以顯著提高執行效率。[@more@]

declare
type tab is table of m_tables%rowtype index by binary_integer;
m_t tab;
cursor c is select * from m_tables;
begin
open c;
fetch c bulk collect into m_t;
close c;
forall i in 1..m_t.count
insert into m_tables
values m_t(i);
commit;
end;

declare
type m_t is table of mao.m_tables%rowtype index by binary_integer;
m m_t;
begin
select * bulk collect into m from mao.m_tables s where s.owner ='MAO' AND TABLE_NAME='M_TABLES';
for i in 1..m.count loop
dbms_output.put_line(m(i).table_name);
end loop;
end;

來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/9907339/viewspace-1054811/,如需轉載,請註明出處,否則將追究法律責任。

相關文章