Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2012
    Posts
    2

    Unanswered: Using a loop to delete from multiple tables, tracking number of rows deleted

    Hi,

    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
    is

    v_trans_id NUMBER;

    cursor c1 is
    select case_id
    from case a
    where a.opening_date <= (sysdate - to_yminterval (05-01'));

    c1_rec NUMBER;

    BEGIN

    open c1;

    LOOP

    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;

    END LOOP;

    close c1;
    commit;

    end;
    /

    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,

    C

    Hope this was enough to explain the problem.
    Thanks in advance for any tips!

  2. #2
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    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).

    Alan

  3. #3
    Join Date
    Jun 2012
    Posts
    2
    Hey, and thanks for the reply.
    By removing the loop, do you mean having each delete as
    Delete from caseevent where case_id in (select case_id from case where openingdate <= ....) like in the cursor?

    C

  4. #4
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Yes. 3 DELETE statements in all, no loops.

  5. #5
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    @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.

    Also worth looking at for user defined long running PL/SQL processing is the DBMS_APPLICATION_INFO package which also populates v$session. Also see DBMS_APPLICATION_INFO: For code instrumentation, for quick start info.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •