RETURNING INTO子句的使用是否比单独的SELECT语句更快? [英] Is the use of the RETURNING INTO clause faster than a separate SELECT statement?
问题描述
1)
update foo set bar = bar + 1 where a = 123;
select bar into var from foo where a = 123;
2)
update foo set bar = bar + 1 where a = 123 RETURNING bar into var;
我认为第二个更快,因为它似乎需要少一趟数据库.这是真的吗?
I assume the second is faster as it appears to require one less trip to the database. Is this true?
推荐答案
就像我想的那样:
通常,应用程序需要有关受SQL操作影响的行的信息,例如,生成报告或采取后续措施. INSERT,UPDATE和DELETE语句可以包含RETURNING子句,该子句将受影响的行中的列值返回到PL/SQL变量或主机变量中.这样一来,就无需在插入或更新之后或删除之前选择该行.结果,需要更少的网络往返,更少的服务器CPU时间,更少的游标和更少的服务器内存.
Often, applications need information about the row affected by a SQL operation, for example, to generate a report or take a subsequent action. The INSERT, UPDATE, and DELETE statements can include a RETURNING clause, which returns column values from the affected row into PL/SQL variables or host variables. This eliminates the need to SELECT the row after an insert or update, or before a delete. As a result, fewer network round trips, less server CPU time, fewer cursors, and less server memory are required.
这篇关于RETURNING INTO子句的使用是否比单独的SELECT语句更快?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!