谈谈where条件中函数的使用(deterministic)

我们平时在SQL语句的WHERE条件中使用函数是很常见的事情，考虑一下下面两个SQL在执行机制上有什么区别：

1. SELECT * FROM T WHERE col1 = UPPER('hello');

2. SELECT * FROM T WHERE col1 = DBMS_RANDOM.VALUE(1,100);

UPPER('hello')经过处理后会变成'HELLO'，它相当于一个常量，因此第1个SQL类似于：

SELECT * FROM T WHERE col1 = 'HELLO'；

而第2个SQL由于DBMS_RANDOM.VALUE(1,100)是不确定的，因此不能进行处理。这就导致T表有多少行，DBMS_RANDOM.VALUE这个函数就要执行多少次。

而第1个SQL其实只执行了很少次数的UPPER函数，与表T的行数无关。

如果我们想自己写一个函数SQUARE，实现平方的功能，那么在下面的SQL中，SQUARE函数会执行多少次呢？

SELECT * FROM T WHERE col = SQUARE(10);

我们可以通过实验来测试它，实验的大致方法是：

1.创建一个包，包里面定义一个NUMBER类型的变量，初始值设为0。

2.在我们自己定义的函数中增加一个将全局变量+1的赋值操作，这样每调用一次该函数，全局变量就会增加1。

3.最后输出全局变量的值，就是该函数被调用过的次数。当然每次测试前要将全局变量清零。

说到这里我要提一个oracle函数的属性，就是deterministic。它表示一个函数在输入不变的情况下输出是否确定，像oracle的内置函数UPPER,TRUNC等都是deterministic函数，而像DBMS_RANDOM.VALUE就不是deterministic函数，因为同样的输入不一定会导致同样的输出。

如果我们在定义SQUARE函数时没有加deterministic属性，那么经过我的测试，SELECT * FROM T WHERE col = SQUARE(10);

这个SQL会执行ROWNUMBER次SQUARE函数，也就是如果表T有100行，那就执行100次。

如果我们在定义SQUARE函数时加上了deterministic属性，那么经过我的测试，SELECT * FROM T WHERE col = SQUARE(10);

这个SQL只会执行2次SQUARE函数，而不管表T有多少行。

因此给出结论：当我们自己创建函数时，如果能够确定该函数是确定的，那就一定要加上deterministic属性，这样在where条件中使用该函数会提高一大截性能！否则，你写的函数将来被多少人使用就是害了多少人！

补充：经测试，如果对表T上主键col1使用自定义非deterministic函数，则该函数也只会执行一次！

来源：http://blog.sina.com.cn/s/blog_6ff05a2c0100n2qo.html

-----------------------------------------------------------------------------------------

英文说明： 

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.