oracle pl/sql forall insert 和plain SQL insert 的查询性能差异
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2672764/
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
Query performance difference pl/sql forall insert and plain SQL insert
提问by Prakash
We have been using temporary table to store intermediate results in pl/sql Stored procedure. Could anyone tell if there is a performance difference between doing bulk collect insert through pl/sql and a plain SQL insert.
我们一直在使用临时表来存储 pl/sql 存储过程中的中间结果。谁能告诉通过 pl/sql 进行批量收集插入和普通 SQL 插入之间是否存在性能差异。
Insert into [Table name] [Select query Returning huge amount of data]
插入到【表名】【选择查询返回海量数据】
or
或者
Cursor for [Select query returning huge amount of data]
[选择查询返回大量数据] 的光标
open cursor
打开游标
fetch cursor bulk collect into collection
获取游标批量收集到集合中
Use FORALL to perform insert
使用 FORALL 执行插入
Which of the above 2 options is better to insert huge amount of temporary data?.
上面两个选项哪个更适合插入大量临时数据？。
回答by jva
Some experimental data for your problem (Oracle 9.2)
您的问题的一些实验数据（Oracle 9.2）
bulk collect
批量收集
DECLARE
TYPE t_number_table IS TABLE OF NUMBER;
v_tab t_number_table;
BEGIN
SELECT ROWNUM
BULK COLLECT INTO v_tab
FROM dual
CONNECT BY LEVEL < 100000;
FORALL i IN 1..v_tab.COUNT
INSERT INTO test VALUES (v_tab(i));
END;
/
-- 2.6 sec
insert
插入
-- test table
CREATE global TEMPORARY TABLE test (id number)
ON COMMIT preserve ROWS;
BEGIN
INSERT INTO test
SELECT ROWNUM FROM dual
CONNECT BY LEVEL < 100000;
END;
/
-- 1.4 sec
direct path inserthttp://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c21dlins.htm
直接路径插入http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c21dlins.htm
BEGIN
INSERT /*+ append */ INTO test
SELECT ROWNUM FROM dual
CONNECT BY LEVEL < 100000;
END;
/
-- 1.2 sec
回答by Rene
Insert into select must certainly be faster. Skips the overhead of storing the data in a collection first.
插入 select 肯定会更快。首先跳过将数据存储在集合中的开销。
回答by Jeffrey Kemp
It depends on the nature of the work you're doing to populate the intermediate results. If the work can be done relatively simply in the SELECT statement for the INSERT, that will generally perform better.
这取决于您为填充中间结果所做的工作的性质。如果可以在 INSERT 的 SELECT 语句中相对简单地完成工作，那通常会执行得更好。
However, if you have some complex intermediate logic, it may be easier (from a code maintenance point of view) to fetch and insert the data in batches using bulk collects/binds. In some cases it might even be faster.
但是，如果您有一些复杂的中间逻辑，则使用批量收集/绑定批量获取和插入数据可能更容易（从代码维护的角度来看）。在某些情况下，它甚至可能更快。
One thing to note very carefully:the query plan used by the INSERT INTO x SELECT ...will sometimes be quite different to that used when the query is run by itself (e.g. in a PL/SQL explicit cursor). When comparing performance, you need to take this into account.
需要非常仔细地注意一件事： 使用的查询计划INSERT INTO x SELECT ...有时与查询自己运行时使用的查询计划大不相同（例如，在 PL/SQL 显式游标中）。在比较性能时，您需要考虑到这一点。
回答by Kevin Meade
Tom Kyte of asktomhome fame has answered this question more firmly. If you are willing to do some searching you can find the question and his response which constains detailed testing results and explanations. He shows plsql cursor vs. plsql bulk collect including affect of periodic commit, vs. sql insert as select.
asktomhome 成名的汤姆·凯特 (Tom Kyte) 更坚定地回答了这个问题。如果你愿意做一些搜索，你可以找到包含详细测试结果和解释的问题和他的回答。他展示了 plsql 游标与 plsql 批量收集，包括定期提交的影响，与作为选择的 sql 插入。
insert as select wins hands down all the time and the difference on even modest datasets is dramatic.
insert as select 一直都胜出，即使是适度的数据集的差异也是巨大的。
That said. the comment was made earlier about the complexity of intermediary computations. I can think of three situations where this would be relevant.
那说。之前就中间计算的复杂性发表了评论。我可以想到三种相关的情况。
1) If computations require going outside of the Oracle database, then clearly a simple insert as select does not do the trick.
1) 如果计算需要在 Oracle 数据库之外进行，那么很明显，简单的插入作为选择并不能解决问题。
2) If the solution requires the use of PLSQL function calls then context switching can potentially kill your query and you may have better results with plsql calling plsql functions. PLSQl was made to call SQL but not the other way around. Thus calling PLSQL from SQL is expensive.
2) 如果解决方案需要使用 PLSQL 函数调用，则上下文切换可能会终止您的查询，并且使用 plsql 调用 plsql 函数可能会获得更好的结果。PLSQl 被用来调用 SQL，而不是相反。因此从 SQL 调用 PLSQL 是昂贵的。
3) If computations make the sql code very difficulty to read then even though it may be slower, a plsql bulk collect solution may be better for these other reasons.
3) 如果计算使 sql 代码很难阅读，那么即使它可能更慢，由于这些其他原因，plsql 批量收集解决方案可能会更好。
Good luck.
祝你好运。
回答by uma
When we declare cursor explicitly, oracle will allocate a private SQL work area in our RAM. When you have select statement that returns multiple rows will be copied from table or view to private SQL work area as ACTIVE SET. Its size is the number of rows that meet your search criteria. Once cursor is opened, your pointer will be placed in the first row of ACTIVE SET. Here you can perform DML. For example if you perform some update operation. It will update any changes in rows in the work area and not in the table directly. So it is not using the table every time we need to update. It fetches once to the work area, then after performing operation, the update will be done once for all operations. This reduces input/output data transfer between database and user.
当我们显式声明游标时，oracle 将在我们的 RAM 中分配一个私有的 SQL 工作区。当您有返回多行的 select 语句时，将从表或视图中复制到私有 SQL 工作区作为 ACTIVE SET。它的大小是满足搜索条件的行数。一旦打开光标，您的指针将被放置在 ACTIVE SET 的第一行。在这里您可以执行 DML。例如，如果您执行一些更新操作。它将更新工作区行中的任何更改，而不是直接更新表中的行。因此，每次我们需要更新时，它都不会使用该表。它取一次到工作区，然后在执行操作后，对所有操作进行一次更新。这减少了数据库和用户之间的输入/输出数据传输。
回答by uma
I Suggest using PL\SQL explicit cursor, u r just going to perform any DML operation at the private workspace alloted for the cursor. This will not hit the database server performance during peak hours
我建议使用 PL\SQL 显式游标，您只需在为游标分配的私有工作区执行任何 DML 操作。这不会在高峰时段影响数据库服务器的性能
