Results 1 to 13 of 13
  1. #1
    Join Date
    Oct 2009
    Posts
    26

    Unanswered: Performance problem on mass delete

    Hi,

    we have a table with about 120 millions of records that grows at 2.5 millions of records per week.

    For not letting this table explode we would like to delete old records once a week. Therefore we have an SQL like

    Code:
    delete from table where creationDate < someDate
    As this delete affects about 2.5 million rows it is really slow which seems to be up to the transaction log that gets really big.

    Which is the best, so the most performant, way for scuch a mass delete operation?

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    First came in my mind was to create an index on creationDate column.

  3. #3
    Join Date
    Aug 2011
    Posts
    46
    Have you given a thought to range partition this table ?

  4. #4
    Join Date
    Oct 2009
    Posts
    26
    Quote Originally Posted by tonkuma View Post
    First came in my mind was to create an index on creationDate column.
    The creationDate column is indexed of course. What slows the statement down seems to be the writing to the transaction log which is independent of the time for selecting the rows.

  5. #5
    Join Date
    Oct 2009
    Posts
    26
    Quote Originally Posted by amitrai4 View Post
    Have you given a thought to range partition this table ?
    As the application on this database is a Hibernate one (which should be independent of the db system used) partitioning is not an option as it is not supported by all db systems.

  6. #6
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How about UNION ALL view?

  7. #7
    Join Date
    Oct 2009
    Posts
    26
    Quote Originally Posted by tonkuma View Post
    How about UNION ALL view?
    Can you please give me some more information on how a UNION ALL view works and might help. Unfortunately I'm not really a DB expert.

  8. #8
    Join Date
    Aug 2011
    Posts
    46
    Quote Originally Posted by tonkuma View Post
    How about UNION ALL view?
    Or MDC with DB2_MDC_ROLLOUT registry variable/ CURRENT MDC ROLLOUT MODE special register

  9. #9
    Join Date
    Nov 2011
    Posts
    334
    I think using a table with data patition instead of your table will be a better solution.
    You can use detach command to rollout data quickly .

  10. #10
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    all doc/info is at
    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows
    I really do not understand that company's have their db handled by people that do not know anything about it.. and just executing what people from the forum suggest..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  11. #11
    Join Date
    Nov 2011
    Location
    Shen Zhen,China
    Posts
    37
    temporary alter the table with ACTIVATE NOT LOGGED INITIALLY attribute

  12. #12
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by amitrai4 View Post
    Have you given a thought to range partition this table ?
    Yes agree. On 9.7 the DETACH is much more smooth. The local indexes take away your REORG worries.

  13. #13
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    You want to have a general solution that works with lot's of different database systems (because you use Hibernate)? Then you have to stick to the common functionality, which is DELETE and which logs all changes. This will be slower than the here proposed solutions to do some sort of partitioning or deactivating logging. All those are approaches specific to DB2. So you have to decide what you want.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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