1

I have the following scenario:

A table containing more than a million records. I need to create a loop that deletes 10'000 rows per execution (loop) based on the column number of the rows. So if in the end of the execution the column has 500 rows for example, then i need to delete all these rows.

So I imagined that this might work:

declare COUNTER int;

while (select count(*) from Table) >= 10000)
loop
     DELETE FROM (SELECT * FROM STAGE1 WHERE STAGE1.ID <= 10000);
endloop;

It's something like this that I need, but I don't know how to increase the Counter variable in the loop. And I suppose that I need to put an IF THEN ... ELSE... statement inside the WHILE to check the SELECT * FROM STAGE1 WHERE STAGE1.ID <= 10000, and in the ELSE, delete the rest of the rows that I have: i.e the last 500 rows.

EDIT:

I'm trying to make this work:

DECLARE COUNTER INTEGER :=0;
 BEGIN
 DBMS_OUTPUT.PUT_LINE('START'); 
loop -- keep looping 
COUNTER := COUNTER + 1;

  --do the delete 1000in each iteration
  Delete TEST where  rownum < 1000;

  -- exit the loop when there where no more 1000 reccods to delete. 
  exit when SQL%rowcount <= 1000;
  commit; 
end loop;
  DBMS_OUTPUT.PUT_LINE(COUNTER); 
 commit; 
END;

I put 10'000 rows in my TEST table. In the end the counter should be 9 and at the end of the loop the table should contain 1000 rows, but in my case the counter is 11 and the table is empty.

Why doesn't it work?

  • Are there additional filter conditions or are you trying to empty the table? – Mat Sep 29 '14 at 15:34
  • @Mat im just trying to empty the table, actually it will be execute by a .NET job that im developing (i have no experience with oracle queries). Thanks – gog Sep 29 '14 at 16:05
  • Then use truncate table. Can get rid of billions of rows in seconds. – Mat Sep 29 '14 at 16:15
  • @Mat , you mean to forget all the loop code and execute a delete all records direct in the table in just one query? – gog Sep 29 '14 at 16:34
  • 2
    Not delete, truncate. That's like a drop, only keeping the table structure. It's very fast, essentially doesn't generate redo. But please read the docs for it. In particular, it's considered DDL in Oracle, meaning it does an implicit commit (before & after). So no rollback. – Mat Sep 29 '14 at 17:15
0

Try this:

DECLARE 
COUNTER INTEGER :=0;
CANT INTEGER;
 BEGIN
 DBMS_OUTPUT.PUT_LINE('START'); 
loop -- keep looping 
COUNTER := COUNTER + 1;

  --do the delete 1000in each iteration
  Delete TEST where  rownum <= 1000;

  -- exit the loop when there where no more 1000 reccods to delete. 
  -- DBMS_OUTPUT.PUT_LINE('SQL%rowcount: '|| SQL%rowcount); --this return 999 and breaks the while
  -- exit when SQL%rowcount <= 1000;
  SELECT COUNT(*) INTO CANT FROM TEST;
  exit when CANT <= 1000;
  commit; 
end loop;
  DBMS_OUTPUT.PUT_LINE('Counter: '||COUNTER); 
  DBMS_OUTPUT.PUT_LINE('Left: '||cant);
 commit; 
END;

I only change the stop condition of the while and rownum <= 1000. With 10000 rows it returns:

START
Counter: 9
Left: 1000

Left refers to remaining rows. I hope this helps.

| improve this answer | |
  • does the "select count(*) into cant from TEST" will be a problem for the performance, or even lock the table? And do you have any ideas of why the SQL%rowcount does not work? – gog Sep 29 '14 at 20:20
  • i think performance in this case will not affected, implicit cursors afects perfomance when you select lot of rows, for example select * bulk collect into cant from TEST, and no, i think it not lock the table. Sql%rowcount return number of rows affected by a sql statement, in this case is 999, that's why only do one iteration. – Aramillo Sep 29 '14 at 20:30
  • in the case it returns the number of rows affected in the delete statement. Right, Thanks very much! – gog Sep 29 '14 at 20:37
  • Yes. That's correct. You're welcome.:) – Aramillo Sep 29 '14 at 20:39
1

As I understand the problem, you want to leave 1000 rows in a table that starts with > 1 million. In SQL Server, I ran I to problems with the delete statement locking the table, and also taking a long time to execute. All the deleted records have to go into the transaction log.

What worked better was to select the records I wanted to keep into a new table, and then rename the tables.

With Oracle, your mileage may vary

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.