达梦数据库-调用SQL内置函数

问题背景：如何判定某个SQL内置函数的调用次数。

        在性能优化的过程中，会遇到某些性能问题的重要原因由于错误的函数调用导致，即用户自定义函数被调用次数过多（有时会超过预期值的几个数量级）。每次调用不仅引发一次SQL到PL/SQL并且再切换回来的上下文切换，而且当函数被没必要调用时，还增加了总开销。

        为了性能调优，SQL语句的执行顺序是很重要的：

        （1）、JOIN

        （2）、WHERE

        （3）、GROUP BY

        （4）、SELECT

        （5）、HAVING

        （6）、ORDER BY

测试与分析过程

1、创建以下带有存储过程函数计数器的封装包和测试程序的环境

create or replace package counter_pkg is 
	v_nr number:=0;
	procedure p_check;
end;

create or replace package body counter_pkg is
	procedure p_check is
	begin 
		dbms_output.put_line('Fired:'||counter_pkg.v_nr);
		counter_pkg.v_nr:=0;
	end;
end;

create or replace function f_change_nr (i_nr NUMBER) return number 
is
begin
	counter_pkg.v_nr:=counter_pkg.v_nr+1;
	return i_nr+1;
end;

2、比较oracle和达梦中单表执行相同sql调用函数的引用次数

达梦：

oracle：

oracle在SELECT和WHERE子句中，同样的函数单独起作用且不能被重复使用，这就死oracle调用的总数等于10的原因。

达梦：

oracle：

oracle在第二种情况下，主查询被封装为内联视图。函数F_CHANGE_NR被多启用了两倍，导致10次调用而不是5次。可以给内联视图添加/*+ NO_MERGER */屏蔽可以使双重启用问题消失。

进一步的研究，把查询作为视图使用，观察调用次数是否存在差异

create or replace view v_emp_1 as
select employee_id,employee_name, f_change_nr(employee_id) change_nr
from employee
where department_id=102
order by 3;

达梦：

oracle：

oracle在视图中引用函数列变为复杂的视图，但如果视图中存在order by则会存在这种完全不同的情况。这样的视图引用会使某些函数的开销翻倍。更重要的是，由于数据的增长或其他原因而致使执行计划变化时，这个开销可能会突然发生。总的来说，无论何时，order by 子句都应尽可能在最高级别使用而不是在中间视图。

3、多表连接的情况下

部门表新增一个无员工的部门

insert into dmhr.DEPARTMENT (DEPARTMENT_ID,DEPARTMENT_NAME,MANAGER_ID,LOCATION_ID) values ('999','新部门','9999','7');

连表查询

select employee_id,f_change_nr(employee_id) change_nr,department_name
from employee,
department
where dmhr.employee.department_id(+)=dmhr.department.department_id;

达梦：

oracle：

在第857行表示没有任何员工的部门。由于是在连接之后使用的，F_CHANGE_NR函数被启用了857次。所以在857次中有1次的调用是没必要的。说明连接会改变函数调用次数。

4、DETERMINISTIC优化函数调用次数

create or replace function f_change_nr (i_nr NUMBER) return number 
deterministic  
is
begin
	counter_pkg.v_nr:=counter_pkg.v_nr+1;
	return i_nr+1;
end;

deterministic它可以做如下设定，对于同样的IN参数，函数的返回值也是一样的。

select employee_id,f_change_nr(dmhr.department.department_id) change_nr,department_name
from dmhr.employee,
dmhr.department
where dmhr.employee.department_id(+)=dmhr.department.department_id;

达梦：

oracle：

这次为了匹配不同部门数，deterministic子句进行了工作，对F_CHANGE_NR函数的调用次数减少到47次和实际部门数相匹配

https://eco.dameng.com