Updating partition key column

Rated 4.63/5 based on 984 customer reviews

So the answer is, yes, it is preferred to be part of the PK.If not another key, which is equally good enough to be a PK. 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?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).

Idea being, your partitioning should align itself with the primary key.

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.

As far back as Oracle 8.1.5 documentation, row movement applied to updatable partition keys.

A list partition where the partition key is a specific value is a good example of this.

Leave a Reply