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 :
svrmgrl << EOF
alter table tb_name nologging;
v_total number :=0;
cursor V_Cur is
select rkxh from tb_name where f_a=xxx and f_b=yyy;
fetch V_Cur into v_id;
exit when V_Cur%notfound;
delete from tb_name where id=v_id;
v_total:= v_total + 1;
if v_counter>=10000 then --commit for eevery 1000
v_counter := 0;
when others then
alter session disable parallel dml;
alter table tb_name logging;
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 ?
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);
SQL> insert into test select rownum, sysdate+rownum from dba_source;
148542 rows created.
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;
5 for r in c loop
6 delete from test where c1 = r.c1;
8 if cntr = commit_rows then
11 cntr := 0;
12 end if;
13 end loop;
SQL> set serverout on
SQL> select count(*) from test;
SQL> execute test_del(100000,10000);
PL/SQL procedure successfully completed.
SQL> select count(*) from test;
Oracle Certified Professional 8i, 9i.
"The degree of normality in a database is inversely proportional to that of its DBA"
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)
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.
"I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."
"Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"
DELETE with ur condition and
with rownum < (max records u think u can delete);
EXIT WHEN sql%ROWCOUNT = 0;
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).