使用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方法的效率反而不如其他兩種方法,有興趣的讀者可以進一步測試。
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/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 使用oracle bulk collect 提高select FETCH LOOP的速度OracleOOP
- 【索引】Oracle查詢指定索引提高查詢效率索引Oracle
- Oracle提高查詢效率的方法Oracle
- Oracle 的 bulk collect用法Oracle
- oracle中bulk collect into用法 (批次效率提取遊標資料)Oracle
- 遊標+bulk collect into limit的不同方法查詢資料MIT
- 使用BULK COLLECT+FORALL加速批量提交
- 使用bulk collect實現cursor 批量fetch!
- 【PL/SQL】初試 bulk collectSQL
- 提高mysql查詢效率的六種方法MySql
- Oracle中巧用bulk collect實現cursor批次fetchOracle
- 提高mysql查詢效率及一些使用技巧記錄MySql
- BULK COLLECT FAILS WITH ORA-04030AI
- pl/sql中bulk collect的用法SQL
- [20180110]Oracle Bulk Collect and LimitOracleMIT
- 提高查詢速度使用materizlizedZed
- 使用Forall 與bulk collect的快速複製表資料
- APPEND,bulk collect,正常插入比較APP
- 【WITH Clause】使用WITH子句提高查詢統計效率-顛覆思維定勢
- 談談MYSQL索引是如何提高查詢效率的MySql索引
- oracle10g_plsql_rercursor_type_table of_小引例_bulk collect intoOracleSQL
- 使用謂詞(NSPredicate)來提高集合遍歷與過濾查詢的效率
- 【效能優化】Oracle 效能優化:降低列值聚簇因子 提高查詢效率優化Oracle
- [20180111]Oracle Bulk Collect and Limit2OracleMIT
- Oracle資料庫的BULK COLLECT用法之批量增刪改<轉>Oracle資料庫
- PL/SQL LOB和檔案操作,bulk collectSQL
- NULL列時,如何使得IS NULL或者IS NOT NULL可以使用索引來提高查詢效率Null索引
- 使用RESULT CACHE加速SQL查詢效率SQL
- 突破常識:SQL增加DISTINCT後查詢效率反而提高SQL
- 多行資料的批處理之bulk collect
- 用BULK COLLECT 減小LOOP的開銷(1)OOP
- sql 查詢效率SQL
- 【效能最佳化】Oracle 效能最佳化:降低列值聚簇因子 提高查詢效率Oracle
- ElasticSearch在數十億級別資料下,如何提高查詢效率?Elasticsearch
- MySQL索引憑什麼能讓查詢效率提高這麼多?MySql索引
- 如何利用mysql5.7提供的虛擬列來提高查詢效率MySql
- 在大資料量下提高查詢效率的方法—ES搜尋引擎大資料
- 提高sql查詢速度SQL