在 Oracle 中插入一百万行的最快方法
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18414529/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Fastest way to insert a million rows in Oracle
提问by user1016594
How can I insert more than a million rows in Oracle in optimal way for the following procdeure? It hangs if I increase FOR loop to a million rows.
对于以下过程，如何以最佳方式在 Oracle 中插入超过一百万行？如果我将 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;
回答by Lokesh
Your approach will lead to memory issues. Fastest way will be this [Query edited after David's comment to take care of null scenario] :
您的方法将导致内存问题。最快的方法是[在大卫的评论后编辑查询以处理空场景]：
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. 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.
选择插入是最快的方法，因为所有内容都保留在 RAM 中。如果此查询滑入全局临时区域，则该查询可能会变慢，但这需要 DB 调整。我认为没有比这更快的了。
Few more details on memory use by Query:
关于 Query 内存使用的更多细节：
Each query will have its own PGA [Program global area] which is basically RAM available to each query. 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. If data needed by query is so huge that even temp area is not sufficient then you will tablespace error.
每个查询都有自己的 PGA [程序全局区域]，它基本上是每个查询可用的 RAM。如果这个区域不足以返回查询结果，那么 SQL 引擎开始使用 Golabl 临时表空间，就像硬盘一样，查询开始变慢。如果查询所需的数据如此之大，以至于临时区域都不够用，那么您将出现表空间错误。
So always design query so that it stays in PGA else its a Red flag.
所以总是设计查询，使其留在 PGA 中，否则它是一个红旗。
回答by ntalbs
Inserting one row at a time with single insertstatement within loop is slow. The fastest way is to use insert-selectlike the following, which generates a million rows and bulk insert.
insert在循环中使用单个语句一次插入一行很慢。最快的方法是insert-select像下面这样使用，它会生成一百万行和批量插入。
insert into dtr_debtors1(SSN)
select level from dual connect by level <= 1000000;
回答by Rahul Tripathi
Try to drop all the index created on your table and then try to insert using the selectquery. You can try this link which will help you in inserting millions of rowsfast into your database.
尝试删除在表上创建的所有索引，然后尝试使用select查询插入。您可以试试这个链接，它可以帮助您将数百万行快速插入到您的数据库中。
回答by Nvr
1) If you want to insert using PL/SQL, then use BULK COLLECT INTOand for insert DML use BULK BIND FOR ALL.
1) 如果要使用 PL/SQL 插入，则使用BULK COLLECT INTO和插入 DML 使用 BULK BIND FOR ALL。
2) In SQL multi insert use INSERT ALLstatement.
2) 在 SQL 中多插入使用INSERT ALL语句。
3) Another method INSERT INTO <tb_nm> SELECT.
3）另一种方法INSERT INTO <tb_nm> SELECT。
4) Use SQL LOADERUtility.
4）使用SQL LOADER实用程序。
