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秒

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)

　　

创建函数与视图

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 类型的函数实际一次都没有执行。

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，针对每条都要调用一次函数，性能上肯定更慢。