Übersichtstabellen sind eine leistungsfähige Methode zum Verbessern der Antwortzeit von Abfragen. In vielen Umgebungen, bei denen einige der grundlegenden Strukturen von Abfragen vorhergesehen werden können, können Übersichtstabellen für die folgenden Zwecke verwendet werden:
In den SQL-Compiler sind Kenntnisse von Übersichtstabellen integriert. Im SQL-Compiler sind das Umschreiben von Abfragen (siehe Umschreiben der Abfrage durch den SQL-Compiler) und das Optimierungsprogramm (siehe Datenzugriffskonzepte und Optimierung) daran beteiligt, Abfragen mit Übersichtstabellen abzugleichen und zu bestimmen, ob eine Übersichtstabelle in Basistabellen durch eine Abfrage ersetzt werden soll. Wenn Übersichtstabellen zur Beantwortung von Abfragen verwendet werden, können die EXPLAIN-Einrichtungen (siehe Kapitel 26, Die SQL-EXPLAIN-Einrichtung) dafür benutzt werden zu ermitteln, welche Übersichtstabelle ausgewählt wurde. Da Übersichtstabellen sich in vielerlei Hinsicht wie reguläre Tabellen verhalten, treffen die Überlegungen zum Optimieren des Datenzugriffs unter Verwendung von Tabellenbereichsdefinitionen, durch Erstellen von Indizes und Ausgeben von RUNSTATS auch auf Übersichtstabellen zu.
Um Ihnen das Verständnis der Leistungsfähigkeit von Übersichtstabellen zu erleichtern, wird das folgende Beispiel einer mehrdimensionalen Analyseabfrage bereitgestellt und gezeigt, wie dabei Übersichtstabellen genutzt werden.
In diesem Beispiel wird von einem Szenario ausgegangen, in dem ein Geschäft eine Reihe von Kunden und eine Reihe von Kreditkartenkonten umfaßt. Das Geschäft zeichnet die Gruppe der Transaktionen auf, die mit den Kreditkarten durchgeführt wurden. Alle Transaktionen enthalten eine Reihe von Artikeln, die gemeinsam gekauft wurden. Diese Umgebung kann als Mehrfachstern (Multi-Star) kategorisiert werden, da zwei große Tabellen vorhanden sind, von denen die eine Transaktionsartikel enthält und die andere die Kauftransaktionen identifiziert, die beide zusammen die Nabe des Sterns bilden.
Es gibt drei hierarchische Dimensionen, die eine Transaktion beschreiben: Produkt, Standort und Zeit. Die Produkthierarchie wird in zwei normalisierten Tabellen aufgezeichnet, die die Produktgruppe und die Produktlinie darstellen. Die Standorthierarchie enthält Informationen zu Ort, Bundesland und Staat und wird in einer einzigen denormalisierten Tabelle dargestellt. Die Zeithierarchie enthält Informationen zu Tag, Monat und Jahr und ist in einem einzigen Datumsfeld codiert. Die Datumsdimensionen werden aus dem Datumsfeld der Transaktion unter Verwendung integrierter Funktionen extrahiert. Es gibt auch andere Tabellen in diesem Szenario, die Kontoinformationen für Kunden und Kundeninformationen darstellen.
Eine Übersichtstabelle wird mit der Summe und der Anzahl der Verkäufe für jede Stufe der folgenden Hierarchien erstellt:
Eine breite Palette von Abfragen kann ihre Antworten aus diesen gespeicherten Ergebnisdaten extrahieren. Im folgenden Beispiel werden die Summe und die Anzahl der Verkäufe für die Dimensionen 'Produktgruppe' (Product Group) und 'Produktlinie (Product Line) berechnet; danach wird dies für die Dimensionen 'Ort' (City), 'Bundesland' (State) und 'Staat' (Country) und zuletzt für die Dimension 'Zeit' (Time) durchgeführt. Das Beispiel enthält auch einige weitere Spalten in der Klausel GROUP BY.
CREATE TABLE dba.PG_SALESSUM AS ( SELECT l.id AS prodline, pg.id AS pgroup, loc.country, loc.state, loc.city, l.name AS linename, pg.name AS pgname, YEAR(pdate) AS year, MONTH(pdate) AS month, t.status, SUM(ti.amount) AS amount, COUNT(*) AS count FROM cube.transitem AS ti, cube.trans AS t, cube.loc AS loc, cube.pgroup AS pg, cube.prodline AS l WHERE ti.transid = t.id AND ti.pgid = pg.id AND pg.lineid = l.id AND t.locid = loc.id AND YEAR(pdate) > 1990 GROUP BY l.id, pg.id, loc.country, loc.state, loc.city, year(pdate), month(pdate), t.status, l.name, pg.name ) DATA INITIALLY DEFERRED REFRESH DEFERRED; REFRESH TABLE dba.SALESCUBE;
Die Übersichtstabelle ist normalerweise viel kleiner als die Basisfakttabellen. Sie können durch Angeben der Option DEFERRED steuern, wann die Übersichtstabelle aktualisiert wird (wie im aufgeführten Beispiel gezeigt).
Abfragen, die solche vorberechneten Summen nutzen können, sind unter anderem:
Obwohl die präzise Anwort für keine dieser Abfragen in der Übersichtstabelle enthalten ist, könnte der Aufwand zur Berechnung der Antwort mit Hilfe der Übersichtstabelle erheblich geringer ausfallen als bei Verwendung der umfangreichen Basistabelle, da ein Teil der für die Antwort benötigten Berechnungen bereits erfolgt ist. Aufwendige Verknüpfungen, Sortierungen und Spaltenberechnungen von Basisdaten werden mit Hilfe von Übersichtstabellen reduziert oder sogar vermieden.
Es folgen Beispielabfragen, die erhebliche Leistungsverbesserungen erfahren würden, weil sie die in der Übersichtstabelle bereits berechneten Ergebnisse verwenden könnten. Das erste Beispiel liefert die Gesamtverkäufe für 1995 und 1996:
SET CURRENT REFRESH AGE=ANY SELECT YEAR(pdate) AS year, SUM(ti.amount) AS amount FROM cube.transitem AS ti, cube.trans AS t, cube.loc AS loc, cube.pgroup AS pg, cube.prodline AS l WHERE ti.transid = t.id AND ti.pgid = pg.id AND pg.lineid = l.id AND t.locid = loc.id AND YEAR(pdate) IN (1995, 1996) GROUP BY year(pdate);
Das zweite Beispiel liefert die Gesamtverkäufe nach Produktgruppe für 1995 und 1996:
SET CURRENT REFRESH AGE=ANY SELECT pg.id AS "PRODUCT GROUP", SUM(ti.amount) AS amount FROM cube.transitem AS ti, cube.trans AS t, cube.loc AS loc, cube.pgroup AS pg, cube.prodline AS l WHERE ti.transid = t.id AND ti.pgid = pg.id AND pg.lineid = l.id AND t.locid = loc.id AND YEAR(pdate) IN (1995, 1996) GROUP BY pg.id;
Deutlichere Verbesserungen in der Antwortzeit für solche Abfragen können mit größeren Datenbanken erreicht werden. Dies liegt daran, daß die Übersichtstabelle langsamer anwächst als die Basistabelle. Ein Vorteil von Übersichtstabellen besteht darin, daß DB2 Universal Database sie dazu verwendet, überlappende Arbeit zwischen Abfragen effektiv zu beseitigen, indem die Berechnung nur einmal bei der Erstellung der Übersichtstabellen durchgeführt und ihr Inhalt für eine sehr große Zahl von Abfragen erneut verwendet wird.