DETERMINISTIC 关键字详解

电玩女神 2022-06-11 05:59 227阅读 0赞

# 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 marked**REFRESH 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

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