Questions related to efficient DML in procedure/functions
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) and Table3 delete 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 Forall block? i.e.
fetch cursor loop forall loop for table 1 forall loop for table 2 forall loop for table 3 end loop
- 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
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
INSERT INTO table1( list_of_columns ) <<your SELECT statement>> UPDATE table2 SET (<<list of columns>>) = (<<your SELECT statement joined to table2>>) WHERE EXISTS( <<your SELECT statement joined to table2>> ); DELETE FROM table3 WHERE EXISTS( <<your SELECT statement joined to table3>> );
If the SELECT statement 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 three FORALL statements since that involves fewer context shifts between the SQL and PL/SQL engines.
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?
I'm not sure I understand the question. Wouldn't you just do a FORALL loop just as you would for an INSERT or an UPDATE
FORALL i IN l_array.first .. l_array.last DELETE FROM some_table WHERE some_key = l_array(i);
Or are you asking a different question?