1

Currently my code skeleton looks like: 

varchar rowidvariable[batchlimitcount][19];

stmt = "Select rowid from table_name where xx"
delstmt = "delete from table_name where rowid=:rowidvariable"
prepare delstatement using delstmt;

prepare cursor from stmt;
declare cursor from preparecursor;
open cursor;

while(1)
{
    fetch cursor into rowidvariable;

    somecondition {break};        

    exec sql for fetchedCount 
        execute delstatement using :rowidvariable;

    commit;

}

It was pointed out to me that locking the table using SELECT FOR UPADATE would be the way to go to ensure 100% that the rows are locked and ROWID in any instance (however small the chance) doesn't get changed.

However as commit; releases the lock and its extremely important that I do delete in batches as there are millions of records, there seems to be challenge in moving forward with the proposed solution.

Please advise if there are any better alternatives. Thanks in advance.

Reference to me earlier question : Link

Note the whole process is happening in oracle-pro-c.

pOrinG
  • 896
  • 3
  • 13
  • 27
  • Can you please elaborate your code. Of course using ROWID is the fastest way to access a row in a table, however it is still a row-by-row processing which is slow by default. For the moment I don't see any reason to use a loop (or bulk processing) at all. Try to put all your conditions into a single DELETE statement, that should be the best. – Wernfried Domscheit May 30 '18 at 17:06
  • @WernfriedDomscheit The thing is, this type of purge is not a one-off process and while deleting many records at once it might create a lot of undo at once which might paralyze the db. Hence batch processing so few rows are deleting and committed causing less burden on the system. – pOrinG May 31 '18 at 07:31

1 Answers1

-2

sounds like the issue is that you have to delete millions of rows and so you want to do it in baches

if so, this might work for you -- it will loop through and delete rows and commit so that you don't run out of undo and you don't have to worry about locking rows

begin
   loop
      delete from xx where yyy=zzz and rownum < 1000;
      exit when sql%rowcount = 0;
      commit;
   end loop;
   commit;
end;
/
Peter M
  • 192
  • 5