Interval-Reference-Partitionierung: Partition-Merge und Row-Movement

5. Februar 2017 Aus Von Markus Flechtner

Wie häufig in diesem Blog, dient auch dieser Beitrag zur Beantwortung der Fragen von Kursteilnehmern. Diesmal geht es um das Thema Interval-Reference-Partitionierung: wie verhalten sich derart partitionierte Tabellen beim Partition-Merge und beim Row-Movement?

Unser Testbeispiel ist ein typischer Anwendungsfall für Interval-Reference-Partitionierung: eine nach Monaten partitionierte Auftragstabelle und eine zweite Tabelle mit den zugehörigen Positionen:

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.

Natürlich brauchen wir auch Daten in den beiden Tabellen:

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.

Welche Partitionen haben nun unsere beiden Tabellen:

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

Feststellung 1: es werden nur die Partitionen angelegt, die wirklich benötigt werden (das ist eine Eigenschaft der Interval-Partitionierung, aber es gilt auch für die Child-Tabelle)

Feststellung 2: die Benennung der Partitionen stimmt bei der Master- und der Child-Tabelle überein.

Jetzt fassen wir zwei Partitionen der Master-Tabelle zu einer Partition zusammen:

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

Wie wirkt sich das auf die Partitionen aus?

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

Was passiert, wenn wir Partitionen auf Child-Ebene zusammenfassen wollen?

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

Feststellung 3: es werden neue Partitionen (Namen) angelegt und die Partitionsnamen (Master / Child) passen nicht mehr zusammen (Master: SYS_P4277, Child: SYS_P4278).

Feststellung 4: die neue Child-Partition (SYS_P4278) wird es “Nicht-Interval”-partitioniert gekennzeichnet (Spalte INTERVAL), d.h. sie ist nicht automatisch durch Interval-Partitionierung entstanden.

Feststellung 5: Partition-Merge nur auf der Child-Ebene ist nicht möglich (ORA-14650)

Schauen wir uns jetzt einmal die zugehörigen Indizes an. Nach dem Merge sieht es dort wie folgt aus:

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

Feststellung 6: beim Partition Merge werden die Datensätze physisch verschoben. Wenn man – wie im obigen Beispiel – nicht mit “ALTER TABLE . MERGE PARTITIONS .. UPDATE INDEXES” arbeitet werden die Indizes UNUSABLE. In diesem Fall ist in Rebuild erforderlich.

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

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

Zusammengefasst: Partition-Merge bei Interval-Reference-Partitionierung ist möglich und wirkt sich auf beide Tabellen aus. Am besten mit “UPDATE INDEXES” arbeiten, damit die Indizes nicht UNUSABLE werden.

Zur zweiten Frage: was passiert, wenn, wir einen Datensatz so ändern, dass er in eine andere Partition verschoben werden muss? (ROW MOVEMENT)

Die Ausgangssituation:

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

Jetzt verschieben wir Auftrag #1 in eine andere Partition:

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, “ROW MOVEMENT” muss vorher erlaubt werden:

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

Feststellung 7: ROW MOVEMENT muss zuerst auf der Child-Tabelle aktiviert werden.

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;

Wie sieht es jetzt in den Tabellen aus?

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

Feststellung 8: Ein Update auf die Master-Tabelle, der zu einem ROW MOVEMENT führt, führt auch zu einer entsprechenden Verschiebung der Child-Datensätze.

Zum Abschluss legen wir neue Datensätze an, die zu neuen Partitionen führen:

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

Feststellung 9: bei Partitionen die durch Inserts und Interval-Referenz-Partitionierung erzeugt werden, stimmt die Benennung (Master + Child) überein.

MOS-Notes:

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

Werbung (Amazon-Partner-Link)