KingbaseES 与Oracle 函数稳定性对于性能影响差异比较
一、函数的属性
KingbaseES 函数在定义时有三种稳定性级别：volatile、stable 和 immutable。默认情况下，创建函数的稳定性为volatile。以下是这三种函数的区别：
- Volatile 函数可以做任何事情，包括修改数据库。在调用中，输入同样的参数可能会返回不同的结果，比如：currtid 。在一个Query中，对于每一行都会重新计算该函数。
- Stable 函数不能修改数据库，单个Query中所有行给定同样的参数确保返回相同的结果。这种稳定级别允许优化器将多次函数调用转换为一次。在索引扫描的条件中使用这种函数是可行的，因为索引扫描只计算一次比较值(comparison value)，而不是每行都计算一次。
- Immutable 函数不能修改数据库，在任何情况下，只要输入参数相同，返回结果就相同。这种级别的函数，优化器可以提前进行计算，在查询过程中作为常量参数。比如：SELECT...WHERE x=2+2 可以简化为SELECT...WHERE x=4。
- Deterministic 这是KingbaseES 为了与oracle 兼容而增加的一个属性，等价于 immutable 。
稳定性级别使得优化器可以判断不同函数的行为。为了得到最佳的优化结果，在创建函数时我们应该指定严格的稳定性级别。
二、不同属性对于性能的影响
以下举例说明函数的属性影响相同参数情况多次执行的效率。
1、构建函数和数据
构建数据
create table test(id integer); 并插入500条完全相同的数据。 insert into test select 1 from generate_series(1,500); create table t1(id integer , name varchar(64)); insert into t1 select generate_series(1,1000000),md5(generate_series(1,1000000));
构建函数
create or replace function test_volatile(v_id integer)
returns bigint
volatile
language sql
as
$$ select count(*) from t1 where id=v_id $$ ;
create or replace function test_stable(v_id integer)
returns bigint
stable
language sql
as
$$ select count(*) from t1 where id=v_id $$ ;
create or replace function test_immutable(v_id integer)
returns bigint
immutable
language sql
as
$$ select count(*) from t1 where id=v_id $$ ;
2、以列为参数调用函数
可以看到三个函数调用时间基本没有差别，因为，传入的参数是id 变量值（虽然实际值是相同的）。
test=# select count(*) from test where test_volatile(id)=1; count ------- 500 (1 row) Time: 26839.593 ms (00:26.840) test=# test=# select count(*) from test where test_stable(id)=1; count ------- 500 (1 row) Time: 26969.679 ms (00:26.970) test=# test=# select count(*) from test where test_immutable(id)=1; count ------- 500 (1 row) Time: 27316.593 ms (00:27.317)
3、函数在等式右边的情况
test=# select count(*) from test where id=test_volatile(1); count ------- 500 (1 row) Time: 27456.622 ms (00:27.457) test=# select count(*) from test where id=test_stable(1); count ------- 500 (1 row) Time: 26994.071 ms (00:26.994)
test=# select count(*) from test where id=test_immutable(1); count ------- 500 (1 row) Time: 68.484 ms
4、传入常量值
可以看到，对于常量值，stable 和 immutable 类型的函数实际只需调用一次。
test=# select count(*) from test where test_volatile(1)=1; count ------- 500 (1 row) Time: 26931.872 ms (00:26.932) test=# select count(*) from test where test_immutable(1)=1; count ------- 500 (1 row) Time: 63.230 ms test=# select count(*) from test where test_stable(1)=1; count ------- 500 (1 row) Time: 64.120 ms
5、select 列表包含函数
test=# explain analyze select test_volatile(1) from test; QUERY PLAN --------------------------------------------------------------------------------------------------------- Seq Scan on test (cost=0.00..133.00 rows=500 width=8) (actual time=65.768..27456.622 rows=500 loops=1) Planning Time: 0.133 ms Execution Time: 27456.622 ms (3 rows) test=# explain analyze select test_stable(1) from test; QUERY PLAN --------------------------------------------------------------------------------------------------------- Seq Scan on test (cost=0.00..133.00 rows=500 width=8) (actual time=78.336..27451.777 rows=500 loops=1) Planning Time: 0.150 ms Execution Time: 27451.777 ms (3 rows) test=# explain analyze select test_immutable(1) from test; QUERY PLAN -------------------------------------------------------------------------------------------------- Seq Scan on test (cost=0.00..8.00 rows=500 width=8) (actual time=0.023..0.067 rows=500 loops=1) Planning Time: 64.822 ms Execution Time: 0.107 ms (3 rows)
6、for循环调用
可以看到，对于相同的输入参数， test_immutable 在同一query 只执行一次，volatile 与 stable 则要执行多次。
test=# \set SQLTERM / test=# test=# begin test-# for i in 1..100 loop test-# perform test_volatile(1); test-# end loop; test-# end; test-# / ANONYMOUS BLOCK Time: 5456.121 ms (00:05.456) test=# test=# begin test-# for i in 1..100 loop test-# perform test_stable(1); test-# end loop; test-# end; test-# / ANONYMOUS BLOCK Time: 5311.829 ms (00:05.312) test=# begin test-# for i in 1..100 loop test-# perform test_immutable(1); test-# end loop; test-# end; test-# / ANONYMOUS BLOCK Time: 66.533 ms
对于参数值，不管哪类函数，实际执行次数与循环次数相同。
test=# begin test-# for i in 1..100 loop test-# perform test_volatile(i); test-# end loop; test-# end; test-# / ANONYMOUS BLOCK Time: 5582.093 ms (00:05.582)
test=# begin test-# for i in 1..100 loop test-# perform test_stable(i); test-# end loop; test-# end; test-# / ANONYMOUS BLOCK Time: 5486.094 ms (00:05.486)
test=# test=# begin test-# for i in 1..100 loop test-# perform test_immutable(i); test-# end loop; test-# end; test-# / ANONYMOUS BLOCK Time: 5475.526 ms (00:05.476)
三、oracle是怎么处理的?
构建数据
create table test(id integer); insert into test select 1 from dba_objects where rownum<501; create table t1(id number,name varchar(64)); insert into t1 select object_id,dbms_random.string('U',32) from dba_objects; --共插入100W条
构建存储过程
create or replace function test_deterministic(v_id integer) return integer deterministic as cnt integer; begin select count(*) into cnt from t1 where id = v_id; return cnt; end ; create or replace function test_volatile(v_id integer) return integer as cnt integer; begin select count(*) into cnt from t1 where id = v_id; return cnt; end ;
测试结果如下：部分场景与KES 执行次数不同。 相同次数情况下 ， Oracle 函数的执行效率要比KES 高效
SQL> select * from test where test_volatile(id)=1; no rows selected Elapsed: 00:00:08.09 SQL> select * from test where test_deterministic(id)=1; --这个与KingbaseES 不同，只需调用一次 no rows selected Elapsed: 00:00:00.06 SQL> select * from test where id=test_volatile(1); no rows selected Elapsed: 00:00:08.17 SQL> select * from test where id=test_deterministic(1); no rows selected Elapsed: 00:00:00.06 SQL> select * from test where test_volatile(1)=1; --这个与KingbaseES 不同，只需调用一次 no rows selected Elapsed: 00:00:00.03 SQL> select * from test where test_deterministic(1)=1; no rows selected Elapsed: 00:00:00.02
注意：以上是因为test.id 值是完全相同的，如果值不同会怎样？
SQL> insert into test select object_id from dba_objects where rownum<501; 500 rows created. SQL>SQL> select * from test where test_deterministic(id)=1; --执行次数 no rows selected Elapsed: 00:00:08.34
结论：Oracle 函数对于每个id 值都要执行一次，但如果id 是相同的，Oracle 则只需要执行一次
KINGBASE研究院
