13. 使用批次處理

使用批次處理的 SQL 語法可以減少與資料庫溝通與 context switch 的次數，進而提升效能， Oracle 在處理 SQL 語句時使用到兩種引擎 (engine) ， PL/SQL engine 與 SQL engine :

PL/SQL engine 處理的是 cursor、loop … 等語法，而 SQL engine 處理的則是 select、insert … 等語法，當我們執行一段程式時，資料便會在這兩個引擎之間不斷轉換，這個動作稱做 context switch ，例如一段 SQL 如下:

for i in 1..100 loop  insert into TT values (i);  commit; end loop;

此 SQL 使用了 for loop 來插入資料，首先 PL/SQL engine 處理 i=1 這個數值，並將截取出來的數值傳送至 SQL engine 讓他處理 insert … 動做， SQL engine 處理完之後又將回到 PL/SQL engine 重新擷取 i=2 這個數值，然後再送達至 SQL engine 插入下一筆資料，如此不斷重複，直到 i=100 為止，所以最後總共會進行 100 次 context switch 。

批次處理的原理就是將需要處理的數據一次打包，再將數據送達 SQL engine一併處理，如此數據就不需在這兩種引擎之間重複的進行轉換， Oracle 提供了 Bulk collect into 與 Forall 兩種批次處理的語法， Bulk collect into 用來批次處理查詢語法 (select statement) 而 Forall 則用來處裡批次修改語法 (DML statement) 。

• Bulk collect into

例如一段原始的 PL/SQL 如下，使用 cursor 將 manager_id=100 的資料篩選出來之後與部門的 Table (departments) 做 join 找出相對應的部門名稱，並將結果插入 edep 這個 Table 之中 :

此程式共有三段 SQL 需執行:

1. cursor所描述的 select statement

2. loop 所描述的 select statement

3. loop 所描述的 insert statement

執行完畢後於edep共插入15筆資料:

經由 SQL Trace ，我們來看看這三段 SQL 總共執行了多少次，首先為 cursor 所描述的 select statement :

透過 SQL Trace 可以得知 cursor 所描述的 select statement 執行了 1 次。

loop 中的 select statement 則執行了 15 次:

最後的 insert statement 執行了 15 次 :

所以總共 insert 語法執行了 15 次， select 語法執行了 16 次 (1+15) 。透過 bulk collect into 語法改寫程式可以減少 select statement 執行的次數，將上述 PL/SQL 修改如下 :

改寫後執行的結果相同， insert 一樣執行了 15 次，但是 select statement 只需要執行一次就可以達到相同的結果 :

同樣的概念也可以使用 fetch bulk collect into 語法來改寫，寫法稍微不同，但是結果一樣是 insert 執行 15 次 、 select 只有執行一次 :

藉由 bulk collect into 來減少 select statement 執行的次數，由此提高整體執行的速度。

• FORALL

FORALL 語法主要是用來減少 insert statement 執行的次數， FORALL與 FOR…LOOP 不同的是， FOR…LOOP 為逐筆處理，假設欲插入 100 筆資料，則 insert 語法將執行 100 次；而 FORALL 語法則是將資料打包，插入這 100 筆資料只需執行一次 insert 語法，例如一段 PL/SQL 如下，我們欲將五位員工的薪資 update 為 1.5 倍，首先使用 FOR…LOOP 語法 :

FOR…LOOP 語法將逐次處理每筆資料，透過 SQL Trace 可以看出 update 語法總共執行了五次:

使用 FORALL 改寫如下:

FORALL 所使用的是批次處理的功能，將這五筆資料打包之後一併處理，因此由 SQL Trace 的結果可以看出 update 語法僅執行了一次而已:

經由上述範例我們可以知道透過批次處理的方式可以有效的減少 SQL 執行的次數進而提升效能，然而批次打包處理的概念仍就是暫時將資料存放在記憶體裡一次處理，因此在使用批次處理功能的同時也需注意系統資源是否充足，否則在處理大量資料的時候有可能會出現記憶體不足的錯誤。

除了 bulk collect into 與 forall 兩個語法之外，另外一個與批次處理類似的概念為 multi table insert ，這個功能從 Oracle 9i 就開始有的，以往 insert 資料到 Table 時，每個 Table 都需要執行一次 insert 的語法，而 multi table insert 則可將所有 insert 語法組合起來，只需與 Database 做一次溝通便可達成 insert 多個 Table 的目的，例如我們對 regions 與 jobs 這兩個 table 各新增一筆資料，以往需執行兩次 insert 語法如下:

SQL> insert into regions(region_id,region_name) values (7,'test'); SQL> insert into jobs(job_id,job_title,min_salary,max_salary)        values ('RP_RP','test',1,100);

使用兩個 insert 語法需要與資料庫做兩次溝通才能完成兩筆資料的insert ，如果使用 insert all 語法改寫如下，只需要與資料庫做一次溝通便可完成兩筆資料的insert了 :

SQL> insert all into regions(region_id,region_name) values (region_id,region_name) into jobs(job_id,job_title,min_salary,max_salary)  values (job_id,job_title,min_salary,max_salary) select '7' region_id,'test' region_name,'RP_RP' job_id,'test' job_title, '1' min_salary,'100' max_salary from dual;

到了 Oracle 10g 將 multi insert 的功能做了加強，不僅可以同時insert 多個 table 而且還可以針對不同條件來 insert ，例如將薪資大於 5000 小於 15000 的員工歸類到 emp_med_salary 這個 table 中；薪資大於 15000 的員工歸類到 emp_high_salary 這個 table ； 其餘的員工則歸類到 emp_low_salary ，可使用下列語法來達成這個目的 :

SQL> insert all when (salary >15000) then into emp_high_salary when (salary>5000 and salary<=15000) then into emp_med_salary else into emp_low_salary select * from employees;

最後要注意的是，使用 multi insert 只能使用在 Table 這個物件上，對於 view 或是透過 db_link 存取遠端物件則無法使用。