確定性函式改造

前提

往往我們在編寫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