怎样将内存中的大批记录插入到oracle表中效率最高？

Question

现在我采用 insert into table values()一次插入一条记录，这样十分钟只能写下30多万条的。
而我将它写入文件中只需要一分钟不到就能写下的。

Answer 1

参考pl/sql的BULK INSERT 方法所用时间为普通INSERT的3/20

Answer 2

对于插入数据这样的东东，我很感兴趣：）
Waiting......

Answer 3

刚刚开始一个新的项目:这个系统中有一个表,目前是9600万纪录(全部完成以后应该是1.5亿条纪录)每天要从一个INSTANCE传到另一个,用的是文件传输.
目前的运行状态是用SQLLDR倒入960万行纪录用4分钟(INDEX预先DISABLE掉),然后REBUILD INDEX用15-20分钟.服务器配置: IBM RS6000 H80, 18个CPU, 32GB RAM, 655GB HDD

Answer 4

kingsumsha问一个简单问题：
你说先把index disable 掉，怎么才能把它disable掉呢？？？

Answer 5

不好意思,记错了,是bulk binds.所用时间为普通INSERT的3/32
请看:
How Do Bulk Binds Improve Performance?
The assigning of values to PL/SQL variables in SQL statements is called binding.
The binding of an entire collection at once is called bulk binding. Bulk binds improve
performance by minimizing the number of context switches between the PL/SQL
and SQL engines. With bulk binds, entire collections, not just individual elements,
are passed back and forth. For example, the following DELETE statement is sent to
the SQL engine just once, with an entire nested table:
DECLARE
TYPE NumList IS VARRAY(20) OF NUMBER;
depts NumList := NumList(10, 30, 70); -- department numbers
BEGIN
...
FORALL i IN depts.FIRST..depts.LAST
DELETE FROM emp WHERE deptno = depts(i);
END;
In the example below, 5000 part numbers and names are loaded into index-by
tables. Then, all table elements are inserted into a database table twice. First, they
are inserted using a FOR loop, which completes in 32 seconds. Then, they are
bulk-inserted using a FORALL statement, which completes in only 3 seconds.
SQL> SET SERVEROUTPUT ON
SQL> CREATE TABLE parts (pnum NUMBER(4), pname CHAR(15));
Table created.
SQL> GET test.sql
1 DECLARE
2 TYPE NumTab IS TABLE OF NUMBER(4) INDEX BY BINARY_INTEGER;
3 TYPE NameTab IS TABLE OF CHAR(15) INDEX BY BINARY_INTEGER;
4 pnums NumTab;
5 pnames NameTab;
6 t1 NUMBER(5);
7 t2 NUMBER(5);
8 t3 NUMBER(5);
9 PROCEDURE get_time (t OUT NUMBER) IS
10 BEGIN SELECT TO_CHAR(SYSDATE,’SSSSS’) INTO t FROM dual; END;
11 BEGIN
12 FOR j IN 1..5000 LOOP -- load index-by tables
13 pnums(j) := j;
14 pnames(j) := ’Part No. ’ || TO_CHAR(j);
15 END LOOP;
16 get_time(t1);
17 FOR i IN 1..5000 LOOP -- use FOR loop
18 INSERT INTO parts VALUES (pnums(i), pnames(i));
19 END LOOP;
20 get_time(t2);
21 FORALL i IN 1..5000 -- use FORALL statement
22 INSERT INTO parts VALUES (pnums(i), pnames(i));
23 get_time(t3);
24 DBMS_OUTPUT.PUT_LINE(’Execution Time (secs)’);
25 DBMS_OUTPUT.PUT_LINE(’---------------------’);
26 DBMS_OUTPUT.PUT_LINE(’FOR loop: ’ || TO_CHAR(t2 - t1));
27 DBMS_OUTPUT.PUT_LINE(’FORALL: ’ || TO_CHAR(t3 - t2));
28* END;
SQL> /
Execution Time (secs)
---------------------
FOR loop: 32
FORALL: 3
PL/SQL procedure successfully completed.
To bulk-bind input collections, you use the FORALL statement. To bulk-bind output
collections, you use the BULK COLLECT clause.

Answer 6

用bulk-bind插入数据的时候，如果bind的colletion记录非常多，回滚段该怎么考虑呢？

Answer 7

是不是可以考虑前面先别在内存中存放那么多记录呢？我现在也要处理几千万的数据，可以考虑分表啊，我的按key并行取出记录，处理后成批往表里写。to  KingSunSha(弱水三千)：index disabled 是什么意思？
drop 还是 alter index disable？后者似乎有限制。

Answer 8

我觉得首先把INDEX删掉，然后重建，速度会快许多的

Answer 9

再来：
improving PL/SQL performance
Using the NOCOPY Compiler Hint
Suppose a subprogram declares an IN parameter, an OUT parameter, and an IN OUT
parameter. When you call the subprogram, the IN parameter is passed by reference.
That is, a pointer to the IN actual parameter is passed to the corresponding formal
parameter. So, both parameters reference the same memory location, which holds
the value of the actual parameter.
By default, the OUT and IN OUT parameters are passed by value. That is, the value
of the IN OUT actual parameter is copied into the corresponding formal parameter.
Then, if the subprogram exits normally, the values assigned to the OUT and IN OUT
formal parameters are copied into the corresponding actual parameters.
When the parameters hold large data structures such as collections, records, and
instances of object types, all this copying slows down execution and uses up
memory. To prevent that, you can specify the NOCOPY hint, which allows the
PL/SQL compiler to pass OUT and IN OUT parameters by reference.
In the following example, you ask the compiler to pass IN OUT parameter my_
staff by reference instead of by value:
DECLARE
TYPE Staff IS VARRAY(200) OF Employee;
PROCEDURE reorganize (my_staff IN OUT NOCOPY Staff) IS ...
Remember, NOCOPY is a hint, not a directive. So, the compiler might pass my_staff
by value despite your request. Usually, however, NOCOPY succeeds. So, it can
benefit any PL/SQL application that passes around large data structures.
In the example below, 25000 records are loaded into a local nested table, which is
passed to two local procedures that do nothing but execute NULL statements.
However, a call to one procedure takes 21 seconds because of all the copying. With
NOCOPY, a call to the other procedure takes much less than 1 second.
SQL> SET SERVEROUTPUT ON
SQL> GET test.sql
1 DECLARE
2 TYPE EmpTabTyp IS TABLE OF emp%ROWTYPE;
3 emp_tab EmpTabTyp := EmpTabTyp(NULL); -- initialize
4 t1 NUMBER(5);
5 t2 NUMBER(5);
6 t3 NUMBER(5);
7 PROCEDURE get_time (t OUT NUMBER) IS
8 BEGIN SELECT TO_CHAR(SYSDATE,’SSSSS’) INTO t FROM dual; END;
9 PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) IS
10 BEGIN NULL; END;
Using the NOCOPY Compiler Hint
11 PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
12 BEGIN NULL; END;
13 BEGIN
14 SELECT * INTO emp_tab(1) FROM emp WHERE empno = 7788;
15 emp_tab.EXTEND(24999, 1); -- copy element 1 into 2..25000
16 get_time(t1);
17 do_nothing1(emp_tab); -- pass IN OUT parameter
18 get_time(t2);
19 do_nothing2(emp_tab); -- pass IN OUT NOCOPY parameter
20 get_time(t3);
21 DBMS_OUTPUT.PUT_LINE(’Call Duration (secs)’);
22 DBMS_OUTPUT.PUT_LINE(’--------------------’);
23 DBMS_OUTPUT.PUT_LINE(’Just IN OUT: ’ || TO_CHAR(t2 - t1));
24 DBMS_OUTPUT.PUT_LINE(’With NOCOPY: ’ || TO_CHAR(t3 - t2));
25* END;
SQL> /
Call Duration (secs)
--------------------
Just IN OUT: 21
With NOCOPY: 0

Answer 10

bulk-bind和批处理结合起来使用，回滚也没有问题！

Answer 11

KingSunSha的做法比较适合于我的。
谢谢大家！
给分的。