Best way to delete records
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Best way to delete records

  1. #1
    Join Date
    Feb 2001
    Posts
    128
    Hi,
    I have to delete records which are older than 6 months in a audit table. The number of rows older than 6months is 40 million.
    A direct delete is running out of rollback segment space, though I have added alot of space and I don't know if it is the right way to do it.
    In order to issue commits, I have to use cursors. I think it will take forever to complete.
    The option of creating a temporary table, truncating the audit table and inserting from the temp back to audit is my last option.

    Can anyone suggest a easier and faster way to do this?

    Thanks
    Vj



  2. #2
    Join Date
    Apr 2001
    Posts
    219
    You want to distribute the deletes over many connections/transactions to reduce your freelists from becoming inbalanced. If you do not seperate the deletes one of your freelist's will become huge with old blocks and the others will be using mostly new blocks. So, you might want to create a PL/SQL procedure that uses automatous transactions or use a few SQL*Plus sessions using SQL, but spread out the load. If you are not using freelists then I am wasting my time.
    ______________________
    Applications come and go,
    but the data remains!

  3. #3
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Deleting with cursors is probably your best option. You could setup multiple cursors that delete different ranges of rows and let them run in parallel.

    In the future, you can setup your audit table as a partitioned table. That way, you can truncate a partition at a time without affecting the rest of the table.
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  4. #4
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    something like (made it in 30 seconds, there must be some mistakes) :

    select trunc(sysdate - min(date)) into var_begin from table;
    select trunc(sysdate - 180) into var_end from table;

    for i in var_begin .. var_end
    loop
    delete table where trunc(date) = trunc(sysdate-i);
    commit;
    end loop;

    idea = deleting day by day ...

    I dunno if it would be faster than using a temp table

  5. #5
    Join Date
    Feb 2001
    Posts
    128
    thanks guys...

  6. #6
    Join Date
    Apr 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    1,203
    You should really consider setting you audit table in a partitioned table. Prtitioned on Month, then after 11 months, you just truncate the oldest partition.
    OCP 8i, 9i DBA
    Brisbane Australia

  7. #7
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    That was a good one!!

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  8. #8
    Join Date
    Feb 2001
    Posts
    128
    ok, I will look into setting that up. thanks. By the way, could you tell me which query would be faster and eficient...

    Hi,
    Could you please tell me which query would be faster and efficient. I have 40 million rows to delete. I have a index on
    modified_dtm

    Thanks


    declare
    AMonth DATE;
    begin

    ROLLBACK;
    SET TRANSACTION USE ROLLBACK SEGMENT BIG_RBS01;

    -- within 6 month old for DMA, DA, and AH audit tables
    AMonth := ADD_MONTHS(TRUNC(sysdate, 'MM'),-6);

    loop
    if (AMonth < to_date('19990101','yyyymmdd')) then
    exit;
    end if;

    dbms_output.put_line( 'DELETE DMA audit table, AMonth ' || to_char(gasMonth, 'YYYYMMDD') );
    DELETE Table
    WHERE TRUNC(modified_dtm,'MM') = AMonth;
    COMMIT ;
    dbms_output.put_line( 'DONE .....' );
    AMonth := ADD_MONTHS(AMonth, -1);
    end loop;
    end;

    OR

    begin
    SET TRANSACTION USE ROLLBACK SEGMENT BIG_RBS01;
    delete table where
    modified_dtm < to_date('20-FEB-2000 23:59:59', 'DD-MON-YYYY hh24:mi;ss');
    commit;
    end;


    Thanks

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    I think You do not need to recover the table in case if the table data is accidently deleted.
    So, Set the table in NOLOGGING mode.

    ALTER TABLE AUDIT_TABLE NOLOGGING.

    This will ensure that the minimum redo/undo info is generated againt any DML on that table.

    DELETE FROM AUDIT_TABLE
    WHERE DT_COLUMN BETWEEN (starting date) and (Ending Date);

    COMMIT;

    This method is faster than any other method I foresee.



  10. #10
    Join Date
    Dec 1999
    Location
    Cincinnati, Ohio USA
    Posts
    99
    One other option, depending on how many rows are left after the delete would be to

    select rows into new table that you want to keep.
    Truncate old table
    insert saved rows back in the original table.
    Doug

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