优化Unpivot脚本：改用Cross Apply提升后端数据提取效率

当前使用Unpivot的脚本从后端提取数据耗时较长，希望改用Cross Apply或其他方式优化数据提取效率，原脚本如下：

SELECT 
    cm.cust_acct_no as "dd",
    ( 
    SELECT
        COALESCE(sum((select kca.active_account from kyc_count_account kca where kca.CUST_ACCT_NO = joint_acct)),0,0)
      FROM
        (SELECT
          (lpad('0', (16-length(t.link_cua_acno_01)), '0')||t.link_cua_acno_01) AS acct01
          ,(lpad('0', (16-length(t.link_cua_acno_02)), '0')||t.link_cua_acno_02) AS acct02
          ,(lpad('0', (16-length(t.link_cua_acno_03)), '0')||t.link_cua_acno_03) AS acct03
          ,(lpad('0', (16-length(t.link_cua_acno_04)), '0')||t.link_cua_acno_04) AS acct04
          ,(lpad('0', (16-length(t.link_cua_acno_05)), '0')||t.link_cua_acno_05) AS acct05
          ,(lpad('0', (16-length(t.link_cua_acno_06)), '0')||t.link_cua_acno_06) AS acct06
          ,(lpad('0', (16-length(t.link_cua_acno_07)), '0')||t.link_cua_acno_07) AS acct07
          ,(lpad('0', (16-length(t.link_cua_acno_08)), '0')||t.link_cua_acno_08) AS acct08
        FROM cbsdbap1.cusvcc t
        WHERE substr(t.key_1,4,16) = cm.cust_acct_no and t.code = 'CC' and rownum <= 1
    ) temp unpivot include NULLS (joint_acct FOR kind IN (acct01, acct02, acct03, acct04, acct05, acct06, acct07, acct08))) as "Number of Joint Accounts"
  FROM CUSM cm

可行调优方案 #

1. 用CROSS APPLY替换Unpivot，重构子查询逻辑 #

原脚本的嵌套Unpivot和多层子查询会引发多次表扫描，改用CROSS APPLY直接将多列转成行，同时把嵌套的kyc_count_account查询改为JOIN，减少重复查询开销：

SELECT 
    cm.cust_acct_no AS "dd",
    COALESCE(SUM(kca.active_account), 0) AS "Number of Joint Accounts"
FROM CUSM cm
LEFT JOIN cbsdbap1.cusvcc t 
    ON SUBSTR(t.key_1,4,16) = cm.cust_acct_no 
    AND t.code = 'CC'
CROSS APPLY (
    SELECT LPAD(t.link_cua_acno_01, 16, '0') AS joint_acct FROM DUAL WHERE t.link_cua_acno_01 IS NOT NULL
    UNION ALL
    SELECT LPAD(t.link_cua_acno_02, 16, '0') AS joint_acct FROM DUAL WHERE t.link_cua_acno_02 IS NOT NULL
    UNION ALL
    SELECT LPAD(t.link_cua_acno_03, 16, '0') AS joint_acct FROM DUAL WHERE t.link_cua_acno_03 IS NOT NULL
    UNION ALL
    SELECT LPAD(t.link_cua_acno_04, 16, '0') AS joint_acct FROM DUAL WHERE t.link_cua_acno_04 IS NOT NULL
    UNION ALL
    SELECT LPAD(t.link_cua_acno_05, 16, '0') AS joint_acct FROM DUAL WHERE t.link_cua_acno_05 IS NOT NULL
    UNION ALL
    SELECT LPAD(t.link_cua_acno_06, 16, '0') AS joint_acct FROM DUAL WHERE t.link_cua_acno_06 IS NOT NULL
    UNION ALL
    SELECT LPAD(t.link_cua_acno_07, 16, '0') AS joint_acct FROM DUAL WHERE t.link_cua_acno_07 IS NOT NULL
    UNION ALL
    SELECT LPAD(t.link_cua_acno_08, 16, '0') AS joint_acct FROM DUAL WHERE t.link_cua_acno_08 IS NOT NULL
) accts
LEFT JOIN kyc_count_account kca 
    ON kca.CUST_ACCT_NO = accts.joint_acct
GROUP BY cm.cust_acct_no;

优化点说明：

• 用JOIN+CROSS APPLY替代多层嵌套结构，降低执行计划的复杂度
• 用LPAD(col,16,'0')简化补零逻辑，比原脚本先计算长度再拼接更高效
• 将单行查询kyc_count_account改为批量JOIN，减少多次单条查询的IO消耗

2. 添加索引优化查询速度 #

针对查询中用到的过滤和关联字段创建索引，大幅提升查找效率：

• 给cbsdbap1.cusvcc表创建复合索引：CREATE INDEX idx_cusvcc_key1_code ON cbsdbap1.cusvcc(key_1, code);
• 给kyc_count_account表创建索引：CREATE INDEX idx_kyc_cust_acct ON kyc_count_account(CUST_ACCT_NO);
• 给CUSM表的cust_acct_no字段创建索引（若未存在）：CREATE INDEX idx_cusm_cust_acct ON CUSM(cust_acct_no);

3. 简化过滤条件 #

原脚本用SUBSTR(t.key_1,4,16) = cm.cust_acct_no，如果key_1的结构固定为前3位+16位账号，可在cusvcc表新增计算列存储截取后的账号，再给计算列建索引，避免每次查询执行SUBSTR函数：

ALTER TABLE cbsdbap1.cusvcc ADD cust_acct_no AS SUBSTR(key_1,4,16);
CREATE INDEX idx_cusvcc_cust_acct ON cbsdbap1.cusvcc(cust_acct_no, code);

之后查询直接用t.cust_acct_no = cm.cust_acct_no，减少函数运算开销。

内容的提问来源于stack exchange，提问作者DM19