Oracle11新特性——PLSQL函式快取結果（一）

打算寫一系列的文章介紹11g的新特性和變化。

這篇介紹11g新增功能PLSQL函式快取結果的功能。

Oracle11g新增的SQL快取結果集的功能前面已經介紹過了。同時Oracle對PL/SQL的函式也進行了相應的增加。允許函式快取返回結果。

先看一個簡單的例子：

SQL> CREATE TABLE T AS SELECT * FROM DBA_SOURCE;

表已建立。

SQL> CREATE OR REPLACE FUNCTION F_NO_RESULT_CACHE RETURN NUMBER AS
2 V_RETURN NUMBER;
3 BEGIN
4 SELECT COUNT(*) INTO V_RETURN FROM T;
5 RETURN V_RETURN;
6 END;
7 /

函式已建立。

SQL> SET TIMING ON
SQL> SELECT F_NO_RESULT_CACHE FROM DUAL;

F_NO_RESULT_CACHE
-----------------
593334

已用時間: 00: 00: 12.26
SQL> SELECT F_NO_RESULT_CACHE FROM DUAL;

F_NO_RESULT_CACHE
-----------------
593334

已用時間: 00: 00: 07.53
SQL> SELECT F_NO_RESULT_CACHE FROM DUAL;

F_NO_RESULT_CACHE
-----------------
593334

已用時間: 00: 00: 08.17

對於普通的函式，需要每次都重新執行，而如果採用了RESULT_CACHE功能：

SQL> CREATE OR REPLACE FUNCTION F_RESULT_CACHE RETURN NUMBER RESULT_CACHE AS
2 V_RETURN NUMBER;
3 BEGIN
4 SELECT COUNT(*) INTO V_RETURN FROM T;
5 RETURN V_RETURN;
6 END;
7 /

函式已建立。

已用時間: 00: 00: 00.03
SQL> SELECT F_RESULT_CACHE FROM DUAL;

F_RESULT_CACHE
--------------
593334

已用時間: 00: 00: 07.87
SQL> SELECT F_RESULT_CACHE FROM DUAL;

F_RESULT_CACHE
--------------
593334

已用時間: 00: 00: 00.06
SQL> DISC從 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 斷開
SQL> CONN YANGTK/yangtk@ORA11G已連線。
SQL> SELECT F_RESULT_CACHE FROM DUAL;

F_RESULT_CACHE
--------------
593334

已用時間: 00: 00: 00.04

對於採用了RESULT_CACHE的函式，Oracle自動將函式的返回結果快取，下次執行的時候，不會實際執行函式，而是直接返回結果。

而且由於快取的結果儲存在SGA中，因此所有的會話可以共享這個結果。在上面的例子中，斷開連線並重建登陸後，新的會話也是可以利用儲存在SGA中的函式快取的。

注意，函式的RESULT_CACHE功能自動和函式的輸入引數關聯，即使輸入引數不起任何作用，不同的輸入引數也會導致RESULT_CACHE不生效。

SQL> CREATE OR REPLACE FUNCTION F_RESULT_CACHE(P_IN NUMBER) RETURN NUMBER RESULT_CACHE AS
2 V_RETURN NUMBER;
3 BEGIN
4 SELECT COUNT(*) INTO V_RETURN FROM T;
5 RETURN V_RETURN;
6 END;
7 /

函式已建立。

已用時間: 00: 00: 00.07
SQL> SELECT F_RESULT_CACHE(1) FROM DUAL;

F_RESULT_CACHE(1)
-----------------
593334

已用時間: 00: 00: 06.90
SQL> SELECT F_RESULT_CACHE(1) FROM DUAL;

F_RESULT_CACHE(1)
-----------------
593334

已用時間: 00: 00: 00.04
SQL> SELECT F_RESULT_CACHE(2) FROM DUAL;

F_RESULT_CACHE(2)
-----------------
593334

已用時間: 00: 00: 07.15

下面看一下RELIES_ON語句對RESULT_CACHE的影響。建立了上面的函式只完成了一部分功能，如果函式中訪問了資料庫中的物件，那麼需要指定RELIES_ON語句來說明結果依賴的物件。如果沒有制定，會導致函式訪問的資料變化後，RESULT CACHE仍然生效，這時候會返回錯誤的結果：

SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
593334

已用時間: 00: 00: 08.11
SQL> SELECT F_RESULT_CACHE(1) FROM DUAL;

F_RESULT_CACHE(1)
-----------------
593334

已用時間: 00: 00: 00.34
SQL> DELETE T WHERE ROWNUM = 1;

已刪除 1 行。

已用時間: 00: 00: 00.04
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
593333

已用時間: 00: 00: 08.23
SQL> SELECT F_RESULT_CACHE(1) FROM DUAL;

F_RESULT_CACHE(1)
-----------------
593334

已用時間: 00: 00: 00.06
SQL> COMMIT;

提交完成。

已用時間: 00: 00: 00.03
SQL> SELECT F_RESULT_CACHE(1) FROM DUAL;

F_RESULT_CACHE(1)
-----------------
593334

已用時間: 00: 00: 00.06

由於沒有指定依賴關係，Oracle並不會自動維護RESULT CACHE的正確性，這種依賴關係需要在建立函式的時候透過RELIES_ON來建立：

SQL> EXEC DBMS_RESULT_CACHE.FLUSH

PL/SQL 過程已成功完成。

已用時間: 00: 00: 00.03
SQL> CREATE OR REPLACE FUNCTION F_RESULT_CACHE(P_IN NUMBER)
2 RETURN NUMBER RESULT_CACHE RELIES_ON (T) AS
3 V_RETURN NUMBER;
4 BEGIN
5 SELECT COUNT(*) INTO V_RETURN FROM T;
6 RETURN V_RETURN;
7 END;
8 /

函式已建立。

已用時間: 00: 00: 00.04
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
593333

已用時間: 00: 00: 09.60
SQL> SELECT F_RESULT_CACHE(1) FROM DUAL;

F_RESULT_CACHE(1)
-----------------
593333

已用時間: 00: 00: 04.82
SQL> SELECT F_RESULT_CACHE(1) FROM DUAL;

F_RESULT_CACHE(1)
-----------------
593333

已用時間: 00: 00: 00.06
SQL> DELETE T WHERE ROWNUM = 1;

已刪除 1 行。

已用時間: 00: 00: 00.03
SQL> SELECT COUNT(*) FROM T;

COUNT(*)
----------
593332

已用時間: 00: 00: 00.26
SQL> SELECT F_RESULT_CACHE(1) FROM DUAL;

F_RESULT_CACHE(1)
-----------------
593332

已用時間: 00: 00: 00.29

新增了RELIES_ON語句後，Oracle會根據依賴物件自動INVALIDATE結果集，從而保證RESULT CACHE的正確性。