使用自定义函数创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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/19602/viewspace-1002778/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/19602/viewspace-1002778/