Partitionierte Views anlegen

Mit Partitionierung ist ein Aufteilen der Daten anhand bestimmter Kriterien gemeint - vor allem riesige Datenbestände können so einen echten Performancegewinn “erleiden” ;) Man kann dabei auch auf Remote-Tabellen zugreifen(Distributed Partitioned View) - aber dieser Artikel soll nur eine kleine Einführung geben.

Partitionierte Views sind dabei kein wirklicher Ersatz für die partitionierten Tabellen. Man kann sich aber zumindest teilweise helfen.

Eine partitionierte View und deren Tabellen unterliegen einigen Einschränkungen. So muss die Kriteriumsspalte innerhalb des Primary Keys vorkommen (oder selbst der Primary Key sein) und eine Identity-Spalte führt zum Fehler. Alle Tabellen müssen gleiche Primary Keys vorweisen. Das Kriterium muss der jeweiligen Tabelle mittels CHECK-Einschränkung bekannt gegeben werden. Dabei darf die CHECK-Einschränkung nur aus folgenden Operatoren bestehen: AND, OR, BETWEEN, <, <=, =, >= und >. Da gibt es noch ein paar Einschränkungen, aber für den Anfang muss das ausreichen…

Ein einfacher Aufbau sieht z.B. so aus:

CREATE TABLE Test_PartitionedView_1 (
    ID int,
    ReportYear int,
    Value int,
    CHECK (ReportYear < 2000),
    PRIMARY KEY (
        ID,
        ReportYear
    )
)
CREATE TABLE Test_PartitionedView_2 (
    ID int,
    ReportYear int,
    Value int,
    CHECK (ReportYear >= 2000 AND ReportYear < 2005),
    PRIMARY KEY (
        ID,
        ReportYear
    )
)
CREATE TABLE Test_PartitionedView_3 (
    ID int,
    ReportYear int,
    Value int,
    CHECK (ReportYear >= 2005),
    PRIMARY KEY (
        ID,
        ReportYear
    )
)
GO
CREATE VIEW Test_PartitionedView
AS
    SELECT  ID,
            ReportYear,
            Value
    FROM    Test_PartitionedView_1
    UNION ALL
    SELECT  ID,
            ReportYear,
            Value
    FROM    Test_PartitionedView_2
    UNION ALL
    SELECT  ID,
            ReportYear,
            Value
    FROM    Test_PartitionedView_3
GO

Nachdem die 3 Tabellen und die dazugehörige View erstellt wurde, ist eigentlich auch schon alles fertig. Die View kann direkt angesprochen werden. INSERT INTO funktioniert genauso wie UPDATE und DELETE (das klappt aber nur, weil die View keine Joins oder ähnliches beinhaltet!).

Man kann z.B. ein paar Daten einspielen

INSERT INTO Test_PartitionedView (ID, ReportYear, Value)
SELECT  ROW_NUMBER() OVER(ORDER BY ReportYear, Value),
        ReportYear,
        Value
FROM    (
                   SELECT 1998 AS ReportYear, 1 AS Value
             UNION SELECT 1999, 1
             UNION SELECT 1998, 2
             UNION SELECT 1998, 3
             UNION SELECT 1998, 4
             UNION SELECT 1998, 5
             UNION SELECT 1998, 7
             UNION SELECT 2000, 1
             UNION SELECT 2000, 2
             UNION SELECT 2004, 1
             UNION SELECT 2004, 2
             UNION SELECT 2004, 3
             UNION SELECT 2004, 4
             UNION SELECT 2004, 5
             UNION SELECT 2004, 6
             UNION SELECT 2007, 1
             UNION SELECT 2007, 4
             UNION SELECT 2007, 5
             UNION SELECT 2009, 1
             UNION SELECT 2009, 2
        ) AS ins

und dabei zuschauen, wie der SQL Server die “Magie“ übernimmt. Denn der verteilt nun die Daten auf die 3 Tabellen, was einfach zu überprüfen ist.

SELECT * FROM Test_PartitionedView_1
SELECT * FROM Test_PartitionedView_2
SELECT * FROM Test_PartitionedView_3

Bei der Aktualisierung müsste aufgepasst werden, wenn’s der SQL Server nicht automatisch übernehmen würde. Die Aktualisierung

UPDATE Test_PartitionedView
SET    ReportYear = 2009
WHERE  ID = 2

verschiebt den entsprechenden Eintrag aus der ersten Tabelle in die dritte.

Jetzt fragt man sich vollkommen zu recht: “Was soll das?“. Nun ja, auf den ersten Blick bringt es - außer eventuell zusätzlichen Aufwand - nicht wirklich viel. Vorstellbar ist, die 3 Tabellen nun auf (physikalisch) unterschiedlichen Festplatten zu speichern und damit einen nicht unerheblichen Performancevorteil zu ergattern. Noch weitaus besser ist aber der Abfrageoptimierer - denn der arbeitet immer *g*.

Der schafft es nämlich bei Abfragen mit Literalwerten für die Kriteriums-Spalte (!), nur die entsprechenden Tabellen überhaupt zu bemühen. Dazu folgende Beispiele:

SELECT *
FROM   Test_PartitionedView

Hier werden alle Daten abgefragt und dementsprechend natürlich auch alle Tabellen involviert. Der Abfrageplan sieht so aus

SELECT *
FROM   Test_PartitionedView
WHERE  ReportYear BETWEEN 1990 AND 2004

Hier werden nur noch die Daten aus den ersten beiden Tabellen abgefragt. Der Abfrageplan ist entsprechend gekürzt.

Bei riesigen Tabellen bringt das schon einiges - vor allem, wenn man die Aufteilung geschickt wählt.

Leider gilt das, wie bereits erwähnt, nur für Literale in der Abfrage. Wird das Jahr als Variable übergeben, so greift die Optimierung leider nicht mehr (ist ja auch klar, die Abfragen sollten ja möglichst mit allen möglichen Daten funktionieren). Es gibt aber dennoch unzählige Fälle, in denen man partitionierte Views verwenden kann (seid kreativ *g*).