oracle 函数中的DETERMINISTIC

DETERMINISTIC在学习的时候说明是指使用之后对于相同的参数函数会返回相同的值。

一开始看有一些疑惑，对于我们常用的函数来说，尤其是大部分的自定义函数来说，相同的函数一定会有相同的返回值，那么这个标识的作用是什么呢？

首先使用我们首先用系统自带的随机数生成函数来测试。

1.对于输入相同参数返回不同值的函数来说

test1:

create or replace function test_deterministic (i int) 
return number deterministic 
as
retn number;
begin
  retn:= SYS.dbms_random.value(1,10);
  return retn;
end;

建立函数之后，

连续执行多次下述语句之后，发现返回值并不相同，因此并不是我们认为的那种每次查询都会相同。
select test_deterministic (10)  from  dual ;

如果需要上述查询每次返回的值是相同的，可以使用result_cache 属性。

create or replace function test_deterministic (i int) 
return number result_cache
as
retn number;
begin
  retn:= SYS.dbms_random.value(1,10);
  return retn;
end;

这样每次的返回值就是一样的了。具体说明请查看result_cache属性。

经过查询资料，网上都说更多的是针对同一次查询的时候会有优化的作用。

因此执行下述语句，

select level,test_deterministic (10)  from  dual connect by level<10;

结果如下：

因此可以看到，在同一次查询中，对于不确定的输出起到了作用。

 

在用不加deterministic 的函数验证。

create or replace function test_nodeterministic (i int) 
return number   
as
retn number;
begin
  retn:= SYS.dbms_random.value(1,10);
  return retn;
end;

运行

select level,test_nodeterministic (10)  from  dual connect by level<10;

结果：

 

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

对于上述查询来说，

select level,test_nodeterministic (10)  from  dual connect by level<10;需要调用9次函数查询，如果函数越多，那么调用次数就会越多。但是如果对于某个需要确定的返回值函数来说多次调用肯定会降低效率。

 

我们新建函数来说明：

create or replace function test_nodeterministic2 (i int) 
return number   
as
retn number;
begin
  retn:= i ** 2;
  return retn;
end;
 

查询：

SELECT level , test_nodeterministic2(5) FROM dual connect by level<10;

会执行9次函数调用。

 

create or replace function test_deterministic2 (i int) 
return number   deterministic
as
retn number;
begin
  retn:= i ** 2;
  return retn;
end;

SELECT level , test_deterministic2(5) FROM dual connect by level<10;

会执行2次函数调用。即使将level 后的值放大也是如此，可以看到这样的话查询的效率会有很大的提升。

  
因此当我们自己创建函数时，如果能够确定该函数是确定的，那就一定要加上deterministic属性，这样在同义词查询使用该该函数时性能会有很大的提升！