Delete Records for a huge table ( rollback error)
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: Delete Records for a huge table ( rollback error)

Hybrid View

  1. #1
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433

    Delete Records for a huge table ( rollback error)

    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 ?
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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 ...

    Code:
    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 ...

    Code:
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Feb 2000
    Location
    Singapore
    Posts
    1,758
    You can use somthing like this. You can pass the condition and no. of rows you want to commit as parameters to this.
    Code:
    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>
    Sanjay G.
    Oracle Certified Professional 8i, 9i.

    "The degree of normality in a database is inversely proportional to that of its DBA"

  4. #4
    Join Date
    Oct 2000
    Posts
    467

    Re: Delete Records for a huge table ( rollback error)

    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.
    Vinit

  5. #5
    Join Date
    Aug 2000
    Location
    Shanghai
    Posts
    433
    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 ?
    1. www.dbasupport.com
    2. www.dbforums.com
    3. www.itpub.net
    4. www.csdn.net
    5. www.umlchina.com
    6. www.tek-tips.com
    7. www.cnforyou.com
    8. fm365.federal.com.cn
    9. www.programmersheaven.com
    10.http://msdn.microsoft.com/library/default.asp
    ligang1000@hotmail.com

  6. #6
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    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.
    funky...

    "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"

  7. #7
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    From another thread - really neat:
    Originally posted by jmodic
    Code:
    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).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width