确定性函数改造

前提

往往我们在编写sql时，会对一些特殊重复计算处理用到函数。函数是数据库一个重要特性，可以让用户高效的重复使用数据计算，但函数有些执行速度要比想象的慢，尤其在条件筛选方面会有潜在的性能问题。

说明

确定性函数用关键词Deterministic标识，表示函数的返回值完全由输入参数决定。
确定性函数的优点：
其返回结果能够进行缓存处理以提升性能。
但是要注意：
是不是一个确定性函数是需要用户来负责的，就是说对函数进行编译的时候不会检查出这个函数是否是确定性的，也就是必须保证只要使用特定的输入值集，那么不管何时调用，确定性函数始终都会返回相同的结果。

实例

原始sql

SELECT   
'20221231' AS mdtrdt,
              mdacno,
             mdsbno,
             mdcyno,
             mditcd,
             mdacbl AS mdcubl,
             mdlabl,
             mdopdt,
             mdvldt,
             mdmadt,
             CONVERT_FDMD_PERD_DETERMINISTIC(mdperd, mdvldt),
             mdcldt,
             TRIM(mdinkd),
             NVL(mdinrt, 0),
             mdstcd,
             mdcuno,
             mdaucd,
             cust_acct, 
             sub_acct_seq_no
        FROM in_fdmdl1 
        ,(SELECT business_code, node_type
                FROM tb_crinit2 t
               WHERE business_state = '1'
               START WITH (business_code = PROFIT_CONSTANTS2.BUSI_CODE_FDCC OR
                          business_code = PROFIT_CONSTANTS2.BUSI_CODE_FDPS)
              CONNECT BY parent_id = PRIOR business_code)
       WHERE in_date = '20221231'
         AND mdstcd NOT IN ('H', 'I', 'Q')
         AND (mdcldt = '0' OR mdcldt >= FUN_DAY_YESTERDAY('20221231'))
         AND mditcd = business_code
         AND node_type = '02'
         AND mdvldt <= '20221231';

原执行计划

1   #NSET2: [2019, 49679->5908849, 1058]
2     #PRJT2: [2019, 49679->5908849, 1058]; exp_num(19), is_atom(FALSE)
3       #HASH2 INNER JOIN: [2019, 49679->5908849, 1058];  KEY_NUM(1);
4         #PRJT2: [3, 4->35, 200]; exp_num(1), is_atom(FALSE)
5           #SLCT2: [3, 4->35, 200];
6             #HIERARCHICAL QUERY: [3, 85->51, 200]; key_num(0)
7               #UNION FOR OR2: [1, 12->2, 200]; key_num(1)
8                 #BLKUP2: [1, 6->1, 200]; IDX_TB_CRINIT2(TB_CRINIT2)
9                   #SLCT2: [1, 6->1, 200];
10                    #SSCN: [1, 6->241, 200]; IDX_TB_CRINIT2(TB_CRINIT2)
11                #BLKUP2: [1, 6->1, 200]; IDX_TB_CRINIT2(TB_CRINIT2)
12                  #SLCT2: [1, 6->1, 200];
13                    #SSCN: [1, 6->241, 200]; IDX_TB_CRINIT2(TB_CRINIT2)
14              #SLCT2: [1, 7->49, 200];
15                #SSCN: [1, 7->12291, 200]; I_TBCRINIT2_LH(TB_CRINIT2)
16        #HASH RIGHT SEMI JOIN2: [1961, 312628->5908923, 858]; key_num(1)  (ANTI),
17          #CONST VALUE LIST: [1, 3->3, 48]; row_num(3), col_num(1),
18          #SLCT2: [1961, 312628->5918256, 858];
19            #CSCN2: [1961, 6759529->6759529, 858]; INDEX34435162_34434582(IN_FDMDL1_IN_FDMDL1_P20221231)

同时分析下ET，确定慢的部分

LINEID     OP        TIME(US)             PERCENT RANK                 SEQ         N_ENTER
---------- --------- -------------------- ------- -------------------- ----------- -----------
1          PRJT2     1                    0%      20                   4           4
2          BLKUP2    7                    0%      18                   8           4
3          SLCT2     7                    0%      18                   5           4
4          CONSTV    8                    0%      17                   17          2
5          UNION_OR2 11                   0%      16                   7           7
6          SSCN      18                   0%      15                   10          2
7          SLCT2     52                   0%      14                   9           4
8          SLCT2     58                   0%      13                   12          4
9          CNNTB     97                   0%      12                   6           72
10         SLCT2     113                  0%      11                   14          169
11         SSCN      1494                 0%      10                   15          102
LINEID     OP     TIME(US)             PERCENT RANK                 SEQ         N_ENTER
---------- ------ -------------------- ------- -------------------- ----------- -----------
12         SSCN   19134                0.02%   9                    13          2
13         DLCK   21836                0.03%   8                    0           2373
14         BLKUP2 23039                0.03%   7                    11          4
15         HRS2   291446               0.36%   6                    16          13524
16         HI3    607385               0.75%   5                    3           13524
17         SLCT2  3396682              4.22%   4                    18          13522
18         NSET2  5430177              6.74%   3                    1           9133
19         CSCN2  22166928             27.51%  2                    19          6761
20         PRJT2  48604664             60.33%  1                    2           13522
20 rows got

分析发现in_fdmdl1分区表的IN_FDMDL1_IN_FDMDL1_P20221231产生的是全表扫描，借此我们先分析主驱动表的where筛选。
实验查询发现筛选度很高，但没有走联合索引，而当我们把函数不确定值带入返回的确认实际值时，走了索引且速度很快。

试验1：修改函数的实现方式
将FUN_DAY_YESTERDAY转化为TO_CHAR(add_days(to_date(p_date,'YYYY-MM-DD'),-1),'YYYYMMDD')
走了索引且速度很快

试验2：将sql改写成动态拼串，然后用execute immediate执行拼串sql，走了索引且速度依然很快。

初步总结

通过试验1.2分析：由于分析器无法确认函数内操作而造成每次查询此sql都要重新评估执行计划。

再优化

而如何可以在不修改原有函数的处理方式或者不改成动态sql执行的方式，来优化现有sql速度呢？
根据函数内分析，我们可以利用确认性函数。

修改函数将FUN_DAY_YESTERDAY内转化为确认性函数，且命名为将FUN_DAY_YESTERDAY_DETERMINISTIC。

create or replace
  function FUN_DAY_YESTERDAY_DETERMINISTIC
             P_DATE VARCHAR2,
             P_TYPE INTEGER :=1,
  RETURN  VARCHAR2 DETERMINISTIC AS 
  ....
 BEGIN
   ...
 END;

修改后试验发现，速度提升到秒级。

总结

对于使用频繁的函数，确定性函数的定义将在很大的程度上提高了数据库的性能。

DM 武汉达梦数据库股份有限公司
24小时免费服务热线：400 991 6599