Performance of delete operation
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 3 123 LastLast
Results 1 to 10 of 28

Thread: Performance of delete operation

  1. #1
    Join Date
    Aug 2000
    Posts
    32

    Unhappy

    Hello,

    My database is running in archive mode. I have a table with large number of rows in it. Every day I need to delete the previous day's rows, which are around 400,000. When I do the delete operation, database is generating so many archive log files and running out of disk, also the operation is taking long time. I don't want to archive log this operation. Is there any option which I can escape logging the delete operation? Or is there any other way that I can achieve the performance. My table has no LOB data types and it has just character, number and date fields.

    I will appreciate your help.

    Thanks,
    Vimala

  2. #2
    Join Date
    Feb 2001
    Posts
    389
    1) Deleting 400,000 rows generating a lot of archived file ..????? check the size of ur redo log file.

    2) Why not partiton the table


  3. #3
    Join Date
    Aug 2000
    Posts
    32
    Yes. My log file size is only 5MB and database is generating lots of archive log files when I delete 400 thousand records at a time.

    What is the need in partitioning table? I don't need the data in it for more than one day.

  4. #4
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Is TRUNCATE an option?
    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."

  5. #5
    Join Date
    Nov 2001
    Location
    Portland, OR
    Posts
    2
    Truncating the table will bypass writing to the Archivelogs and take a fraction of the time. Truncating does not allow for any sort of logic, it will simply empty the table. I truncated a table with 1.6 million rows in under 10s. Your performance will vary but it will be appreciably faster.

  6. #6
    Join Date
    Aug 2000
    Posts
    32
    No, I don't want to delte all the records in that table.

    I want to delete only yesterday's data and I want to keep today's data which could be around 100 thousand records at the time of deletion. The table should have only today's data.

  7. #7
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Oh, in that case, partitioning would be perfect for you.

    You would store a days worth of data in each partition. You can then truncate one partition worth of data without affecting the rest of the data in another partition.

    PS. make sure you use local indexes, it will make you life much easier...
    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."

  8. #8
    Join Date
    Jun 2001
    Posts
    24
    You might want to try this steps.

    1 Check to see if users are logged on
    if not
    2. shutdown immediate
    3. comment outout archiving in Init.ora file
    4. startup restrict mount
    5. alter database noarchivelog
    6. alter database open
    7. do your delete command
    once done
    8. shutdown immediate
    9. uncomment out archiving in Init.ora file
    10. startup mount
    11. alter database archivelog
    12. alter database open
    13. alter system disable restricted session

    Hopefully this works for you.


    smiles off

  9. #9
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Originally posted by ybadejo
    You might want to try this steps.

    1 Check to see if users are logged on
    if not
    2. shutdown immediate
    3. comment outout archiving in Init.ora file
    4. startup restrict mount
    5. alter database noarchivelog
    6. alter database open
    7. do your delete command
    once done
    8. shutdown immediate
    9. uncomment out archiving in Init.ora file
    10. startup mount
    11. alter database archivelog
    12. alter database open
    13. alter system disable restricted session

    Hopefully this works for you.


    I would discourage using this method. First, you have to bounce your database. That by itself is bad enough. However, by switching out of archivelog mode, you have now ruined your chances for any kind of recovery using archived redo logs.
    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."

  10. #10
    Join Date
    Jun 2001
    Posts
    24
    well, I will assume you have a good backup before doing.
    smiles off

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