Updating partition key column willard libby carbon dating

Posted by / 13-Sep-2018 21:37

Updating partition key column

This behavior can be seen in inserts and deletes on data-only-locked tables that do not use partitions.It exists for updates only when the partition-key values are updated such that the row moves from one partition to another.

Partitioning enhances the performance, manageability, and availability of a wide variety of applications and helps reduce the total cost of ownership for storing large amounts of data.

Can you help us to understand why there is a dead lock on updating the partition key column on a reference partition table? Session two is waiting for the child lock on row 1. It's actually an update to the child that causes this.

(basically, want to know why the third update on the below script is blocking, a) we are not updating a rows that is blocked by another session and b) we are not even updating the primary key column (since that could lead to Full table lock on child table due to un-indexed foreign keys) ) drop table child_tab purge; drop table parent_tab purge; create table parent_tab ( col1 number, col2 number, constraint parent_tab_pk primary key(col1) ) enable row movement partition by list(col2) ( partition p1 values (1), partition p2 values (2), partition p3 values (3), partition p4 values (4), partition p5 values (default) ); create table child_tab ( col1 number not null, col2 number, constraint child_tab_fk foreign key(col1) references parent_tab ) enable row movement partition by reference(child_tab_fk); insert into parent_tab(col1,col2) select level,level from dual connect by level When you update the partitioning key in the parent table, Oracle must also update the corresponding rows in the child. I received the deadlock when updating the parent: update /* opt_param('_and_pruning_enabled', 'false') */ "CHRIS"."CHILD_TAB" partition ( dataobj_to_partition("CHRIS"."PARENT_TAB" , :1)) move to partition (dataobj_to_partition( "CHRIS"."PARENT_TAB" , :1)) set "COL1" = "COL1" where "COL1" = :1 So, incase of reference partition table, care should be taken **NOT** to update the partition key column either using hand coded/ frame work generated sql. Chirs, do you think any solution available to prevent this deadlock other than not updating partition key column?

So session 1 is waiting for the lock on child row 2.

14402, 00000, "updating partition key column would cause a partition change"// *Cause: An UPDATE statement attempted to change the value of a partition// key column causing migration of the row to another partition// *Action: Do not attempt to update a partition key column or make sure that// the new partition key is within the range containing the old// partition key.

updating partition key column-20updating partition key column-19updating partition key column-55

The new partition ID is determined with the newly updated record's partition key columns.