75 ways to demystify DB2 #79: Techtip : In DB2, will Update Record in a Partitioned Table move the record to the corresponding partition?

Body

Yes, update record in a partitioned table will move the record to the corresponding partition.

Below is a test to show exactly what happens when a row is updated in a partition table for the given scenario:

1.    Create a partition table ranged by column "ID", partition 0: ID (1-10), partition 1: ID (11-20)
2.    Insert 3 rows: ID = 1, ID = 2 and ID =11
3.    Use command db2 "select datapartitionnum(<column name>) from <table name>" to check which partition each record is in, the result shows that 1st and 2nd rows are in partition 0 and 3rd row is in partition 1.
4.    Turn on db2 trace.
5.    Update 2nd row to ID=12.
6.    Turn off db2 trace.
7.    Use command db2 "select datapartitionnum(<column name>) from <table name>" to check which partition each record is in after the update, the result shows that 1st row is in partition 0 and 2nd and 3rd rows are in partition 1 now.
 
This will show that the update action does move the record to the respective partition automatically when the partition range column is changed.
 
Analysis of the trace results and the source code shows that there are 3 major actions been performed:
1.    Delete the old record from the respective partition
2.    Insert the updated record into a temp table
3.    Insert the updated record into the actual table's respective partition
 
Thus, the update action is Delete -> Insert -> Insert.
For partition tables, the cost for UDI is high. The advantage of partition tables lays in search. Although overall performance of the system shouldn't be affected much (depends on how powerful the system is and what is running on it), when looking for large amount of UDI, normal table will be faster than partition tables.