Oracle中创建函数时的Deterministic选项

原文连接：http://blog.sina.com.cn/s/blog_6ff05a2c0100n2qo.html

我们平时在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条件中使用该函数会提高一大截性能！否则，你写的函数将来被多少人使用就是害了多少人！

转载于:https://www.cnblogs.com/yuananyun/archive/2011/11/13/2247241.html