how to fetch, delete, commit from cursor

Question

I am trying to delete a lot of rows from a table. I want to try the approach of putting rows I want to delete into a cursor and then keep doing fetch, delete, commit on each row of the cursor until it is empty.

In the below code we are fetching rows and putting them in a TYPE.

How can I modify the below code to remove the TYPE from the picture and just simply do fetch,delete,commit on the cursor itself.

    OPEN bulk_delete_dup;
    LOOP
        FETCH bulk_delete_dup BULK COLLECT INTO arr_inc_del LIMIT c_rows;

        FORALL i IN arr_inc_del.FIRST .. arr_inc_del.LAST
              DELETE FROM UIV_RESPONSE_INCOME 
              WHERE ROWID = arr_inc_del(i);

        COMMIT;
        arr_inc_del.DELETE;
        EXIT WHEN bulk_delete_dup%NOTFOUND;
    END LOOP;
    arr_inc_del.DELETE;
    CLOSE bulk_delete_dup;

Answer 1

Why do you want to commit in batches? That is only going to slow down your processing. Unless there are other sessions that are trying to modify the rows you are trying to delete, which seems problematic for other reasons, the most efficient approach would be simply to delete the data with a single DELETE, i.e.

DELETE FROM uiv_response_income uri
 WHERE EXISTS( 
    SELECT 1
      FROM (<<bulk_delete_dup query>>) bdd
     WHERE bdd.rowid = uri.rowid
  )

Of course, there may well be a more optimal way of writing this depending on how the query behind your cursor is designed.

If you really want to eliminate the BULK COLLECT (which will slow the process down substantially), you could use the WHERE CURRENT OF syntax to do the DELETE

SQL> create table foo
  2  as
  3  select level col1
  4    from dual
  5  connect by level < 10000;

Table created.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    cursor c1 is select * from foo for update;
  3    l_rowtype c1%rowtype;
  4  begin
  5    open c1;
  6    loop
  7      fetch c1 into l_rowtype;
  8      exit when c1%notfound;
  9      delete from foo where current of c1;
 10    end loop;
 11* end;
SQL> /

PL/SQL procedure successfully completed.

Be aware, however, that since you have to lock the row (with the FOR UPDATE clause), you cannot put a commit in the loop. Doing a commit would release the locks you had requested with the FOR UPDATE and you'll get an ORA-01002: fetch out of sequence error

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    cursor c1 is select * from foo for update;
  3    l_rowtype c1%rowtype;
  4  begin
  5    open c1;
  6    loop
  7      fetch c1 into l_rowtype;
  8      exit when c1%notfound;
  9      delete from foo where current of c1;
 10      commit;
 11    end loop;
 12* end;
SQL> /
declare
*
ERROR at line 1:
ORA-01002: fetch out of sequence
ORA-06512: at line 7

You may not get a runtime error if you remove the locking and avoid the WHERE CURRENT OF syntax, deleting the data based on the value(s) you fetched from the cursor. However, this is still doing a fetch across commit which is a poor practice and radically increases the odds that you will, at least intermittently, get an ORA-01555: snapshot too old error. It will also be painfully slow compared to the single SQL statement or the BULK COLLECT option.

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    cursor c1 is select * from foo;
  3    l_rowtype c1%rowtype;
  4  begin
  5    open c1;
  6    loop
  7      fetch c1 into l_rowtype;
  8      exit when c1%notfound;
  9      delete from foo where col1 = l_rowtype.col1;
 10      commit;
 11    end loop;
 12* end;
SQL> /

PL/SQL procedure successfully completed.

Of course, you also have to ensure that your process is restartable in case you process some subset of rows and have some unknown number of interim commits before the process dies. If the DELETE is sufficient to cause the row to no longer be returned from your cursor, your process is probably already restartable. But in general, that's a concern if you try to break a single operation into multiple transactions.

Answer 2

A few things. It seems from your company's "no transaction over 8 second" rule (8 seconds, you in Texas?), you have a production db instance that traditionally supported apps doing OLTP stuff (insert 1 row, update 2 rows, etc), and has now also become the batch processing db (remove 50% of the rows and replace with 1mm new rows).

Batch processing should be separated from OLTP instance. In a batch ("data factory") instance, I wouldn't try deleting in this case, I'd probably do a CTAS, drop old table, rename new table, rebuild indexes/stats, recompile invalid objs approach.

Assuming you are stuck doing batch processing in your "8 second" instance, you'll probably find your company will ask for more and more of this in the future, so ask the DBAs for as much rollback as you can get, and hope you don't get a snapshot too old by fetching across commits (cursor select driving the deletes, commit every 1000 rows or so, delete using rowid).

If DBAs cant help, you may be able to first create a temp table containing the rowids that you wish to delete, and then loop through the temp table to delete from main table (avoid fetching across commits), but your company will probably have some rule against this as well as this is another (basic) batch technique.

Something like:

declare

-- assuming index on someCol
cursor sel_cur is
select rowid as row_id
from someTable
where someCol = 'BLAH';

v_ctr pls_integer := 0;
begin
for rec in sel_cur
loop
  v_ctr := v_ctr + 1;
  -- watch out for snapshot too old...
  delete from someTable
  where rowid = rec.row_id;
  if (mod(v_ctr, 1000) = 0) then
    commit;
  end if;
end loop;
commit;

end;