[PL/SQL] Oracle 11G SQL/PLSQL New Features

自從Oracle 11g 2007年 release以來, 不斷有一些新的功能被提出討論. 11G的重點在DBA管理工作的自動化, 這使得DBA有時間去做一些更有附加價值的事情而不是聚焦在重複性的資料庫維護動作. 而對開發人員, SQL和PL/SQL則是另外一個重點, 本文介紹一些新的功能和筆者的想法, 順道將網路上其他人對這些新功能的介紹做一些整理.

1. PL/SQL的語法： 新增 "Continue" 關鍵字, 讓它更像C語言. 在迴圈裡面使用"Continue",
讓程式跳過Continue以下的指令而執行下一個迴圈，如此程式碼裡可以少看到很
多"GOTO". 下面是一個例子.

begin
for i in 1..3
loop
dbms_output.put_line(』i=』||to_char(i));
if ( i = 2 )
then
continue;
end if;
dbms_output.put_line(』Only if i is not equal to 2′);
end loop;
end;

結果會長成

i=1 Only if i is not equal to 2
i=2
i=3 Only if i is not equal to 2    

2. Stored procedure 的編譯和狀態控制改善

- 除了"Enabled"和"Invalid"以外，多一種Stored Procedure的狀態就是"Disabled".
 主要是資料庫管理面上的需要，舉例, 當有Schema改變，程式還沒有要跟著改變時，
 可以將程式狀態先行換為'Disabled'，此時'Invalid'還是屬於異常狀態，需要特別
 處理，而'Disabled'則可以待開發人員確認後再處理.
- Native PL/SQL compiling
 所謂 Native就是PL/SQL在編譯後直接產生機器碼並儲存在 System tablespace, 在
 執行時也直接進入 shared memory. 而無需透過C編譯器,即所謂的 Intrepreted模
 式. 其實此功能在10G即已存在只是聽說在RAC下會有問題,所以11G作了一些改善.
- 減少PL/SQL stored procedure 變成invalidatied的機制
 減少因為DDL而導致程式變成 Invalid的機會.

3. 新的轉置SQL"PIVOT"語法
在Oracle要作轉置基本上可行但是要對SQL動很多手腳, 下面就是一個實例,可以看到SQL裡
面用了一些max, decode,sum等指令,有點小麻煩.

select sum("10") "10",sum("20") "20",
sum("30") "30",sum("40") "40"
from(
select max(decode(department_id,10,salary,null)) "10",
max(decode(department_id,20,salary,null)) "20",
max(decode(department_id,30,salary,null)) "30",
max(decode(department_id,40,salary,null)) "40"
from employees
group by department_id
) ;

到了11G你可以這樣做, SQL看起有邏輯而且簡單多了!

select *
from (select department_id,sum(salary) salary
from employees
where department_id > 0
group by department_id)
pivot (sum(salary)
for department_id in (10,20,30,40)
);

4. SQL/PLSQL 的延展性 (Scalibility) : 新的SQL hint /*+result_cache*/   
  在SQL中加入此 Hint, 讓SQL的結果被Cached在Memory中而不是Cached讀取的資料在
buffer cache. 針對執行頻繁而且結果較固定的SQL, 使用此HINT對效能應該很有幫助.

5.  改善optimizer收集statistics的效能
Oracle 11g改善dbms_stats的效能. DBA通常會定期執行此程式收集和更新Statistics,
這個動作多多少少會對database的效能有一點副作用, 這一個改善應可將此副作用降低.

6. SQL execution Plan Management
Oracle 11g 的SPM允許你更進一步控制SQL的 Execution plan,避免系統環境出現異動時,
Execution Plan跑掉.請參閱Link.

參考文件:

http://www.dba-oracle.com/oracle11g/oracle_11g_new_features.htm

http://www.oracle-base.com/articles/11g/PlsqlNewFeaturesAndEnhancements_11gR1.php#continue_statement