Topic
  • 5 replies
  • Latest Post - ‏2012-04-23T05:45:14Z by sakumar9
sakumar9
sakumar9
79 Posts

Pinned topic Is UPDATE = DELETE and INSERT?

‏2012-04-07T20:24:15Z |
Is UPDATE equivalent to DELETE and INSERT in DB2?
Updated on 2012-04-23T05:45:14Z at 2012-04-23T05:45:14Z by sakumar9
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Is UPDATE = DELETE and INSERT?

    ‏2012-04-08T23:27:57Z  
    sakumar9, If you are asking if DB2 does an Delete / Insert when it does an Update, the answer is no.
  • doole
    doole
    208 Posts

    Re: Is UPDATE = DELETE and INSERT?

    ‏2012-04-10T18:05:51Z  
    sakumar9, If you are asking if DB2 does an Delete / Insert when it does an Update, the answer is no.
    Logically, there is no difference between updating a row and deleting the old copy of the row and inserting the new copy. The delete/insert model takes a lot more I/O and processing, however, so DB2 prefers to update the row in place. There are also a number of other features that are tied specifically to update processing, such as monitoring and trigger invocation.

    There are a few cases where DB2 must use delete and insert. For example, an updating of the partitioning key in a range or hash partitioned table may require the row to be moved between physical tables or even database members. In this case, DB2 cannot do the update in place and must use delete and insert. (Note that the operation is still considered an update as far as monitoring and triggers are concerned.)


    Doug Doole
    DB2 for Linux, UNIX and Windows
    IBM Toronto Labs
  • sakumar9
    sakumar9
    79 Posts

    Re: Is UPDATE = DELETE and INSERT?

    ‏2012-04-11T08:10:13Z  
    The reason I asked this question is that I can see overflow records in my MQTs. This MQT is incrementally refreshed.

    1. This MQT only has DATE and INTEGER columns. In that case, UPDATE should not cause any overflow. Am I right?
    2. Along with UPDATES, it also does INSERTs which again should not cause overflows. Correct?
    3. It also does some DELETE which I don't understand why because our base table is INSERT only and no deletes happen on that. Then why do I see DELETE in access plan?
    4. Even if it does DELETE, that also does not create any overflow records.

    Is my understanding correct? You can find the access plan for refresh table attached.

    For you reference: MSD_MNTHLY_TABLE is my MQT and MSS_MNTHLY_TABLE is the staging table.
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: Is UPDATE = DELETE and INSERT?

    ‏2012-04-13T17:04:12Z  
    • sakumar9
    • ‏2012-04-11T08:10:13Z
    The reason I asked this question is that I can see overflow records in my MQTs. This MQT is incrementally refreshed.

    1. This MQT only has DATE and INTEGER columns. In that case, UPDATE should not cause any overflow. Am I right?
    2. Along with UPDATES, it also does INSERTs which again should not cause overflows. Correct?
    3. It also does some DELETE which I don't understand why because our base table is INSERT only and no deletes happen on that. Then why do I see DELETE in access plan?
    4. Even if it does DELETE, that also does not create any overflow records.

    Is my understanding correct? You can find the access plan for refresh table attached.

    For you reference: MSD_MNTHLY_TABLE is my MQT and MSS_MNTHLY_TABLE is the staging table.
    do you use compression?
  • sakumar9
    sakumar9
    79 Posts

    Re: Is UPDATE = DELETE and INSERT?

    ‏2012-04-23T05:45:14Z  
    Yes, all our tables are compressed.