ORA-14402: updating partition keyword columns will cause partition changes, ora-14402 keywords

ORA-14402: updating partition keyword columns will cause partition changes, ora-14402 keywords

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. This condition allows the update operation on the partition fields through the row migration of the opening table:

alter table xxx enable row movement;

Then, the partition field can be successfully updated. At the same time, rowid is changed due to the physical movement of the row, the index category of the corresponding column value will be marked as Division, and a new category will be inserted. After the definition of the partition is redefined, you will be able to remember the relevant row migration.

alter table xxx disable row movement;

The following three conditions require opening 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 necessary to describe that row movement is not a row-wide shift. The largest region is that the rowid of the row-wide shift does not change. When the row-wide shift is a row-based update operation, the number of rows does not have enough blank capacity to store data rows. Oracle moves this row to other data centers and keeps the rowid of this row unchanged at the same time, in the original data volume creation, the cursor points to the new row location. In this case, when a row of data is retrieved, two data records are displayed, increasing IO and reducing performance.

If you directly create a primary region and use the global index, when a partition is dropped, the primary region becomes invalid,

ALTER TABLE PHAECDA1.HCPSGLSINFOPZOW_NEW ADD CONSTRAINT PK_STAMP PRIMARY KEY(T_STAMP) tablespace xx;

Changed to local index, but does not contain partition, partition Partition

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 region + partition region as the new primary region, automatically creating a partition index, the index is divided by Partition to the Partition table of the table.

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 region,

Of course, the primary region can also be directly used as a partition