DETERMINISTIC 关键字详解

DETERMINISTIC Clause

1、DETERMINISTIC 官方说明

Specify DETERMINISTIC to indicate that the function returns the same result value wheneverit is called with the same values for its arguments.

You must specify this keyword if youintend to call the function in the expression of a function-based index or fromthe query of a materialized view that is markedREFRESH FAST or ENABLE QUERY REWRITE. When Oracle Database encounters adeterministic function in one of these contexts, it attempts to use previouslycalculated results when possible rather than re-executing the function.If you subsequently change the semantics of the function, you mustmanually rebuild all dependent function-based indexes and materialized views.

Do not specify this clause to define afunction that uses package variables or that accesses the database in any waythat might affect the return result of the function. The results of doing sowill not be captured if Oracle Database chooses not to re-execute the function.

The following semantic rules govern theuse of the DETERMINISTIC  clause:

• You can declare a top-level subprogram DETERMINISTIC.
• You can declare a package-level subprogram DETERMINISTIC in the package specification but not in the package body.
• You cannot declare DETERMINISTIC a private subprogram (declared inside another subprogram or inside a package body).
• A DETERMINISTIC subprogram can call another subprogram whether the called program is declared DETERMINISTIC or not.

The hint DETERMINISTIC helps the optimizer avoidredundant function calls. If a stored function was called previously with thesame arguments, the optimizer can elect to use the previous result. Thefunction result should not depend on the state of session variables or schema objects.Otherwise, results might vary across calls. Only DETERMINISTIC functions can becalled from a function-based index or a materialized view that hasquery-rewrite enabled.

DETERMINISTIC:它表示一个函数在输入不变的情况下输出是否确定。如果你的函数当输入一样时,会返回同样的结果.这样, 数据库就用前一个计算的值,而不需要再重新计算一次.这对于使用函数索引等,会直到相当大的好处.

像oracle的内置函数UPPER,TRUNC等都是deterministic函数，而像DBMS_RANDOM.VALUE就不是deterministic函数，因为同样的输入不一定会导致同样的输出。

 

2、测试示例

2.1 创建测试表

--创建测试表

createTABLE tb_test

as

selectemp.empno,emp.ename,trim(to_char(ROWNUM,'x')) HEX from EMPCONNECTBYROWNUM<30;

 

2.2 创建函数

   --创建16进制字符串转换乘10进制数函数

CREATE  OR   REPLACE   FUNCTION HEX2TEN(P_STR       INVARCHAR2,

                                     P_FROM_BASE IN NUMBER DEFAULT 16)

   RETURN  NUMBER  IS

   L_NUM  NUMBER DEFAULT 0;

   L_HEX  VARCHAR2(16)  DEFAULT '0123456789ABCDEF';

 BEGIN

   FOR I  IN  1 .. LENGTH(P_STR)   LOOP

     L_NUM := L_NUM * P_FROM_BASE +

              INSTR(L_HEX,UPPER(SUBSTR(P_STR, I,1))) -1;

   END  LOOP;

 

   RETURN   L_NUM;

 END HEX2TEN;

2.3 创建函数索引

SQL> create index test_ind_fun on tb_test(hex2ten(HEX));

 

create index test_ind_fun on tb_test(hex2ten(HEX))

 

ORA-30553:函数不能确定

2.4 DETERMINISTIC函数

  --创建16进制字符串转换乘10进制数函数(DETERMINISTIC)

  CREATE  OR   REPLACE  FUNCTION   HEX2TEN(P_STR       IN   VARCHAR2,

                                    P_FROM_BASE  IN  NUMBER  DEFAULT  16)

  RETURN  NUMBER    DETERMINISTIC    IS

  L_NUM NUMBER   DEFAULT0;

  L_HEX VARCHAR2(16)   DEFAULT   '0123456789ABCDEF';

BEGIN

  FOR I  IN   1 .. LENGTH(P_STR)   LOOP

    L_NUM := L_NUM * P_FROM_BASE +INSTR(L_HEX, UPPER(SUBSTR(P_STR, I,1))) -1;

  END   LOOP;

 

  RETURN L_NUM;

END HEX2TEN;

2.5 创建DETERMINISTIC函数索引

SQL> create index test_ind_fun on tb_test(hex2ten(HEX));

Indexcreated