Delete Records for a huge table ( rollback error)

[ligang]

there is a huge database , the data volume is 450GB, the largest table contains 60million records and the record size is 1K ~1.5K
(no lob column)
now trying to delete 500K records in this table (based on 2 indexed columns)
the platform is RS6000 s7A , 2GB memeber , CPU 4*262MHZ , Oracle8.1.5

I notice that the deletion is using one rollback segment and it grows to 10GB and failed to allocate next extent . How to solve this problem .

the example of code for deletion is :

#!/bin/sh
svrmgrl << EOF
connect username/password;
alter table tb_name nologging;
declare
v_id tb_name.id%type;
v_counter number:=0;
v_total number :=0;
cursor V_Cur is
select rkxh from tb_name where f_a=xxx and f_b=yyy;
begin
open V_Cur;
loop
fetch V_Cur into v_id;
exit when V_Cur%notfound;
v_counter:= v_counter+1;
delete from tb_name where id=v_id;
v_total:= v_total + 1;
if v_counter>=10000 then --commit for eevery 1000
commit;
v_counter := 0;
end if;
end loop;
commit;
close V_Cur;
exception
when others then
rollback;
close V_Cur;
end;
/
alter session disable parallel dml;
alter table tb_name logging;
disconnect
exit
EOF

question1 : It seems Oracle put not only the deleted data blocks in rolllback (otherwise how it is possible that the rollback is used up to 10GB !)
question2: in this case , how to optimize the deletion function ?

[slimdave]

I think that you have chosen just about the worse possible technique for deleting these rows.

Firstly, you are commiting within a cursor, and that is asking for trouble.

Secondly you are trying to proceduralize the process, when it would be simpler, faster and more reliable just to perform simple SQL operations.

How about this ...

delete from tb_name where f_a=xxx and f_b=yyy;

If you don't have enough RBS space to do the delete in one go, just keep on ...

delete from tb_name where f_a=xxx and f_b=yyy
and rownum < 100001;

... until no more rows are deleted.

Keep it simple, and keep it in SQL.

[SANJAY_G]

You can use somthing like this. You can pass the condition and no. of rows you want to commit as parameters to this.

SQL> create table test (c1 number primary key, c2 date);

Table created.

SQL> insert into test select rownum, sysdate+rownum from dba_source;

148542 rows created.

SQL> ed
Wrote file afiedt.buf

  1  create or replace procedure test_del (c1_cond in number, commit_rows in number ) is
  2  cntr integer := 0;
  3  cursor c is select c1 from test where c1 > c1_cond;
  4  begin
  5  for r in c loop
  6  delete from test where c1 = r.c1;
  7  cntr:=cntr+1;
  8  if cntr = commit_rows then
  9  dbms_output.put_line('Commiting...');
 10     commit;
 11     cntr := 0;
 12  end if;
 13  end loop;
 14  commit;
 15* end;
SQL> /

Procedure created.

SQL> set serverout on
SQL> select count(*) from test;

  COUNT(*)
----------
    148542

SQL> execute test_del(100000,10000);
Commiting...
Commiting...
Commiting...
Commiting...

PL/SQL procedure successfully completed.

SQL> select count(*) from test;

  COUNT(*)
----------
    100000

SQL>

[vinit]

Originally posted by ligang

the example of code for deletion is :

#!/bin/sh
svrmgrl << EOF
connect username/password;
alter table tb_name nologging;

Nologging will help only in certain cases like creating/rebuilding index. Delete info will be logged in the redo.

Secondly since you are committing inside a cursor, this should span several rollback segs and not just one, unless all your other RBS are full.

Slimdave...good input.

[ligang]

actually I remember I read a book about how to optimize the deletion and updation (DML) , somehow , maybe there is a slice about using a cursor Vs Simple SQL. in some condition , using a cursor is better ( I am not sure)

could anyone reveal the logic behind ?

[abhaysk]

Originally posted by ligang
somehow , maybe there is a slice about using a cursor Vs Simple SQL. in some condition , using a cursor is better ( I am not sure)

Cursor will only slow up the process. SO using cursor would be bad idea..well if its unavoidable then theres no go..cases like "With reference to data in X table you wanted to delete Records in Y table based on different conditions".

If you know what records you want to delete ( present case )..use SLIMDAVE's method ( Pure SQLs )...its very efficient.

Abhay.

[DaPi]

From another thread - really neat:

Originally posted by jmodic

LOOP
  DELETE with ur condition and
         with rownum < (max records u think u can delete);
  EXIT WHEN sql%ROWCOUNT = 0;
  COMMIT;
END LOOP;

slimdave is right that IN GENERAL using the cursor is not a good method. If "ur condition" produces a FTS, then experience shows that the cursor method is a bit faster (the table is read only once) and commiting inside it does not cause a problem. (You might want to try picking up ROWID in the cursor and using that in the delete statement).