与过程/函数中的高效 DML 相关的问题
我有两个关于执行 DML 时 PL/SQL 脚本性能的问题。当然，EXECUTE IMMEDIATE 是最慢的，这就是为什么我们有 forall、bulk insert 等。我的问题是
- 我必须以 3 种不同的方式操作数据表。
Table1（插入数据）、Table2（更新数据）和Table3删除数据。所有这些都将根据使用游标获取的值来完成。问题是这里什么会更有效率？- 将这些语句中的每一个放入单独的
Forall块中？即
- 将这些语句中的每一个放入单独的
获取游标 环形 表 1 的 forall 循环 表 2 的 forall 循环 表 3 的 forall 循环 结束循环
OR
- 全局循环并在该循环中执行这些语句，即
获取游标循环 for i IN array.count 环形 3条DML语句 循环结束 循环结束
现在我的第二个问题
- 是什么是删除循环中记录的有效方法？我通过游标获取了要删除的记录的值。现在删除它们的有效方法是什么？
附： 执行我的格式化
I have 2 questions regarding performance of PL/SQL script when executing DML. Ofcourse the EXECUTE IMMEDIATE is the slowest one thats why we have forall, bulk insert etc. My Questions are
- I have to manipulate data in 3 different tables.
Table1(insert data),Table2(update data) andTable3delete data. All of these would be done based on the values fetched using a cursor. the question is what would be more efficient here?- Putting each of these statements in individual
Forallblock? i.e.
- Putting each of these statements in individual
fetch cursor loop forall loop for table 1 forall loop for table 2 forall loop for table 3 end loop
OR
- a global loop and execute these statments in that loop i.e.
fetch cursor loop for i IN array.count loop 3 statements for DML end loop end loop
Now my second question
- what is the efficient way to delete records in loop? I fetched the values of the records to be deleted through the cursor. now what would be the efficient way to delete them?
P.S:
Execuse my formatting
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
最有效的方法是编写三个 SQL 语句，假设从游标获取的数据在过程运行期间保持稳定，
如果
SELECT语句可能会在每个语句中返回不同的结果三个 DML 语句，那么接受使用游标、将数据批量收集到 PL/SQL 集合并循环遍历集合以确保结果一致的性能影响是有意义的。如果您正在这样做，那么使用三个FORALL语句会更有效，因为这会减少 SQL 和 PL/SQL 引擎之间的上下文转换。我不确定我是否理解这个问题。您不会像执行 INSERT 或 UPDATE 一样执行
FORALL循环吗？或者您是否在问不同的问题？
The most efficient approach would be to write three SQL statements, assuming the data fetched from the cursor is stable over the period of time that the procedure is running
If the
SELECTstatement will potentially return different results in each of the three DML statements, then it makes sense to accept the performance hit of using a cursor, bulk collecting the data into PL/SQL collections, and looping over the collections in order to ensure consistent results. If that's what you're doing, it will be more efficient to have threeFORALLstatements since that involves fewer context shifts between the SQL and PL/SQL engines.I'm not sure I understand the question. Wouldn't you just do a
FORALLloop just as you would for anINSERTor anUPDATEOr are you asking a different question?