Interval-Reference-Partitionierung: Partition-Merge und Row-Movement
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)