Interval-Reference-Partitioning: Partition-Merge and Row-Movement

2017-02-05 Off By Markus Flechtner

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.

Our example is a typical use case for interval-reference partitioning: master table with orders (partitioned by order date(month)) and a second table with line items,

TVD12@TVD12> create table orders
 2 (
 3 order_id number not null constraint pk$orders primary key using index,
 4 order_date date not null
 5 )
 6 partition by range (order_date) interval (numtoyminterval(1,'month'))
 7 (
 8 partition p2014_01 values less than (to_date('01-feb-2014','DD-MON-YYYY'))
 9 )
 10 ;

Table created.

TVD12@TVD12>
TVD12@TVD12> create table line_items
 2 (
 3 order_id number not null,
 4 pos number not null,
 5 item_id number not null,
 6 quantity number not null,
 7 constraint pk$line_items primary key (order_id, pos) using index,
 8 constraint fk$order_id foreign key (order_id) references orders (order_id)
 9 )
 10 partition by reference (fk$order_id);

Table created.

Let’s insert some data:

TVD12@TVD12> insert into orders
 2 select rownum,to_date('01-JAN-2014','DD-MON-YYYY')+rownum*3
 3 from all_objects where rownum<100;

99 rows created.

TVD12@TVD12> insert into line_items
 2 select order_id,1,99,1
 3 from orders where rownum < 50;

49 rows created.

Which partitions were created?

TVD12@TVD12> select partition_name,high_value from user_tab_partitions
 2 where table_name='ORDERS'
 3 ;

PARTITION_ HIGH_VALUE
---------- ----------------------------------
P2014_01 TO_DATE(' 2014-02-01 00:00:00', ..
SYS_P4268 TO_DATE(' 2014-03-01 00:00:00', ..
SYS_P4269 TO_DATE(' 2014-04-01 00:00:00', ..
SYS_P4270 TO_DATE(' 2014-05-01 00:00:00', ..
SYS_P4271 TO_DATE(' 2014-06-01 00:00:00', ..
SYS_P4272 TO_DATE(' 2014-07-01 00:00:00', ..
SYS_P4273 TO_DATE(' 2014-08-01 00:00:00', ..
SYS_P4274 TO_DATE(' 2014-09-01 00:00:00', ..
SYS_P4275 TO_DATE(' 2014-10-01 00:00:00', ..
SYS_P4276 TO_DATE(' 2014-11-01 00:00:00', ..

10 rows selected.

TVD12@TVD12> select partition_name,high_value,interval from user_tab_partitions
 2 where table_name='LINE_ITEMS'
 3 ;

PARTITION_ HIGH_VALUE INT
---------- ---------- ---
P2014_01 NO
SYS_P4268 YES
SYS_P4269 YES
SYS_P4270 YES
SYS_P4271 YES

Conclusion 1: only the necessary partitions are created (this is a feature for interval partitioned tables, but it’s valid for the child table, too)

Conclusion 2: partition naming is consistent between master and child.

Now we’ll merge two partitions of master table:

TVD12@TVD12> alter table orders merge partitions &p1,&p2;
Enter value for p1: SYS_P4268
Enter value for p2: SYS_P4269
old 1: alter table orders merge partitions &p1,&p2
new 1: alter table orders merge partitions SYS_P4268,SYS_P4269

What happens to the partitions?


TVD12@TVD12> column high_value format a85
TVD12@TVD12> select partition_name,high_value from user_tab_partitions where table_name='ORDERS';

PARTITION_ HIGH_VALUE
---------- -------------------------------------------------------------------------------------
P2014_01 TO_DATE(' 2014-02-01 00:00:00', ..
SYS_P4270 TO_DATE(' 2014-05-01 00:00:00', ..
SYS_P4271 TO_DATE(' 2014-06-01 00:00:00', ..
SYS_P4272 TO_DATE(' 2014-07-01 00:00:00', ..
SYS_P4273 TO_DATE(' 2014-08-01 00:00:00', ..
SYS_P4274 TO_DATE(' 2014-09-01 00:00:00', ..
SYS_P4275 TO_DATE(' 2014-10-01 00:00:00', ..
SYS_P4276 TO_DATE(' 2014-11-01 00:00:00', ..
SYS_P4277 TO_DATE(' 2014-04-01 00:00:00', ..

9 rows selected.

TVD12@TVD12>
TVD12@TVD12> column high_value format a10
TVD12@TVD12> select partition_name,high_value,interval from user_tab_partitions where table_name='LINE_ITEMS';

PARTITION_ HIGH_VALUE INT
---------- ---------- ---
P2014_01 NO
SYS_P4270 YES
SYS_P4271 YES
SYS_P4278 NO

What happens if we try to merge partitions on child level only?

TVD12@TVD12> alter table line_items merge partitions &p3,&p4;
Enter value for p3: SYS_P4270
Enter value for p4: SYS_P4271
old 1: alter table line_items merge partitions &p3,&p4
new 1: alter table line_items merge partitions SYS_P4270,SYS_P4271
alter table line_items merge partitions SYS_P4270,SYS_P4271
 *
ERROR at line 1:
ORA-14650: operation not supported for reference-partitioned tables

Conclusion 3: during merge new partitions are created and partition names (master/child) don’t match anymore (master: SYS_P4277, child: SYS_P4278).

Conclusion 4: the new child partition (SYS_P4278) is marked as “Non-Interval” (column INTERVAL), i.e. it’s a “range partition”

Conclusion 5: partition level on child level only is not possible (ORA-14650)

Let’s take a look at the indexes after the merge:

TVD12@TVD12> column table_name format a15
TVD12@TVD12> column index_name format a30
TVD12@TVD12>
TVD12@TVD12> select table_name,index_name,partitioned,status
 2 from user_indexes where
 3 table_name in ('ORDERS','LINE_ITEMS')
 4 order by table_name,index_name;

TABLE_NAME INDEX_NAME PAR STATUS
--------------- ------------------------------ --- --------
LINE_ITEMS PK$LINE_ITEMS NO UNUSABLE
ORDERS PK$ORDERS NO UNUSABLE

Conclusion 6: partition merge leads to physical row movement. If – like in the example – you do not use “ALTER TABLE . MERGE PARTITIONS .. UPDATE INDEXES” indexes will become invalid and you have to rebuild them.

TVD12@TVD12> alter index pk$orders rebuild;
Index altered.

TVD12@TVD12> alter index pk$line_items rebuild;
Index altered.

Summary: partition merge is possible with interval reference partitionierung and affects both master and child table. You should use “UPDATE INDEXES”.

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:

TVD12@TVD12> execute dbms_stats.gather_table_stats(ownname=>user,tabname=>'ORDERS');
PL/SQL procedure successfully completed.

TVD12@TVD12> execute dbms_stats.gather_table_stats(ownname=>user,tabname=>'LINE_ITEMS');
PL/SQL procedure successfully completed.

TVD12@TVD12>
TVD12@TVD12> 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.

TVD12@TVD12> 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

TVD12@TVD12> select order_id,order_date from orders where order_date<to_date('01-FEB-2014','DD-MON-YYYY');

ORDER_ID ORDER_DATE
---------- ------------------
 1 04-JAN-14
 2 07-JAN-14
..

Let’s update order #1:

TVD12@TVD12> 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

OK, we should enable row movement first.

TVD12@TVD12> alter table orders enable row movement;
alter table orders enable row movement
*
ERROR at line 1:
ORA-14662: row movement cannot be enabled

Conclusion 7: ROW MOVEMENT must be enabled on child level first.

TVD12@TVD12>
TVD12@TVD12> alter table line_items enable row movement;
Table altered.

TVD12@TVD12> alter table orders enable row movement;
Table altered.

TVD12@TVD12>
TVD12@TVD12> pause

TVD12@TVD12>
TVD12@TVD12> update orders set order_date=to_date('27-FEB-2014','DD-MON-YYYY') where order_id=1;
1 row updated.

TVD12@TVD12>
TVD12@TVD12> commit;

Let’s take a look at the tables:

TVD12@TVD12> execute dbms_stats.gather_table_stats(ownname=>user,tabname=>'ORDERS');

PL/SQL procedure successfully completed.

TVD12@TVD12> execute dbms_stats.gather_table_stats(ownname=>user,tabname=>'LINE_ITEMS');

PL/SQL procedure successfully completed.
TVD12@TVD12>
TVD12@TVD12> 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.

TVD12@TVD12> 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 9
LINE_ITEMS SYS_P4270 10
LINE_ITEMS SYS_P4271 10
LINE_ITEMS SYS_P4278 20

Conclusion 8: records in both master and child table are moved when an update causes row movement in the master table.
Finally, we’ll create a row which has to be stored in new partitions.

TVD12@TVD12> insert into orders (order_id,order_date) values (100,sysdate);
1 row created.

TVD12@TVD12> insert into line_items (order_id,pos,item_id,quantity) values (100,1,999,2);
1 row created.

TVD12@TVD12> commit;
Commit complete.
TVD12@TVD12>
TVD12@TVD12> column high_value format a85
TVD12@TVD12> select partition_name,high_value from user_tab_partitions where table_name='ORDERS';

PARTITION_ HIGH_VALUE
---------- -------------------------------------------------------------------------------------
P2014_01 TO_DATE(' 2014-02-01 00:00:00', ..
SYS_P4270 TO_DATE(' 2014-05-01 00:00:00', ..
SYS_P4271 TO_DATE(' 2014-06-01 00:00:00', ..
SYS_P4272 TO_DATE(' 2014-07-01 00:00:00', ..
SYS_P4273 TO_DATE(' 2014-08-01 00:00:00', ..
SYS_P4274 TO_DATE(' 2014-09-01 00:00:00', ..
SYS_P4275 TO_DATE(' 2014-10-01 00:00:00', ..
SYS_P4276 TO_DATE(' 2014-11-01 00:00:00', ..
SYS_P4277 TO_DATE(' 2014-04-01 00:00:00', ..
SYS_P4279 TO_DATE(' 2017-03-01 00:00:00', ..

10 rows selected.

TVD12@TVD12>
TVD12@TVD12> column high_value format a10
TVD12@TVD12> select partition_name,high_value,interval from user_tab_partitions where table_name='LINE_ITEMS';

PARTITION_ HIGH_VALUE INT
---------- ---------- ---
P2014_01 NO
SYS_P4270 YES
SYS_P4271 YES
SYS_P4278 NO
SYS_P4279 YES

Conclusion 9: when partitions are automatically created by interval partitioning, the names (master and child) are identical.
MOS-Notes:

1519042.1: How to Create Interval-Reference Partitioned Tables in Oracle 12c
1568010.1: 12c Partitioning Enhancements, New Features

Ad (Amazon Link):

CategoryCommon Oracle 12c