Partitionspflege mit Oracle 11g leicht gemacht

von Frank Schneede, ORACLE Deutschland GmbH

Die Technik der Partitionierung ist eigentlich nicht neu, sondern bereits mit der Oracle Datenbankversion 8 als RANGE-Partitionierung eingeführt worden. Schrittweise um HASH-, LIST-Partitioning sowie zusammengesetzte Partitionierungsmöglichkeiten und verschiedene Maintenance-Operationen ergänzt, bietet die Partitioning Option schon in Oracle 10g reichhaltige Möglichkeiten, große Datenmengen in Datawarehouses zu strukturieren und zu verwalten. In der neuen Datenbankversion Oracle 11g sind die zur Verfügung stehenden Techniken erneut umfangreich erweitert worden.

Eine besonders hilfreiche Erweiterung der Partitioning Option in 11g stellen

INTERVALL-Partitionierung
REFERENZ-Partitionierung

dar. Die Verwaltung eines Objektes, das nach einer zeitlichen Dimension (z. B. Verkäufe nach Tag/Woche/Monat) partitioniert ist, nimmt insbesondere beim Periodenwechsel sehr viel Zeit in Anspruch. Wenn der DBA nicht durch rechtzeitige Anlage einer neuen bzw. durch das Vorhandensein einer Default-Partition dafür Sorge getragen hat, dass die ins DWH fließenden Daten abgelegt werden können, kommt es sogar zu Fehlern im ETL-Prozess. Ähnlich aufwändig gestaltet sich die Erweiterung von partitionierten Tabellen, die über referentielle Integritäten (z. B. Bestellungen und Bestellpositionen) verbunden sind.

In folgendem Tipp werden die Vorteile der Verwendung von INTERVALL- und REFERENZ-Partitionierung an zwei kleinen Beispielen demonstriert.

Das erste Beispiel zeigt die Anlage einer RANGE-partitionierte Tabelle NEWSALES, die Tagesverkäufe enthält, deren Umstellung auf INTERVALL-Partitionierung und die Automatismen dieser Partitionierungsart. Anschließend werden noch Modifikationen an der Tabellenstruktur demonstriert.

Im ersten Schritt wird die Tabelle NEWSALES angelegt. Da die Partitionierung nach einem Feld des Datentypes DATE erfolgt, muss natürlich auf die NLS-Settings geachtet werden:

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE=GERMAN;

Session wurde geändert.

SQL> CREATE TABLE newsales
  2  ( prod_id       NUMBER(6)    NOT NULL
  3  , cust_id       NUMBER       NOT NULL
  4  , time_id       DATE         NOT NULL
  5  , channel_id    CHAR(1)      NOT NULL
  6  , promo_id      NUMBER(6)    NOT NULL
  7  , quantity_sold NUMBER(3)    NOT NULL
  8  , amount_sold   NUMBER(10,2) NOT NULL
  9  )
 10  PARTITION BY RANGE (time_id)
 11  ( PARTITION p_before_1_jan_2008 VALUES LESS THAN (TO_DATE('01-JAN-2008','dd-mon-yyyy'))
 12  , PARTITION p_1_jan_2008        VALUES LESS THAN (TO_DATE('02-JAN-2008','dd-mon-yyyy'))
 13  , PARTITION p_2_jan_2008        VALUES LESS THAN (TO_DATE('03-JAN-2008','dd-mon-yyyy'))
 14  , PARTITION p_3_jan_2008        VALUES LESS THAN (TO_DATE('04-JAN-2008','dd-mon-yyyy'))
 15  , PARTITION p_4_jan_2008        VALUES LESS THAN (TO_DATE('05-JAN-2008','dd-mon-yyyy'))
 16  , PARTITION p_5_jan_2008        VALUES LESS THAN (TO_DATE('06-JAN-2008','dd-mon-yyyy'))
 17  )
 18  ENABLE ROW MOVEMENT
 19  PARALLEL
 20  /

Tabelle wurde erstellt.

SQL>

Die Versorgung der Tabelle mit Daten funktioniert einwandfrei, bis für einen Datensatz keine Zielpartition ermittelt werden kann. Es erscheint eine Fehlermeldung:

SQL> INSERT INTO newsales VALUES (11160,17450,TO_DATE('01-JAN-2008','DD-MON-YYYY'),'I',9999,19,798) ;

1 Zeile wurde erstellt.

SQL> INSERT INTO newsales VALUES (1340,33710,TO_DATE('02-JAN-2008','DD-MON-YYYY'),'S',9999,16,1264) ;

1 Zeile wurde erstellt.

SQL> INSERT INTO newsales VALUES (25270,65880,TO_DATE('05-JAN-2008','DD-MON-YYYY'),'I',9999,5,210) ;

1 Zeile wurde erstellt.

SQL> INSERT INTO newsales VALUES (1615,73480,TO_DATE('05-JAN-2008','DD-MON-YYYY'),'I',9999,8,96) ;

1 Zeile wurde erstellt.

SQL> INSERT INTO newsales VALUES (1900,84910,TO_DATE('06-JAN-2008','DD-MON-YYYY'),'I',9999,42,378) ;

INSERT INTO newsales VALUES (1900,84910,TO_DATE('06-JAN-2008','DD-MON-YYYY'),'I',9999,42,378)
            *
FEHLER in Zeile 1:
ORA-14400: Eingefügter Partitionsschlüssel kann keiner Partition zugeordnet werden


SQL>

Dieser Fehler wird durch die Umstellung der starren RANGE-Partitionierung auf die sich dynamisch erweiternde INTERVALL-Partitionierung nachhaltig vermieden, die Daten lassen sich anschließend problemlos einfügen:

SQL> ALTER TABLE newsales
     SET INTERVAL (NUMTODSINTERVAL(1,'DAY'));

Tabelle wurde geändert.

SQL> INSERT INTO newsales VALUES (1900,84910,TO_DATE('06-JAN-2008','DD-MON-YYYY'),'I',9999,42,378) ;

1 Zeile wurde erstellt.

SQL> INSERT INTO newsales VALUES (8085,37900,TO_DATE('09-JAN-2008','DD-MON-YYYY'),'S',9999,1,68) ;

1 Zeile wurde erstellt.

SQL> INSERT INTO newsales VALUES (755,26590,TO_DATE('09-JAN-2008','DD-MON-YYYY'),'I',9999,11,132) ;

1 Zeile wurde erstellt.

SQL> INSERT INTO newsales VALUES (10,68060,TO_DATE('09-JAN-2008','DD-MON-YYYY'),'P',9999,28,4900) ;

1 Zeile wurde erstellt.

SQL> INSERT INTO newsales VALUES (13425,109310,TO_DATE('10-JAN-2008','DD-MON-YYYY'),'I',9999,1,68) ;

1 Zeile wurde erstellt.

SQL> INSERT INTO newsales VALUES (1955,65190,TO_DATE('10-JAN-2008','DD-MON-YYYY'),'S',9999,28,1512) ;

1 Zeile wurde erstellt.

SQL> COMMIT;

Transaktion mit COMMIT abgeschlossen.

SQL>

Die Abfrage des Data Dictionaries zeigt, dass auch für die anderen geladenen Daten automatisch Zielpartitionen angelegt worden sind. Diese tragen jedoch systemgenerierte Namen:

SQL> SELECT partition_name, high_value
     FROM   user_tab_partitions
     WHERE  table_name = 'NEWSALES'
     ORDER BY partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------
P_BEFORE_1_JAN_2008            TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
P_1_JAN_2008                   TO_DATE(' 2008-01-02 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
P_2_JAN_2008                   TO_DATE(' 2008-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
P_3_JAN_2008                   TO_DATE(' 2008-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
P_4_JAN_2008                   TO_DATE(' 2008-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
P_5_JAN_2008                   TO_DATE(' 2008-01-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
SYS_P176                       TO_DATE(' 2008-01-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
SYS_P177                       TO_DATE(' 2008-01-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
SYS_P178                       TO_DATE(' 2008-01-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL

9 Zeilen ausgewählt.

SQL>

Da die Benutzung systemgenerierter Partitionsnamen möglicherweise aus Sicht der Applikation nicht gewünscht ist, muss an dieser Stelle manuell nachgearbeitet werden. Dieses stellt jedoch kein Problem dar, denn es sind auf dieser Tabelle auch weiterhin alle Operationen möglich, die für eine Restrukturierung notwendig erscheinen, wie z. B. MERGE oder RENAME von Partitionen. Im Data Dictionary können die Änderungen nachvollzogen werden:

SQL> ALTER TABLE NEWSALES
  2  MERGE PARTITIONS FOR( TO_DATE('01-JAN-2008','DD-MON-YYYY'))
  3  ,                FOR( TO_DATE('02-JAN-2008','DD-MON-YYYY'))
  4  INTO PARTITION p_1_to_2_jan_2008
  5  /

Tabelle wurde geändert.

SQL> ALTER TABLE newsales
  2  RENAME PARTITION sys_p176
  3  TO p_6_jan_2008;

Tabelle wurde geändert.

SQL> SELECT partition_name, high_value
     FROM   user_tab_partitions
     WHERE  table_name = 'NEWSALES'
     ORDER BY partition_position;

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------------------------------------------
P_BEFORE_1_JAN_2008            TO_DATE(' 2008-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
P_1_TO_2_JAN_2008              TO_DATE(' 2008-01-03 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
P_3_JAN_2008                   TO_DATE(' 2008-01-04 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
P_4_JAN_2008                   TO_DATE(' 2008-01-05 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
P_5_JAN_2008                   TO_DATE(' 2008-01-06 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
P_6_JAN_2008                   TO_DATE(' 2008-01-07 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
SYS_P177                       TO_DATE(' 2008-01-10 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL
SYS_P178                       TO_DATE(' 2008-01-11 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CAL

8 Zeilen ausgewählt.

SQL>

Anhand dieses ersten Beispiels ist ersichtlich, welche Vorteile sich durch die Verwendung der INTERVALL-Partitionierung aus Sicht der Administration ergeben. Der kleine Wermutstropfen der systemgenerierten Partitionsnamen ist dabei zu verschmerzen, denn wenn durch die Anwendung sprechende Partitionsnamen erwartet werden, so lässt sich ein Automatismus entwickeln, der gegebenenfalls eine Umbenennung durchführt.

Eine INTERVALL-Partitionierung ist natürlich immer dort möglich, wo als Partitionierungskriterium ein Feld des Typs NUMBER oder DATE verwendet wird. Der folgende Code-Abschnitt zeigt die Anlage einer solchen Tabelle, die hier nach der verkauften Menge (QUANTITY_SOLD) partitioniert ist:

SQL> CREATE TABLE newsales
  2  ( prod_id       NUMBER(6)    NOT NULL
  3  , cust_id       NUMBER       NOT NULL
  4  , time_id       DATE         NOT NULL
  5  , channel_id    CHAR(1)      NOT NULL
  6  , promo_id      NUMBER(6)    NOT NULL
  7  , quantity_sold NUMBER(3)    NOT NULL
  8  , amount_sold   NUMBER(10,2) NOT NULL
  9  )
 10  PARTITION BY RANGE (quantity_sold)
 11  ( PARTITION p_sold_lt_10 VALUES LESS THAN (10)
 12  , PARTITION p_sold_lt_20 VALUES LESS THAN (20)
 13  , PARTITION p_sold_lt_30 VALUES LESS THAN (30)
 14  )
 15  ENABLE ROW MOVEMENT
 16  PARALLEL
 17  /

Tabelle wurde erstellt.

SQL> ALTER TABLE newsales
     SET INTERVAL (10);

Tabelle wurde geändert.

SQL>

Das zweite Beispiel zeigt die Anlage einer Tabelle ORDERS mit Bestellungen, die über eine Foreign Key Beziehung mit der Tabelle ORDER_ITEMS verbunden ist. Diese Child-Tabelle wird dann REFERENZ-partitioniert. Zum Abschluß wird noch die Erweiterung der Tabellen um eine neue Partition demonstriert.

Im ersten Schritt wird die RANGE-partitionierte Tabelle ORDERS mit Bestellungen angelegt. Auch hier ist wieder auf die korrekten NLS-Settings zu achten:

SQL> ALTER SESSION SET NLS_DATE_LANGUAGE=GERMAN;

Session wurde geändert.

SQL> CREATE TABLE orders                         
  2  ( order_id     NUMBER(12)  NOT NULL
  3  , order_date   DATE        NOT NULL
  4  , order_mode   VARCHAR2(8)
  5  , order_status VARCHAR2(1)
  6  )
  7  PARTITION BY RANGE (order_date)
  8  ( PARTITION p_before_jan_2008 VALUES LESS THAN (TO_DATE('01-JAN-2008','DD-MON-YYYY'))
  9  , PARTITION p_2008_jan        VALUES LESS THAN (TO_DATE('01-FEB-2008','DD-MON-YYYY'))
 10  , PARTITION p_2008_feb        VALUES LESS THAN (TO_DATE('01-MRZ-2008','DD-MON-YYYY'))
 11  , PARTITION p_2008_mar        VALUES LESS THAN (TO_DATE('01-APR-2008','DD-MON-YYYY'))
 12  , PARTITION p_2008_apr        VALUES LESS THAN (TO_DATE('01-MAI-2008','DD-MON-YYYY'))
 13  , PARTITION p_2008_may        VALUES LESS THAN (TO_DATE('01-JUN-2008','DD-MON-YYYY'))
 14  , PARTITION p_2008_jun        VALUES LESS THAN (TO_DATE('01-JUL-2008','DD-MON-YYYY'))
 15  , PARTITION p_2008_jul        VALUES LESS THAN (TO_DATE('01-AUG-2008','DD-MON-YYYY'))
 16  , PARTITION p_2008_aug        VALUES LESS THAN (TO_DATE('01-SEP-2008','DD-MON-YYYY'))
 17  , PARTITION p_2008_sep        VALUES LESS THAN (TO_DATE('01-OKT-2008','DD-MON-YYYY'))
 18  , PARTITION p_2008_oct        VALUES LESS THAN (TO_DATE('01-NOV-2008','DD-MON-YYYY'))
 19  )
 20  ENABLE ROW MOVEMENT
 21  PARALLEL
 22  /

Tabelle wurde erstellt.

SQL> ALTER TABLE orders ADD CONSTRAINT orders_pk
     PRIMARY KEY (order_id);

Tabelle wurde geändert.

SQL>

Die Tabelle der Bestellungen ist nach dem Bestelldatum partitioniert und verfügt über eine Child-Tabelle, die die Bestellpositionen enthält. Zwischen den Tabellen besteht eine Foreign Key - Beziehung und die Bestellpositionen sollen ohne redundante Speicherung des Bestelldatums auf die gleiche Weise partitioniert sein. Dieses wird durch die in Oracle 11g neu eingeführte REFERENZ-Partitionierung erreicht:

SQL> CREATE TABLE order_items
  2  ( order_id     NUMBER(12) NOT NULL
  3  , product_id   NUMBER     NOT NULL
  4  , quantity     NUMBER     NOT NULL
  5  , sales_amount NUMBER     NOT NULL
  6  , CONSTRAINT order_items_orders_fk FOREIGN KEY (order_id) REFERENCES orders(order_id)
  7  )
  8  PARTITION BY REFERENCE (order_items_orders_fk)
  9  ENABLE ROW MOVEMENT
 10  PARALLEL
 11  /

Tabelle wurde erstellt.

SQL>

Die eingerichtete Tabellenstruktur lässt sich über Data Dictionary Views verifizieren. Beim Abfragen der eingerichteten Partitionen wird deutlich, dass in der Child-Tabelle mit den Bestellpositionen bereits die entsprechenden Partitionen der Bestelltabelle erzeugt worden sind:

SQL> SELECT table_name, partitioning_type, ref_ptn_constraint_name 
     FROM   user_part_tables 
     WHERE  table_name IN ('ORDERS','ORDER_ITEMS');

TABLE_NAME                     PARTITION REF_PTN_CONSTRAINT_NAME
------------------------------ --------- ------------------------------
ORDERS                         RANGE
ORDER_ITEMS                    REFERENCE ORDER_ITEMS_ORDERS_FK

2 Zeilen ausgewählt.

SQL> SELECT table_name, partition_name, high_value 
     FROM   user_tab_partitions 
     WHERE  table_name IN ('ORDERS','ORDER_ITEMS') 
     ORDER BY partition_position, table_name;

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------
ORDER_ITEMS                    P_BEFORE_JAN_2008
ORDERS                         P_BEFORE_JAN_2008              TO_DATE(' 2008-01-01 00:00:00', 'SYYYY 
ORDER_ITEMS                    P_2008_JAN                     
ORDERS                         P_2008_JAN                     TO_DATE(' 2008-02-01 00:00:00', 'SYYYY 
ORDER_ITEMS                    P_2008_FEB                     
ORDERS                         P_2008_FEB                     TO_DATE(' 2008-03-01 00:00:00', 'SYYYY 
ORDER_ITEMS                    P_2008_MAR                     
ORDERS                         P_2008_MAR                     TO_DATE(' 2008-04-01 00:00:00', 'SYYYY 
ORDER_ITEMS                    P_2008_APR                     
ORDERS                         P_2008_APR                     TO_DATE(' 2008-05-01 00:00:00', 'SYYYY 
ORDER_ITEMS                    P_2008_MAY                     
ORDERS                         P_2008_MAY                     TO_DATE(' 2008-06-01 00:00:00', 'SYYYY 
ORDER_ITEMS                    P_2008_JUN                     
ORDERS                         P_2008_JUN                     TO_DATE(' 2008-07-01 00:00:00', 'SYYYY 
ORDER_ITEMS                    P_2008_JUL                     
ORDERS                         P_2008_JUL                     TO_DATE(' 2008-08-01 00:00:00', 'SYYYY 
ORDER_ITEMS                    P_2008_AUG                     
ORDERS                         P_2008_AUG                     TO_DATE(' 2008-09-01 00:00:00', 'SYYYY 
ORDER_ITEMS                    P_2008_SEP                     
ORDERS                         P_2008_SEP                     TO_DATE(' 2008-10-01 00:00:00', 'SYYYY 
ORDER_ITEMS                    P_2008_OCT                     
ORDERS                         P_2008_OCT                     TO_DATE(' 2008-11-01 00:00:00', 'SYYYY 

22 Zeilen ausgewählt.

SQL>

Die angestrebte Struktur ist also nun vorhanden, allerdings kommt es auch hier zu den bekannten Fehlermeldungen, wenn Daten des aktuellen Monats (November 2008) eingefügt werden müssen:

SQL> INSERT INTO orders VALUES (5, TO_DATE('26-NOV-2008','DD-MON-YYYY'), 'manual', 'U') ;
INSERT INTO orders VALUES (5, TO_DATE('26-NOV-2008','DD-MON-YYYY'), 'manual', 'U')
            *
FEHLER in Zeile 1:
ORA-14400: Eingefügter Partitionsschlüssel kann keiner Partition zugeordnet werden


SQL> INSERT INTO order_items VALUES (5, 1, 40, 42000) ;
INSERT INTO order_items VALUES (5, 1, 40, 42000)
            *
FEHLER in Zeile 1:
ORA-14400: Eingefügter Partitionsschlüssel kann keiner Partition zugeordnet werden


SQL>

Da die Datenstrukturen als RANGE-Partitionen angelegt worden sind und es keine Default-Partition gibt, kommt es zu diesem Fehler. Es muss also in diesem Schritt eine Erweiterung der Tabellen stattfinden, indem in der Parent-Tabelle ORDERS eine neue Partition angelegt wird:

SQL> ALTER TABLE orders
     ADD PARTITION 
     p_2008_nov  VALUES LESS THAN (TO_DATE('01-DEZ-2008','DD-MON-YYYY'));

Tabelle wurde geändert.

SQL>

Das Hinzufügen der Partition in der Tabelle ORDERS führt automatisch dazu, dass in der Child-Tabelle ORDER_ITEMS eine Partition hinzugefügt wird, wie der Blick ins Data Dictionary zeigt:

SQL> SELECT table_name, partition_name, high_value 
     FROM   user_tab_partitions
     WHERE  table_name IN ('ORDERS','ORDER_ITEMS')
     AND    partition_name LIKE '%NOV'
     ORDER BY partition_position, table_name;

TABLE_NAME                     PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------
ORDER_ITEMS                    P_2008_NOV
ORDERS                         P_2008_NOV                     TO_DATE(' 2008-12-01 00:00:00', 'SYYYY

SQL>

Das gezeigte Beispiel demonstriert die Vorteile von REFERENZ-Partitionierung, nämlich

Platzersparnis durch Vermeidung der redundanten Speicherung des Partitionierungsschlüssels
Automatische Anlage einer Partition in der Child-Tabelle bei der Partitionspflege der Parent-Tabelle

Ein kleiner Hinweis noch zum Abschluss:
Es ist derzeit noch nicht möglich, die Methoden REFERENZ- und INTERVALL-Partitionierung miteinander zu kombinieren. Die Parent-Tabelle muss laut Dokumentation eine bestehende partitionierte Tabelle sein, die nach einer beliebigen Methode, außer INTERVALL-Partitionierung, aufgeteilt sein kann.

Mehr zu diesem Thema bzw. zu weiteren Themen rund um die Datenbank lesen Sie in den nächsten Ausgaben ...

Zurück zur Community-Seite