oracle 将unpivot脚本调优为另一种数据提取方式

djmepvbi 于 7个月前发布在 Oracle

关注(0)|答案(1)|浏览(72)

我想转换的unpivot脚本，因为它需要很长的时间来提取数据从后端。然而，我可以知道简单的方法提取数据从后端使用交叉应用.或其他方式。
示例脚本：

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

字符串
调优此脚本的好方法

oracle

来源：https://stackoverflow.com/questions/77321633/tune-the-unpivot-script-to-another-way-for-data-extraction

1条答案

按热度按时间

syqv5f0l1#

如果没有CREATE TABLE或INSERT语句来帮助调试性能，则很难提出建议。
在Oracle 12中，一个可能的解决方案是使用LATERAL join：

SELECT cm.cust_acct_no as "dd",
       ( SELECT COUNT(1)
         FROM   kyc_count_account kca
         WHERE  (  kca.CUST_ACCT_NO = LPAD(t.link_cua_acno_01, 16, '0')
                OR kca.CUST_ACCT_NO = LPAD(t.link_cua_acno_02, 16, '0')
                OR kca.CUST_ACCT_NO = LPAD(t.link_cua_acno_03, 16, '0')
                OR kca.CUST_ACCT_NO = LPAD(t.link_cua_acno_04, 16, '0')
                OR kca.CUST_ACCT_NO = LPAD(t.link_cua_acno_05, 16, '0')
                OR kca.CUST_ACCT_NO = LPAD(t.link_cua_acno_06, 16, '0')
                OR kca.CUST_ACCT_NO = LPAD(t.link_cua_acno_07, 16, '0')
                OR kca.CUST_ACCT_NO = LPAD(t.link_cua_acno_08, 16, '0')
                )
         AND    kca.active_account = 1
       ) as "Number of Joint Accounts"
FROM   CUSM cm
       LEFT OUTER JOIN LATERAL (
         SELECT *
         FROM   cbsdbap1.cusvcc t
         WHERE  SUBSTR(t.key_1, 4, 16) = cm.cust_acct_no
         AND    t.code = 'CC'
         FETCH FIRST ROW ONLY
       ) t
       ON (1 = 1)

字符串

赞(0）回复(0）举报 7个月前

我来回答

oracle 将unpivot脚本调优为另一种数据提取方式

1条答案

相关问题

热门标签

最新问答