我想调整一个过程,该过程具有许多动态SQL语句,这些语句需要花费太多时间才能执行。我需要帮助来调整这些或一些替代解决方案。
例:
EXECUTE IMMEDIATE 'CREATE TABLE C1_LAN_GTT_1 NOLOGGING PARALLEL 4 AS
SELECT /*+INDEX(C,CR_DECISION_M_CD1)*/ A.AGREEMENTID,
A.AGREEMENTNO, A.LESSEEID, A.PRODUCTFLAG, A.APP_FORMNO, A.AMTFIN
FROM LEA_AGREEMENT_DTL A, CR_DECISION_M C
WHERE A.AGREEMENTID = C.APPID
AND A.STATUS = ''A''
AND TRUNC(C.AUTHDATE) BETWEEN' || '''' || P_FROM_DATE || '''' ||
' AND ' || '''' || P_TO_DATE || '''';
当我在 PLSQL 开发人员中以调试模式检查过程时,我发现这些动态语句花费了太多时间。
查询没有数据仍需要时间来执行。
如果没有更多细节,例如执行计划以及表、索引和数据量和分布的完整详细信息,我不知道您面临什么性能问题。但是,我可能会从这样的事情开始:
declare
p_from_date date := date '2018-01-01';
p_to_date date := date '2018-02-01';
myddl long := 'create table c1_lan_gtt_1 nologging parallel 4 as
select /*+index(c cr_decision_m_cd1)*/ a.agreementid,
a.agreementno, a.lesseeid, a.productflag, a.app_formno, a.amtfin
from lea_agreement_dtl a
join cr_decision_m c on c.appid = a.agreementid
where a.status = ''A''
and c.authdate between date ''' || to_char(p_from_date,'YYYY-MM-DD') || '''' ||
' and date ''' || to_char(p_to_date,'YYYY-MM-DD') || '''';
begin
dbms_output.put_line(myddl);
end;
生成
create table c1_lan_gtt_1 nologging parallel 4 as
select /*+index(c cr_decision_m_cd1)*/ a.agreementid,
a.agreementno, a.lesseeid, a.productflag, a.app_formno, a.amtfin
from lea_agreement_dtl a
join cr_decision_m c on c.appid = a.agreementid
where a.status = 'A'
and trunc(c.authdate) between date '2018-01-01' and date '2018-02-01'
编辑:由于索引CR_DECISION_M_CD1
在(APPID, TRUNC(AUTHDATE))
(来自评论(,我已经恢复了trunc()
功能,即使逻辑上不需要它。
检查P_FROM_DATE和P_TO_DATE是绑定变量 在此处输入链接说明
新版本的执行立即语句使用存在而不是连接:
EXECUTE IMMEDIATE 'CREATE TABLE C1_LAN_GTT_1 NOLOGGING PARALLEL 4 AS
SELECT A.AGREEMENTID, A.AGREEMENTNO, A.LESSEEID, A.PRODUCTFLAG, A.APP_FORMNO, A.AMTFIN
FROM LEA_AGREEMENT_DTL A
WHERE A.STATUS = ''A''
AND EXISTS (
SELECT /*+INDEX(C CR_DECISION_M_CD1)*/ NULL
FROM CR_DECISION_M C
WHERE C.APPID = A.AGREEMENTID
AND TRUNC(C.AUTHDATE) BETWEEN '''||P_FROM_DATE||''' AND '''||P_TO_DATE||'''
)';