Deep Dive mit Carmen Rasche: Partitionierte Tabellen – Teil 2

In diesen Artikeln wollen wir das Thema der partitionierten Tabellen ein wenig differenzierter betrachten. Dabei stehen die Fragen „Wann sollten partitionierte Tabellen verwendet werden?“ und „Welche Aspekte spielen für das Design eine Rolle?“ im Vordergrund.

Der Migrationsprozess

Sicherlich wäre es wünschenswert, partitionierte Tabellen bereits beim Datenmodell zu berücksichtigen. Doch die Realität ist häufig eine andere. In der Regel sollen bestehende Tabellen nachträglich partitioniert werden. Die Konvertierung ist einfach: Sie können den Befehl ALTER TABLE verwenden. Hierbei wird eine neue partitionierte Tabelle erstellt, die Daten werden in die Tabelle übernommen und alle bestehenden Indizes werden als partitionierte Indizes neu erstellt. Dieser Vorgang kann sehr zeitintensiv sein und die Datenübernahme erfolgt auch nicht parallel. Aus diesem Grund könnte es sinnvoll sein, dass die Datenübernahme manuell ausgeführt wird. Sinnvollerweise sollten hierbei ALIAS-Namen verwendet werden:

INSERT INTO #2017             --ALIAS-NAME
SELECT *
FROM UMSATZ_KUMULIERT_NICHT_PARTITIONIERT
WHERE JAHR = ‘2017‘;

INSERT INTO #2016             --ALIAS-NAME
SELECT *
FROM UMSATZ_KUMULIERT_NICHT_PARTITIONIERT
WHERE JAHR = ‘2016‘;

INSERT INTO #2015             ALIAS-NAME
SELECT *
FROM UMSATZ_KUMULIERT_NICHT_PARTITIONIERT
WHERE JAHR = ‘2015‘;

Wie gehen Sie bei der Migration vor?

1. Stellen Sie sicher, dass Sie genügend Plattenspeicher haben, um die partitionierte und die nicht partitionierte Tabelle zu speichern.
2. Vor der Migration sollten Sie die nicht partitionierte Tabelle und deren Indizes sichern.
3. Erstellen Sie anschließend die partitionierte Tabelle.
4. Ermitteln Sie vor der Migration die Anzahl Zeilen, die jede Partition erhalten soll.
5. Erstellen Sie die ALIAS-Namen für den Migrationsprozess.
6. Nutzen Sie den Befehl OVRDBF, um die Blockgröße für die Quell- und die Zieldatei auf 256 KB zu erhöhen.
7. Übernehmen Sie die Daten. Nutzen Sie hierfür konkurrierende, parallel laufende INSERT-Anweisungen mit SUBSELECT wie gezeigt.
8. Prüfen Sie nach der Datenübernahme die Anzahl Zeilen je Partition.
9. Erstellen Sie jetzt die notwendigen Indizes.
10. Löschen Sie abschließend die nicht partitionierte Tabelle und deren Indizes.
11. Falls erforderlich, müssen sie abschließend die neue Tabelle und deren Indizes umbenennen.

Indizes und partitionierte Tabellen

Für partitionierte Tabellen können sowohl Binärindizes als auch Encodded Vector Indizes erstellt werden. Zu beachten ist, dass EVIs immer als partitionierte Indizes erstellt werden. Bei den Binärindizes haben Sie die Wahl: Der Binärindex kann partitioniert werden, muss es aber nicht. Falls Sie den Index nicht partitionieren, umspannt der Index die gesamte Datei und wird aus diesem Grund auch „SPANNING INDEX“ genannt.

-- SPANNING INDEX
CREATE  INDEX KUMULIERTER_UMSATZ_I1_SPANNING
ON KUMULIERTER_UMSATZ(FILIALE) NOT PARTITIONED;

CREATE  INDEX KUMULIERTER_UMSATZ_I1_PARTITIONED
ON KUMULIERTER_UMSATZ(FILIALE) PARTITIONED;

CREATE ENCODED VECTOR INDEX KUMULIERTER_UMSATZ_E1_PARTITIONED 
ON KUMULIERTER_UMSATZ(FILIALE) INCLUDE (SUM(ERLOESE), SUM(KOSTEN));

Was müssen Sie bei Primär- und UNIQUE-Keys beachten?

Ein eindeutiger Index kann nur partitioniert werden, wenn die Schlüssel identisch oder eine Teilmenge des Partitionierungsschlüssels sind.

Wenn ein eindeutiger Index eine Teilmenge des Partitionsschlüssels ist, wird dieser Index standardmäßig als partitionierter Index implementiert. Nur wenn in der Indexdefinition explizit die Klausel NON PARTITIONED verwendet wird, wird ein Spanning-Index erstellt.

Bedenken müssen Sie zudem, dass ein nicht partitionierter Index die gesamte Struktur der physischen Tabelle enthält. Wenn zu einem späteren Zeitpunkt in der Tabelle eine Partition hinzugefügt oder gelöscht wird, muss der nicht partitionierte Index neu erstellt werden; und da partitionierte Tabellen in der Regel keine kleinen Tabellen sind, kann dieser Prozess sehr viel Zeit beanspruchen und entsprechend viele Systemressourcen binden. Nur wenn die Partitionen an das Ende der Tabelle angehängt oder am Ende der Tabelle gelöscht werden, kann die Neuerstellung der Indizes verhindert werden.

Partitionierte Tabellen und der SQL-Optimizer

Die Optimierung der SQL-Statements für partitionierte Tabellen ist vergleichbar mit der Optimierung eines UNION ALL-Statements.

Die Abfrage:

SELECT *
FROM KUMULIERTER_UMSATZ
WHERE  FILIALE = '00011';

entspricht im Wesentlichen der Abfrage:

SELECT *
FROM 	1. member
WHERE   FILIALE = '00011'

UNION ALL

SELECT *
FROM 	2. member
WHERE   FILIALE = '00011'

UNION ALL

SELECT *
FROM 	3. member
WHERE   FILIALE = '00011'

Grundsätzlich gilt, dass der Optimierungsprozess und die Ausführungspläne mit partitionierten Tabellen aufwendiger werden, denn nur wenn die lokalen Selektionskriterien der SQL-Abfrage den Partitionierungsschlüssel enthalten, kann dieser bei der Datenselektion vom Optimizer berücksichtigt werden.

Hinzu kommt, dass die Daten der partitionierten Tabellen für viele Abfragen „materialisiert“ werden müssen. Was heißt das konkret? Stellen Sie sich vor, dass bezogen auf die letzten zwei Jahre die Filiale mit den höchsten Erlösen ermittelt werden soll:

SELECT FILIALE, SUM(ERLOESE) AS ERLOESE
FROM KUMULIERTER_UMSATZ
WHERE JAHR = YEAR(CURRENT DATE) - 1  OR JAHR = YEAR(CURRENT DATE)
GROUP BY FILIALE
ORDER BY ERLOESE DESC
FETCH FIRST 3 ROWS ONLY;

Um das Ergebnis zu liefern, werden die Daten in eine Hash-Tabelle gestellt und dann erst kumuliert. Die Datenselektion erfolgt trotz vorhandenem Index mittels Table Scan, das heißt, die gesamte Tabelle wird gelesen.

Diese temporären Objekte beanspruchen Arbeitsspeicher und temporären Speicher. Ohne eingehende Analyse der Laufzeitumgebung ist es schwer vorherzusagen, ob und wie viel mehr Ressourcen genutzt werden und ob sich die Antwortzeiten verschlechtern.

Bedenken Sie daher:

Partitionierte Tabellen erlauben zwar die Definition von 256 Membern, aber es versteht sich hoffentlich von selbst, dass man mit Augenmaß und Verantwortung die Anzahl der tatsächlich benötigten Member festlegt. Als Faustregel gilt, dass Sie – wenn möglich – nicht mehr als 36 Member verwenden. Und partitionierte Tabellen benötigen natürlich Indizes. Erstellen wir zum Beispiel einen EVI mit inkludierten Summenspalten:

CREATE ENCODED VECTOR INDEX  KUMULIERTER_UMSATZ_E1
     FOR SYSTEM NAME KUMUMS01 
     ON  KUMULIERTER_UMSATZ ( Jahr, FILIALE ASC )   
     INCLUDE (  SUM ( ERLOESE ) , SUM ( KOSTEN ) )   ;

Dann kann die oben verwendete Abfrage deutlich effizienter ausgeführt werden:

Grundsätzlich sollten Sie die Indizes für eine partitionierte Tabelle ebenfalls partitionieren. Wie bereits erwähnt, sind diese Indizes deutlich pflegeleichter. Falls Sie jedoch native Zugriffe aus den HLL-Programmen realisieren müssen oder Sie wissen, dass in Ihrem Unternehmen SQL-Abfragen auch noch von der CQE ausgeführt werden, dann benötigen Sie auf alle Fälle zusätzlich auch nicht partitionierte Indizes und müssen den erhöhten Mehraufwand für die Indexpflege in Kauf nehmen. Sie haben gelesen, dass EV-Indizes immer partitioniert sind? Auch hier sollten Sie vorsorgen, wenn die CQE noch aktiv ist oder native Satzzugriffe erfolgen: In diesem Fall erstellen Sie nicht nur den EVI, sondern zusätzlich auch einen nicht partitionierten Binärindex.

Und wie sieht es mit den Statistikdaten aus? Statistiken werden für jede Partition separat erhoben. Achten Sie darauf, dass hier tatsächlich alle Statistiken für jede Partition vorhanden sind.

---

Für 88 Euro gibt’s hier sechs Monate lang tiefgreifendes IBM i und SQL Wissen. Hier kann man abonnieren.