pl/sql forall insert 和 plain SQL insert 的查询性能差异
我们一直在pl/sql存储过程中使用临时表来存储中间结果。谁能告诉我通过 pl/sql 进行批量收集插入和普通 SQL 插入之间是否存在性能差异。
Insert into [表名] [选择查询返回大量数据]
或
Cursor for [选择返回大量数据的查询]
打开游标
fetch 游标批量收集到集合
使用 FORALL 执行插入
以上 2 个选项哪个插入效果更好大量临时数据？
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.
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
Which of the above 2 options is better to insert huge amount of temporary data?.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
针对您的问题的一些实验数据 (Oracle 9.2)
批量收集
插入
直接路径插入
http://download.oracle.com/docs/cd /B10500_01/server.920/a96524/c21dlins.htm
Some experimental data for your problem (Oracle 9.2)
bulk collect
insert
direct path insert
http://download.oracle.com/docs/cd/B10500_01/server.920/a96524/c21dlins.htm
Insert into select 肯定会更快。跳过首先将数据存储在集合中的开销。
Insert into select must certainly be faster. Skips the overhead of storing the data in a collection first.
这取决于您正在执行的填充中间结果的工作的性质。如果可以在 INSERT 的 SELECT 语句中相对简单地完成这项工作，那么通常会执行得更好。
但是，如果您有一些复杂的中间逻辑，（从代码维护的角度来看）使用批量收集/绑定来批量获取和插入数据可能会更容易。在某些情况下，它甚至可能更快。
需要非常仔细地注意一件事：
INSERT INTO x SELECT ...使用的查询计划有时与查询本身运行时使用的查询计划有很大不同（例如，在 PL/SQL 显式游标中）。在比较性能时，您需要考虑到这一点。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.
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.Asktomhome 出名的 Tom Kyte 更坚定地回答了这个问题。如果您愿意进行一些搜索，您可以找到问题和他的回答，其中包含详细的测试结果和解释。他展示了 plsql 游标与 plsql 批量收集（包括定期提交的影响）与 sql insert as select 的比较。
insert as select 总是很容易获胜，即使是适度的数据集，差异也是巨大的。
就是这样说的。早些时候就中间计算的复杂性发表了评论。我可以想到与此相关的三种情况。
1) 如果计算需要在 Oracle 数据库之外进行，那么显然简单的 insert as select 并不能解决问题。
2) 如果解决方案需要使用 PLSQL 函数调用，那么上下文切换可能会终止您的查询，并且通过 plsql 调用 plsql 函数可能会获得更好的结果。 PLSQl 是为了调用 SQL 而设计的，但反过来则不然。因此从 SQL 调用 PLSQL 的成本很高。
3) 如果计算使 sql 代码非常难以阅读，那么即使它可能较慢，由于这些其他原因，plsql 批量收集解决方案可能会更好。
祝你好运。
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.
insert as select wins hands down all the time and the difference on even modest datasets is dramatic.
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.
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.
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.
Good luck.
当我们显式声明游标时，oracle 将在 RAM 中分配一个私有 SQL 工作区。当您有返回多行的 select 语句时，将从表或视图复制到私有 SQL 工作区作为 ACTIVE SET。它的大小是满足您的搜索条件的行数。一旦光标打开，您的指针将被放置在 ACTIVE SET 的第一行。在这里您可以执行DML。例如，如果您执行某些更新操作。它将更新工作区中的行中的任何更改，而不是直接更新表中的任何更改。所以它并不是每次我们需要更新时都使用该表。它只取一次到工作区，然后执行操作后，所有操作都会更新一次。这减少了数据库和用户之间的输入/输出数据传输。
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.
我建议使用 PL\SQL 显式游标，您只需在分配给游标的私有工作区中执行任何 DML 操作。这不会影响高峰时段数据库服务器的性能
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