CODEWITHSUNDEEP
sqloracle-databaseplsqlprocedureplsqldeveloper
Tom Mart
Tom Mart

Reputation: 57

PL SQL bulk collect fetchall not completing

I made this procedure to bulk delete data (35m records). Can you see why this pl/sql procedure runs without exiting and rows are not getting deleted ?

create or replace procedure clear_logs
as 
 CURSOR c_logstodel IS SELECT * FROM test where id=23;
 TYPE typ_log is table of test%ROWTYPE;   
 v_log_del typ_log;
BEGIN
OPEN c_logstodel;
LOOP   
FETCH c_logstodel BULK COLLECT INTO v_log_del LIMIT 5000;   
EXIT WHEN c_logstodel%NOTFOUND;
    FORALL i IN v_log_del.FIRST..v_log_del.LAST   
    DELETE FROM test WHERE id =v_log_del(i).id;   
    COMMIT;
END LOOP;
CLOSE c_logstodel;
END clear_logs;

Upvotes: 1

Views: 1505

Answers (2)

Belayer
Belayer

Reputation: 14861

First off when using BULK COLLECT LIMIT X the %NOTFOUND takes on a slightly unexpected meaning. In this case %NOTFOUND actually means Oracle could not retrieve X rows. (I guess technically it always does you fetch the next 1 and it says it could not fill the 1 row buffer.) Just move the EXIT WHEN %NOTFOUND to after the FORALL. But there is actually no reason to retrieve the data and then delete the retrieved rows. While one statement would be considerable faster 35M rows would require signifient rollback space. There is an interment solution.
Although not commonly used Delate statements generate rownum as do selects. This value can be user to limit the number or rows processed. So to break into a given commit size just limit rownum on the delete:

create or replace procedure clear_logs
as 
    k_max_rows_per_interation constant integer := 50000;
begin
    loop   
        delete 
          from test 
         where id=23
           and rownum <= k_max_rows_per_interation;
        exit when sql%rowcount < k_max_rows_per_interation;
        commit;
    end loop;
    commit;
end;

As @Stilgar points out deletes are expensive, meaning slow, so their solution may be better. But this has the advantage that it does not essentially take the table completely out-of-service during the operation. NOTE: I tend to use a much larger commit interval size, generally around 400,000 - 300,000 rows. I suggest you talk with your DBA see what they think this limit should be. Remember it is their job to properly size rollback space for typical operations. If this is normal in your operation they need to set it correctly. If you can get rollback space for 35M deletes then that is the fastest you are going to get.

Upvotes: 0

Tom Mart
Tom Mart

Reputation: 57

Adding in rowid instead of column name, exit when v_delete_data.count = 0; instead of EXIT WHEN c_logstodel%NOTFOUND; and changing chunk limit to 50,000 allowed the script clear 35 million rows in 15 mins

create or replace procedure clear_logs
as 
 CURSOR c_logstodel IS SELECT  rowid FROM test where id=23;
 TYPE typ_log is table of rowid index by binary_integer;
 v_log_del typ_log;
BEGIN
OPEN c_logstodel;
LOOP   
FETCH c_logstodel BULK COLLECT INTO v_log_del LIMIT 50000;   
 exit when v_log_del.count = 0;
    FORALL i IN v_log_del.FIRST..v_log_del.LAST   
    DELETE FROM test WHERE  rowid =v_log_del(i);  
    exit when v_log_del.count = 0;
    COMMIT;
END LOOP;
COMMIT;
CLOSE c_logstodel;
END clear_logs;

Upvotes: 2

Related Questions