oracle函数里select有用吗,[ORECLE]谈谈where条件中函数的使用(deterministic)

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

(2010-12-07 09:57:58)

标签：

oracle

函数

function

deterministic

where

it

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

分享：

喜欢

0

赠金笔

加载中，请稍候......

评论加载中，请稍候...

发评论

登录名： 密码： 找回密码 注册记住登录状态

昵   称：

评论并转载此博文

发评论

以上网友发言只代表其个人观点，不代表新浪网的观点或立场。