Tags: database, db2, delete, deleting, foreign, key, logs, million, mysql, nested, oracle, perform, plusrows, records, relationships, sql, tables, transaction, writing

deleting records without writing to the transaction logs

On Database » DB2

4,909 words with 3 Comments; publish: Sun, 30 Dec 2007 23:50:00 GMT; (250125.00, « »)

I need to delete records from several large tables - 1 million plus

rows. Some of these tables have nested foreign key relationships.

When I perform the delete statements, it can take forever as the

database works out the key relationships and commits things to the log.

Is there a way to speed up some of these deletes with out writing the

information to the database logs?

I am going to probably drop the RI on the tables and manually delete

from the child tables to improve things.

Any tips or suggestions would be appreciated. We have been using the

db2 loader with "replace" functionality but have new requirements that

prevent us from overwriting all the data.

Thanks,

Michael

All Comments

Leave a comment...

  • 3 Comments
    • vb.db2.todaysummary.com.sharedvision.com wrote:

      > I need to delete records from several large tables - 1 million plus

      > rows. Some of these tables have nested foreign key relationships.

      > When I perform the delete statements, it can take forever as the

      > database works out the key relationships and commits things to the log.

      > Is there a way to speed up some of these deletes with out writing the

      > information to the database logs?

      > I am going to probably drop the RI on the tables and manually delete

      > from the child tables to improve things.

      > Any tips or suggestions would be appreciated. We have been using the

      > db2 loader with "replace" functionality but have new requirements that

      > prevent us from overwriting all the data.

      > Thanks,

      > Michael

      You can try teh following (I have no clue if all steps will work):

      1. Turn the RI into NOT ENFORCED (using ALTER TABLE).

      2. ACTIVATE NOT LOGGED INITIALLY (be aware of what happens in case of

      error!)

      3. DELETE (make sure auto-commit is OFF if you use CLP

      4. COMMIT

      5. SET INTEGRITY OFF for the involved tables

      6. Turn the RI back on

      7 SET INTEGRITY using UNCHECKED

      You can also just turn RI back on without SET INTEGRITY but DB2 will

      double check which may or may not take too long for your taste.

      Cheers

      Serge

      --

      Serge Rielau

      DB2 SQL Compiler Development

      IBM Toronto Lab

      #1; Sun, 30 Dec 2007 23:52:00 GMT
    • How about unloading the rows which shall remain in the tables,reload them

      with REPLACE and SET INTEGRITY IMMEDIATE UNCHECKED.

      This works well when the amount of records to be kept is much smaller than

      the amount which must be deleted.

      HTH

      Joachim

      <vb.db2.todaysummary.com.sharedvision.com> schrieb im Newsbeitrag

      news:1106589888.815765.224760.db2.todaysummary.com.c13g2000cwb.googlegr oups.com...

      >I need to delete records from several large tables - 1 million plus

      > rows. Some of these tables have nested foreign key relationships.

      > When I perform the delete statements, it can take forever as the

      > database works out the key relationships and commits things to the log.

      > Is there a way to speed up some of these deletes with out writing the

      > information to the database logs?

      > I am going to probably drop the RI on the tables and manually delete

      > from the child tables to improve things.

      > Any tips or suggestions would be appreciated. We have been using the

      > db2 loader with "replace" functionality but have new requirements that

      > prevent us from overwriting all the data.

      > Thanks,

      > Michael

      #2; Sun, 30 Dec 2007 23:53:00 GMT
    • vb.db2.todaysummary.com.sharedvision.com wrote:

      > I need to delete records from several large tables - 1 million plus

      > rows. Some of these tables have nested foreign key relationships.

      > When I perform the delete statements, it can take forever as the

      > database works out the key relationships and commits things to the

      log.

      > Is there a way to speed up some of these deletes with out writing the

      > information to the database logs?

      Don't know if this will work for you, but in case you don't want to

      drop your ri constraint & activate non-logging, and if your concern

      about deletion time is primarily that it is slowing down a linear

      workflow...

      Then you can move the delete step out of the normal load workflow into

      an independent process. This process would get scheduled to run at

      frequent intervals (ex: every 5 minutes), would delete all candidate

      rows up to a small amount (ex: 50,000 rows). This approach won't

      lessen the load on the server, and won't delete those rows prior to

      some other process. But it will do it with minimal impact to logs &

      locking, and might allow you to complete the load process more quickly.

      Of course, you'll want to be able to quickly find the candidate rows

      with an index or mdc.

      buck

      Perhaps you could delete the rows using a process that is scheduled

      indepdently , continually deletes (say) 50,000 rows at a time.

      #3; Sun, 30 Dec 2007 23:54:00 GMT