Partitionierung für Fortgeschrittene

von Frank Schneede, ORACLE Deutschland B.V. & Co. KG

In großen Datenbanken wird Partitionierung eingesetzt, um die Datenmengen, die eine einzelne Abfrage bewegen muss, zu reduzieren. Die aktuelle Datenbankversion Oracle 11g bietet umfangreiche Erweiterungen, die dem DBA die Arbeit mit partitionierten Objekten stark vereinfacht. Einige grundlegende Funktionen wurden bereits in dem Community-Artikel Partitionspflege mit Oracle 11g leicht gemacht vorgestellt. Um die Beschreibung der Möglichkeiten abzurunden, die durch den Einsatz von Partitionierung gegeben sind, soll dieser Artikel die in der DBA Community bislang nicht näher erläuterten Partitionierungsmethoden der Zusammengesetzten Partitionierung und System Partitionierung beschreiben.

Bereits mit der Datenbankversion Oracle 8i wurde die Zusammengesetzte Partitionierung als Range-Hash Partitionierung eingeführt. In der Datenbankversion Oracle 9i ist die Erweiterung um Range-List Partitionierung erfolgt. Die Erfordernisse vieler Datawarehouses benötigen jedoch noch andere Methoden der Zusammengesetzten Partitionierung, die in der aktuellen Datenbank Oracle 11g ergänzt worden sind.

Wenn sich in einer großen Tabelle kein offensichtliches Partitionierungskriterium findet, so kann trotzdem mittels der neuen System Partitionierung eine Aufteilung der Daten und damit eine Steigerung der Abfrageperformance erreicht werden. System Partitionierung ist noch weitestgehend unbekannt und soll daher im folgenden Artikel ebenfalls behandelt werden.

Zusammengesetzte Partitionierung

In Projekten, in denen es um die Analyse sehr großer Datenmengen geht, ist es sehr wichtig, die Daten so aufzuteilen, dass eine möglichst große Zahl von Abfragen von der Aufteilung profitieren können. Bei der Festlegung der Partitionierungsstrategie war man bislang eingeschränkt. Ein Kriterium, das sich in Bereichen darstellen lies, musste stets an erster Stelle kommen. Hierfür wurde oft das meistens ohnehin vorhandene Kriterium der Dimension Zeit benutzt. Als Subpartitionierung war lediglich eine Aufteilung in eine festgelegte Anzahl von Subpartitionen möglich. Letzteres konnte wahlweise mit Hash Partitionierung oder durch die Auflistung diskreter Werte in Form von List Partitionierung geschehen.

Der eigentliche Gedanke, den man bei der Definition einer Partitionierungsstrategie beachten sollte, nämlich die Kardinalität des Partitionierungskriteriums, musste zwangsläufig hintenan gestellt werden. In Oracle 11g hat sich das nun grundlegend geändert, die Partitionierungsstrategie kann ohne weiteres den Erfordernissen des Geschäftes - oder besser der Abfragen - angepasst werden. Die folgende Auflistung zeigt die nun möglichen Kombinationen.

Range-Hash Partitionierung (eingeführt in 8i)
Range-List Partitionierung (eingeführt in 9i)
Range-Range Partitionierung (eingeführt in 11g)
List-Hash Partitionierung (eingeführt in 11g)
List-List Partitionierung (eingeführt in 11g)
List-Range Partitionierung (eingeführt in 11g)
Interval-Hash Partitionierung (eingeführt in 11g)
Interval-List Partitionierung (eingeführt in 11g)
Interval-Range Partitionierung (eingeführt in 11g)
Hash-Hash Partitionierung (eingeführt in 11g R2)

Die Intervall Partitionierung ist als Sonderfall der Range Partitionierung zu betrachten. Einzige Einschränkung ist (noch), dass die Intervall Partitionierung nur als oberste Partitionierungsmethode gewählt werden darf.

Das folgende Beispiel aus der Vergangenheit zeigt, dass komplexe Partitionierungsstrategien nicht abgebildet wurden, weil die Technologie es einfach nicht hergab. Hier wurde eine idealerweise mehrstufige Partitionierung als Range Partitionierung auf mehreren Spalten abgebildet.

CREATE TABLE fahrzeuge
   ( fgstnr              NUMBER       NOT NULL
   , marke               VARCHAR2(10) NOT NULL
   , modell              VARCHAR2(8)  NOT NULL
   , motor               VARCHAR2(8)  NOT NULL
   , getriebe            VARCHAR2(6)  NOT NULL
   , anz_tueren          NUMBER
   , farbe               VARCHAR2(10)
   , zubehoer            VARCHAR2(2800)
   , auslieferung_kunde  VARCHAR2(6)
   , land_kunde          VARCHAR2(3)
   , prodmonat           VARCHAR2(6)  NOT NULL
   , modelljahr          VARCHAR2(4)  NOT NULL
   )
  TABLESPACE  users
  PARTITION BY RANGE (modelljahr, land_kunde, marke)
  (
  PARTITION pfzg000101 VALUES LESS THAN ('2007', 'AUT', 'BMW'),
....
  PARTITION pfzg030102 VALUES LESS THAN ('2010', 'AUT', 'DAIMLER'),
  PARTITION pfzg030103 VALUES LESS THAN ('2010', 'AUT', 'VW'),
  PARTITION pfzg030104 VALUES LESS THAN ('2010', 'AUT', MAXVALUE),
  PARTITION pfzg030201 VALUES LESS THAN ('2010', 'CH',  'BMW'),
  PARTITION pfzg030202 VALUES LESS THAN ('2010', 'CH',  'DAIMLER'),
  PARTITION pfzg030203 VALUES LESS THAN ('2010', 'CH',  'VW'),
  PARTITION pfzg030204 VALUES LESS THAN ('2010', 'CH',  MAXVALUE),
  PARTITION pfzg030301 VALUES LESS THAN ('2010', 'DEU', 'BMW'),
  PARTITION pfzg030302 VALUES LESS THAN ('2010', 'DEU', 'DAIMLER'),
  PARTITION pfzg030303 VALUES LESS THAN ('2010', 'DEU', 'VW'),
  PARTITION pfzg030304 VALUES LESS THAN ('2010', 'DEU', MAXVALUE),
  PARTITION pfzgmax    VALUES LESS THAN (MAXVALUE, MAXVALUE, MAXVALUE)
  );

Absicht einer Partitionierungsstrategie ist es, das Datenvolumen, welches durch Abfragen bearbeitet werden muss, durch Ausblenden nicht relevanter Partitionen einzuschränken. Dieser Vorgang wird als Partition Pruning bezeichnet. Und hier lag in früheren Datenbankversionen bei der oben beschriebenen Datenstruktur das Problem. Partition Pruning fand nämlich nur statt, wenn über die Prädikate auf den Spalten modelljahr, land_kunde, marke, modelljahr & land, modeljahr & land_kunde & marke eine eingeschränkt wurde, die Abfrage also die führenden Bestandteile des Partitionierungsschlüssels beinhaltete. Wurde hingegen nur mit den Prädikaten auf land_kunde, marke oder land_kund & marke gefiltert, so konnte kein Partition Pruning stattfinden! Durch die Einführung des Multi-Column Partition Pruning in Oracle 10g R2 besteht allerdings mittlerweile kein Handlungsbedarf mehr, die Partitionierungsstrategie zu ändern.

Die Ausführungspläne zweier einfacher Statements zeigen das vorausgesagte Verhalten. In der ersten Abfrage erfolgt ein statischer Zugriff auf eine einzelne Partition.

SQL> EXPLAIN PLAN FOR
     SELECT fgstnr
     FROM  fahrzeuge
     WHERE modelljahr = '2009'
     AND   land_kunde = 'DEU'
     AND   marke      = 'VW';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 704259602

----------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |  8428 |   139K|   136   (0)| 00:00:02 |       |       |
|   1 |  PARTITION RANGE SINGLE|           |  8428 |   139K|   136   (0)| 00:00:02 |    36 |    36 |
|*  2 |   TABLE ACCESS FULL    | FAHRZEUGE |  8428 |   139K|   136   (0)| 00:00:02 |    36 |    36 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("MODELLJAHR"='2009' AND "LAND_KUNDE"='DEU' AND "MARKE"='VW')

14 rows selected.

Die zweite Abfrage zeigt, dass nun mit dynamischem Multi-Column Partition Pruning, im Ausführungsplan sichtbar durch KEY(MC), ebenfalls Partition Pruning erfolgt.

SQL> EXPLAIN PLAN FOR
     SELECT fgstnr
     FROM  fahrzeuge
     WHERE land_kunde = 'DEU'
     AND   marke      = 'VW';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
Plan hash value: 2866490058

----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           | 33333 |   488K|   542   (1)| 00:00:07 |       |       |
|   1 |  PARTITION RANGE MULTI-COLUMN|           | 33333 |   488K|   542   (1)| 00:00:07 |KEY(MC)|KEY(MC)|
|*  2 |   TABLE ACCESS FULL          | FAHRZEUGE | 33333 |   488K|   542   (1)| 00:00:07 |KEY(MC)|KEY(MC)|
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("LAND_KUNDE"='DEU' AND "MARKE"='VW')

14 rows selected.

In Oracle 11g kann man die Partitionierung verbessern, indem die Partitionierung der Tabelle FAHRZEUGE z. B. nach List-Range erfolgt. Dieses Beispiel soll nur die grundsätzliche Möglichkeit der Partitionierung darstellen. In der Realität muss die gewählte Strategie von der Abfragecharakteristik abhängig gemacht werden. In diesem Beispiel bedeutet das, ob eher nach Modelljahr oder nach Markt/Marke abgefragt wird. Entsprechend sollte dann die Partitionierung festgelegt werden.

CREATE TABLE fahrzeuge11g
   ( fgstnr              NUMBER       NOT NULL
   , marke               VARCHAR2(10) NOT NULL
   , modell              VARCHAR2(8)  NOT NULL
   , motor               VARCHAR2(8)  NOT NULL
   , getriebe            VARCHAR2(6)  NOT NULL
   , anz_tueren          NUMBER
   , farbe               VARCHAR2(10)
   , zubehoer            VARCHAR2(2800)
   , auslieferung_kunde  VARCHAR2(6)
   , land_kunde          VARCHAR2(3)
   , prodmonat           VARCHAR2(6)  NOT NULL
   , modelljahr          VARCHAR2(4)  NOT NULL
   )
  TABLESPACE  users
  PARTITION BY LIST (modelljahr)
  SUBPARTITION BY RANGE (land_kunde, marke)
  SUBPARTITION TEMPLATE (
  SUBPARTITION AUTBMW VALUES LESS THAN ('AUT', 'BMW'),
  SUBPARTITION AUTDAM VALUES LESS THAN ('AUT', 'DAIMLER'),
  SUBPARTITION AUTVW  VALUES LESS THAN ('AUT', 'VW'),
  SUBPARTITION AUT    VALUES LESS THAN ('AUT', MAXVALUE),
  SUBPARTITION CHBMW  VALUES LESS THAN ('CH',  'BMW'),    
  SUBPARTITION CHDAM  VALUES LESS THAN ('CH',  'DAIMLER'),
  SUBPARTITION CHVW   VALUES LESS THAN ('CH',  'VW'),     
  SUBPARTITION CH     VALUES LESS THAN ('CH',  MAXVALUE),     
  SUBPARTITION DEUBMW VALUES LESS THAN ('DEU', 'BMW'),    
  SUBPARTITION DEUDAM VALUES LESS THAN ('DEU', 'DAIMLER'),
  SUBPARTITION DEUVW  VALUES LESS THAN ('DEU', 'VW'),
  SUBPARTITION DEU    VALUES LESS THAN ('DEU', MAXVALUE),
  SUBPARTITION MAX    VALUES LESS THAN (MAXVALUE, MAXVALUE))
  (PARTITION p2007 VALUES ('2007'),
   PARTITION p2008 VALUES ('2008'),
   PARTITION p2009 VALUES ('2009'),
   PARTITION p2010 VALUES ('2010'),
   PARTITION other VALUES (DEFAULT));

Neben dem wesentlich übersichtlicheren Code zeigt sich, dass die Ausführungspläne sich leicht verändern. Im Fall der ersten Abfrage, die genau auf die Partitionierungsschlüssel abgestimmt ist, wird auch hier nur eine einzelne Subpartition durchgearbeitet. In der zweiten Abfrage wird über alle Partitionen iteriert und jeweils die über den Filter ermittelte Subpartition durchgearbeitet. Partition Pruning können wir also in beiden Beispielen feststellen. Die Unterschiede liegen zwischen den beiden Beispielen eher im Detail, was sich allerdings bei sehr großen Datenmengen - hier wurde mit jeweils 300000 Datensätzen gearbeitet - durchaus bemerkbar machen kann.

SQL> EXPLAIN PLAN FOR
     SELECT fgstnr
     FROM  fahrzeuge11g
     WHERE modelljahr = '2009'
     AND   land_kunde = 'DEU'
     AND   marke      = 'VW';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 2813051260

--------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |              |  8339 |   162K|   136   (0)| 00:00:02 |       |       |
|   1 |  PARTITION LIST SINGLE  |              |  8339 |   162K|   136   (0)| 00:00:02 |   KEY |   KEY |
|   2 |   PARTITION RANGE SINGLE|              |  8339 |   162K|   136   (0)| 00:00:02 |    12 |    12 |
|*  3 |    TABLE ACCESS FULL    | FAHRZEUGE11G |  8339 |   162K|   136   (0)| 00:00:02 |    38 |    38 |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("LAND_KUNDE"='DEU' AND "MARKE"='VW')

15 rows selected.

SQL> EXPLAIN PLAN FOR
     SELECT fgstnr
     FROM  fahrzeuge11g
     WHERE land_kunde = 'DEU'
     AND   marke      = 'VW';

Explained.

SQL> SELECT * FROM TABLE(dbms_xplan.display());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
Plan hash value: 3889413624

--------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name         | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |              | 33333 |   488K|   541   (1)| 00:00:07 |       |       |
|   1 |  PARTITION LIST ALL     |              | 33333 |   488K|   541   (1)| 00:00:07 |     1 |     5 |
|   2 |   PARTITION RANGE SINGLE|              | 33333 |   488K|   541   (1)| 00:00:07 |    12 |    12 |
|*  3 |    TABLE ACCESS FULL    | FAHRZEUGE11G | 33333 |   488K|   541   (1)| 00:00:07 |       |       |
--------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("LAND_KUNDE"='DEU' AND "MARKE"='VW')

15 rows selected.

Dieses Beispiel illustriert, dass mit der neuen Zusammengesetzten Partitionierung eine deutlich bessere Abbildung der Geschäftsvorfälle möglich ist. Welche Partitionierungsstrategie letztlich zum optimalen Ergebnis führt, ist von den gestellten Abfragen abhängig und muss jeweils im Einzelfall geprüft werden.

System Partitionierung

Die Methode der System Partitionierung ist für einen möglicherweise seltenen Anwendungsfall entwickelt worden, trotzdem möchte ich sie hier in Grundzügen vorstellen. Es ist denkbar, dass ein DBA kein offensichtliches Kriterium für eine sinnvolle Partitionierung findet. Die Konsequenz wäre vor Oracle 11g gewesen, dass mit einer sehr großen monolithischen Tabellen gearbeitet werden müsste, was zu einem erhöhten Pflegeaufwand für Indizes usw. führt. Falls die Anwendungsentwicklung sicherstellen kann, dass auf einzelne Partitionen in "intelligenter" Weise zugegriffen wird, dann ist es möglich, aus der Anwendung die Datensätze in der geeigneten Partition zu platzieren. Der DBA bzw. die Datenbank muss nur entsprechende Partitionen zur Verfügung stellen. Genau das ist der Einsatzzweck der System Partitionierung.

Hier ein kleines Beispiel. Es sind keine Partitionierungsschlüssel oder -grenzen festgelegt, die Datenbank erzeugt schlicht zwei Segmente anstelle einer monolithischen Tabelle.

CREATE TABLE fahrzeugesp
   ( fgstnr              NUMBER       NOT NULL
   , marke               VARCHAR2(10) NOT NULL
   , modell              VARCHAR2(8)  NOT NULL
   , motor               VARCHAR2(8)  NOT NULL
   , getriebe            VARCHAR2(6)  NOT NULL
   , anz_tueren          NUMBER
   , farbe               VARCHAR2(10)
   , zubehoer            VARCHAR2(2800)
   , auslieferung_kunde  VARCHAR2(6)
   , land_kunde          VARCHAR2(3)
   , prodmonat           VARCHAR2(6)  NOT NULL
   , modelljahr          VARCHAR2(4)  NOT NULL
   )
PARTITION BY SYSTEM
(
   PARTITION p1 TABLESPACE users,
   PARTITION p2 TABLESPACE users
);

SQL> SELECT partition_name
  2  FROM  user_segments
  3  WHERE segment_name = 'FAHRZEUGESP';

PARTITION_NAME
------------------------------
P1
P2

Ein lokaler Index kann angelegt werden und wäre dann ebenso partitioniert wie die Tabelle.

SQL> CREATE INDEX in_fahrzeugesp ON fahrzeugesp (marke) LOCAL;

Index created.

SQL> SELECT partition_name
     FROM user_segments
     WHERE segment_name = 'IN_FAHRZEUGESP';

PARTITION_NAME
------------------------------
P1
P2

Eine Überprüfung der Partitionierung zeigt die gewählte Methode System. Da bei der System Partitionierung keine Werte zugrundegelegt werden, zeigt die Spalte high_value der Data Dictionary View user_tab_partitions natürlich keinen Wert.

SQL> SELECT partitioning_type
     FROM  user_part_tables
     WHERE table_name = 'FAHRZEUGESP';

PARTITION
---------
SYSTEM

SQL> SELECT partition_name, high_value
     FROM  user_tab_partitions
     WHERE table_name = 'FAHRZEUGESP';

PARTITION_NAME   HIGH_VALUE
---------------- ---------------------
P1
P2

Wenn nun kein Partitionierungsschlüssel oder ein sonstiger über Range, List oder Hash Partitionierung erzeugter Wert existiert, woher weiß die Datenbank dann, wohin ein einzufügender Satz geschrieben werden soll? Ganz einfach: Oracle weiß es nicht! Diese Entscheidung muss aus der Anwendung heraus gefällt werden. Folgendes Beispiel:

SQL> INSERT INTO fahrzeugesp ( fgstnr
  2                          , marke
....
 12                          , modelljahr
 13                          )
 14  VALUES ( 1
 15         , 'BMW'
....
 25         , '2008'
 26         );
INSERT INTO fahrzeugesp ( fgstnr
            *
ERROR at line 1:
ORA-14701: partition-extended name or bind variable must be used for DMLs on tables partitioned by the System method


SQL> INSERT INTO fahrzeugesp
  2              PARTITION (p1) ( fgstnr
  3                             , marke
....
 13                             , modelljahr
 14                             )
 15  VALUES ( 1
 16         , 'BMW'
....
 26         , '2008'
 27         );

1 row created.

SQL> commit;

Commit complete.

Beim Löschen und Aktualisieren von Datensätzen tritt natürlich ein ähnliches Problem auf. Wenn die Partition nicht explizit angegeben wird, so muss die Datenbank alle Partitionen scannen, um den betreffenden Datensatz zu finden. Daher sollte auch in diesem Fall die Angabe der Partition erfolgen.

SQL> DELETE fahrzeugesp PARTITION (p1) WHERE fgstnr = 1;

1 row deleted.

SQL> commit;

Commit complete.

Mit der Nutzung der System Partitionierung können die Vorteile der Partitionierungstechnik genutzt werden, vorausgesetzt, die Anwendung lässt sich dementsprechend gestalten. Es sind jedoch auch einige systemimmanente Einschränkungen zu beachten, die folgenden Operationen/Features sind bei System Partitionierung nicht möglich.

create table as select
insert into table as select
Split Partition Operationen
Unique Local Indizes, da diese einen Partitionierungsschlüssel benötigen

Um die geschilderten Einschränkungen zu umgehen, kann man die gewünschte Zieltabelle anlegen und dann explizit die Partitionen befüllen.

Insgesamt bietet die Partitioning Option ab Version Oracle 11g einige Funktionen, die eine Optimierung des Datenmodells in Hinblick auf die geschäftlichen Erfordernisse ermöglicht. Für den Fall, dass alle gängigen Partitionierungsmethoden nicht einsetzbar sind, kann durchaus auch die Methode der System Partitionierung evaluiert werden. Um die oben gezeigten Beispiele nachvollziehen zu können, ist hier ein Skript mit den verwendeten Statements hinterlegt.

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

Zurück zur Community-Seite