Sie fragen sich vielleicht, warum Sie eine Partition von einer Tabelle zur anderen verschieben sollten, aber tatsächlich gibt es dafür mehrere Gründe. Der folgende tauchte in einer Session auf, die ich mit einem Kunden abhielt, der „alte“ Daten archivieren wollte. Die aktuellen Daten sind partitioniert (INTERVAL) und seine initiale Idee war es, die Reihen in die Archiv-Tabelle mit INSERT AS SELECT zu verschieben. Die Archiv-Tabelle muss mindestens 10 Jahre verfügbar bleiben, wird aber nur sehr selten genutzt. Da es auf der aktuellen Tabellen mit bis zu 300 Millionen Rows globale Indizes gibt, konnten wir die alten Daten nicht einfach in der aktuellen Tabelle belassen.
Aber anstatt eine nicht-partitionierte Tabelle für die archivierten Daten zu erstellen, riet ich ihm eine Tabelle zu erstellen, die das gleiche Layout hat wie die aktuelle, denn
- nach den 10 Jahren kann er die älteste Partition weglassen
- er kann Partitionen austauschen, anstatt Daten herumzuschieben
Also hier ist das Ergebnis unserer Diskussion:
1 Umgebung
Den Startpunkt bilden zwei Tabellen mit identischer Definition:
- Table ACTIVE: Eine Interval-partitionierte Tabelle mit den aktuellen Daten seit dem 1. Januar 2013. Jedes Jahr wird automatisch eine neue Partition erstellt.
- Table ARCHIVE: Eine Interval-Partitionierte Tabelle mit „alten“ Daten bis zum 31. Dezember 2012. Jedes Jahr muss manuell eine neue Partition erstellt werden.
1.1 Beispiel
1.1.1 Table ACTIVE
CREATE TABLE active
(
id NUMBER CONSTRAINT pk_active PRIMARY KEY,
myvalue VARCHAR2(20 CHAR),
mydate DATE
)
PARTITION BY RANGE (mydate)
INTERVAL (NUMTOYMINTERVAL(1,'YEAR'))
(
PARTITION PDUMMY VALUES LESS THAN
(TO_DATE('01.01.2013','DD.MM.YYYY')),
PARTITION P2012 VALUES LESS THAN
(TO_DATE('01.01.2013','DD.MM.YYYY')),
PARTITION P2013 VALUES LESS THAN
(TO_DATE('01.01.2014','DD.MM.YYYY')),
PARTITION P2014 VALUES LESS THAN
(TO_DATE('01.01.2015','DD.MM.YYYY')),
PARTITION P2015 VALUES LESS THAN
(TO_DATE('01.01.2016','DD.MM.YYYY'))
);
1.1.2 Table ARCHIVE
CREATE TABLE archive
(
id NUMBER CONSTRAINT pk_archive PRIMARY KEY,
myvalue VARCHAR2(20 CHAR),
mydate DATE
)
PARTITION BY RANGE (mydate)
(
PARTITION POLD VALUES LESS THAN
(TO_DATE('01.01.2010','DD.MM.YYYY')),
PARTITION P2010 VALUES LESS THAN
(TO_DATE('01.01.2011','DD.MM.YYYY')),
PARTITION P2011 VALUES LESS THAN
(TO_DATE('01.01.2012','DD.MM.YYYY'))
);
2 Vorbereitung
Mit EXCHANGE PARTITION wird eine Tabelle mit einer Partition ausgetauscht. Dies impliziert, dass sowohl die Tabelle als auch die Partition die gleiche Definition haben. Leider gibt es keine Möglichkeit eine Partition von einer Tabelle mit einer Partition zu einer anderen Tabelle zu tauschen. Um diese Beschränkung zu umgehen müssen wir stattdessen eine Interims-Tabelle benutzen.
2.1 Table INTERIM
Die unpartitionierte Table INTERIM muss mit demselben Layout erstellt werden wie die Table ACTIVE. Diese Tabelle wird leer bleiben, außer für die Dauer der Migration.
CREATE TABLE interim ( id NUMBER CONSTRAINT pk_interim PRIMARY KEY, myvalue VARCHAR2(20 CHAR), mydate DATE );
3 Partitionen Tauschen
3.1 Partition in ARCHIVE leeren
Zuerst muss eine leere Partition in der ARCHIVE-Tabelle erstellt werden.
ALTER TABLE archive
ADD PARTITION P2012 VALUES LESS THAN
(TO_DATE('01.01.2013','DD.MM.YYYY'));
Übrigens: Wenn wir die Tabelle als Interval-partitioniert erstellen (wie die Table ACTIVE), wird dieser Befehl fehlschlagen, da es nicht erlaubt ist, Partitionen manuell hinzuzufügen, die Partitionen werden automatisch angelegt, wenn Daten eingetragen werden, für die es noch keine Partition gibt. Um das zu umgehen, muss lediglich eine Zeile zur Tabelle hinzugefügt werden, die die benötigte Partition erstellt:
INSERT INTO archive VALUES
(99999999,'dummy',to_date('31.12.2012','DD.MM.YYYY'));
ROLLBACK;
In diesem Fall wird die Partition mit einem vom System vorgegebenen Namen (z.B. SYS_P64) ohne Inhalt angelegt.
3.2 Partitions-Austausch Teil 1
Jetzt wird die Table INTERIM mit der Partition P2012 der Table ACTIVE getauscht:
ALTER TABLE active EXCHANGE PARTITION p2012 WITH TABLE interim;
DIe Partition P2012 in der Table ACTIVE ist jetzt leer und die Table INTERIM hat ein paar Zeilen:
SELECT count(*) FROM active PARTITION (P2012) NO ROWS SELECTED SELECT count(*) FROM interim COUNT(*) ============= xxxxxxxxxxxxx
3.3 Partitions-Austausch Teil 2
Im nächsten Schritt wird die Table INTERIM mit der Partition P2012 der Table ARCHIVE getauscht:
ALTER TABLE archive EXCHANGE PARTITION p2012 WITH TABLE interim;
Als Ergebnis sollte die Table INTERIM wieder leer sein und die Partition P2012 der Table ARCHIVE sollte den Inhalt des Jahres 2012 enthalten.
SELECT count(*) FROM active PARTITION (P2012) NO ROWS SELECTED SELECT count(*) FROM interim NO ROWS SELECTED SELECT count(*) FROM archive PARTITION (P2012) COUNT(*) ============= Xxxxxxxxxxxxx
Das war’s! Wir sind fertig mit der Migration der Tabellen-Daten innerhalb von Sekunden, weil keine Daten verschoben werden mussten, sondern nur die Daten-Verzeichnis-Informationen verändert wurde.
3.4 Aufräumen
Jetzt kann die Partition P2012 in der Table ACTIVE gelöscht werden.
ALTER TABLE aktiv DROP PARTITION p2012;
3.5 Index wiederherstellen
Wenn es globale Indices auf den Tabellen gab, müssen sie neu aufgebaut werden, da sie durch den Tausch „UNUSABLE“ geworden sind.
ALTER INDEX idx… REBUILD;
4 Fazit
EXCHANGE PARTITION ist ein großartiges Tool um Daten zwischen verschiedenen Tabellen zu verschieben, ohne dass es eine Downtime oder große Batch-Vorgänge braucht.
Das einzige Risiko für diesen Vorgang ist die Bestätigung, ob die Partition während des EXCHANGE PARTITION die Voraussetzungen der archivierten Tabelle erfüllt. Hinsichtlich der Logik dieses Szenarios wird die auszutauschende Partition wahrscheinlich die älteste in der aktuellen, aber die neueste in der archivierten Tabelle sein. Wenn man die älteste Partition seiner Tabelle austauscht, besteht das Risiko, dass sie Daten enthält, die älter sind als erwartet. In diesem Fall könnte P2012 der Table ACTIVE Daten von 2011 oder sogar 2012 enthalten. Der EXCHANGE PARTITION zur Table ARCHIVE würde fehlschlagen, da die Partitions-Kriterien nicht eingehalten werden, denn in ARCHIVE gibt es eine Partition P2011, die Daten von 2011 enthält, und eine Partition P2010, die Daten von 2010 enthält. Um diesen Fehler zu vermeiden, könnte es eine gute Idee sein, eine Dummy-Partition zur Table ACTIVE hinzuzufügen für fälschlicherweise eingefügte alte Daten.