Updating partition key column
There are many types of scenarios where partitioning scheme does exactly follows the primary key's first column - for instance in a data warehouse scenario where the snapshot date of a fact table is usually the partition column as well as the first column in the primary key.
But equally, in OLTP environments where the PK is an IDENTITY or other surrogate key, it makes little sense to use this for the partition, since partitioning on arbitrary numbers is not normally terribly useful.
We intend to use a single SMALLINT column, PARTN_ID (Partition ID), as the partition key column on each partitioned table. If we UPDATE column PARTN_ID for existing rows via static SQL, will DB2 automatically redistribute the updated rows to the partitions indicated by the new values in PARTN_ID, or will the redistribution only happen at the next REORG? Will a RUNSTATS be required or merely advisable after the redistribution or REORG? Will plans need to be rebound after the redistirbution or REORG?
The column value would determine the partition to which INSERTed rows would be put. 1) The DBMS (in this case DB2) will locate data according to data partitioning key. If you update the partitioning key, and the partitioning map says they key belongs in another partition, it will be moved there, but the runstats is now out of balance, because the row count is now different among the partitions.
One of the relatively newer features in Oracle concerns the moving of rows. An early use of row movement was highlighted in Oracle8i, and row movement then, as well as now, applied to moving rows in a partitioned table.
Why would a row move and who or what controls that movement? With newer releases of the Oracle RDBMS, where else does row movement come into play, and are there any gotcha's with respect to row movement operations?
EDIT For part 2, I In addition to JNK's answer, you probably should read this article which discusses aligning table partitions and index partitions.I am designing a database in IBM DB2 UDB version 8 for z/OS and OS/390.The database will have several very large tables that will require partitioning.In that case, the algorithm that populates PARTN_ID would be updated, static SQL UPDATEs of existing PARTN_ID columns for the client would be executed (thus moving the data), and RUNSTATS would be executed.
I agree that a partitioning key should satisfy well the SQL predicates, but we are also targeting overall performance. db2 will move a row between partitions when you update the partitioning key.So the answer is, yes, it is preferred to be part of the PK.