ORACLE - Partitioning with changing values

Question

Assuming following table:

create table INVOICE(                                 
 INVOICE_ID NUMBER                           
,INVOICE_SK NUMBER 
,INVOICE_AMOUNT NUMBER
,INVOICE_TEXT VARCHAR2(4000 Char)
,B2B_FLAG NUMBER  -- 0 or 1                       
,ACTIVE NUMBER(1) -- 0 or 1                            
)
PARTITION BY LIST (ACTIVE)
SUBPARTITION BY LIST (B2B_FLAG)
( PARTITION p_active_1 values (1)
  ( SUBPARTITION sp_b2b_flag_11 VALUES (1)
  , SUBPARTITION sp_b2b_flag_10 VALUES (0)
  )
,
 PARTITION p_active_0 values (0)
  ( SUBPARTITION sp_b2b_flag_01 VALUES (1)
  , SUBPARTITION sp_b2b_flag_00 VALUES (0)
  )
)

For perfomance reasons the table should get a "Composite List-List" partitioning, see http://docs.oracle.com/cd/E18283_01/server.112/e16541/part_admin001.htm#i1006565.

The problematic point is, that the ACTIVE-Flag will change requently for a huge amount of records and sometimes also the B2B_FLAG. Will Oracle automatically recognize the records, for which the partitioning value has changed and move them to the appropriate partion or do I have to call some kind of maintenance function, in order to reorganize the partitions?

Answer 1

You need to enable row movement on the table or the update statement will fail with ORA-14402: updating partition key column would cause a partition change.

See the following testcase:

create table T_TESTPART
(
  pk       number(10),
  part_key number(10)
)
partition by list (part_key) (
 partition p01 values (1),
 partition p02 values (2),
 partition pdef values (default)
);
alter table T_TESTPART
  add constraint pk_pk primary key (PK);

Now insert a row and try to update the partitioning value:

insert into t_testpart values (1,1);
update t_testpart set part_key = 2 where pk = 1;

You will now get the Error mentioned above. If you enable row movement, the same statement will work and oracle will move the row to the other partition:

alter table t_testpart enable row movement;
update t_testpart set part_key = 2 where pk = 1;

I did not do any performance tests, but Oracle will probably delete the row from the first partition and insert it to the second partition. Consider this when using it in large scale. In my own databases, I usually only use partitioning on columns that do not change.

Further reading: http://www.dba-oracle.com/t_callan_oracle_row_movement.htm