oracle11g函数执行次数的测试

[phoenix_tk]

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期待能稳定执行次数，可是对于结果理解的复杂度却再次增加。

[碧水幽幽泉]

看了半天，没看明白！

[phoenix_tk]

引用 2 楼 qq646748739 的回复:

看了半天，没看明白！

操作操作试试。主要是执行的与预期结果偏差太大。 预期是一个sql语句中嵌套使用一个函数执行的结果。例如select dd,dd,dd,dd from (select test_fun dd from dual);在上层sql使用4次，预期是这个函数执行一次即可，实际上oracle执行了n多次。 另一方面，几乎相同的语句，函数执行的次数也不同。 这些对于某些大数据量结果集的函数操作将会非常危险，函数执行次数倍增，影响数据库性能、执行效率等等。

[jdsnhan]

纯各人观点 从trace上看，oracle对于子查询里面的内容以不同的形式对待，有时候是view，有时候是table select test_fun dd from dual connect by level<=2 oracle把他当做了一个视图，既然是视图，那么优先得到视图的结果后，此时，才会执行外面的查询。此时，函数已经有结果了，所以，执行了两次。 select test_fun dd from dual，oracle把他当做了一个表，既然是表，那就直接访问，相当于访问了4个字段，而不是一个字段，所以，执行了4次。 至于 select dd,dd,dd,dd from (select dbms_random.value dd from dual); 为什么也当做了视图，那就不知道了。

[闪电灬星光]

http://blog.sina.com.cn/s/blog_6bc86e6e0101i3p8.html

刚刚查到这个，按其指导做了个测试，初步看有效果。
--------------------------------------------------------------
drop table WE_APP_T;
drop table WE_VER_T;

-- Create table
create table WE_APP_T
(
id NUMBER(38) not null,
name VARCHAR2(100)
);
alter table WE_APP_T
add primary key (ID);


-- Create table
create table WE_VER_T
(
id NUMBER(38),
appid NUMBER(38),
version_code NUMBER(38),
version_name VARCHAR2(100)
);

insert into WE_APP_T values(1,'APP1');
insert into WE_APP_T values(2,'APP2');
insert into WE_APP_T values(3,'APP3');

insert into WE_VER_T values(1,1,1,'1.0.1');
insert into WE_VER_T values(2,1,2,'1.0.2');
insert into WE_VER_T values(3,1,3,'1.0.3');
insert into WE_VER_T values(4,2,1,'1.0.1');
insert into WE_VER_T values(5,2,2,'1.0.2');
insert into WE_VER_T values(6,2,3,'1.0.3');
insert into WE_VER_T values(7,3,1,'1.0.1');
insert into WE_VER_T values(8,3,2,'1.0.2');
insert into WE_VER_T values(9,3,3,'1.0.3');
commit;

CREATE OR REPLACE FUNCTION "FUN_TESTWH"
(name in varchar2)
RETURN number
IS
BEGIN
dbms_output.put_line('1');
return 1;
END;
/

-- 原sql
select *
from (select v.*, fun_testwh(v.version_name) hasauth
from we_app_t app, we_ver_t v
where app.id = v.appid
and app.name = 'APP1') t
where hasauth = 1;

-- 修改后sql
select *
from (select v.*, (select fun_testwh(v.version_name) from dual) hasauth
from we_app_t app, we_ver_t v
where app.id = v.appid
and app.name = 'APP1') t
where hasauth = 1;

--------------------------------
看dbms的打印1的数量对比