CODEWITHSUNDEEP
oracle-databaseplsql
Matthew Moisen
Matthew Moisen

Reputation: 18299

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?

Upvotes: 3

Views: 519

Answers (1)

T.S.
T.S.

Reputation: 19384

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

Upvotes: 7

Related Questions