Updating partition key column
For example, if I have a table that has a partition key: Can I move a row from the first partition to the third partition by changing the Sample ID from 1 to (say) 500,000?Note: I'm tagging this as both sql server 20, since they both support partitioning. USE [Test] GO CREATE TABLE [IDRanges]( [ID] [int] NOT NULL ) GO CREATE PARTITION FUNCTION IDRange1 (int) AS RANGE LEFT FOR VALUES (10) ; GO --Add one record to each partition INSERT INTO IDRanges ([ID]) VALUES (17) INSERT INTO IDRanges ([ID]) VALUES (7) GO --Verify records in partition SELECT $PARTITION.It will also be completely wrong when it tries to use partition elimination to only query the relevant partition.I think you need to delete and re-insert each record to get them to move.PARTITION_NAME LAST_ANALYZED NUM_ROWS BLOCKS SAMPLE_SIZE HIGH_VALUE PORTAL_SERVICE_1 12/8/2016 41 1 PORTAL_SERVICE_2 6/8/2016 44 2 PORTAL_SERVICE_3 10/8/2016 29602 2014 29602 3 PORTAL_SERVICE_OTHERS 24/5/2016 0 110 DEFAULT i get error: Error report - SQL Error: ORA-14402: updating partition key column would cause a partition change 14402.00000 - "updating partition key column would cause a partition change" i am not allowed to use Enable Row Movement.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.By creating a partition key that is flexible (via an algorithm) we can adjust performance for particular clients, without redefining partition ranges. it is recommended to run runstats regulary in order to keep the statistics updated, but you should consider the beneift when doing so.
See the partitioning function just tells you what the partitioning function says. You can set up a partitioning function and run it without actually partitioning a table, as has been demonstrated here already.Then I declared the partitioning function and scheme: After you do this, when you query sys.partitions (I have 2005), you see that the table now has two partitions instead of just one for the table.This indicates that we have fully implemented partitioning for this table.IDRange1([ID]) AS Partition, COUNT(*) AS [COUNT] FROM IDRanges GROUP BY $PARTITION.
IDRange1([ID]) ORDER BY Partition ; GO --Move row between partitions UPDATE IDRanges SET [ID] = 8 WHERE [ID] = 17 GO --Verify records in partition SELECT $PARTITION.
Can anybody please suggest any alternative to update the row.