Oracle 与 PostgreSQL 函数行为的差异引发性能差异

对于Oracle，对于数据修改的操作通过存储过程处理，而对于函数一般不进行数据修改操作。同时，函数可以通过 Select 进行调用，而存储过程则不行。

一、对于volatile 函数的行为

1、Oracle 行为

创建函数：

create or replace function fun01 return number as
begin
  insert into t1 values(1);
  return 1;
end;


create or replace function fun02 return number as
  v_cnt number;
begin
  select count(*) into v_cnt from t1;
  return v_cnt;
end;

（1）、函数有修改操作，不允许select 调用

有修改操作，不允许 select 调用：

SQL> select fun01() from dual;
select fun01() from dual
       *
ERROR at line 1:
ORA-14551: cannot perform a DML operation inside a query
ORA-06512: at "SYS.FUN01", line 3

 

没有修改操作，可以select 调用：

SQL> select fun02() from dual;

   FUN02()
----------
         2

 

有修改操作的函数，只能通过call 调用

SQL> var a number;
SQL> call fun01() into :a;

Call completed.

（2）、视图包含函数的情景

create or replace view v_t2 as select id, fun01() as t1_cnt from t2;

test=# select count(*) from v_t2;
 count
-------
     2
(1 row)

经验证，虽然 fun01 含有 insert 操作，但实际函数（fun01）是没有调用的，也就是函数内的 insert 没有执行。这个个人理解应该是个不严谨的地方。

2、PostgreSQL 行为

创建函数：

create or replace function fun01() returns number as
$$
begin
  insert into t1 values(1);
  return 1;
end;
$$ 
language plpgsql;

（1）、函数有修改操作，可以通过Select 调用

test=# select count(*) from t1;
 count
-------
     3
(1 row)

test=# select fun01();
 fun01
-------
     1
(1 row)

test=# select count(*) from t1;
 count
-------
     4
(1 row)

（3）、视图包含函数的情景

create or replace view v_t2 as select id, fun01() as t1_cnt from t2;

test=# select count(*) from v_t2;
 count
-------
     2
(1 row)

经验证，查询视图时，针对每一行，都会调用fun01 函数一次，也就是 insert 操作实际发生的。

二、对于Stable函数的行为

以上的例子中，由于函数含有insert 操作，因此，函数只能是 volatile。 如果函数是 stable or immutable，那又是何种现象了？

构建数据： t1 表的数据量比较大，count(*) 一次大概要2秒

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

test=# create table t1(id integer,name text); CREATE TABLE test=# create table t2(id integer); CREATE TABLE   insert into t1 select generate_series(1,50000000),'a'; insert into t2 select generate_series(1,10);   test=# select count(*) from t1;   count ----------  50000000 (1 row)   Time: 2059.901 ms (00:02.060)

　　

创建函数与视图

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

create or replace function test_volatile(id integer)     returns bigint     volatile     language sql as $$ select count(*) from t1 $$ ; /   create or replace function test_stable(id integer)     returns bigint     stable     language sql as $$ select count(*) from t1 $$ ; /   create or replace function test_immutable(id integer)     returns bigint     immutable     language sql as $$ select count(*) from t1 $$ ; /   create view v_t2_volatile as select id,test_volatile(1) from t2; create view v_t2_stable as select id,test_stable(1) from t2; create view v_t2_immutable as select id,test_immutable(1) from t2;

　　

验证视图的访问效率：immutable or stable 类型的函数实际一次都没有执行。

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

test=# select count(*) from v_t2_immutable;  count -------     10 (1 row)   Time: 1.027 ms   test=# select count(*) from v_t2_stable;  count -------     10 (1 row)   Time: 0.950 ms test=# select count(*) from v_t2_volatile;  count -------     10 (1 row)   Time: 23231.599 ms (00:23.232)

　　

三、性能“问题”

对于 select count(*) from v_t2 (视图），由于 oracle 并不执行函数 ，因此，性能肯定更快。而对于 postgresql，针对每条都要调用一次函数，性能上肯定更慢。