oracle deterministic关键字

http://inthirties.com:90/viewthread.jsp?tid=1395

这个deterministic是不是很眼熟呀，

我们在online redefinition里见到过这个关键字，这个关键在在自定义的函数索引里也出现了。

先看看文档

DETERMINISTIC Clause

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

You must specify this keyword if you intend to call the function in the expression of a function-based index or from the query of a materialized view that is marked REFRESH FAST or ENABLE QUERY REWRITE. When Oracle Database encounters a deterministic function in one of these contexts, it attempts to use previously calculated results when possible rather than reexecuting the function. If you subsequently change the semantics of the function, you must manually rebuild all dependent function-based indexes and materialized views.

Do not specify this clause to define a function that uses package variables or that accesses the database in any way that might affect the return result of the function. The results of doing so will not be captured if Oracle Database chooses not to reexecute the function.

The following semantic rules govern the use 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.

要实现自己的函数，加入函数索引，必须加入这个deterministic关键字，保证你这行的返回是一个确定数。

否则出现

30553, 00000, “The function is not deterministic”
// *Cause: The function on which the index is defined is not deterministic
// *Action: If the function is deterministic, mark it DETERMINISTIC. If it
// is not deterministic (it depends on package state, database state,
// current time, or anything other than the function inputs) then
// do not create the index. The values returned by a deterministic
// function should not change even when the function is rewritten or
// recompiled.

同样如果你的自定义函数也会有不确定的值的时候，也会导致这个问题。
比如
用to_date(datafield, ‘yyyy’) 这个是不确定的值的，你执行的命令的时间不同，这个结果是不一样的。
如果用这个做函数索引，将出现以下错误。

01743, 00000, “only pure functions can be indexed”
// *Cause: The indexed function uses SYSDATE or the user environment.
// *Action: PL/SQL functions must be pure (RNDS, RNPS, WNDS, WNPS). SQL
// expressions must not use SYSDATE, USER, USERENV(), or anything
// else dependent on the session state. NLS-dependent functions
// are OK.

• 提供Oracle管理/故障处理/优化/安装/RAC/备份恢复技术服务,提供专业的Oracle培训和咨询服务。
• 邮件: inthirties@gmail.com
• MSN: inthirties@hotmail.com
• QQ: inthirties@qq.com
• 电话: 13828706466
• 技术博客 http://blog.csdn.net/inthirties
• 个人站点 http://blog.inthirties.com