将数据插入Oracle表的最快方法是什么? [英] What is the fastest way to insert data into an Oracle table?

查看:69
本文介绍了将数据插入Oracle表的最快方法是什么?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我正在用PL/SQL编写数据转换,该数据转换处理数据并将其加载到表中.根据PL/SQL Profiler,转换最慢的部分之一是实际插入到目标表中.该表具有单个索引.

I am writing a data conversion in PL/SQL that processes data and loads it into a table. According to the PL/SQL Profiler, one of the slowest parts of the conversion is the actual insert into the target table. The table has a single index.

为准备加载数据,我使用表的行类型填充了一个变量,然后将其插入表中,如下所示:

To prepare the data for load, I populate a variable using the rowtype of the table, then insert it into the table like this:

insert into mytable values r_myRow;

执行以下操作似乎可以提高性能:

It seems that I could gain performance by doing the following:

  • 在插入过程中关闭日志记录
  • 一次插入多个记录

这些方法明智吗?如果是这样,语法是什么?

Are these methods advisable? If so, what is the syntax?

推荐答案

最好使用PL/SQL表和FORALL绑定一次到插入语句中一次插入几百行.有关详细信息,请参见此处.

It's much better to insert a few hundred rows at a time, using PL/SQL tables and FORALL to bind into insert statement. For details on this see here.

还要小心如何构造PL/SQL表.如果可能的话,最好改为使用"INSERT INTO t1 SELECT ..."直接在SQL中进行所有转换,因为在PL/SQL中进行逐行操作仍会比SQL慢.

Also be careful with how you construct the PL/SQL tables. If at all possible, prefer to instead do all your transforms directly in SQL using "INSERT INTO t1 SELECT ..." as doing row-by-row operations in PL/SQL will still be slower than SQL.

在任何一种情况下,您都可以使用INSERT /*+APPEND*/来使用直接路径插入,它基本上绕过了数据库高速缓存,并直接分配新的块并将其写入数据文件.这也可以减少日志记录的数量,具体取决于您的使用方式.这也有一些含义,因此请阅读精手动.

In either case, you can also use direct-path inserts by using INSERT /*+APPEND*/, which basically bypasses the DB cache and directly allocates and writes new blocks to data files. This can also reduce the amount of logging, depending on how you use it. This also has some implications, so please read the fine manual first.

最后,如果您要截断并重建表,可能值得先删除(或将其标记为不可用),然后再重建索引.

Finally, if you are truncating and rebuilding the table it may be worthwhile to first drop (or mark unusable) and later rebuild indexes.

这篇关于将数据插入Oracle表的最快方法是什么?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆