Is the use of the RETURNING INTO clause faster than a separate SELECT statement?
oracle, plsql
Solution
Just as I thought:
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.
Taken from Oracle docs here
Problem
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?