Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

commit question

user13328581Jan 17 2023

Hello Experts
I haven't noticed much of a difference and i have looked at the execution plan as well but I might be missing something that I don't know yet.
I have a stored procedure with four delete statements. The tables are all related based on a foreign key relationship. Please see pseudo code below

CREATE OR REPLACE PROCEDURE test(
  empno_in     IN NUMBER
)
IS

BEGIN

delete from test1 p where p.id = empno_in;
delete from test1_child c where c.id = empno_in;
delete from test1_child_desc cd where cd.id = empno_in;
delete from test1_child_location cl where cl.id = empno_in;
commit;
end

Hence, my questions are as follows below;

  1. should I only commit at the end, so is the best practice to commit after each delete statement transaction. Kindly note, I only committed at the end.
Comments
Post Details
Added on Jan 17 2023
6 comments
261 views