我编写了用于更新表列的PL/SQL代码,该代码基于从前端应用程序传递的输入,例如,如果INSTTABLE = 8
然后运行以下块的话。我有这样的90+应用程序,并在procedure.During中运行更新,在dev environment
中测试它,尽管代码运行良好,并且完成了预期的工作--首先将列值设置为零,然后调用几个函数来进行财务计算,但这花费了很长时间(超过一个小时)。最初,我认为这是由于数百万行/数据造成的,但我在这里发布它是为了检查代码是否有进一步改进的可能,以减少执行时间。
提前感谢您的帮助!
PS:我不能对函数进行更改,例如BAS2_RWA_CALC
内部calc/返回值,因为它被prod中的许多其他procs所使用,因此在执行main之前,我确实在单独的update语句中将列设置为0。将其设置为零的原因是生产过程遵循相同的逻辑,即先将列设置为0,然后再进行计算。如果不这样做,比较查询就会有差异。
--explain plan for update set values to zero
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 25545 | 4365K| 1383 (1)| 00:00:01 |
| 1 | UPDATE | MORT_BACK_SEC | | | | |
|* 2 | TABLE ACCESS FULL| MORT_BACK_SEC | 25545 | 4365K| 1383 (1)| 00:00:01 |
------------------------------------------------------------------------------------
--Explain plan for merge
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | MERGE STATEMENT | | 1 | 2605 | 1541 (1)| 00:00:01 |
| 1 | MERGE | MORT_BACK_SEC | | | | |
| 2 | VIEW | | | | | |
|* 3 | HASH JOIN | | 1 | 2498 | 1541 (1)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| RPT_ACCT_HIER | 1 | 491 | 156 (0)| 00:00:01 |
|* 5 | TABLE ACCESS FULL| MORT_BACK_SEC | 25545 | 48M| 1384 (1)| 00:00:01 |
--------------------------------------------------------------------------------------
IF INSTTABLE = 8 THEN
--Block to set columns to zero value
UPDATE usb.MORT_BACK_SEC SET
BAS_EB_RWA = 0, BAS_AB_RWA = 0, BAS_EB_RWA_COMMT = 0, BAS_AB_RWA_COMMT = 0, IMP_BAS_EB_RWA = 0, IMP_BAS_AB_RWA = 0, IDS_BAS_EB_RWA = 0,
IDS_BAS_AB_RWA = 0, DIS_BAS_EB_RWA = 0, DIS_BAS_AB_RWA = 0, PRE_BAS_EB_RWA = 0, PRE_BAS_AB_RWA = 0, BAS_AIR_EB_RWA = 0, BAS_AIR_AB_RWA = 0,
BAS_DELAY_REC_EB_RWA = 0, BAS_DELAY_REC_AB_RWA = 0, BAS_TPR_EB_RWA = 0, BAS_TPR_AB_RWA = 0, BAS_UNR_EB_RWA = 0, BAS_UNR_AB_RWA = 0,
BAS_ICAAP_EB_RWA = 0, BAS_ICAAP_AIR_EB_RWA = 0, BAS_ICAAP_DELAY_REC_EB_RWA = 0, BAS_ICAAP_DIS_EB_RWA = 0, BAS_ICAAP_IDS_EB_RWA = 0,
BAS_ICAAP_IMP_EB_RWA = 0, BAS_ICAAP_PRE_EB_RWA = 0, BAS_ICAAP_TPR_EB_RWA = 0, BAS_ICAAP_UNR_EB_RWA = 0, IMP_BAS_EB_TOTAL_CAPITAL = 0,
IMP_BAS_AB_TOTAL_CAPITAL = 0, IDS_BAS_EB_TOTAL_CAPITAL = 0, IDS_BAS_AB_TOTAL_CAPITAL = 0, DIS_BAS_EB_TOTAL_CAPITAL = 0, DIS_BAS_AB_TOTAL_CAPITAL = 0,
PRE_BAS_EB_TOTAL_CAPITAL = 0, PRE_BAS_AB_TOTAL_CAPITAL = 0, BAS_AIR_EB_TOTAL_CAPITAL = 0, BAS_AIR_AB_TOTAL_CAPITAL = 0,
BAS_DELAY_REC_EB_TOTAL_CAPITAL = 0, BAS_DELAY_REC_AB_TOTAL_CAPITAL = 0, BAS_TPR_EB_TOTAL_CAPITAL = 0, BAS_TPR_AB_TOTAL_CAPITAL = 0,
BAS_UNR_EB_TOTAL_CAPITAL = 0, BAS_UNR_AB_TOTAL_CAPITAL = 0, IMP_BAS_EB_EXPECTED_LOSS = 0, IMP_BAS_AB_EXPECTED_LOSS = 0, IDS_BAS_EB_EXPECTED_LOSS = 0,
IDS_BAS_AB_EXPECTED_LOSS = 0, DIS_BAS_EB_EXPECTED_LOSS = 0, DIS_BAS_AB_EXPECTED_LOSS = 0, PRE_BAS_EB_EXPECTED_LOSS = 0, PRE_BAS_AB_EXPECTED_LOSS = 0,
BAS_AIR_EB_EXPECTED_LOSS = 0, BAS_AIR_AB_EXPECTED_LOSS = 0, BAS_DELAY_REC_EB_EXPECTED_LOSS = 0, BAS_DELAY_REC_AB_EXPECTED_LOSS = 0,
BAS_TPR_EB_EXPECTED_LOSS = 0, BAS_TPR_AB_EXPECTED_LOSS = 0, BAS_UNR_EB_EXPECTED_LOSS = 0, BAS_UNR_AB_EXPECTED_LOSS = 0
WHERE AS_OF_DATE = TO_DATE('06/30/2019','MM/DD/YYYY');
COMMIT;
MERGE INTO ( SELECT /*+ index(a idx1) */ D.*,
CASE WHEN GL_ACCOUNT_ID IN (1370050, 1450035) THEN 0
ELSE USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) END AS V_BAS_EB_RWA,
CASE WHEN GL_ACCOUNT_ID IN (1370050, 1450035) THEN 0
ELSE USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) END AS V_BAS_AB_RWA,
0 AS V_BAS_EB_RWA_COMMT,
0 AS V_BAS_AB_RWA_COMMT,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IMP_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_IMP_BAS_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IMP_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_IMP_BAS_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IDS_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_IDS_BAS_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IDS_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_IDS_BAS_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DIS_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_DIS_BAS_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DIS_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_DIS_BAS_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (PRE_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_PRE_BAS_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (PRE_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_PRE_BAS_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (AIR_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_AIR_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (AIR_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_AIR_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DELAY_REC_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_DELAY_REC_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DELAY_REC_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_DELAY_REC_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (TPR_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_TPR_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (TPR_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_TPR_AB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (UNR_CUR_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_UNR_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (UNR_AVG_BOOK_BAL, 0), NVL (BAS_CAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_UNR_AB_RWA,
CASE WHEN GL_ACCOUNT_ID IN (1370050, 1450035) THEN 0
ELSE USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) END AS V_BAS_ICAAP_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (AIR_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_AIR_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DELAY_REC_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_DELAY_REC_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (DIS_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_DIS_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IDS_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_IDS_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (IMP_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_IMP_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (PRE_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_PRE_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (TPR_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_TPR_EB_RWA,
USB.BAS2_RWA_CALC (BAS_CAPITAL_CALC_CD, NVL (UNR_CUR_BOOK_BAL, 0), NVL (BAS_ICAAP_FACTOR_K, 0), 0.06, 0.08) AS V_BAS_ICAAP_UNR_EB_RWA
FROM USB.MORT_BACK_SEC D ) A
USING (SELECT * FROM USB.rpt_acct_hier) B
ON (a.gl_account_id = b.acct_member and a.as_of_date = TO_DATE('06/30/2019','MM/DD/YYYY') and b.acct_gen2 = 'a1000')
WHEN MATCHED THEN UPDATE SET
BAS_EB_RWA = V_BAS_EB_RWA,
BAS_AB_RWA = V_BAS_AB_RWA,
BAS_EB_RWA_COMMT = V_BAS_EB_RWA_COMMT,
BAS_AB_RWA_COMMT = V_BAS_AB_RWA_COMMT,
IMP_BAS_EB_RWA = V_IMP_BAS_EB_RWA,
IMP_BAS_AB_RWA = V_IMP_BAS_AB_RWA,
IDS_BAS_EB_RWA = V_IDS_BAS_EB_RWA,
IDS_BAS_AB_RWA = V_IDS_BAS_AB_RWA,
DIS_BAS_EB_RWA = V_DIS_BAS_EB_RWA,
DIS_BAS_AB_RWA = V_DIS_BAS_AB_RWA,
PRE_BAS_EB_RWA = V_PRE_BAS_EB_RWA,
PRE_BAS_AB_RWA = V_PRE_BAS_AB_RWA,
BAS_AIR_EB_RWA = V_BAS_AIR_EB_RWA,
BAS_AIR_AB_RWA = V_BAS_AIR_AB_RWA,
BAS_DELAY_REC_EB_RWA = V_BAS_DELAY_REC_EB_RWA,
BAS_DELAY_REC_AB_RWA = V_BAS_DELAY_REC_AB_RWA,
BAS_TPR_EB_RWA = V_BAS_TPR_EB_RWA,
BAS_TPR_AB_RWA = V_BAS_TPR_AB_RWA,
BAS_UNR_EB_RWA = V_BAS_UNR_EB_RWA,
BAS_UNR_AB_RWA = V_BAS_UNR_AB_RWA,
BAS_ICAAP_EB_RWA = V_BAS_ICAAP_EB_RWA,
BAS_ICAAP_AIR_EB_RWA = V_BAS_ICAAP_AIR_EB_RWA,
BAS_ICAAP_DELAY_REC_EB_RWA = V_BAS_ICAAP_DELAY_REC_EB_RWA,
BAS_ICAAP_DIS_EB_RWA = V_BAS_ICAAP_DIS_EB_RWA,
BAS_ICAAP_IDS_EB_RWA = V_BAS_ICAAP_IDS_EB_RWA,
BAS_ICAAP_IMP_EB_RWA = V_BAS_ICAAP_IMP_EB_RWA,
BAS_ICAAP_PRE_EB_RWA = V_BAS_ICAAP_PRE_EB_RWA,
BAS_ICAAP_TPR_EB_RWA = V_BAS_ICAAP_TPR_EB_RWA,
BAS_ICAAP_UNR_EB_RWA = V_BAS_ICAAP_UNR_EB_RWA,
IMP_BAS_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_IMP_BAS_EB_RWA, 0), 2),
IMP_BAS_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_IMP_BAS_AB_RWA, 0), 2),
IDS_BAS_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_IDS_BAS_EB_RWA, 0), 2),
IDS_BAS_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_IDS_BAS_AB_RWA, 0), 2),
DIS_BAS_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_DIS_BAS_EB_RWA, 0), 2),
DIS_BAS_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_DIS_BAS_AB_RWA, 0), 2),
PRE_BAS_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_PRE_BAS_EB_RWA, 0), 2),
PRE_BAS_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_PRE_BAS_AB_RWA, 0), 2),
BAS_AIR_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_AIR_EB_RWA, 0), 2),
BAS_AIR_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_AIR_AB_RWA, 0), 2),
BAS_DELAY_REC_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_DELAY_REC_EB_RWA, 0), 2),
BAS_DELAY_REC_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_DELAY_REC_AB_RWA, 0), 2),
BAS_TPR_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_TPR_EB_RWA, 0), 2),
BAS_TPR_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_TPR_AB_RWA, 0), 2),
BAS_UNR_EB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_UNR_EB_RWA, 0), 2),
BAS_UNR_AB_TOTAL_CAPITAL = ROUND (USB.BAS2_MGRL_CAPITAL (TO_DATE('06/30/2019','MM/DD/YYYY'), V_BAS_UNR_AB_RWA, 0), 2),
-- CALCULATE BAS_EB_EXPECTED_LOSS AND BAS_AB_EXPECTED_LOSS
IMP_BAS_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (IMP_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
IMP_BAS_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (IMP_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
IDS_BAS_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (IDS_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
IDS_BAS_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (IDS_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
DIS_BAS_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (DIS_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
DIS_BAS_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (DIS_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
PRE_BAS_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (PRE_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
PRE_BAS_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (PRE_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_AIR_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (AIR_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_AIR_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (AIR_AVG_BOOK_BAL, 0,BAS_PD, BAS_LGD, 0),
BAS_DELAY_REC_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (DELAY_REC_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_DELAY_REC_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (DELAY_REC_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_TPR_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (TPR_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_TPR_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (TPR_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_UNR_EB_EXPECTED_LOSS = USB.BAS2_EL_CALC (UNR_CUR_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0),
BAS_UNR_AB_EXPECTED_LOSS = USB.BAS2_EL_CALC (UNR_AVG_BOOK_BAL, 0, BAS_PD, BAS_LGD, 0)
WHERE
(AS_OF_DATE = TO_DATE('06/30/2019','MM/DD/YYYY'));
COMMIT;
END IF;
--Function: BAS_RWA_CALC logic
CREATE OR REPLACE FUNCTION USB."BAS2_RWA_CALC" (v_formula in char,v_bal in number,v_k_factor in number, v_bas_min in number,v_rwa_adj_rate in number) return number
is
v_rwa number(15,2);
begin
v_rwa := nvl(v_bal,0)*nvl(v_k_factor,0)/nvl(v_bas_min,0);
v_rwa := v_rwa*(1+v_rwa_adj_rate);
return round(v_rwa,2);
end;
/
[1]: https://i.stack.imgur.com/k7ikw.png
发布于 2019-07-19 06:00:00
有几种方法可以减少SQL和PL/SQL之间上下文切换的开销。
PRAGMA UDF;
在第一个is
之后,将pragma udf;
添加到代码中,以告诉pragma udf;
对SQL函数进行优化。在我的快速测试中,这减少了SQL调用中60%的上下文切换开销。这种方法的问题在于它需要修改每个人的函数,在PL/SQL中调用它时可能会导致性能损失。PRAGMA UDF;
创建新函数,并在中调用这些函数,则使用包装函数。我的测试表明,这可以减少52%的上下文切换,这并不像以前的解决方案那么好,但非常接近。这种方法将所有原始逻辑保持在同一个位置,从而保留了不重复的规则。但是,它确实创建了新的对象。WITH
function 将包装器函数创建为SQL语句中的公共表表达式。这可以减少大约32%的开销。这是一种纯SQL方法,但速度不如其他解决方案,SQL语句看起来很难看。这些方法对你不起作用,但对于未来的读者来说,最好的选择是:
发布于 2019-07-17 22:14:29
在合并语句中,有59个函数调用。太多了。看起来,对于MORT_BACK_SEC
中的每一行,您都调用了27次‘MORT_BACK_SEC
’,即使您没有使用这些调用的结果。现在,数据库可能足够聪明,只对您实际从MORT_BACK_SEC
中使用的行进行这些调用,但我不相信这一点。如果其他地方不需要它们(我看不出来),我建议将这些计算推到WHEN MATCHED
块中。
对于所做的每一次更新,您还需要调用BAS2_MGRL_CAPITAL
和BAS2_EL_CALC
16次。我不知道这些函数有多复杂,但是如果它们很简单,并且可以被内联CASE
表达式替换,那么我就会这样做,因为函数调用的数量会影响这个语句的性能。我不知道这会产生什么样的计划,但我怀疑这很重要--我怀疑语句的运行时间将被所有这些函数调用的执行时间所支配。
我会尝试为您重新排列MERGE
语句,但是我不知道哪些函数参数是MORT_BACK_SEC
中的列,哪些是在MERGE
语句之外定义的变量。尽可能多地将函数调用从语句本身中筛选出来,如果其中任何一个是可以的话,当然可以将它们从初始的MERGE
表中提取出来。我想你的MERGE
应该从
MERGE INTO MORT_BACK_SEC a
USING RPT_ACCT_HIER b
ON (a.GL_ACCOUNT_ID = b.ACCT_MEMBER AND
a.AS_OF_DATE = V_DATE AND
b.ACCT_GEN2 = 'a1000')
然后从那里拿走。
当然,由于您没有WHEN NOT MATCHED THEN INSERT
,所以一旦将所有函数调用移到UPDATE
块中,就可以以UPDATE
而不是MERGE
的形式重新执行这些操作。由你决定。但是,我再次怀疑这很重要,因为这些函数调用是我怀疑这个语句花费时间的地方。
https://stackoverflow.com/questions/57083542
复制相似问题