有關使用PL/SQL提高效能的學習：

最近遇到一些有關sql優化的問題，於是學習了有關使用PL/SQL提高效能的部分，以下摘錄的是以前沒有特別注意的部分，並且附有試驗的例子。

1、使用rowid進行跌代處理，通過rowid檢索資料是最快的方法，甚至比唯一參考索引還快。
如果在迴圈中需要根據cursor取到的條件進行進一步的操作，則在cursor中同時取出rowid，並且根據rowid定位記錄會是個高效率的方法，比取到主鍵或者唯一性索引的效率都好。
以下的例子是取表的10萬條記錄進行後續處理，id是該表的主鍵。優化後節省將近2秒鐘，效率提升16%。
優化前：
declare
   cursor cur_test is
   select id from user_tab
   where rownum <= 100000;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in cur_test loop
      update user_tab
      set user_name = user_name
      where id = n_row.id;
     
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;

20050420 10:40:14.791
20050420 10:40:26.509

PL/SQL procedure successfully completed.

優化後：
declare
   cursor cur_test is
   select rowid from user_tab
   where rownum <= 100000;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in cur_test loop
      update user_tab
      set user_name = user_name
      where rowid = n_row.rowid;
     
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;

20050420 10:40:38.112
20050420 10:40:48.051

PL/SQL procedure successfully completed.

2、確保比較運算中的資料型別相同。
要確保在where條件中的等號左右的資料型別是一致的，否則oracle雖然會進行自動的轉換，但是效率上會有所缺失。

3、根據條件出現的頻率來排序IF條件。
如果在塊兒中間有多個IF條件判斷，則應該考慮將命中率高的IF條件放在前面，這樣對效率的提升也是有好處的。
下面的例子迴圈1億次，判斷條件的次序調整，節省將近4秒鐘，效率提升23%。現在判斷條件後只是進行簡單的累加，如果是做更復雜的處理，效率的提升會更明顯。
優化前：
declare
   ln_number number := 0;
   ln_if number := 0;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..100000000 loop
      if ln_if = 1 then
         ln_number := ln_number + 1;
      elsif ln_if = 0 then
         ln_number := ln_number + 2;
      end if;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 10:56:44.411
20050420 10:57:01.062

PL/SQL procedure successfully completed.

優化後：
declare
   ln_number number := 0;
   ln_if number := 0;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..100000000 loop
      if ln_if = 0 then
         ln_number := ln_number + 1;
      elsif ln_if = 1 then
         ln_number := ln_number + 2;
      end if;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 10:57:01.498
20050420 10:57:14.385

PL/SQL procedure successfully completed.

4、使用PLS_INTEGER PL/SQL資料型別進行整數運算。
這種資料型別可以用於代替各種數值資料系列型別的宣告中，只要變數的值是一個整數，且在-2147483647到+2147483647。該資料型別可以使用更少的內部命令來處理，因此用這種資料型別可以提高效能。
這種資料型別可以替代整數的NUMBER型的變數定義，而實際的處理效率是比NUMBER型更快捷的。
以下的例子是個簡單的迭加，迴圈1億次，使用該資料型別節省大概0.5秒的時間，效率提升16%，不知道對於更復雜的資料處理，是不是效率上會有更大的提升。
優化前：
declare
   ln_number number;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..100000000 loop
      ln_number := ln_number + 1;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 10:47:17.676
20050420 10:47:21.301

PL/SQL procedure successfully completed.

優化後：
declare
   ln_number pls_integer;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..100000000 loop
      ln_number := ln_number + 1;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 10:47:21.989
20050420 10:47:25.149

PL/SQL procedure successfully completed.

5、減少對SYSDATE的呼叫
單獨取SYSDATE的時候不再需要從dual表中獲得，可以直接付給變數：
ld_date := sysdate;
但是應該減少呼叫SYSDATE的次數，如果可以，應該在塊兒的最開始付給某個變數，而不是需要的時候再取得，除非你需要精確的時間戳。
下面的例子迴圈10萬次，分別在迴圈中取得時間和迴圈外取得時間，節省時間0.152秒，效率提升99%。但是要注意這兩個不是等價的優化，如果你需要每次迴圈取得不同的時間戳，就應該將按照前面的指令碼編寫。
優化前：
declare
   ld_date date;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..100000 loop
      ld_date := sysdate;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 11:32:41.432
20050420 11:32:41.586

PL/SQL procedure successfully completed.

優化後：
declare
   ld_date date;
   ld_date1 date;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   ld_date1 := sysdate;
   for n_row in 1..100000 loop
      ld_date := ld_date;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 11:32:42.218
20050420 11:32:42.220

PL/SQL procedure successfully completed.

6、減少MOD函式的使用，可以用其他函式代替，但是效率不同。
不是所有的oracle函式的效率支出是等同的，MOD就是個消耗會比較多的函式，雖然他的功能是不錯的，但是如果可以用其他的函式取代，應減少該函式的使用次數。
下面的例子是進行了100萬次迴圈，分別使用mod作為判斷條件或者不使用，優化後節省0.7秒多，效率提升90%。
優化前：
declare
   ln_number pls_integer := 0;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..1000000 loop
      ln_number := ln_number + 1;
      if mod(ln_number,1000) = 0 then
         ln_number := ln_number + 2;
      end if;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 11:20:34.435
20050420 11:20:35.225

PL/SQL procedure successfully completed.

優化後：
declare
   ln_number pls_integer := 0;
   ln_num1 pls_integer := 0;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..1000000 loop
      ln_number := ln_number + 1;
      ln_num1 := ln_num1 + 1;
      if ln_num1 = 1000 then
         ln_number := ln_number + 2;
         ln_num1 := 0;
      end if;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 11:20:35.461
20050420 11:20:35.542

PL/SQL procedure successfully completed.

7、將參考表載入到PL/SQL表中可以加快查詢速度。這是因為利用了PL/SQL中陣列索引的優勢。
下面這個例子是主表100萬條記錄，根據主鍵取參考表的對應欄位，參考表只有6條記錄。優化後的節省將近45秒，效率提升97%。
優化前：
declare
   cursor cur_test is
   select changecentsreason_id
   from usercentsdetails_tab
   where rownum <= 1000000;
  
   ls_name changecentsreason_tab.changecentsreason%type;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in cur_test loop
      select changecentsreason
      into ls_name
      from changecentsreason_tab
      where id = n_row.changecentsreason_id;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;

20050420 10:21:09.461
20050420 10:21:55.407

PL/SQL procedure successfully completed.

優化後：
declare
   type ref_array is table of varchar2(20) index by binary_integer;
   ref_tab ref_array;
  
   cursor cur_test is
   select changecentsreason_id
   from usercentsdetails_tab
   where rownum <= 1000000;
  
   cursor cur_ref is
   select id,changecentsreason
   from changecentsreason_tab;
  
   ls_name changecentsreason_tab.changecentsreason%type;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row1 in cur_ref loop
      ref_tab(n_row1.id) := n_row1.changecentsreason;
   end loop;
  
   for n_row in cur_test loop
      ls_name := ref_tab(n_row.changecentsreason_id);
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;

20050420 10:19:20.333
20050420 10:19:21.604

PL/SQL procedure successfully completed.

8、時間範圍的取得
以前為了查詢一整天的記錄，我通常的寫法是：
between to_date(sysdate) - 1 and to_date(to_char(to_date(sysdate),'yyyymmdd')||' 23:59:59','yyyymmdd hh24:mi:ss') - 1
多次的格式轉換對效能也是有影響的，寫成：
between to_date(sysdate) - 1 and to_date(sysdate) - 0.000011574
0.000011574表示1秒。這樣的效率應該會有所提升。
下面的例子迴圈了100萬次，優化後節省36秒多，效率提升89%。不過這是比較特別的情況，因為其實不需要在迴圈中這樣做的，迴圈只是放大了效率提升的效果。
優化前：
declare
   ld_date date ;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..1000000 loop
      ld_date := to_date(to_char(to_date(sysdate),'yyyymmdd')||' 23:59:59','yyyymmdd hh24:mi:ss') - 1;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 11:24:19.530
20050420 11:24:50.525

PL/SQL procedure successfully completed.

優化後：
declare
   ld_date date ;
begin
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
   for n_row in 1..1000000 loop
      ld_date := to_date(sysdate) - 0.000011574;
   end loop;
   dbms_output.put_line(to_char(SYSTIMESTAMP,'yyyymmdd hh24:mi:ss.ff3'));
end;
/
20050420 11:24:50.750
20050420 11:25:05.229

PL/SQL procedure successfully completed.

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