Oracle PLSQL长时间运行问题

huangapple
go评论93阅读模式
英文:

Oracle PLSQL long run issue

问题

有以下代码长期问题，请求协助纠正。提前致以感谢。

  1. Declare
  2. l_status varchar2(10);
  4. Begin
  6. Insert into @table_name (ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD)
  7.   Select ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD 
  8.            from {{sourceTableLatest('Aggregation[table]')}} partition ({{getSourcePartitionNameLatest('Aggregation[table]')}});
  10. Commit;
  12. For i in ( Select mfu_source.Customer_Surrogate_ID CUST_SROGT_ID, Agg.TRD_REF_NUM TRD_REF_NUM, mfu_source.Enterprise_Customer_ID ENTRP_CUST_ID_NUM
  13.           from {{sourceTableLatest('Aggregation[table]')}} partition ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG,
  14.           {{sourceTableLatest('mfu_table')}} partition ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source
  15.          Where (mfu_source.TA_LINK_ID=AGG.TRD_REF_NUM or mfu_source.TRANSACTION_ID=AGG.TRD_REF_NUM)
  16.          and trim(AGG.ENTRP_CUST_ID_NUM) is null) 
  17.          Loop
  18.          
  20.     Update @table_name set ENTRP_CUST_ID_NUM= i.ENTRP_CUST_ID_NUM where TRD_REF_NUM=i.TRD_REF_NUM;
  21.     
  22.     Commit;
  23. End Loop;
  25. END;
  27. 上述代码存在长期问题，需要简单和容易地进行修正。
  29. <details>
  30. <summary>英文:</summary>
  32. there is long run issue with below code kindly help to rectify this issue. Thanks in advance.
  35.     Declare
  36.     l_status varchar2(10);
  37.     
  38.     Begin
  39.     
  40.     Insert into @table_name (ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD)
  41.       Select ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD 
  42.                from {{sourceTableLatest(&#39;Aggregation[table]&#39;)}} partition ({{getSourcePartitionNameLatest(&#39;Aggregation[table]&#39;)}});
  43.     
  44.     Commit;
  45.     
  46.     For i in ( Select mfu_source.Customer_Surrogate_ID CUST_SROGT_ID, Agg.TRD_REF_NUM TRD_REF_NUM, mfu_source.Enterprise_Customer_ID ENTRP_CUST_ID_NUM
  47.               from {{sourceTableLatest(&#39;Aggregation[table]&#39;)}} partition ({{getSourcePartitionNameLatest(&#39;Aggregation[table]&#39;)}}) AGG,
  48.               {{sourceTableLatest(&#39;mfu_table&#39;)}} partition ({{getSourcePartitionNameLatest(&#39;mfu_table&#39;)}}) mfu_source
  49.              Where (mfu_source.TA_LINK_ID=AGG.TRD_REF_NUM or mfu_source.TRANSACTION_ID=AGG.TRD_REF_NUM)
  50.              and trim(AGG.ENTRP_CUST_ID_NUM) is null) 
  51.              Loop
  52.     		 
  53.     		 
  54.     	Update @table_name set ENTRP_CUST_ID_NUM= i.ENTRP_CUST_ID_NUM where TRD_REF_NUM=i.TRD_REF_NUM;
  55.     	
  56.     	Commit;
  57.     End Loop;
  58.     
  59.     END;
  63. the above code take long run issue need to rectify the issue with simple and easy.
  65. </details>
  68. # 答案1
  69. **得分**: 2
  71. 你不需要使用循环，可以使用单个UPDATE语句来运行它。除此之外，你应该切换到现代（已经存在超过20年）的ANSI连接语法。
  73. ```sql
  74. UPDATE @table_name SET ENTRP_CUST_ID_NUM = ( 
  75.     SELECT mfu_source.Enterprise_Customer_ID 
  76.     FROM {{sourceTableLatest('Aggregation[table]')}} PARTITION ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG
  77.     JOIN {{sourceTableLatest('mfu_table')}} PARTITION ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source ON mfu_source.TA_LINK_ID = AGG.TRD_REF_NUM OR mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
  78.     WHERE TRIM(AGG.ENTRP_CUST_ID_NUM) IS NULL 
  79.     AND TRD_REF_NUM = AGG.TRD_REF_NUM)
  80. WHERE EXISTS 
  81.     (SELECT 'x' 
  82.     FROM {{sourceTableLatest('Aggregation[table]')}} PARTITION ({{getSourcePartitionNameLatest('Aggregation[table]')}}) AGG
  83.     JOIN {{sourceTableLatest('mfu_table')}} PARTITION ({{getSourcePartitionNameLatest('mfu_table')}}) mfu_source ON mfu_source.TA_LINK_ID = AGG.TRD_REF_NUM OR mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
  84.     WHERE TRIM(AGG.ENTRP_CUST_ID_NUM) IS NULL 
  85.     AND TRD_REF_NUM = AGG.TRD_REF_NUM)
英文:

You don't need a loop, you can run it with a single UPDATE statement. Apart from that, you should switch to modern (well, it exists for more than 20 years) ANSI join syntax.

  1. UPDATE @table_name SET ENTRP_CUST_ID_NUM = ( 
  2. SELECT mfu_source.Enterprise_Customer_ID 
  3. FROM {{sourceTableLatest(&#39;Aggregation[table]&#39;)}} PARTITION ({{getSourcePartitionNameLatest(&#39;Aggregation[table]&#39;)}}) AGG
  4. JOIN {{sourceTableLatest(&#39;mfu_table&#39;)}} PARTITION ({{getSourcePartitionNameLatest(&#39;mfu_table&#39;)}}) mfu_source ON mfu_source.TA_LINK_ID = AGG.TRD_REF_NUM OR mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
  5. WHERE TRIM(AGG.ENTRP_CUST_ID_NUM) IS NULL 
  6. AND TRD_REF_NUM = Agg.TRD_REF_NUM)
  7. WHERE EXISTS 
  8. (SELECT &#39;x&#39; 
  9. FROM {{sourceTableLatest(&#39;Aggregation[table]&#39;)}} PARTITION ({{getSourcePartitionNameLatest(&#39;Aggregation[table]&#39;)}}) AGG
  10. JOIN {{sourceTableLatest(&#39;mfu_table&#39;)}} PARTITION ({{getSourcePartitionNameLatest(&#39;mfu_table&#39;)}}) mfu_source ON mfu_source.TA_LINK_ID = AGG.TRD_REF_NUM OR mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
  11. WHERE TRIM(AGG.ENTRP_CUST_ID_NUM) IS NULL 
  12. AND TRD_REF_NUM = Agg.TRD_REF_NUM)

答案2

得分: 1

你可以使用单个 MERGE 语句（而不是循环中的多个语句）：

  1. 申明
  2. l_status varchar2(10);
  3. 开头
  4. 插入到 @table_name (ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD)
  5. 选择 ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD 
  6. 从   {{sourceTableLatest(&#39;Aggregation[table]&#39;)}} 分区 ({{getSourcePartitionNameLatest(&#39;Aggregation[table]&#39;)}});
  7. 合并到 @table_name dst
  8. 使用 (
  9. 选择 mfu_source.Customer_Surrogate_ID CUST_SROGT_ID,
  10. Agg.TRD_REF_NUM TRD_REF_NUM,
  11. mfu_source.Enterprise_Customer_ID ENTRP_CUST_ID_NUM
  12. 从   {{sourceTableLatest(&#39;Aggregation[table]&#39;)}} 分区 ({{getSourcePartitionNameLatest(&#39;Aggregation[table]&#39;)}}) AGG
  13. 内连接 {{sourceTableLatest(&#39;mfu_table&#39;)}} 分区 ({{getSourcePartitionNameLatest(&#39;mfu_table&#39;)}}) mfu_source
  14. 在    mfu_source.TA_LINK_ID     = AGG.TRD_REF_NUM
  15. 或 mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
  16. 其中  去除空格(AGG.ENTRP_CUST_ID_NUM) 是 空
  17. ) src
  18. 在 dst.TRD_REF_NUM = src.TRD_REF_NUM
  19. 当 匹配时 然后
  20. 更新
  21. 设置 dst.ENTRP_CUST_ID_NUM = src.ENTRP_CUST_ID_NUM;
  22. 提交;
  23. 结尾;
  24. /
英文:

You can use a single MERGE statement (rather than multiple statements in a loop):

  1. Declare
  2. l_status varchar2(10);
  3. Begin
  4. Insert into @table_name (ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD)
  5. Select ENTRP_CUST_ID_NUM, CUST_SROGT_ID, TRD_REF_NUM, CNTRY_CD 
  6. from   {{sourceTableLatest(&#39;Aggregation[table]&#39;)}} partition ({{getSourcePartitionNameLatest(&#39;Aggregation[table]&#39;)}});
  7. MERGE INTO @table_name dst
  8. USING (
  9. Select mfu_source.Customer_Surrogate_ID CUST_SROGT_ID,
  10. Agg.TRD_REF_NUM TRD_REF_NUM,
  11. mfu_source.Enterprise_Customer_ID ENTRP_CUST_ID_NUM
  12. from   {{sourceTableLatest(&#39;Aggregation[table]&#39;)}} partition ({{getSourcePartitionNameLatest(&#39;Aggregation[table]&#39;)}}) AGG
  13. INNER JOIN {{sourceTableLatest(&#39;mfu_table&#39;)}} partition ({{getSourcePartitionNameLatest(&#39;mfu_table&#39;)}}) mfu_source
  14. ON    mfu_source.TA_LINK_ID     = AGG.TRD_REF_NUM
  15. or mfu_source.TRANSACTION_ID = AGG.TRD_REF_NUM
  16. Where  trim(AGG.ENTRP_CUST_ID_NUM) is null
  17. ) src
  18. ON dst.TRD_REF_NUM = src.TRD_REF_NUM
  19. WHEN MATCHED THEN
  20. UPDATE
  21. SET dst.ENTRP_CUST_ID_NUM = src.ENTRP_CUST_ID_NUM;
  22. Commit;
  23. END;
  24. /

huangapple
  • 本文由 发表于 2023年6月19日 15:13:23
  • 转载请务必保留本文链接：https://go.coder-hub.com/76504373.html
  • oracle
  • oracle11g
  • plsql
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定