Unanswered: Using a loop to delete from multiple tables, tracking number of rows deleted
I have a procedure that deletes x amount of rows from multiple tables based on the key from another table. I got a cursor that fetches all case_id's from cases that are created more than 5 years and 1 month ago. Below is parts of the code (the rest is just the same delete from more tables - 18 in total).
CREATE OR REPLACE procedure delete_old_cases
cursor c1 is
from case a
where a.opening_date <= (sysdate - to_yminterval (05-01'));
fetch c1 into c1_rec;
EXIT WHEN c1%NOTFOUND;
DELETE FROM CASEEVENTS WHERE CASE_ID = c1_rec;
DELETE FROM CASENOTES WHERE CASE_ID = c1_rec;
DELETE FROM CASE_INVOLVED WHERE CASE_ID = c1_rec;
The procedure works, and does what it is supposed to, deleting all rows in the 3 tables with the same case_id. What I need is some kind of logging, showing how many rows are deleted from each table, to display in a tool that monitors the jobs in my datawarehouse.
Case_id is unique in the table CASE (used in the cursor), but can have more rows in the 3 tables inside the loop.
I tried dbms_output.put_line(sql%rowcount); after each delete statement, but that only shows the amount of rows deleted from that iteration - I need the totals.
I found a workaround, where I first count the number of rows in the tables, then initiate the loop with deletes, then i counted the number of rows after loop as done, and printed the amount based on 1st count - 2nd count = new total. But its not pretty, and I imagine its a waste of resources, making the same count twice. There can potentially be many rows, and this procedure will be running on 55 difference schemas, so I wouldnt mind keeping the resources down
I also considered making the deletes outside of the loop, with something like this:
DELETE FROM CASEEVENTS WHERE CASE_ID IN (sELECT CASE_ID FROM CASE WHERE opening_dato <= (sysdate - to_yminterval('05-01')); dbms_output.put_line(sql%rowcount);
DELETE FROM [.....] etc.
Would that be more or less cost-effective? It would solve the problem with logging, but I dont want to if it eats more resources.
Hope this was enough to describe my problme.
Thanks in advance for any help,
Hope this was enough to explain the problem.
Thanks in advance for any tips!
If you stick with loops you could keep track of deleted rows by storing the deleted count in a variable and adding SQL%ROWCOUNT to it with each delete.
However I would have thought removing the LOOP is a much better way to do it in terms of efficiency in that you execute fewer sql statements and you reduce the number of sql/plsql context switches (also PLSQL isnt that fast).
@Rabatami, I agree with Littlefoot. 3 delete statements, no loops... is the way to go.
Another hint is that if the deletes take a long time and depending on some criteria, they will show up in the v$session_longops view where you can check on their progress.