如何提高oracle批量插入效率

[kevincom]

我有一个日志分析程序，将文本文件中的记录分析后插入到数据库，每天需要插入记录条数目前是一千五百万，这个数据还在不断增长，程序使用的是java多线程来处理，请问每次批量插入多少条记录是效率最高的，我试过一次批量插入1000、500、200、100、50、20,速度都不是很快，按现在的速度每天最多也就只能处理到 1800-2000万，请问我如何来做？

[iqlife]

declare 
type id_table_type is table of number(6)
index by binary_integer;
type name_table_type is table of varchar2(10)
index by binary_integer;
id_table id_table_type;
name_table name_table_type;
start_time number(10);
end_time number(10);
begin
for i in 1..5000 loop 
id_table(i):=i;
name_table(i) := 'name'||i;
end loop;
start_time :=dbms_utility.get_time;
for i in 1..id_table.count loop
insert into demo values(id_table(i),name_table(i));
end loop;
end_time := dbms_utility.get_time;
dbms_output.put_line('不使用批量插入数据总计时间:'||to_char((end_time-start_time)/100));
for i in 5001..10000 loop 
id_table(i):=i;
name_table(i) := 'name'||i;
end loop;
start_time :=dbms_utility.get_time;
forall i in 5001..id_table.count
insert into demo values(id_table(i),name_table(i));
end_time := dbms_utility.get_time;
dbms_output.put_line('使用批量插入数据总计时间:'||to_char((end_time-start_time)/100));
for i in 5001..10000 loop 
id_table(i):=i;
name_table(i) := 'N'||i;
end loop;
start_time :=dbms_utility.get_time;
forall i in 5001..id_table.count
update demo  set name=name_table(i) where id =id_table(i);
end_time := dbms_utility.get_time;
dbms_output.put_line('使用批量更新数据总计时间:'||to_char((end_time-start_time)/100));
for i in 1..10000 loop 
id_table(i):=i;
end loop;
start_time :=dbms_utility.get_time;
forall i in 1..id_table.count
DELETE FROM demo  where id=id_table(i);
end_time := dbms_utility.get_time;
dbms_output.put_line('使用批量删除数据总计时间:'||to_char((end_time-start_time)/100));
end;
运行结果: 不使用批量插入总计时间:.96
使用批量插入总计时间:.04
使用批量更新总计时间:.64
使用批量删除总计时间:2.98

昨天才学习的批量插入,以上是在我机器上执行的时间,字段为2个
机器配置内存1.5G,CPU P4 3.0

[luyi6101]

  FETCH v_rows BULK COLLECT
      INTO v_rowidarray, v_fff LIMIT 10000;
    FORALL i IN 1 .. v_rowidarray.count()

[YY_MM_DD]

from suziguo
是否可以考虑2点

1 使用Parallel（需要多处理器）
2 使用Nologging（恢复时可能会出问题）

做完好备份下数据库，以免nologging操作无法恢复。

[阿三]

可不可以利用外部表直接与txt文件关联操作

[kevincom]

引用 7 楼 zxf_feng 的回复:

可不可以利用外部表直接与txt文件关联操作

能说具体一些吗？

[kevincom]

我看了一下oracle外部表的相关描述，好像实现起来不太容易呢，我的日志文件和数据库不在同一台服务器，日志文件是在java应用程序里用logback打出来的，每天自动备份成按天的文件，对于日志文件入库后还要做统计分析等工作，如果用外部表，好像不是很容易实现这些功能

[Dave]

2. 假如tab1表中的没有数据的话
DROP TABLE TAB1;
CREATE TABLE TAB1 AS SELECT * FROM TAB2;
然后在创建索引

3. 用Hint 提示减少操作时间

INSERT /*+Append*/ INTO tab1
SELECT * FROM tab2;

4. 采用不写日志及使用Hint提示减少数据操作的时间。

建议方案是先修改表为不写日志：
sql> alter table table_name NOLOGGING;

插入数据：

INSERT /*+Append*/ INTO tab1
SELECT * FROM tab2;

插入完数据后，再修改表写日志：
sql> alter table table_name LOGGING;

这里的区别就在于如果插入数据的同时又写日志，尤其是大数据量的insert操作，需要耗费较长的时间。

5. 用EXP/IMP 处理大量数据

(1)给当前的两个表分别改名
alter table tab1 rename to tab11;
alter table tab2 rename to tab1;
(2)导出改名前的tab2
exp user/pwd@... file=... log=... tables=(tab1)
(3)把名字改回来
alter table tab1 rename to tab2;
alter table tab11 rename to tab1;
(4)导入数据
imp user/pwd@... file=... log=... fromuser=user touser=user tables=(tab1)


Oracle 插入大量数据
http://blog.csdn.net/tianlesoftware/archive/2009/10/30/4745144.aspx


------------------------------------------------------------------------------
Blog： http://blog.csdn.net/tianlesoftware
网上资源： http://tianlesoftware.download.csdn.net
相关视频：http://blog.csdn.net/tianlesoftware/archive/2009/11/27/4886500.aspx
Q Q 群：62697716

[kevincom]

谢谢，让插入表在插入时不写日志有助提升插入速度是吧，我试试

我不是从一个表导入另一个表，我是从文本文件分析数据，批量插入到数据库

[doveli]

学习了，多谢
学习了，多谢
学习了，多谢
学习了，多谢

[kingstarer]

不要索引(停索引插数据，插完再开索引) 用hint 提高服务器性能 使用绑定变量