Partitioning -- 4 : Row Movement

Do you expect Primary Keys to be updatable ?  Some argue that Primary Key values should be immutable.  The argument is that a Primary Key should not be modified.

What about Partition Keys ?  Would you allow a Partition Key to be updated ?

Let me take the SALES_DATA table again :

SQL> desc sales_data
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 SALE_ID                                   NOT NULL NUMBER
 SALE_DATE                                          DATE
 INVOICE_NUMBER                                     VARCHAR2(21)
 CUSTOMER_ID                                        NUMBER
 PRODUCT_ID                                         NUMBER
 SALE_VALUE                                         NUMBER

SQL> insert into sales_data
  2  values (sales_data_seq.nextval, 
  3          to_date('09-SEP-2019','DD-MON-YYYY'),
  4          'INV320001X',
  5          45,
  6          52,
  7          10000)
  8  /

1 row created.

SQL> commit;

Commit complete.

SQL> 

After the INSERT, I realise that the year in the SALE_DATE is wrong -- it is 2019 instead of 2018.  I need to update the row to set the year to 2018.
(Since the SALES_DATA table is partitioned to have a separate Partition for each year, this row has gone into the P_2019 Partition).

SQL> select * from sales_data
  2  where invoice_number='INV320001X' and customer_id=45;

   SALE_ID SALE_DATE INVOICE_NUMBER        CUSTOMER_ID PRODUCT_ID SALE_VALUE
---------- --------- --------------------- ----------- ---------- ----------
    320001 09-SEP-19 INV320001X                     45         52      10000

SQL> select * from sales_data partition (P_2019);

   SALE_ID SALE_DATE INVOICE_NUMBER        CUSTOMER_ID PRODUCT_ID SALE_VALUE
---------- --------- --------------------- ----------- ---------- ----------
    320001 09-SEP-19 INV320001X                     45         52      10000

SQL> 
SQL> update sales_data
  2  set sale_date = to_date('09-SEP-2018','DD-MON-YYYY')
  3  where sale_id=320001
  4  /
update sales_data
       *
ERROR at line 1:
ORA-14402: updating partition key column would cause a partition change


SQL> 

I encounter an error.  Oracle does not like updating a Partition Key value such that the row would have to move to a different Partition --- from the P_2019 Partition to the P_2018 Partition.

How would I allow updates that result in a row moving to a different Partition ?

SQL> alter table sales_data enable row movement;

Table altered.

SQL> update sales_data
  2  set sale_date = to_date('09-SEP-2018','DD-MON-YYYY')
  3  where sale_id=320001
  4  /

1 row updated.

SQL> commit;

Commit complete.

SQL> select * from sales_data partition (P_2019);

no rows selected

SQL> select * from sales_data partition (P_2018)
  2  where sale_id=320001
  3  /

   SALE_ID SALE_DATE INVOICE_NUMBER        CUSTOMER_ID PRODUCT_ID SALE_VALUE
---------- --------- --------------------- ----------- ---------- ----------
    320001 09-SEP-18 INV320001X                     45         52      10000

SQL> 

The ALTER TABLE ... ENABLE ROW MOVEMENT is a DDL command (needs to be issued only once to allow any number of subsequent updates to the tables rows) that allows a row to move from one Partition to another Partition.  In this case, the row moved from P_2019 to P_2018.

Moving rows from one Partition to another Partition is expensive.  Each row moved in such a manner results in
(a) marking deletion of the row from the original Partition
(b) physically inserting the *entire* rows (irrespective of length of the row) into the new Partition -- not just the SALE_DATE value but every column has to be written into a block in the new Partition
(c) updating *every* index (Global or Local) on the Table

Edit  14-Sep-18:
Also see two earlier posts :
"Enable Row Movement"
and
"Enable Row Movement with MSSM"

That is why it is not a good design to have frequently updated Partition Keys resulting in a row moving from one Partition to another.  You may have to reconsider the Partitioning definition or data and transaction flow in the application.

(Do you know where else ENABLE ROW MOVEMENT is required ?  There are other cases, not related to Partitioning, where you may have to ENABLE ROW MOVEMENT for a table.  By default when you CREATE a Table, ROW MOVEMENT is not enabled unless you explicitly enable it).