sql - 批量收集百万行以插入……缺少行？

Question

我想将游标中的所有行插入到表中。但它并没有插入所有行。只插入了一些行。请帮忙 我创建了一个过程 BPS_SPRDSHT，它将输入作为 3 个参数。

PROCEDURE BPS_SPRDSHT(p_period_name VARCHAR2,p_currency_code VARCHAR2,p_source_name VARCHAR2)
IS          
     CURSOR c_sprdsht
     IS
     SELECT gcc.segment1 AS company, gcc.segment6 AS prod_seg, gcc.segment2 dept,
       gcc.segment3 accnt, gcc.segment4 prd_grp, gcc.segment5 projct,
       gcc.segment7 future2, gljh.period_name,gljh.je_source,NULL NULL1,NULL NULL2,NULL NULL3,NULL NULL4,gljh.currency_code Currency, 
       gjlv.entered_dr,gjlv.entered_cr, gjlv.accounted_dr, gjlv.accounted_cr,gljh.currency_conversion_date,
       NULL NULL6,gljh.currency_conversion_rate ,NULL NULL8,NULL NULL9,NULL NULL10,NULL NULL11,NULL NULL12,NULL NULL13,NULL NULL14,NULL NULL15,
       gljh.je_category ,NULL NULL17,NULL NULL18,NULL NULL19,tax_code
  FROM gl_je_lines_v gjlv, gl_code_combinations gcc, gl_je_headers gljh
 WHERE gjlv.code_combination_id = gcc.code_combination_id
   AND gljh.je_header_id = gjlv.je_header_id
   AND gljh.currency_code!='STAT'
   AND gljh.currency_code=NVL (p_currency_code, gljh.currency_code)
   AND gljh.period_name =  NVL (p_period_name, gljh.period_name)
   AND gljh.je_source LIKE  p_source_name||'%';

type t_spr is table of c_sprdsht%rowtype;
v_t_spr   t_spr :=t_spr();

BEGIN 
OPEN c_sprdsht;
LOOP
FETCH c_sprdsht BULK COLLECT INTO v_t_spr limit 50000;
EXIT WHEN c_sprdsht%notfound;
END LOOP;
CLOSE c_sprdsht;



FND_FILE.PUT_LINE(FND_FILE.OUTPUT,'TOTAL ROWS FETCHED FOR SPREADSHEETS- '|| v_t_spr.count);


    IF v_t_spr.count > 0 THEN
        BEGIN
        FORALL I IN v_t_spr.FIRST..v_t_spr.LAST SAVE EXCEPTIONS
            INSERT INTO custom.pwr_bps_gl_register 
            VALUES v_t_spr(i);
        EXCEPTION
            WHEN OTHERS THEN
                l_error_count := SQL%BULK_EXCEPTIONS.count;
                     fnd_file.put_line(fnd_file.output,'Number of failures: ' || l_error_count);
                      FOR l IN 1 .. l_error_count LOOP
                            DBMS_OUTPUT.put_line('Error: ' || l || 
                                ' Array Index: ' || SQL%BULK_EXCEPTIONS(l).error_index ||
                                    ' Message: ' || SQLERRM(-SQL%BULK_EXCEPTIONS(l).ERROR_CODE));
                      END LOOP;
        END;  



    END IF;   

fnd_file.put_line(fnd_file.output,'END TIME: '||TO_CHAR (SYSDATE, 'DD-MON-YYYY HH24:MI:SS'));
END BPS_SPRDSHT;

要插入的总行数=568388插入的行数=48345。

Answer 1

Oracle 使用两个引擎来处理 PL/SQL 代码。所有过程代码均由 PL/SQL 引擎 处理，而所有 SQL 均由 SQL 语句执行器或 SQL 引擎 处理。两个引擎之间的每个上下文切换都会产生开销。

整个 PL/SQL 代码可以用普通的 SQL 编写，这样会更快并且更少的代码>.

INSERT INTO custom.pwr_bps_gl_register
SELECT gcc.segment1 AS company,
  gcc.segment6      AS prod_seg,
  gcc.segment2 dept,
  gcc.segment3 accnt,
  gcc.segment4 prd_grp,
  gcc.segment5 projct,
  gcc.segment7 future2,
  gljh.period_name,
  gljh.je_source,
  NULL NULL1,
  NULL NULL2,
  NULL NULL3,
  NULL NULL4,
  gljh.currency_code Currency,
  gjlv.entered_dr,
  gjlv.entered_cr,
  gjlv.accounted_dr,
  gjlv.accounted_cr,
  gljh.currency_conversion_date,
  NULL NULL6,
  gljh.currency_conversion_rate ,
  NULL NULL8,
  NULL NULL9,
  NULL NULL10,
  NULL NULL11,
  NULL NULL12,
  NULL NULL13,
  NULL NULL14,
  NULL NULL15,
  gljh.je_category ,
  NULL NULL17,
  NULL NULL18,
  NULL NULL19,
  tax_code
FROM gl_je_lines_v gjlv,
  gl_code_combinations gcc,
  gl_je_headers gljh
WHERE gjlv.code_combination_id = gcc.code_combination_id
AND gljh.je_header_id          = gjlv.je_header_id
AND gljh.currency_code!        ='STAT'
AND gljh.currency_code         =NVL (p_currency_code, gljh.currency_code)
AND gljh.period_name           = NVL (p_period_name, gljh.period_name)
AND gljh.je_source LIKE p_source_name
  ||'%';

更新

在 PL/SQL 中**频繁提交*有利于性能是一个神话。

Thomas Kyte 解释得很漂亮 here :

Frequent commits -- sure, "frees up" that undo -- which invariabley leads to ORA-1555 and the failure of your process. Thats good for performance right?

Frequent commits -- sure, "frees up" locks -- which throws transactional integrity out the window. Thats great for data integrity right?

Frequent commits -- sure "frees up" redo log buffer space -- by forcing you to WAIT for a sync write to the file system every time -- you WAIT and WAIT and WAIT. I can see how that would "increase performance" (NOT). Oh yeah, the fact that the redo buffer is flushed in the background

• every three seconds
• when 1/3 full
• when 1meg full

would do the same thing (free up this resource) AND not make you wait.

• frequent commits -- there is NO resource to free up -- undo is undo, big old circular buffer. It is not any harder for us to manage 15 gigawads or 15 bytes of undo. Locks -- well, they are an attribute of the data itself, it is no more expensive in Oracle (it would be in db2, sqlserver, informix, etc) to have one BILLION locks vs one lock. The redo log buffer -- that is continously taking care of itself, regardless of whether you commit or not.