oracle deterministic關鍵字.

在看thomas kyte 的<>時,

發現在它的書就提到的一個關鍵字.deterministic

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.

[@more@]

看了下oracle的官方文件,這個關鍵字表明:如果你的函式當輸入一樣時,會返回同樣的結果.

這樣, 資料庫就用前一個計算的值,而不需要再重新計算一次.

這對於使用函式索引等,會直到相當大的好處.