Oracle中利用DETERMINISTIC聲明提高效能（一） ARRAY對效能的影響

雖然Oracle提供的DETERMINISTIC聲明，本意是確保函數的確定性，但是如何合理利用，是可以用來提高效能的。

這一篇描述ARRAY對效能的影響。

關於DETERMINISTIC函數，以前已經寫過一些文章了，不過對於DETERMINISTIC聲明用來提高效能只是簡單提了一句，並沒有展開來說。

由於函式宣告了DETERMINISTIC特性，Oracle對於相同的輸入，可以只運行一次，而這對於代碼比較複雜，調用時間較長的函數而言，確實可以提高效能。

但是在上面的幾篇文章中也提到了，DETERMINISTIC是基於調用的，因此使用DETERMINISTIC不但與輸入參數是否重複有關，也與SQL調用次數有關。

一個前面文章提到過的簡單的例子就是sqlplus的數組方式FETCH資料，不同的array的值，就會影響DETERMINISTIC函數的運行次數，哪怕

不過這裡要澄清以前一個錯誤的觀點，由於設定ARRAY為1後，訪問DETERMINISTIC函數發現每兩條記錄調用一次，當時認為ARRAY方式的最小值是2，但是現在發現，問題和ARRAY無關，導致問題的原因和DETERMINISTIC的實現演算法有關。

由於DETERMINISTIC並不像RESULT_CACHE那樣，在單獨的記憶體地區中儲存每次調用的結果，因此Oracle需要判斷DETERMINISTIC函數兩次輸入是否一樣，這對於輸入參數相同的情況還簡單一些，但是對於包含大量變化的變數，就使得函數的調用次數很難預料。

看一個簡單的例子來說明這個問題：

SQL> CREATE OR REPLACE FUNCTION F_DETER (V_IN NUMBER)

2 RETURN NUMBER DETERMINISTIC AS

3 BEGIN

4 DBMS_LOCK.SLEEP(1);

5 DBMS_OUTPUT.PUT_LINE(V_IN);

6 RETURN V_IN;

7 END;

8 /

函數已建立。

SQL> CREATE TABLE T_DETER (ID NUMBER, C NUMBER);

表已建立。

SQL> INSERT INTO T_DETER

2 SELECT ROWNUM, 1

3 FROM TAB;

已建立15行。

SQL> SET SERVEROUT ON

SQL> SET TIMING ON

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';

會話已更改。

經過時間: 00: 00: 00.09

SQL> SHOW ARRAY

arraysize 15

SQL> SELECT ID, F_DETER(C), SYSDATE FROM T_DETER;

ID F_DETER(C) SYSDATE

---------- ---------- -------------------

1 1 2011-05-26 08:10:54

2 1 2011-05-26 08:10:54

3 1 2011-05-26 08:10:54

4 1 2011-05-26 08:10:54

5 1 2011-05-26 08:10:54

6 1 2011-05-26 08:10:54

7 1 2011-05-26 08:10:54

8 1 2011-05-26 08:10:54

9 1 2011-05-26 08:10:54

10 1 2011-05-26 08:10:54

11 1 2011-05-26 08:10:54

12 1 2011-05-26 08:10:54

13 1 2011-05-26 08:10:54

14 1 2011-05-26 08:10:54

15 1 2011-05-26 08:10:54

已選擇15行。

1

1

經過時間: 00: 00: 02.80

SQL> CREATE OR REPLACE FUNCTION F_SYSDATE RETURN DATE AS

2 BEGIN

3 RETURN SYSDATE;

4 END;

5 /

函數已建立。

經過時間: 00: 00: 00.04

SQL> SELECT ID, F_DETER(C), F_SYSDATE FROM T_DETER;

ID F_DETER(C) F_SYSDATE

---------- ---------- -------------------

1 1 2011-05-26 08:11:13

2 1 2011-05-26 08:11:14

3 1 2011-05-26 08:11:14

4 1 2011-05-26 08:11:14

5 1 2011-05-26 08:11:14

6 1 2011-05-26 08:11:14

7 1 2011-05-26 08:11:14

8 1 2011-05-26 08:11:14

9 1 2011-05-26 08:11:14

10 1 2011-05-26 08:11:14

11 1 2011-05-26 08:11:14

12 1 2011-05-26 08:11:14

13 1 2011-05-26 08:11:14

14 1 2011-05-26 08:11:14

15 1 2011-05-26 08:11:14