Shulou(Shulou.com)06/01 Report--

By default, oracle partition tables do not allow update operations on partition fields. If there is an update to partition fields, an error--ORA-14402: Updating partition key columns will cause partition changes. This situation allows update operations on partitioned fields by enabling row movement of the table:

alter table xxx enable row movement;

After that, you can successfully update the partition field. At the same time, because the rowid changes due to the physical movement of the row, the index leaf block of the mapped column value will be marked as deleted, and a new leaf block will be inserted. After redefining the partition, remember to turn off the row movement.

alter table xxx disable row movement;

There are three situations where row movement needs to be enabled

1. Update Partition Key

When a row of partition table is updated, if the updated column is a partition column, and the updated column value does not belong to the original partition, if this option is enabled, the row will be deleted from the partition and added to the partition to which the update belongs. Equivalent to an implicit delete+insert, but does not trigger the insert/delete trigger. If this option is not turned on, the error ORA-14402 will be reported in the update;

The special impact of this operation is that it is a DML operation, which is closely related to online transactions. For such an UPDATE, there are actually three steps: first delete the data from the original partition; transfer the original data to the new partition; and update the data.

The impact lies in the following aspects:

An UPDATE is broken down into DELET, INSERT, and UPDATE operations, increasing the performance burden. Among them, the query condition of Delete is the same as that of the original UPDATE. 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 increased.

If the Update statement also involves the Local Index field, the Local Index on both the new and old partitions will be updated.

Another point is that Row Movement conflicts with Domain Index: if a domain index is defined on the table, opening Row Movement fails, and vice versa.

2. Flaskback table to some time

Flashback Table actually deletes and inserts data in the table once through Flashback Query, so ROWID changes

3. Shrink Segment

Shrink Segment helps us compress data segments, defragment data, and lower high water marks to improve performance and save space.

It is necessary to explain that row movement is not a row migration. The biggest difference is that the rowid of the row migration is unchanged. When the row migration is an update row record, the data block does not have enough free space to accommodate the data row. Oracle moves the row to another data block while keeping the rowid of the row unchanged, and establishes a pointer to the new row position in the original data block. In this case, reading one row of data accesses two blocks of data, increasing IO and causing performance degradation.

If you create the primary key directly, use the global index, when a partition is dropped, the primary key will be invalid,

ALTERTABLE PHAECDA1.HCPSGLSINFOPZOW_NEW ADDCONSTRAINT PK_STAMP PRIMARYKEY(T_STAMP) tablespace xx;

Changed to local index, but does not include sub-sections, error reported

ALTERTABLE PHAECDA1.HCPSGLSINFOPZOW_NEW ADDCONSTRAINT PK_STAMP PRIMARYKEY(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 subset of keyword columns in UNIQUE index

Local index, primary key + partition key as the new primary key, automatically create partitions index ,index partitions partition table space into table by partition Partition

ALTERTABLE PHAECDA1.HCPSGLSINFOPZOW_NEW ADDCONSTRAINT PK_STAMP PRIMARYKEY(T_STAMP,CLDATE)USINGINDEXLOCAL;

First create index , then create primary key,

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

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.