Updating partition key column would cause a partition change dating offer landing pages

Posted by / 23-Dec-2016 17:56

Updating partition key column would cause a partition change

Oracle lets you enable or disable the row movement for Tables and Indexes.SYNTAX: When you CREATE or ALTER a partitioned table, a row movement clause either ENABLE ROW MOVEMENT or DISABLE ROW MOVEMENT can be specified. [email protected] select table_name,partition_name,num_rows from user_tab_partitions where table_name='LINE_ITEMS'; TABLE_NAME PARTITION_ NUM_ROWS --------------- ---------- ---------- LINE_ITEMS P2014_01 10 LINE_ITEMS SYS_P4270 10 LINE_ITEMS SYS_P4271 10 LINE_ITEMS SYS_P4278 19 [email protected] update orders set order_date=to_date('27-FEB-2014','DD-MON-YYYY') where order_id=1; update orders set order_date=to_date('27-FEB-2014','DD-MON-YYYY') where order_id=1 * ERROR at line 1: ORA-14402: updating partition key column would cause a partition change select table_name,partition_name,num_rows from user_tab_partitions where table_name='ORDERS'; TABLE_NAME PARTITION_ NUM_ROWS --------------- ---------- ---------- ORDERS P2014_01 9 ORDERS SYS_P4270 10 ORDERS SYS_P4271 11 ORDERS SYS_P4272 10 ORDERS SYS_P4273 10 ORDERS SYS_P4274 10 ORDERS SYS_P4275 10 ORDERS SYS_P4276 9 ORDERS SYS_P4277 20 9 rows selected. Summary: partition merge is possible with interval reference partitionierung and affects both master and child table. Let’s continue with question #2: what happens if we update a row in the master table so that it has to be moved into another partition (ROW MOVEMENT) Initial situation: select table_name,partition_name,num_rows from user_tab_partitions where table_name='ORDERS'; TABLE_NAME PARTITION_ NUM_ROWS --------------- ---------- ---------- ORDERS P2014_01 10 ORDERS SYS_P4270 10 ORDERS SYS_P4271 11 ORDERS SYS_P4272 10 ORDERS SYS_P4273 10 ORDERS SYS_P4274 10 ORDERS SYS_P4275 10 ORDERS SYS_P4276 9 ORDERS SYS_P4277 19 9 rows selected.

For this purpose, we introduced already in 9i (if I recall it right) the package DBMS_REDEFINITION.Next step is to create an interim table of the structure, desired for the original table.In my case, I create it create table interim (id number, channel_id number(1), amount_sold number(4), cust_id number(4), time_id date) partition by range (cust_id) interval (10) (partition p1 values less than (10)); Table created.As often, I use this blog to answer questions of training participants. This time, it’s the question what happens on interval-reference-partitioned tables when merging partitions or when updates cause row movement.

updating partition key column would cause a partition change-33updating partition key column would cause a partition change-84updating partition key column would cause a partition change-17

The Bitmap indexes on the fact table are automatically created as Local (rather than as the default Global) Indexes and are only rebuild the Bitmap Indexes on the partitions that have any data changes.

One thought on “updating partition key column would cause a partition change”