oracle11g - 在Oracle中插入一百万行的最快方法(Fastest way to insert a million rows in Oracle)

Question

How can I insert more than a million rows in Oracle in optimal way for the following procdeure?

(如何针对以下过程以最佳方式在Oracle中插入超过一百万行？)

It hangs if I increase FOR loop to a million rows.

(如果将FOR循环增加到一百万行，它将挂起。)

create or replace procedure inst_prc1 as
   xssn number;
   xcount number;
   l_start Number;
   l_end Number;
   cursor c1 is select max(ssn)S1 from dtr_debtors1;

Begin
  l_start := DBMS_UTILITY.GET_TIME;
  FOR I IN 1..10000 LOOP
    For C1_REC IN C1 Loop
      insert into dtr_debtors1(SSN) values (C1_REC.S1+1);
    End loop;
  END LOOP;
  commit;
  l_end := DBMS_UTILITY.GET_TIME;
  DBMS_OUTPUT.PUT_LINE('The Procedure  Start Time is '||l_start);
  DBMS_OUTPUT.PUT_LINE('The Procedure End Time is '||l_end); 
End inst_prc1;

  ask by user1016594 translate from so

Answer 1

Your approach will lead to memory issues.

(您的方法将导致内存问题。)

Fastest way will be this [Query edited after David's comment to take care of null scenario] :

(最快的方法是这样[在David的评论后编辑查询以解决空情况]：)

insert into dtr_debtors1(SSN)
select a.S1+level
   from dual,(select nvl(max(ssn),0) S1 from dtr_debtors1) a
connect by level <= 10000 

A select insert is the fastest approach as everything stays in RAM.

(选择插入是最快的方法，因为所有内容都保留在RAM中。)

This query can become slow if it slips into Global temp area but then that needs DB tuning .

(如果该查询进入“全局”临时区域，则可能会变慢，但随后需要进行数据库调整。)

I don't think there can be anything faster than this.

(我认为没有什么比这更快的了。)

Few more details on memory use by Query:

(有关查询使用的内存的更多详细信息：)

Each query will have its own PGA [Program global area] which is basically RAM available to each query.

(每个查询将具有自己的PGA（程序全局区域），基本上，每个查询都可以使用RAM。)

If this this area is not sufficient to return query results then SQL engine starts using Golabl temp tablespace which is like hard disk and query starts becoming slow.

(如果此区域不足以返回查询结果，则SQL引擎将开始使用类似于硬盘的Golabl临时表空间，并且查询开始变慢。)

If data needed by query is so huge that even temp area is not sufficient then you will tablespace error.

(如果查询所需的数据如此之大，以至于临时区域都不足够，那么您将产生表空间错误。)

So always design query so that it stays in PGA else its a Red flag.

(因此，始终将查询设计为使其保留在PGA中，否则将保留其红色标记。)