Updating partition key column
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.
I've read some conflicting information today on whether the partition column must be a part of the primary key. If you don't include your partition keys then you will get essentially an expensive table scan that goes across multiple tables (partitions).
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.
What happens if you use a city as a partition key and an office in that city moves elsewhere?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.Idea being, your partitioning should align itself with the primary key. 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?