ORA-14402: updating partition keyword columns will cause partition changes

ORA-14402: updating partition keyword columns will cause partition changes

By default, Oracle Partition Table for partition fields is not allowed to update the operation, if there is a forward update on the partition field, the error -- ORA-14402: updating the partition keyword column will change the partition. In this case, you can enable row movement of the table to allow update operations on partition fields:

Alter table xxx enable row movement;

Then, the partition field can be updated successfully, and the rowid changes due to the physical movement of the row. The index leaf blocks of the corresponding column values are marked as deleted and new leaf blocks are inserted, after the partition is redefined, close row movement.

Alter table xxx disable row movement;

In the following three cases, you must enable row movement.
1. Update the Partition Key.
When a row in a partition table is updated, if the updated partition column is updated and the updated column value does not belong to the original partition, if this option is enabled, the row is deleted from the partition and added to the updated partition. It is equivalent to an implicit delete + insert, but does not trigger the insert/delete trigger. If this option is not enabled, an error ORA-14402 will be reported during the update;
This operation has an effect on DML operations, which are closely related to online transaction. For such an UPDATE, there are three steps: first Delete the data from the original partition, transfer the original data to the new partition, and UPDATE the data.
Its impact lies in the following aspects:
An UPDATE is divided into DELET, INSERT, and UPDATE operations, increasing the performance burden. The query condition of DELETE is the same as that of the original UPDATE query. The query condition of the new UPDATE is the new ROWID generated based on INSERT, and the corresponding Redo Log and Undo Log will be added;
If the Update statement also involves the Local Index field, the Local Index on the New and Old partitions will be updated.
Another point is that the Row Movement conflicts with the Domain Index: If the table defines a Domain Index, enabling the Row Movement will fail, and vice versa.

2. Flaskback table to a certain time
The Flashback Table uses the Flashback Query to delete and insert data in the Table. Therefore, the ROWID changes.
3. Shrink Segment
Shrink Segment can help us compress data segments, organize data fragments, and reduce high water levels to improve performance and save space.

It is worth noting that row movement is not a row migration. The biggest difference is that the rowid of row migration remains unchanged. When row migration is an update row record, the data block does not have enough free space to accommodate data rows. Oracle moves this row to another data block, keeps the rowid of this row unchanged, and points a pointer to the new row location in the original data block. In this case, reading a row of data will access two data blocks and increase IO, resulting in performance degradation.

If you create a primary key directly, the global index is used. When a partition is dropped, the primary key becomes invalid,

Alter table PHAECDA1.HCPSGLSINFOPZOW _ new add constraint PK_STAMP primary key (T_STAMP) tablespace xx;

Change to local index, but does not contain partition segments. An error is returned.

Alter table PHAECDA1.HCPSGLSINFOPZOW _ new add constraint PK_STAMP primary key (T_STAMP) using index local tablespace xxx;

ORA-14039: partitioning columns must form a subset of key columns of a UNIQUE

ORA-14039: partition columns must form a keyword column subset of the UNIQUE index

Local index: Primary Key + Partition key as the new primary key. The Partition index is automatically created. The index is partitioned to the Partition table space of the table according to the Partition.

Alter table PHAECDA1.HCPSGLSINFOPZOW _ new add constraint PK_STAMP primary key (T_STAMP, CLDATE) using index local;

Create an index first, and then create a primary key,

Of course, primary keys can also be directly used as partition segments.