将数据插入 Oracle 表的最快方法是什么？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/143872/
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
What is the fastest way to insert data into an Oracle table?
提问by JoshL
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.
我正在用 PL/SQL 编写一个数据转换来处理数据并将其加载到表中。根据 PL/SQL Profiler，转换中最慢的部分之一是实际插入目标表。该表只有一个索引。
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;
insert into mytable values r_myRow;
It seems that I could gain performance by doing the following:
似乎我可以通过执行以下操作来获得性能：
- Turn logging off during the insert
- Insert multiple records at once
- 在插入期间关闭注销
- 一次插入多条记录
Are these methods advisable? If so, what is the syntax?
这些方法是否可取？如果是这样，语法是什么？
回答by CaptainPicard
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 表和 FORALL 绑定到插入语句。有关这方面的详细信息，请参见此处。
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.
还要注意构建 PL/SQL 表的方式。如果可能的话，最好使用“INSERT INTO t1 SELECT ...”直接在 SQL 中执行所有转换，因为在 PL/SQL 中执行逐行操作仍然比 SQL 慢。
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 manualfirst.
在任何一种情况下，您也可以通过 using 使用直接路径插入INSERT /*+APPEND*/，它基本上绕过 DB 缓存并直接分配新块并将其写入数据文件。这还可以减少日志记录量，具体取决于您的使用方式。这也有一些影响，所以请先阅读精美的手册。
Finally, if you are truncating and rebuilding the table it may be worthwhile to first drop (or mark unusable) and later rebuild indexes.
最后，如果您要截断和重建表，最好先删除（或标记为不可用），然后再重建索引。
回答by Joeri Sebrechts
Regular insert statements are the slowest way to get data in a table and not meant for bulk inserts. The following article references a lot of different techniques for improving performance: http://www.dba-oracle.com/oracle_tips_data_load.htm
常规插入语句是在表中获取数据最慢的方法，不适用于批量插入。以下文章引用了许多用于提高性能的不同技术：http: //www.dba-oracle.com/oracle_tips_data_load.htm
回答by MusiGenesis
If dropping the index doesn't speed things up enough, you need the Oracle SQL*Loader:
如果删除索引的速度不够快，则需要Oracle SQL*Loader：
http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html
http://www.oracle.com/technology/products/database/utilities/htdocs/sql_loader_overview.html
回答by skk
Suppose you have taken eid,ename,sal,job. So create a table first as:
假设你参加了 eid、ename、sal、job。所以首先创建一个表：
SQL>create table tablename(eid number, ename varchar2(20),sal number,job char(10));
Now insert data:-
现在插入数据：-
SQL>insert into tablename values(&eid,'&ename',&sal,'&job');
回答by pbaniya04
Here are my recommendations on fast insert.
这是我对快速插入的建议。
Trigger - Disable any triggers associated with a table. Enable after Inserts are complete.
触发器 - 禁用与表关联的任何触发器。插入完成后启用。
Index - Drop Index and re-create it after your Inserts are complete.
索引 - 删除索引并在插入完成后重新创建它。
Stale stats - Re-analyze table and index stats.
陈旧的统计数据 - 重新分析表和索引统计数据。
Index de-fragmentation - Rebuild Index if needed Use No Logging -Insert using INSERT APPEND (Oracle only). This approach is very risky approach, no redo logs are generated therefore you can't do a rollback - make a backup of table before you start and don't try on live tables. Check if your db has similar option
索引碎片整理 - 如果需要，重建索引使用无日志记录 - 使用 INSERT APPEND 插入（仅限 Oracle）。这种方法是非常冒险的方法，不会生成重做日志，因此您无法进行回滚 - 在开始之前备份表，不要尝试实时表。检查您的数据库是否有类似的选项
Parallel Insert: Running parallel insert will get the job faster.
并行插入：运行并行插入将更快地完成工作。
Use Bulk Insert Constraints - Not much overhead during inserts but still a good idea to check, if it is still slow after even after step 1
使用批量插入约束 - 插入期间的开销不大，但仍然是一个好主意，检查一下，即使在第 1 步之后它仍然很慢
You can learn more on http://www.dbarepublic.com/2014/04/slow-insert.html
您可以在http://www.dbarepublic.com/2014/04/slow-insert.html上了解更多信息
回答by MusiGenesis
Drop the index, then insert the rows, then re-create the index.
删除索引，然后插入行，然后重新创建索引。
回答by Kalpesh Patel
Check this link http://www.dba-oracle.com/t_optimize_insert_sql_performance.htm
检查此链接 http://www.dba-oracle.com/t_optimize_insert_sql_performance.htm
- main points to consider for your case is to use Append hint as this will directly append into the table instead of using freelist. If you can afford to turn off logging than use append with nologging hint to do it
- Use a bulk insert instead instead of iterating in PL/SQL
- Use sqlloaded to load the data directly into the table if you are getting data from a file feed
- 您的情况要考虑的要点是使用附加提示，因为这将直接附加到表中，而不是使用空闲列表。如果您有能力关闭日志记录，则不要使用带有 nologging 提示的 append 来执行此操作
- 在 PL/SQL 中使用批量插入代替迭代
- 如果您从文件提要中获取数据，请使用 sqlloaded 将数据直接加载到表中
回答by Rollo Tomazzi
Maybe one of your best option is to avoid Oracle as much as possible actually. I've been baffled by this myself, but very often a Java process can outperform many of the Oracle's utilities which either use OCI (read: SQL Plus) or will take up so much of your time to get right (read: SQL*Loader).
也许您最好的选择之一是实际上尽可能地避免使用 Oracle。我自己对此感到困惑，但 Java 进程通常可以胜过许多 Oracle 实用程序，这些实用程序要么使用 OCI（阅读：SQL Plus），要么会占用您大量时间来正确处理（阅读：SQL*Loader ）。
This doesn't prevent you to use specific hints either (like /APPEND/).
这也不妨碍您使用特定的提示（例如 / APPEND/）。
I've been pleasantly surprised each time I've turned to that kind of solution.
每次我转向这种解决方案时，我都感到惊喜。
Cheers,
干杯，
Rollo
罗洛
