Updating partition key column mobile alabama dating
The key should be chosen for its ability to satisfy sql where clause and group by queries and by its very nature should not be updateable, even though DB2 permts it.By frequently updating they key, you will run into poor access plans and loads of broadcasting as well as mkaing it next to impossible to co locate the users with their correct partitions, which will require additional network resources for the FCM blocks. Does a row move to another table, or is row movement constrained to the row's container (i.e., a table)?This article looks at three common cases or situations where row movement needs to be enabled.Or two offices in the same city (from different groups or business units within the same company) merge into one location?You could split the default partition and add the new location name.
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.if the total number of rows in a partition (either the one you delete from or the one you insert to) does not change in more than 10% i would not run runstats especially for that (assuming you are running runstats on some regular basis so the new statistics of the tables would be updated eventually).beside for the case that you describe here, it is a good thing to rebind plans after you run runstatns and reorgs so db2 will be able to re-evaluate the access path for the specific scenario you described, when you set the partitioning key the way you said you do get the maximum flexability, and i understand why you would like to move some clients between partitions now and then, but maybe you should take another approache in db2 for zos you can alter the partitioning key (does not matter if you used index based or table based partitioning) and after you do that run a reorg tablespace that will rebalance the rows.All this is possible, but it begs the question of why would you choose a column as the partitioning key if you know in advance that you are going to frequently update it?Not only will this create loads of additional work for you, but from a design point of view its a poor choice.
In OLTP systems, you also tend to partition by date the most (probably not in the PK), but potentially also regionally or by some kind of organizational division (maybe in the PK if you aren't using a surrogate). It has to be part of a Candidate Key if not part of the Primary key itself.