使用自定義函式創Function-Based Indexes時需要使用DETERMINISTIC Functions！

那麼究竟什麼是DETERMINISTIC Functions，看看doc的解釋！

DETERMINISTIC Functions

Any user-written function used in a function-based index must have been declared with the DETERMINISTIC keyword to indicate that the function will always return the same output return value for any given set of input argument values, now and in the future.

[@more@]

SQL> desc tt
名稱 是否為空? 型別
----------------------------------------- -------- ----------------------------

ID NUMBER(38)
NAME CHAR(2)

SQL> select * from tt;

ID NA
---------- --
1 m
2 m
3 m
4 m
5 m
6 n
1000 b
b

已選擇8行。

SQL> create index idx_tt on tt(fun_test(name));
create index idx_tt on tt(fun_test(name))
*
第 1 行出現錯誤:
ORA-30553: 函式不能確定

--沒有成功，看看英文提示是什麼：

SQL> alter session set nls_language=american;

Session altered.

SQL> create index idx_tt on tt(fun_test(name));
create index idx_tt on tt(fun_test(name))
*
ERROR at line 1:
ORA-30553: The function is not deterministic

SQL>

--函式text如下：

SQL> select TEXT from user_source where name='FUN_TEST';

TEXT
--------------------------------------------------------------------------------

function fun_test(p_name varchar2)
return varchar2
is
begin
return upper(p_name) ;
end;

已選擇6行。

--修改函式，使其成為DETERMINISTIC FUNCTION!

SQL> EDIT
已寫入 file afiedt.buf

1 create or replace function fun_test(p_name varchar2)
2 return varchar2 DETERMINISTIC
3 is
4 begin
5 return upper(p_name) ;
6* end;
SQL> /

函式已建立。

--之後建立基於自定義函式的索引時成功！

SQL> create index idx_tt on tt(fun_test(name));

索引已建立。

SQL>

那麼究竟什麼是DETERMINISTIC Functions，看看doc的解釋！

DETERMINISTIC Functions

Any user-written function used in a function-based index must have been declared with the DETERMINISTIC keyword to indicate that the function will always return the same output return value for any given set of input argument values, now and in the future.

--=======================================

How the CBO Evaluates DETERMINISTIC Functions

In some cases, the optimizer can use a previously calculated value rather than executing a user-written function. This is only safe for functions that behave in a restricted manner. The function must return the same output return value for any given set of input argument values.

The function's result must not differ because of differences in the content of package variables or the database, or session parameters such as the globalization support parameters. Furthermore, if the function is redefined in the future, then its output return value must be the same as that calculated with the prior definition for any given set of input argument values. Finally, there must be no meaningful side effects to using a precalculated value instead of executing the function again.

The creator of a function can promise to the Oracle server that the function behaves according to these restrictions by using the keyword DETERMINISTIC when declaring the function with a CREATE FUNCTION statement or in a CREATE PACKAGE or CREATE TYPE statement. The server does not attempt to verify this declaration--even a function that obviously manipulates the database or package variables can be declared DETERMINISTIC. It is the programmer's responsibility to use this keyword only when appropriate.

Calls to a DETERMINISTIC function might be replaced by the use of an already calculated value when the function is called multiple times within the same query, or if there is a function-based index or a materialized view defined that includes a relevant call to the function.