Oracle 多插入语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6035924/
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
Oracle multi insert statement
提问by Eduardo Mauro
In my application I have to add many records. I am using the following construct:
在我的应用程序中，我必须添加许多记录。我正在使用以下结构：
INSERT /*+ append parallel(t1, 4) parallel(t2, 4) */ ALL
INTO t1 (col1, col2, col3) VALUES ('val1_1', 'val1_2', 'val1_3')
INTO t2 (col1, col2, col3) VALUES ('val2_1', 'val2_2', 'val2_3')
INTO t2 (col1, col2, col3) VALUES ('val3_1', 'val3_2', 'val3_3')
.
.
.
SELECT 1 FROM DUAL;
I am also using APPEND and PARALLEL hints. Notice that I am inserting data in two different tables. It seems that parallel is being ignored (the DBA told me). So how I can know if it is being used or not? Is it possible to use PARALLEL hint in such construct? Is it effective?
我也在使用 APPEND 和 PARALLEL 提示。请注意，我在两个不同的表中插入数据。似乎并行被忽略了（DBA 告诉我的）。那么我怎么知道它是否正在被使用呢？是否可以在这样的构造中使用 PARALLEL 提示？有效吗？
采纳答案by Jon Heller
This will probably be enough to get it to work:
这可能足以让它工作：
alter session enable parallel dml;
You can check the actual degree of parallelism with a query like this:
您可以使用这样的查询来检查实际的并行度：
select px_servers_executions, v$sql.*
from v$sql where lower(sql_text) like '%insert%parallel%' order by last_load_time desc;
If you're still not getting parallelism there are many possible reasons. To start, look at these parameters:
如果您仍然没有获得并行性，则有很多可能的原因。首先，看看这些参数：
select * from v$parameter where name like 'parallel%'
But you probably don't want parallelism for your insert statement. Parallelism has a a large amount of overhead, and generally is only useful if you're dealing with many thousands or millions of records.
但是您可能不希望插入语句具有并行性。并行性有大量开销，通常只有在处理数千或数百万条记录时才有用。
I'm guessing your real problem is the time to parse the large SQL statement. Multi-table inserts are especially bad. If you try to insert more than a few hundred rows your query will take many seconds for parsing. And depending on your version of Oracle, it will just hang forever if you try to use 501 tables. It's much faster to run several smaller queries instead of one large query. For example, 5 inserts of 100 rows will run much faster than one insert of 500 rows. (In general this is the exact opposite of how to performance tune for Oracle. This is a special case because of the bugs related to parsing large SQL statements.)
我猜你真正的问题是解析大型 SQL 语句的时间。多表插入尤其糟糕。如果您尝试插入超过几百行，您的查询将花费数秒进行解析。根据您的 Oracle 版本，如果您尝试使用 501 表，它将永远挂起。运行几个较小的查询而不是一个大的查询要快得多。例如，5 次 100 行插入比一次 500 行插入运行得快得多。（通常，这与如何为 Oracle 进行性能调优完全相反。这是一种特殊情况，因为与解析大型 SQL 语句相关的错误。）
回答by xorinox
The APPEND hintis only supported with the subquery syntax of the INSERT statement, not the VALUES clause. If you specify the APPEND hint with the VALUES clause, it is ignored and conventional insert will be used. To use direct-path INSERT with the VALUES clause, refer to "APPEND_VALUES Hint".
该APPEND提示仅与INSERT语句的子查询的语法，而不是VALUES子句的支持。如果您使用 VALUES 子句指定 APPEND 提示，它将被忽略并使用常规插入。要将直接路径 INSERT 与 VALUES 子句一起使用，请参阅“APPEND_VALUES 提示”。
回答by eaolson
There are cases where parallelism is disabled. Including, from the Oracle documentation:
有些情况下并行性被禁用。包括，来自 Oracle 文档：
Parallelism is disabled for DML operations on tables on which you have defined a trigger or referential integrity constraint.
对于已在其上定义触发器或参照完整性约束的表的 DML 操作，并行性被禁用。
Which seems like a pretty big restriction to me. Do you have a trigger or foreign key on your tables?
这对我来说似乎是一个很大的限制。你的表上有触发器或外键吗？
回答by Vincent Malgrat
Enabling parallelism for 30 records would be a waste of resource. Parallelism involves an expensive overhead (splitting work, assigning processes, synchronizing results...) that would not be worth the cost for such a small operation.
为 30 条记录启用并行性将是一种资源浪费。并行涉及昂贵的开销（拆分工作、分配进程、同步结果......），对于如此小的操作来说不值得付出代价。
I suppose you are executing this statement millions of times if you want to optimize it that bad. In that case it would probably more efficient to find a way to turn these million statements into big set operations -- that could nicely profit from parallelism.
如果你想优化它，我想你会执行数百万次这个语句。在这种情况下，找到一种方法将这些百万语句转换为大型集合操作可能会更有效——这可以很好地从并行性中受益。
Update: Another idea might be to run the statements with more than one session, effectively implementing DIY parallelism. Can you design your process so that more than one session read the input data and insert at the same time?
更新：另一个想法可能是用多个会话运行语句，有效地实现 DIY 并行性。您能否设计您的流程，让多个会话同时读取输入数据并插入？
