将数据插入 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:
插入 mytable 值 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*/ 来使用直接路径插入，它基本上绕过了DB 缓存，直接将新块分配并写入数据文件.这还可以减少日志记录量，具体取决于您的使用方式.这也有一些影响，所以请阅读 fine手动第一.
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.
相关文章