oracle11g函数执行次数的测试
oracle11g
create or replace function test_fun return number is
begin
dbms_output.put_line(1);
return dbms_random.value;
end;
create or replace function test_fun1 return number determinitic is
begin
dbms_output.put_line(1);
return dbms_random.value;
end;
上述两个函数,第二个指定了determinitic,也是欺骗了一下oracle,为了看差异方便些。
create or replace function test_fun2(i in number) return number is
begin
dbms_output.put_line(1);
return i;
end;
create or replace function test_fun3(i in number) return number determinitic is
begin
dbms_output.put_line(1);
return i;
end;
上述两个函数有输入参数,输出就是输入,第二个是determinitic
不用函数测试:
select dd,dd,dd,dd from (select dbms_random.value dd from dual); --得到一个dd值
测试函数语句:
select dd,dd,dd,dd from (select test_fun dd from test2 where ids in (1,2)); --test2表里就两行数据
select dd,dd,dd,dd from (select test_fun dd from dual);
select dd,dd,dd,dd from (select test_fun dd from dual connect by level<=1);
select dd,dd,dd,dd from (select test_fun dd from dual connect by level<=2);
select dd,dd,dd,dd from (select test_fun1 dd from test2 where ids in (1,2)); --test2表里就两行数据
select dd,dd,dd,dd from (select test_fun1 dd from dual);
select dd,dd,dd,dd from (select test_fun1 dd from dual connect by level<=1);
select dd,dd,dd,dd from (select test_fun1 dd from dual connect by level<=2);
每个语句的函数分别执行了几次呢?预期函数只被执行一次,可是看output可以看出来:
1、执行8次,有8个dd值
2、执行4次,4个dd值都不同
3、执行1次,只有一个dd值
4、执行2次,两行4列数据,每行的4个dd值相同
5、执行4次,两行4列数据,每列的2个dd值相同
6、执行4次,4个dd值都不同
7、执行1次,只有一个dd值
8、执行1次,两行4列数据,只有一个dd值
再测试有输入的函数:
select dd,dd,dd,dd from (select test_fun2(rownum) dd from test2 where ids in (1,2)); --test2表里就两行数据
select dd,dd,dd,dd from (select test_fun2(9) dd from test2 where ids in (1,2)); --test2表里就两行数据
select dd,dd,dd,dd from (select test_fun2(ids) dd from test2 where ids in (1,2));
select dd,dd,dd,dd from (select test_fun2(rownum) dd from dual);
select dd,dd,dd,dd from (select test_fun2(9) dd from dual);
select dd,dd,dd,dd from (select test_fun2(rownum) dd from dual connect by level<=1);
select dd,dd,dd,dd from (select test_fun2(9) dd from dual connect by level<=1);
select dd,dd,dd,dd from (select test_fun2(rownum) dd from dual connect by level<=2);
select dd,dd,dd,dd from (select test_fun2(9) dd from dual connect by level<=2);
select dd,dd,dd,dd from (select test_fun3(rownum) dd from test2 where ids in (1,2)); --test2表里就两行数据
select dd,dd,dd,dd from (select test_fun3(9) dd from test2 where ids in (1,2)); --test2表里就两行数据
select dd,dd,dd,dd from (select test_fun3(ids) dd from test2 where ids in (1,2));
select dd,dd,dd,dd from (select test_fun3(rownum) dd from dual);
select dd,dd,dd,dd from (select test_fun3(9) dd from dual);
select dd,dd,dd,dd from (select test_fun3(rownum) dd from dual connect by level<=1);
select dd,dd,dd,dd from (select test_fun3(9) dd from dual connect by level<=1);
select dd,dd,dd,dd from (select test_fun3(rownum) dd from dual connect by level<=2);
select dd,dd,dd,dd from (select test_fun3(9) dd from dual connect by level<=2);
每个语句的函数分别执行了几次呢?预期函数也只被执行一次,可是看output可以看出来:
1、执行2次
2、执行8次
3、执行8次
4、执行1次
5、执行4次
6、执行1次
7、执行1次
8、执行2次
9、执行2次
10、执行2次
11、执行4次
12、执行8次
13、执行1次
14、执行4次
15、执行1次
16、执行1次
17、执行2次
18、执行1次
好了,结果出来了,信息量很大,各种差异。可是为什么会是这样的?哪位可以做个详细的解释?
最初预想是函数只会执行一次,别名使用的是函数执行的结果值,可是多次使用别名嵌套查询导致函数执行次数增多,而且使用connect by、rownum等也会使函数执行次数发生变化(目前只测试了这几种,没有将所有语法都测试)。然后使用了determinitic期待能稳定执行次数,可是对于结果理解的复杂度却再次增加。