（oracle）在存储过程中插入非常慢相比，手动插入运行 [英] (oracle) insert in stored procedure very slow compared to insert run manually

问题描述

我从Oracle sql工具运行一个插入，它从各种表中选择大约100.000行，并将它们插入到另一个表中不同的数据库中，这将在3分钟内结束。
我尝试存储过程中的相同查询或oracle中的PLSQL，我们运行大约2小时。有关信息，请使用源Oracle 11g和目标Oracle 10g

I run an insert from Oracle sql tool that selects about 100.000 rows from various tables and inserts them in another table at different databsae and this ends in 3 mins. I try the same query inside a stored procedure or PLSQL in oracle and we run for about 2 hours. For information i use Source Oracle 11g and the destination Oracle 10g

存储过程（每个商店大约需要2小时）：

Stored Procedure (takes about 2 hour per store):

CREATE OR REPLACE PROCEDURE MGS.TRANSFER_DATA(a_date in varchar2) -- yyyymmdd
BEGIN
 BEGIN
 FOR xx IN(
  SELECT STR_CD FROM MGS.STORE ORDER BY STR_CD ASC
 ) LOOP
  CALL_LOG(xx.STR_CD,a_date,sysdate,"START INSERT");
  INSERT INTO STOCK@BBS
  SELECT
   STR_CD, STK.DT, PROD_CD, QTY, ORDER_QTY, ORDER_QTY
  FROM
   MGS.STOCK STK, MGS.SALE SAL
   WHERE STK.STR_CD = xx.STR
   AND STK.STR_CD = SAL.STR_CD(+)
   AND STK.PROD_CD = SAL.PROD_CD(+)
   AND STK.DT = SAL.DT(+)
   AND STK.DT = a_date;
 CALL_LOG(xx.STR_CD,a_date,sysdate,"INSERT SUCESSFULL");
END LOOP;
END;

END TRANSFER_DATA;

END TRANSFER_DATA;

1个商店（只需3分钟）：

I tried query for 1 store (just take 3 minute):

INSERT INTO STOCK@BBS
SELECT
 STR_CD, STK.DT, PROD_CD, QTY, ORDER_QTY, ORDER_QTY
FROM
 MGS.STOCK STK, MGS.SALE SAL
 WHERE STK.STR_CD = 'STORE01'
 AND STK.STR_CD = SAL.STR_CD(+)
 AND STK.PROD_CD = SAL.PROD_CD(+)
 AND STK.DT= SAL.DT(+)
 AND STK.DT= '20120801'; -- yyyymmdd

推荐答案

在plsql中的查询变得缓慢，但我有解决方案：

I haven't got the answer why the query in plsql become slowly, but i have solution bellow :

CREATE OR REPLACE PROCEDURE MGS.TRANSFER_DATA(a_date in varchar2) -- yyyymmdd
BEGIN
 BEGIN
 FOR xx IN(
  SELECT STR_CD FROM MGS.STORE ORDER BY STR_CD ASC
 ) LOOP
  CALL_LOG(xx.STR_CD,a_date,sysdate,"START INSERT");
  execute immediate '
  INSERT INTO STOCK@BBS
  SELECT
   STR_CD, STK.DT, PROD_CD, QTY, ORDER_QTY, ORDER_QTY
  FROM
   MGS.STOCK STK, MGS.SALE SAL
   WHERE STK.STR_CD = ''' || xx.STR || '''
   AND STK.STR_CD = SAL.STR_CD(+)
   AND STK.PROD_CD = SAL.PROD_CD(+)
   AND STK.DT = SAL.DT(+)
   AND STK.DT = ''' || a_date || '''
 ';
 CALL_LOG(xx.STR_CD,a_date,sysdate,"INSERT SUCESSFULL");
END LOOP;
END;

这篇关于（oracle）在存储过程中插入非常慢相比，手动插入运行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！