Oracle的性能参考手册上描述了函数的DETERMINISTIC特性，定义了这种类型的函数给定了确定的输入值，它的输出值也是确定了。

如果在函数索引或物化视图中使用自定义的函数，则这个函数必须声明为DETERMINISTIC。

这篇文章简单描述一下DETERMINISTIC函数的特点。并说明了不正确的声明函数的DETERMINISTIC特性会导致查询到错误的结果。

SQL> CREATE OR REPLACE PACKAGE P_TEST AS
  2  G_NUM NUMBER DEFAULT 0;
  3  END;
  4  /

程序包已创建。

SQL> CREATE OR REPLACE FUNCTION F_DETER(P_DAY IN NUMBER) RETURN DATE DETERMINISTIC
  2  AS
  3  BEGIN
  4  P_TEST.G_NUM := P_TEST.G_NUM + 1;
  5  RETURN TO_DATE('2005-04-17', 'YYYY-MM-DD') + P_DAY;
  6  END;
  7  /

函数已创建。

SQL> EXEC P_TEST.G_NUM := 0

PL/SQL 过程已成功完成。

SQL> EXEC :V_NUMBER := P_TEST.G_NUM

PL/SQL 过程已成功完成。

SQL> PRINT :V_NUMBER

  V_NUMBER
----------
         0

SQL> CREATE TABLE TTT (ID NUMBER);

表已创建。

SQL> INSERT INTO TTT VALUES (1);

已创建 1 行。

SQL> INSERT INTO TTT VALUES (2);

已创建 1 行。

SQL> INSERT INTO TTT VALUES (1);

已创建 1 行。

SQL> INSERT INTO TTT VALUES (3);

已创建 1 行。

SQL> INSERT INTO TTT VALUES (1);

已创建 1 行。

SQL> COMMIT;

提交完成。

SQL> EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'TTT')

PL/SQL 过程已成功完成。

SQL> SELECT F_DETER(ID), F_DETER(ID) + 1 FROM TTT
  2  WHERE F_DETER(ID) > TO_DATE('2004-05-17', 'YYYY-MM-DD');

F_DETER(ID)         F_DETER(ID)+1
------------------- -------------------
2005-04-18 00:00:00 2005-04-19 00:00:00
2005-04-19 00:00:00 2005-04-20 00:00:00
2005-04-18 00:00:00 2005-04-19 00:00:00
2005-04-20 00:00:00 2005-04-21 00:00:00
2005-04-18 00:00:00 2005-04-19 00:00:00

SQL> EXEC :V_NUMBER := P_TEST.G_NUM

PL/SQL 过程已成功完成。

SQL> PRINT :V_NUMBER

  V_NUMBER
----------
        15

在查询中对同一个值的多次调用DETERMINISTIC函数并不会导致优化器重用以前的结果，这是和文档上描述有出入的。不过通过函数访问会利用以前的查询结果。

SQL> CREATE INDEX IND_TTT_F_DETER_ID ON TTT(F_DETER(ID));

索引已创建。

SQL> EXEC P_TEST.G_NUM := 0

PL/SQL 过程已成功完成。

SQL> EXEC :V_NUMBER := P_TEST.G_NUM

PL/SQL 过程已成功完成。

SQL> PRINT :V_NUMBER

  V_NUMBER
----------
         0

SQL> SELECT F_DETER(ID), F_DETER(ID) + 1 FROM TTT
  2  WHERE F_DETER(ID) > TO_DATE('2004-05-17', 'YYYY-MM-DD');

F_DETER(ID)         F_DETER(ID)+1
------------------- -------------------
2005-04-18 00:00:00 2005-04-19 00:00:00
2005-04-18 00:00:00 2005-04-19 00:00:00
2005-04-18 00:00:00 2005-04-19 00:00:00
2005-04-19 00:00:00 2005-04-20 00:00:00
2005-04-20 00:00:00 2005-04-21 00:00:00

SQL> EXEC :V_NUMBER := P_TEST.G_NUM

PL/SQL 过程已成功完成。

SQL> PRINT :V_NUMBER

  V_NUMBER
----------
         0

不过，DETEMINISTIC特性必须由函数的定义者来进行保证，Oracle并不对这个特性进行检验，如果强制将非DETEMINISTIC函数定义为DETEMINISTIC类型，则查询可能会得到错误的结果。

SQL> CREATE OR REPLACE FUNCTION F_NON_DETER(P_DAY IN NUMBER) RETURN DATE DETERMINISTIC
  2  AS
  3  BEGIN
  4  P_TEST.G_NUM := P_TEST.G_NUM + 1;
  5  RETURN SYSDATE + P_DAY;
  6  END;
  7  /

函数已创建。

SQL> SELECT SYSDATE, F_NON_DETER(ID), F_NON_DETER(ID) + 1 FROM TTT
  2  WHERE F_NON_DETER(ID) > SYSDATE;

SYSDATE             F_NON_DETER(ID)     F_NON_DETER(ID)+1
------------------- ------------------- -------------------
2005-04-18 01:05:57 2005-04-19 01:05:57 2005-04-20 01:05:57
2005-04-18 01:05:57 2005-04-20 01:05:57 2005-04-21 01:05:57
2005-04-18 01:05:57 2005-04-19 01:05:57 2005-04-20 01:05:57
2005-04-18 01:05:57 2005-04-21 01:05:57 2005-04-22 01:05:57
2005-04-18 01:05:57 2005-04-19 01:05:57 2005-04-20 01:05:57

SQL> CREATE INDEX IND_TTT_F_NON_DETER_ID ON TTT(F_NON_DETER(ID));

索引已创建。

SQL> SELECT SYSDATE, F_NON_DETER(ID), F_NON_DETER(ID) + 1 FROM TTT
  2  WHERE F_NON_DETER(ID) > SYSDATE;

SYSDATE             F_NON_DETER(ID)     F_NON_DETER(ID)+1
------------------- ------------------- -------------------
2005-04-18 01:06:31 2005-04-19 01:06:26 2005-04-20 01:06:26
2005-04-18 01:06:31 2005-04-19 01:06:26 2005-04-20 01:06:26
2005-04-18 01:06:31 2005-04-19 01:06:26 2005-04-20 01:06:26
2005-04-18 01:06:31 2005-04-20 01:06:26 2005-04-21 01:06:26
2005-04-18 01:06:31 2005-04-21 01:06:26 2005-04-22 01:06:26