14. 進階 SQL 語法

在撰寫 SQL 的過程中，使用一些較進階的語法可以有效減少 SQL 語句的複雜度，藉此提升執行的效率。

• 使用 with 語句

當 SQL 語法使用到子查詢時，使用 with 語法可以用來提升查詢的效率， with 語法最早由 Oracle 9i 開始，作用是優先處理子查詢語句並將結果產生出 result set ，接下來的 SQL 語句只需要從這個 result set 篩選出需要的資料即可，這種處理方式不僅可以減少擷取資料所需的 I/O ，而且子查詢已經產生 result set ，可以避免子查詢與主查詢的執行計畫進行 merge join ，造成不良的執行計畫進而影響效能。例如一段使用到子查詢的語法如下 :

select country_name,job_title,sum(salary) from  (select department_id,job_title,sum(salary) salary    from employees a,jobs b    where a.job_id=b.job_id   group by department_id,job_title) a1,  (select department_id,country_name    from departments a,locations b,countries c   where a.location_id=b.location_id) b1 where a1.department_id=b1.department_id group by country_name,job_title;

執行後須讀取 504 個 Block :

使用 with 語句改寫後只需讀取 497 個 Block :

with a1 as  (select department_id,job_title,sum(salary) salary    from employees a,jobs b    where a.job_id=b.job_id   group by department_id,job_title), b1 as  (select department_id,country_name    from departments a,locations b,countries c   where a.location_id=b.location_id) select country_name,job_title,sum(salary)   from a1,b1 where a1.department_id=b1.department_id group by country_name,job_title;

使用 with 語句的 I/O 次數較少，效率優於原本的子查詢語法。

• Advance Group By

Group By 語法的使用必須將 select 當中所有的欄位都寫進去才行，否則會報 ORA-00979 的錯誤 :

修正後可以撈取出每個部門 ID 加上這個部門所屬的 JOB_ID 的薪資水平 (salary) :

那現在有個問題就是，如果我們在撈出 (部門ID + JOB_ID) 的薪資水平後，想要再統計這個部門的所有薪資，那就必須再寫另外一個 SQL 只針對部門 ID 做 group by :

然後再花些功夫將這兩張表格合併。

Advance Group By 語法的用途是只使用一句 SQL 就可以將這兩張表格合併成一個，只需要在第一句的 group by 後面加上 rollup 就可以達到這個功能，例如查詢中有 a,b,c 三個欄位，使用 group by rollup(a,b,c) 之後會同時針對三種情況做 group by 並輸出結果，首先對 a+b+c 欄位做 group by ，其次是對 a+b 欄位做 group by ，最後是只對 a 欄位做 group by 。

利用 rollup 語法套入第一句 SQL 語法，就可以同時得到 (部門ID + JOB_ID) 的薪資水平，以及部門ID 加總的薪資水平 :

由於 rollup(a,b,c) 只會針對 a+b+c 、 a+b ，以及 c 做 group by ，若是要針對 a,b,c 的所有組合做 group by ，可以使用 CUBE ，例如 :

SQL> select department_id,job_id,sum(salary) from emp        where department_id in (10,20)       group by cube (department_id,job_id)       order by department_id,job_id;

這個結果就會針對 department_id + job_id 、 department_id ，以及 job_id 所有三種組合做 group by :

由於 CUBE 所做的組合太多，查詢出來的結果過於雜亂不易讀取，一般會再加上 grouping_id 輔助來使用 :

SQL> select department_id,job_id,sum(salary),       grouping_id(department_id,job_id) as grouping_id from emp         where department_id in (10,20)       group by cube (department_id,job_id)       order by department_id,job_id;

加上了 grouping_id 方便用來分辨 group by 的種類 :

有了 grouping_id ，就可以只列出所需要的 group by 結果集，例如只列出各個部門加總的薪資，以及各個 JOB_ID 所有的薪資水平，從上面的結果集只需挑選出 grouping_id 為 1 與 2 的結果即可 :

SQL>  select department_id,job_id,sum(salary),         grouping_id(department_id,job_id) as grouping_id from emp        group by cube (department_id,job_id)        having grouping_id(department_id,job_id) in (1,2)        order by department_id,job_id

Advance Group By 最後一種用法就是直接使用 grouping sets 來指定 group by 的欄位，例如只要針對 (department_id + job_id) 以及 (department_id + manager_id) 兩種組合做 group by :

SQL> select department_id,job_id,manager_id,sum(salary) from emp         where department_id=10       group by grouping sets ((department_id,job_id),(department_id,manager_id))       order by department_id,job_id,manager_id;

比起使用 CUBE 再過濾 grouping_id ， grouping sets 使用起來更為簡單明瞭。

• Analytic function

當 SQL 語句使用到運算函數例如加總 (sum) 、 平均 (avg) …等，同樣的會碰到 group by 欄位的問題，例如我們只想統計出 department_id=30 的所有員工薪資與部門平均薪資 :

SQL> select employee_id,job_id,salary,avg(salary) from employees where department_id=30 group by department_id;

由於沒有 group by 所有欄位，所以會造成 ORA-00979 的錯誤 :

若是將所有欄位進行 group by ，語法正確但是結果集卻不是我們想要的，最後只好將 SQL 修改為子查詢的語法來達到所希望的結果 :

SQL> select e.employee_id,e.job_id,e.salary,b.avg_salary        from employees e,             (select department_id,avg(salary) avg_salary                from employees group by department_id) b       where e.department_id=b.department_id and e.department_id=30;

這個時候就可以使用分析函數 (Analytic function) 來避免掉較為複雜的子查詢語法， Analytic function 最早從 Oracle 9i 開始有，透過 Analytic function 可以讓未參與統計的字段出現在 select 語句當中，對於每一行紀錄都能夠返回獨立的統計值，不會像group by那樣強制對每個字段做合併的操作，使用的方式為在運算函數之後加上 over partition by 的語法就可以單獨對這項欄位進行獨立的運算。

使用 Analytic function 將上述 SQL 修正之後便可以很簡潔的得到所要的結果集 :

SQL> select employee_id,job_id,salary,         avg(salary) over(partition by department_id)        from employees where department_id=30

使用 Analytic function 不僅讓程式簡化了，也避免了使用子查詢對於效能的負面影響。

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 存取遠端物件則無法使用。