Bei der Kompilierung einer SQL-Anweisung, schätzt das Optimierungsprogramm den Ausführungsaufwand der verschiedenen Methoden ab, die die Anforderung erfüllen würden. Auf der Grundlage dieser Abschätzung wählt das Optimierungsprogramm den Zugriffsplan aus, den es für optimal hält. Ein Zugriffsplan gibt die Reihenfolge von Operationen an, die erforderlich sind, um eine SQL-Anweisung auszuführen. Wenn ein Anwendungsprogramm gebunden wird, wird ein Paket erstellt. Dieses Paket enthält Zugriffspläne für alle statischen SQL-Anweisungen in dem entsprechenden Anwendungsprogramm. Die Zugriffspläne für dynamische SQL-Anweisungen werden zum Zeitpunkt der Ausführung der Anwendung erstellt.
Es gibt zwei Möglichkeiten, auf Daten in einer Tabelle zuzugreifen: durch direktes Lesen der Tabelle (Tabellensuche) oder über den Zugriff auf einen Index für die Tabelle (Indexsuche).
Eine Tabellensuche ist der Prozeß, bei dem der Datenbankmanager sequentiell auf jede Zeile einer Tabelle zugreift. Lesen Sie im Abschnitt Konzepte der Indexsuche die Informationen zur Funktionsweise von Indexsuchen und in Tabellensuche und Indexsuche die Informationen, unter welchen Bedingungen jeweils die eine oder andere Art der Suche verwendet wird.
Die folgenden Themen behandeln andere Methoden, die ebenfalls in einem Zugriffsplan zum Zugriff auf Daten in einer Tabelle und zur Lieferung von Ergebnissen für die Abfrage verwendet werden können:
Weitere zugehörige Abschnitte:
Als Indexsuche wird der Vorgang bezeichnet, bei dem der Datenbankmanager zu folgenden Zwecken auf einen Index zugreift:
Suchoperationen können in Indizes auch in der entgegengesetzten Richtung zu der Richtung durchgeführt werden, mit der sie definiert wurden. Weitere Informationen finden Sie in der Beschreibung der Option ALLOW REVERSE SCANS in der Anweisung CREATE INDEX im Handbuch SQL Reference.
Die folgenden weiteren Themen werden behandelt:
Der Datenbankmanager verwendet eine B+-Baumstruktur zur Speicherung der Indizes. Eine B+-Baumstruktur hat eine oder mehrere Ebenen, wie die folgende Abbildung zeigt (rid steht für Satz/Zeilen-ID):
Die oberste Ebene wird als Wurzelknoten bezeichnet. Die unterste Ebene besteht aus Blattknoten, in denen die tatsächlichen Werte des Indexschlüssels und ein Zeiger auf die tatsächliche Zeile in der Tabelle gespeichert sind. Die Ebenen zwischen dem Wurzelknoten und dem Blattknoten werden als innere Knoten bezeichnet.
Bei der Suche nach einem bestimmten Indexschlüsselwert durchsucht der Indexmanager den Indexbaum ausgehend vom Wurzelknoten. Der Wurzelknoten enthält einen Schlüssel für jeden Knoten auf der folgenden Ebene. Der Wert jedes dieser Schlüssel ist jeweils der größte vorhandene Schlüsselwert für den entsprechenden Knoten auf der nächsten Ebene. Wenn zum Beispiel ein Index drei Ebenen hat, wie in Abbildung 79 gezeigt, dann durchsucht der Indexmanager zum Auffinden eines Indexschlüsselwerts den Wurzelknoten nach dem ersten Schlüsselwert, der größer oder gleich dem gesuchten Schlüsselwert ist. Dieser Wurzelknotenschlüssel enthält dann einen Zeiger auf einen bestimmten inneren Knoten. Dieselbe Prozedur wird dann für diesen inneren Knoten durchgeführt, um festzustellen, in welchem Blattknoten die Suche fortzusetzen ist. Der endgültige Indexschlüssel wird dann im Blattknoten gefunden. In Abbildung 79 wird zum Beispiel nach "I" gesucht. Der erste Schlüssel im Wurzelknoten, der größer oder gleich "I" ist, ist "N". Dieser Wert zeigt auf den mittleren Knoten auf der nächsten Ebene. Der erste Schlüssel in diesem inneren Knoten, der größer oder gleich "I" ist, ist "L". Dieser Wert zeigt auf einen bestimmten Blattknoten, in dem der Indexschlüssel für "I" zusammen mit den entsprechenden Zeilen-IDs gefunden wird (die Zeilen-ID der entsprechenden Zeilen in der Basistabelle).
Anmerkung: | Auf der Blattknotenebene kann es frühere Blattzeiger geben. Dies kann sehr vorteilhaft sein, da der Indexmanager, wenn er einmal einen bestimmten Schlüsselwert im Index beim Durchlaufen der Baumstruktur gefunden hat, die Blattknoten in beide Richtungen durchsuchen kann, um einen Bereich von Werten abzurufen. Diese Möglichkeit, den Index in beide Richtungen zu durchsuchen, ist nur gegeben, wenn der Index mit dem Parameter ALLOW REVERSE SCANS erstellt wurde. |
Weitere Informationen finden Sie in der Beschreibung der Optionen für die Anweisung CREATE INDEX im Handbuch SQL Reference.
Bei der Bestimmung, ob ein Index für eine bestimmte Abfrage verwendet werden kann, wertet das Optimierungsprogramm jede Spalte des Index beginnend bei der ersten Spalte aus, um zu überprüfen, ob sie zur Erfüllung der folgenden Vergleichselemente verwendet werden kann:
Ein Vergleichselement ist ein Element einer Suchbedingung in einer Klausel WHERE, das eine Vergleichsoperation definiert oder impliziert. Vergleichselemente, die zur Eingrenzung des Bereichs einer Indexsuche verwendet werden können, sind solche, die eine Indexspalte betreffen, für die eine der folgenden Bedingungen gilt:
Zum Beispiel sei ein Index mit der folgenden Definition gegeben:
INDEX IX1: NAME ASC, DEPT ASC, MGR DESC, SALARY DESC, YEARS ASC
Die folgenden Vergleichselemente könnten verwendet werden, um den Bereich der Indexsuche in Index IX1 einzugrenzen:
WHERE NAME = :hv1 AND DEPT = :hv2
oder
WHERE MGR = :hv1 AND NAME = :hv2 AND DEPT = :hv3
Beachten Sie, daß im zweiten Beispiel die Vergleichselemente nach WHERE nicht in derselben Reihenfolge angegeben werden müssen, in der die Schlüsselspalten im Index erscheinen. In den Beispielen werden Host-Variablen (hv = Host-Variable) verwendet, jedoch hätten Parametermarken, Ausdrücke und Konstanten dieselbe Wirkung.
Ein einzelner Index, der mit dem Parameter ALLOW REVERSE SCANS in der Anweisung CREATE INDEX erstellt wurde, kann vorwärts und rückwärts durchsucht werden. Das heißt, solche Indizes unterstützen Suchoperationen in der Richtung, die bei der Erstellung des Index definiert wurde, und Suchoperationen in entgegengesetzter oder umgekehrter Richtung. Die Anweisung könnte ungefähr wie folgt aussehen:
CREATE INDEX iname ON tname (cname DESC) ALLOW REVERSE SCANS
In diesem Fall wird der Index (iname) nach den absteigenden (DESCending) Werten der Spalte cname gebildet. Durch Zulassen von umgekehrten Suchoperationen kann eine Suche in aufsteigender Folge durchgeführt werden, obwohl der Index für die Spalte für Suchoperationen in absteigender Folge definiert ist. Die tatsächliche Verwendung des Index in beiden Richtungen wird nicht von Ihnen, sondern vom Optimierungsprogramm bei der Erstellung und Auswahl von Zugriffsplänen gesteuert.
In der folgenden Klausel WHERE werden nur die Vergleichselemente für NAME und DEPT verwendet, um den Bereich der Indexsuche einzugrenzen, jedoch nicht die Vergleichselemente für SALARY und YEARS:
WHERE NAME = :hv1 AND DEPT = :hv2 AND SALARY = :hv4 AND YEARS = :hv5
Der Grund dafür ist der, daß es eine Schlüsselspalte (MGR) gibt, die diese Spalten von den ersten beiden Indexschlüsselspalten trennt, so daß die Reihenfolge nicht gewährleistet wäre. Wenn aber der Bereich einmal durch die Vergleichselemente NAME = :hv1 und DEPT = :hv2 festgelegt ist, können die verbleibenden Vergleichselemente an den verbleibenden Indexschlüsselspalten ausgewertet werden.
Neben den beschriebenen Gleichheitsvergleichselementen können auch einige Ungleichheitsvergleichselemente zur Eingrenzung des Bereichs einer Indexsuche verwendet werden. Im folgenden werden zwei Arten von Ungleichheitsvergleichselemente behandelt: strenge Ungleichheit und einschließende Ungleichheit.
Vergleichselement strenger Ungleichheit: Die Operatoren der strengen (ausschließenden) Ungleichheit, die für bereichsbegrenzende Vergleichselemente verwendet werden können, sind > und <.
Zur Eingrenzung eines Bereichs für eine Indexsuche wird nur eine Spalte mit Vergleichselementen strenger Ungleichheit berücksichtigt. Im folgenden Beispiel können die Vergleichselemente für die Spalten NAME und DEPT verwendet werden, um den Bereich einzugrenzen, aber das Vergleichselement für die Spalte MGR nicht.
WHERE NAME = :hv1 AND DEPT > :hv2 AND DEPT < :hv3 AND MGR < :hv4
Vergleichselemente einschließender Ungleichheit: Die folgenden Operatoren einschließender Ungleichheit können in Vergleichselementen zur Eingrenzung von Bereichen verwendet werden:
Zur Eingrenzung eines Bereichs für eine Indexsuche werden mehrere Spalten mit Vergleichselementen einschließender Ungleichheit berücksichtigt. Im folgenden Beispiel können alle Vergleichselemente zur Eingrenzung des Bereichs der Indexsuche verwendet werden:
WHERE NAME = :hv1 AND DEPT >= :hv2 AND DEPT <= :hv3 AND MGR <= :hv4
Zur weiteren Verdeutlichung dieses Beispiels seien die folgenden Werte angenommen: :hv2 = 404, :hv3 = 406 und :hv4 = 12345. Der Datenbankmanager durchsucht den Index für die Abteilungen 404 und 405 ganz, bricht aber die Suche in Abteilung 406 ab, wenn er auf den ersten Manager trifft, der eine Personalnummer (Spalte MGR) über dem Wert 12345 hat.
Weitere Informationen finden Sie in Bereichsbegrenzende und bei Indexsuchen als Suchargument verwendbare Vergleichselemente.
Wenn die Abfrage eine Sortierung erfordert, kann ein Index zum Sortieren der Daten verwendet werden, wenn die ordnenden Spalten nacheinander, angefangen bei der ersten Indexschlüsselspalte, im Index auftreten. (Ordnen oder Sortieren kann das Ergebnis solcher Operationen wie ORDER BY, DISTINCT, GROUP BY, Unterabfrage mit "= ANY", Unterabfrage mit "> ALL", Unterabfrage mit "< ALL", INTERSECT bzw. EXCEPT sowie UNION sein.) Eine Ausnahme ist der Fall, wenn die Indexschlüsselspalten auf Gleichheit mit "konstanten Werten" überprüft werden (d. h., mit einem Ausdruck, aus dem sich eine Konstante errechnet). In diesem Fall kann die ordnende Spalte eine andere als die ersten Indexschlüsselspalten sein. Betrachten Sie zum Beispiel folgende Abfrage:
WHERE NAME = 'JONES' AND DEPT = 'D93' ORDER BY MGR
Der Index könnte verwendet werden, um die Zeilen zu sortieren, da die Spalten NAME und DEPT immer dieselben Werte haben und so geordnet werden. Anders ausgedrückt, die gezeigten Klauseln WHERE und ORDER BY sind äquivalent mit folgenden Klauseln:
WHERE NAME = 'JONES' AND DEPT = 'D93' ORDER BY NAME, DEPT, MGR
Ein eindeutiger Index kann auch zum Verkürzen einer Sortieranforderung verwendet werden. Betrachten Sie zum Beispiel die folgende Indexdefinition und Klausel ORDER BY:
UNIQUE INDEX IX0: PROJNO ASC SELECT PROJNO, PROJNAME, DEPTNO FROM PROJECT ORDER BY PROJNO, PROJNAME
Eine zusätzliche Sortierung anhand der Spalte PROJNAME ist nicht erforderlich, da der Index IX0 bereits sicherstellt, daß die Werte der Spalte PROJNO eindeutig sind. Diese Eindeutigkeit sorgt dafür, daß es nur einen Wert für PROJNAME für jeden Wert von PROJNO gibt.
In einigen Fällen können alle angeforderten Daten aus dem Index abgerufen werden, ohne auf die Tabelle zuzugreifen. Dies wird als reiner Indexzugriff bezeichnet.
Betrachten Sie die folgende Indexdefinition zur Illustration des reinen Indexzugriffs:
INDEX IX1: NAME ASC, DEPT ASC, MGR DESC, SALARY DESC, YEARS ASC
In diesem Fall kann die folgende Abfrage nur durch den Zugriff auf den Index ohne Lesen der Basistabelle erfüllt werden:
SELECT NAME, DEPT, MGR, SALARY FROM EMPLOYEE WHERE NAME = 'SMITH'
In anderen Fällen kann es Spalten geben, die nicht im Index vertreten sind. Um Daten für diese Spalten abzurufen, müssen Zeilen der Basistabelle gelesen werden. Wenn zum Beispiel der Index IX1 vorhanden ist, muß für die folgende Abfrage auf die Basistabelle zugegriffen werden, um Daten der Spalten PHONENO und HIREDATE abzurufen:
SELECT NAME, DEPT, MGR, SALARY, PHONENO, HIREDATE FROM EMPLOYEE WHERE NAME = 'SMITH'
Durch Erstellen eines eindeutigen Index mit INCLUDE-Spalten können Sie die Leistung bei Datenabfragen durch Erhöhen der Anzahl von Zugriffsversuchen, die nur auf Indizes abzielen, verbessern.
Betrachten Sie zur Verdeutlichung der Verwendung von INCLUDE-Spalten die folgende Indexdefinition:
CREATE UNIQUE INDEX IX1 ON EMPLOYEE (NAME ASC) INCLUDE (DEPT, MGR, SALARY, YEARS)
Diese Anweisungen erstellen einen eindeutigen Index, der die Eindeutigkeit der Spalte NAME gewährleistet und außerdem die Daten für die Spalten DEPT, MGR, SALARY und YEARS speichert und pflegt.
Die folgende Abfrage kann nur durch den Zugriff auf den Index ohne Lesen der Basistabelle erfüllt werden:
SELECT NAME, DEPT, MGR, SALARY FROM EMPLOYEE WHERE NAME='SMITH'
In allen oben aufgeführten Beispielen wurde eine einzelne Indexsuche zur Erzielung der Ergebnisse durchgeführt. Zur Erfüllung der Vergleichselemente einer Klausel WHERE kann das Optimierungsprogramm auch mehrere Indizes durchsuchen. Betrachten Sie zum Beispiel die beiden folgenden Indexdefinitionen:
INDEX IX2: DEPT ASC INDEX IX3: JOB ASC, YEARS ASC
Die folgenden Vergleichselemente könnten mit Hilfe dieser beiden Indizes aufgelöst werden:
WHERE DEPT = :hv1 OR (JOB = :hv2 AND YEARS >= :hv3)
In diesem Beispiel liefert das Durchsuchen des Index IX2 eine Liste von Zeilen-IDs (RIDs), die das Vergleichselement DEPT = :hv1 erfüllen. Das Durchsuchen des Index IX3 liefert eine Liste der RIDs, die das Vergleichselement JOB = :hv2 AND YEARS >= :hv3 erfüllen. Diese beiden Listen von RIDs können kombiniert und doppelte Werte entfernt werden, bevor auf die Tabelle zugegriffen wird. Diese Methode wird als OR-Verknüpfung von Indizes (Oder-Verknüpfung) bezeichnet.
Die OR-Verknüpfung von Indizes kann auch für Vergleichselemente mit dem Ausdruck IN wie in folgendem Beispiel verwendet werden:
WHERE DEPT IN (:hv1, :hv2, :hv3)
Das Ziel der OR-Verknüpfung von Indizes ist es, doppelte RIDs vollständig zu entfernen; das Ziel der AND-Verknüpfung von Indizes (Index ANDing) ist es dagegen, gemeinsame RIDs zu finden. Die AND-Verknüpfung von Indizes kann bei Anwendungen auftreten, wenn es mehrere Indizes für entsprechende Spalten innerhalb derselben Tabelle gibt und eine Abfrage mit mehreren Vergleichselementen "AND" für die Tabelle ausgeführt wird. Mehrere Indexsuchen für alle mit Indizes versehenen Spalten in einer solchen Abfrage ergeben Werte, mit denen in einem Hash-Verfahren Bitzuordnungen erstellt werden. Die zweite Bitzuordnung wird zur Prüfung der ersten Bitzuordnung verwendet, um die gesuchten Zeilen zu generieren, die zur Erstellung der endgültigen zurückzugebenden Datenmenge abgerufen werden.
Betrachten Sie zum Beispiel die beiden folgenden Indexdefinitionen:
INDEX IX4: SALARY ASC INDEX IX5: COMM ASC
Die folgenden Vergleichselemente könnten mit Hilfe dieser beiden Indizes aufgelöst werden:
WHERE SALARY BETWEEN 20000 AND 30000 AND COMM BETWEEN 1000 AND 3000
In diesem Beispiel generiert das Durchsuchen des Index IX4 eine Bitzuordnung, die das Vergleichselement SALARY BETWEEN 20000 AND 30000 erfüllt. Das Durchsuchen von IX5 und Prüfen gegen die Bitzuordnung für IX4 liefert eine Liste von RIDs, die beide Vergleichselemente erfüllen. Dies wird als "dynamische AND-Verknüpfung über Bitzuordnungen" bezeichnet. Diese Methode wird nur angewandt, sofern die Tabelle ausreichend Kardinalität hat und die Spalten genügend Werte in dem gesuchten Bereich oder genügend Duplizität haben, wenn Gleichheitsvergleichselemente verwendet werden.
Anmerkung: | Beim Zugriff auf eine einzige Tabelle werden von DB2 die AND-Verknüpfung und die OR-Verknüpfung von Indizes nicht kombiniert. |
Bei der Auswahl des Zugriffsplans bezieht das Optimierungsprogramm den Ein-/Ausgabeaufwand für das Laden von Seiten von der Platte in den Pufferpool in die Kalkulation mit ein. In den Berechnungen schätzt das Optimierungsprogramm die Anzahl der E/A-Operationen ab, die zur Erfüllung einer Abfrage erforderlich sind. Diese Schätzung schließt eine Voraussage über den Bedarf an Pufferpool mit ein, da zum Lesen von Zeilen einer Seite, die sich bereits im Pufferpool befindet, keine weiteren E/A-Operationen anfallen.
Für Indexsuchen verwendet das Optimierungsprogramm Informationen aus den Systemkatalogtabellen (SYSCAT.INDEXES), um die Abschätzung des Ein-/Ausgabeaufwands zum Lesen von Datenseiten in den Pufferpool zu unterstützen. Die folgenden Spalten aus der Tabelle SYSCAT.INDEXES werden verwendet:
oder
Wenn keine Statistiken verfügbar sind, verwendet das Optimierungsprogramm Standardwerte für die statistischen Daten, die von einem geringen Grad der Clusterbildung der Daten bezüglich des Index ausgehen. Lesen Sie dazu auch die Informationen in Kapitel 24, Systemkatalogstatistiken und im Abschnitt Erfassen statistischer Daten mit dem Dienstprogramm RUNSTATS.
Sie können einen Clusterungsindex angeben, der zur Clusterung der Zeilen während einer Tabellenreorganisation und zur Erhaltung dieses Merkmals während der Verarbeitung von INSERT-Operationen dient. (Lesen Sie im Abschnitt Reorganisieren von Katalogen und Benutzertabellen die Informationen zur Reorganisation von Tabellen.) Nachfolgende Aktualisierungen und Einfügungen können den Index weniger gut geclustert zurücklassen (wie mit den von RUNSTATS gesammelten Statistiken zu messen ist), so daß von Zeit zu Zeit eine Reorganisation der Tabelle erforderlich wird. Um die Häufigkeit der Reorganisation einer sich permanent ändernden Datenbank zu verringern, kann der Parameter PCTFREE beim Ändern einer Tabelle (ALTER TABLE) verwendet werden. Dieser Parameter ermöglicht es, weitere Einfügungen so in die vorhandenen Daten einzugliedern, daß die Clusterung erhalten bleibt.
Der Grad, zu dem die Daten in bezug auf einen Index in Gruppen zusammengefaßt (d. h. geclustert) sind, kann bedeutende Auswirkungen auf die Leistung haben, so daß einer der Indizes für eine Tabelle auf einem Grad nahe an 100% Clusterbildung gehalten werden sollte.
Im allgemeinen kann nur ein Index eine 100prozentige Clusterbildung aufweisen. Eine Ausnahme bilden nur solche Fälle, in denen die Schlüssel eines anderen Index eine Obermenge der Schlüssel des Clusterungsindex sind oder in denen es eine De-Facto-Korrelation zwischen den Schlüsselspalten der beiden Indizes gibt.
Weitere Informationen zu leistungsrelevanten Gründen für die Verwendung von Clusterungsindizes finden Sie in Hinweise zur Leistung für die Verwaltung von Indizes. Weitere Informationen zur Erstellung eines Clusterungsindex finden Sie unter CREATE INDEX im Handbuch SQL Reference.
Clusterung von Seitenleseoperationen mit Hilfe des Vorablesezugriffs über Listen: Wenn das Optimierungsprogramm einen Index zum Zugriff auf Zeilen verwendet, kann es das Lesen der Datenseiten verzögern, bis alle Zeilen-IDs (RIDs) aus dem Index empfangen wurden. Betrachten Sie zum Beispiel den bereits früher definierten Index IX1:
INDEX IX1: NAME ASC, DEPT ASC, MGR DESC, SALARY DESC, YEARS ASC
Betrachten Sie folgende Suchkriterien:
WHERE NAME BETWEEN 'A' and 'I'
In diesem Fall könnte das Optimierungsprogramm eine Indexsuche in IX1 durchführen, um die Zeilen (und Datenseiten), die abzurufen sind, zu ermitteln. Wenn die Daten nach diesem Index nicht geclustert sind, enthält der Vorablesezugriff über Listen einen Schritt zum Sortieren der durch die Indexsuche ermittelten Liste von Zeilen-IDs (RIDs). Weitere Informationen finden Sie in Vorablesezugriff über Listen.
Der Datenbankmanager versucht nach Möglichkeit das Auftreten eines sequentiellen Zugriffs auf Indexseiten festzustellen und entsprechende Anforderungen zum Vorablesen zu generieren. Auf diese Weise wird die benötigte Zeit für nichtselektive Indexsuchen sowie die benötigte Zeit für Indexsuchen, die auf einen wesentlichen Teil des Index zugreifen, bedeutend verringert.
Das Optimierungsprogramm stützt sich bei der Abschätzung des zu erwartenden Volumens des Vorablesezugriffs auf Indexseiten auf Indexstatistikdaten wie DENSITY und SEQUENTIAL_PAGES, die Merkmale der Tabellenbereiche, in denen sich der Index befindet, und die Informationen über die Auswirkung etwaiger bereichsbegrenzender Vergleichselemente. Von diesen Schätzwerten ausgehend wird der Gesamtaufwand für die Verwendung eines bestimmten Index abgeschätzt.
Weitere Informationen finden Sie in Sequentieller Vorablesezugriff.
Das Optimierungsprogramm wählt eine Tabellensuche, wenn für die Abfrage kein Index verwendet werden kann oder wenn das Optimierungsprogramm feststellt, daß eine Indexsuche aufwendiger ist. Eine Indexsuche ist in folgenden Fällen auswendiger:
Mit den SQL-EXPLAIN-Einrichtungen können Sie feststellen, ob Ihr Zugriffsplan eine Tabellensuche oder eine Indexsuche verwendet. Weitere Informationen finden Sie in Kapitel 26, Die SQL-EXPLAIN-Einrichtung.
Eine Benutzeranwendung fordert eine Menge von Zeilen aus der Datenbank mit Hilfe einer SQL-Anweisung an, in der die gewünschten Zeilen mit Vergleichselementen näher eingegrenzt werden. Wenn das Optimierungsprogramm eine SQL-Anweisung auswertet, wird jedes Vergleichselement einer von vier Kategorien zugeordnet. Die Kategorie wird dadurch bestimmt, wie und wann das jeweilige Vergleichselement im Auswertungsprozeß verwendet wird. Diese Kategorien werden im folgenden in der Reihenfolge von der höchsten bis zur niedrigsten Leistung geordnet aufgelistet:
(Die Bezeichnung SARGable ist aus dem Begriff search argument abgeleitet.)
Der Abschnitt Zusammenfassung der Verwendung von Vergleichselementen enthält einen Vergleich der Merkmale, die sich auf die Leistung der verschiedenen Vergleichselementkategorien auswirken.
Bereichsbegrenzende Vergleichselemente werden zur Festlegung des Rahmens einer Indexsuche verwendet. Sie definieren Start- und/oder Stoppschlüsselwerte für die Indexsuche. Bei Indexsuchen als Suchargument verwendbare Vergleichselemente dienen nicht zur Eingrenzung einer Suche, aber sie können mit Hilfe des Index ausgewertet werden, da die im Vergleichselement verwendeten Spalten Teil des Indexschlüssels sind. Betrachten Sie zum Beispiel den zuvor definierten Index IX1 (siehe Konzepte der Indexsuche) und die folgende Klausel WHERE:
WHERE NAME = :hv1 AND DEPT = :hv2 AND YEARS > :hv5
Die ersten beiden Vergleichselemente (NAME = :hv1, DEPT = :hv2) wären bereichsbegrenzende Vergleichselemente, während YEARS > :hv5 ein bei Indexsuchen als Suchargument verwendbares Vergleichselement wäre.
Der Datenbankmanager verwendet die Indexdaten bei der Auswertung dieser Vergleichselemente, anstatt die Basistabelle zu lesen. Diese bei Indexsuchen als Suchargumente verwendbaren Vergleichselemente (Index SARGable) verringern die Anzahl der Datenseiten, auf die zugegriffen wird, indem die Menge der Zeilen, die aus der Tabelle zu lesen sind, verkleinert wird. Diese Vergleichselementkategorien wirken sich nicht auf die Anzahl der Indexseiten aus, auf die zugegriffen wird.
Vergleichselemente, die nicht vom Indexmanager ausgewertet werden können, sondern nur von den Datenverwaltungsservices, werden als bei Datensuchen verwendbare (Data SARGable) Vergleichselemente bezeichnet. In der Regel machen diese Vergleichselemente den Zugriff auf einzelne Zeilen aus einer Basistabelle erforderlich. Bei Bedarf rufen die Datenverwaltungsservices die zur Auswertung des Vergleichselements benötigten Spalten und andere Spalten ab, um die Spalten für die SELECT-Liste, die nicht aus dem Index abgerufen werden konnten, zur Verfügung zu stellen.
Betrachten Sie zum Beispiel einen einzelnen Index, der für die Tabelle PROJECT definiert ist:
INDEX IX0: PROJNO ASC
Bei der Ausführung der folgenden Abfrage würde das Vergleichselement DEPTNO = 'D11' als bei Datensuchen als Suchargument verwendbares Vergleichselement eingestuft.
SELECT PROJNO, PROJNAME, RESPEMP FROM PROJECT WHERE DEPTNO = 'D11' ORDER BY PROJNO
Restvergleichselemente sind typischerweise solche Vergleichselemente, die E/A-Operationen über den einfachen Zugriff auf eine Basistabelle hinaus erforderlich machen. Beispiele von Restvergleichselementen sind solche mit korrelierten Unterabfragen, quantifizierten Unterabfragen (d. h. Abfragen mit ANY, ALL, SOME oder IN) oder Vergleichselemente, für die Daten der Typen LONG VARCHAR oder LOB (die von der Tabelle getrennt gespeichert werden) gelesen werden. Diese Vergleichselemente werden von den Services für relationale Daten (Relational Data Services) ausgewertet.
Es kommt vor, daß Vergleichselemente, die nur auf einen Index angewandt wurden, noch einmal angewandt werden müssen, wenn auf die Datenseite zugegriffen wird. Zum Beispiel wenden Zugriffspläne mit OR-Verknüpfung oder AND-Verknüpfung von Indizes (siehe Zugriff über mehrere Indizes) die Vergleichselemente immer ein weiteres Mal als Restvergleichselemente an, wenn auf die Datenseite zugegriffen wird.
Die Verwendung von Vergleichselementen in einer Abfrage kann die Verringerung
der zur Erfüllung der Abfrage zu lesenden Daten unterstützen.
Verschiedene Kategorien von Vergleichselementen haben verschiedene
Auswirkungen auf die Leistung einer Abfrage. Die Auswirkungen werden
vom Optimierungsprogramm in der Aufwandskalkulation berücksichtigt. Die
folgende Tabelle zeigt die Rangordnung der verschiedenen
Vergleichselementkategorien und die Art der Auswirkung, die jede Kategorie auf
die Leistung haben kann.
Tabelle 49. Zusammenfassung der Merkmale der Vergleichselementkategorien
Merkmal | Vergleichselementkategorie | |||
---|---|---|---|---|
Bereichsbegrenzend | Indexsuchargument | Datensuchargument | Restvergleichselement | |
Verringern der Index-Ein-/Ausgabe | Ja | Nein | Nein | Nein |
Verringern der Datenseitenein-/ausgabe | Ja | Ja | Nein | Nein |
Verringern der Anzahl intern übergebener Zeilen | Ja | Ja | Ja | Nein |
Verringern der Anzahl der den Kriterien entsprechenden Zeilen | Ja | Ja | Ja | Ja |
Als Verknüpfung wird die Verkettung von Zeilen einer Tabelle mit Zeilen einer oder mehrerer anderer Tabellen bezeichnet. Betrachten Sie zum Beispiel die folgenden beiden Tabellen:
TABELLE1 TABELLE2 ----------------- ----------------- PROJ PROJ_ID PROJ_ID NAME ------ ------- ------- ------ A 1 1 Sam B 2 3 Joe C 3 4 Mary D 4 1 Sue 2 Mike
Die Verknüpfung von Tabelle1 und Tabelle2 an den Stellen, an denen die ID-Spalten gleiche Werte enthalten, wird durch die folgende SQL-Anweisung ausgedrückt:
SELECT PROJ, x.PROJ_ID, NAME FROM TABLE1 x, TABLE2 y WHERE x.PROJ_ID = y.PROJ_ID
Diese Anweisung ergäbe die folgende Menge von Ergebniszeilen:
PROJ PROJ_ID NAME ------ ------- ------ A 1 Sam A 1 Sue B 2 Mike C 3 Joe D 4 Mary
Bei der Verknüpfung zweier Tabellen wird die eine Tabelle als äußere Tabelle und die andere als innere Tabelle ausgewählt. Auf die äußere Tabelle wird zuerst zugegriffen, und sie wird nur einmal durchsucht. Ob die innere Tabelle mehrere Male durchsucht wird, hängt von der Art der Verknüpfung ab und davon, welche Indizes vorhanden sind. Unabhängig davon, ob durch eine Abfrage zwei oder mehr Tabellen miteinander verknüpft werden, verknüpft das Optimierungsprogramm jeweils nur zwei Tabellen gleichzeitig. Bei Bedarf werden temporäre Tabellen mit Zwischenergebnissen erstellt.
Das Optimierungsprogramm wählt eine von zwei Verknüpfungsmethoden (Verknüpfung über Verschachtelungsschleife oder Mischverknüpfung) in Abhängigkeit davon, ob ein Verknüpfungsvergleichselement (Definition siehe Mischverknüpfung) vorhanden ist und welche Verarbeitungsaufwände anhand der Tabellen- und Indexstatistiken ermittelt werden.
Eine Verknüpfung über Verschachtelungsschleife (Nested Loop Join) wird auf eine von zwei Arten ausgeführt:
Betrachten Sie zum Beispiel die Spalte A in den Tabellen T1 und T2 mit folgenden Werten:
Äußere Tabelle T1: Spalte A Innere Tabelle T2: Spalte A ------------------------ ------------------------ 2 3 3 2 3 2 3 1
Folgende Schritte werden bei der Verknüpfung über Verschachtelungsschleife ausgeführt:
Diese Methode kann für angegebene Vergleichselemente verwendet werden, wenn es ein Vergleichselement der folgenden Form gibt:
ausdr(äußere_tabelle.spalte) relop innere_tabelle.spalte
Dabei gilt, daß relop ein relativer Operator (z. B. =, >, >=, < oder <=) und ausdr ein gültiger Ausdruck für die äußere Tabelle ist. Beispiele:
ÄUSSERE.C1 + ÄUSSERE.C2 <= INNERE.C1
und
ÄUSSERE.C4 < INNERE.C3
Diese Methode ist eine Möglichkeit, die Anzahl der Zeilen, auf die in der inneren Tabelle für jeden Zugriff auf die äußere Tabelle zugegriffen wird, wesentlich zu verringern (obwohl dies von einer Reihe von Faktoren abhängig ist, zu denen auch die Selektivität des Verknüpfungsvergleichselements zählt).
Bei der Auswertung einer Verknüpfung über eine Verschachtelungsschleife bestimmt das Optimierungsprogramm auch, ob die äußere Tabelle sortiert wird oder nicht, bevor die Verknüpfung durchgeführt wird. Durch Sortieren der äußeren Tabelle auf der Grundlage der Verknüpfungsspalten kann die Anzahl der Leseoperationen zum Zugriff auf Seiten auf der Platte für die innere Tabelle verringert werden, da es wahrscheinlicher wird, daß sich die Seiten bereits im Pufferpool befinden. Wenn die Verknüpfung einen Index mit einem hohen Grad der Clusterbildung verwendet, um auf die innere Tabelle zuzugreifen, kann die Anzahl der Indexseiten, auf die zugegriffen wird, minimiert werden, wenn die äußere Tabelle sortiert wurde.
Darüber hinaus kann das Optimierungsprogramm eine Sortierung vor der Verknüpfung durchführen, wenn zu erwarten ist, daß durch die Verknüpfung eine spätere Sortierung aufwendiger wird. Eine spätere Sortierung könnte erforderlich sein, um die Klauseln GROUP BY, DISTINCT, ORDER BY oder eine Mischverknüpfung zu unterstützen.
Eine Mischverknüpfung (Merge Join) erfordert ein Vergleichselement der Form tabelle1.spalte = tabelle2.spalte. Ein solches Vergleichselement wird als Gleichheitsverknüpfungsvergleichselement bezeichnet. Eine Mischverknüpfung erfordert entweder über einen Indexzugriff oder durch eine Sortierung geordnete Eingaben für die Verknüpfungsspalten. Um eine Mischverknüpfung verwenden zu können, darf die Verknüpfungsspalte keine Spalte mit Langfelddaten (LONG) oder mit LOB-Daten sein.
Die verknüpften Tabellen werden gleichzeitig durchsucht. Die äußere Tabelle wird bei der Mischverknüpfung nur einmal durchsucht. Die innere Tabelle wird auch nur einmal durchsucht, sofern es keine sich wiederholenden Werte in der äußeren Tabelle gibt. Wenn es doppelte Werte in der äußeren Tabelle gibt, kann eine Gruppe von Zeilen der inneren Tabelle noch einmal durchsucht werden. Betrachten Sie zum Beispiel die Spalte A in den Tabellen T1 und T2 mit folgenden Werten:
Äußere Tabelle T1: Spalte A Innere Tabelle T2: Spalte A ------------------------ ------------------------ 2 1 3 2 3 2 3 3
Bei der Mischverknüpfung werden folgende Schritte ausgeführt:
Eine Hash-Verknüpfung erfordert ein oder mehrere Vergleichselemente der Form tabelle1.spalteX = tabelle2.spalteY, wobei es sich um die gleichen Spaltentypen handeln muß. Spalten des Typs CHAR müssen die gleiche Länge aufweisen. Bei Spalten des Typs DECIMAL muß die Genauigkeit und die Anzahl der Kommastellen übereinstimmen. Der Spaltentyp kann keine LONG-Feldspalte oder LOB-Spalte sein.
Zunächst wird eine Tabelle (als innere Tabelle (INNER) bezeichnet) durchsucht, und die Zeilen werden in Speicherpuffer kopiert, die aus dem Sortierspeicher zugeordnet werden (siehe Konfigurationsparameter Zwischenspeicher für Sortierlisten (sortheap) der Datenbank). Die Speicherpuffer werden nach einem "Hash-Code", der anhand der Spalten des Verknüpfungsvergleichselements (bzw. der Vergleichselemente) berechnet wird, in Partitionen unterteilt. Wenn die Größe der ersten Tabelle die Größe des Sortierspeichers überschreitet, werden Puffer aus ausgewählten Partitionen in temporäre Tabellen geschrieben. Nach Abschluß der Verarbeitungsschritte für die innere Tabelle wird die zweite Tabelle (als äußere Tabelle (OUTER) bezeichnet) durchsucht. Zeilen der äußeren Tabelle werden mit Zeilen aus der inneren Tabelle abgeglichen, indem zuerst ein aus den Spalten des Verknüpfungsvergleichselements (bzw. der Vergleichselemente) generierter "Hash-Code" verglichen wird. Wenn der "Hash-Code" der äußeren Zeilen mit dem "Hash-Code" der inneren Zeile übereinstimmt, werden die tatsächlichen Spalten des Verknüpfungsvergleichselements (bzw. der Vergleichselemente) miteinander verglichen.
Zeilen der äußeren Tabelle, die Partitionen entsprechen, die nicht in eine temporäre Tabelle geschrieben wurden, werden sofort mit den Zeilen der inneren Tabelle im Speicher abgeglichen. Andernfalls werden die äußeren Zeilen, wenn die entsprechende Partition der inneren Tabelle in eine temporäre Tabelle geschrieben wurde, ebenfalls in eine temporäre Tabelle geschrieben. Schließlich werden übereinstimmende Paare von Partitionen aus den temporären Tabellen gelesen, die "Hash-Codes" ihrer Zeilen abgeglichen und die Verknüpfungsvergleichselemente geprüft.
Zur Umsetzung der Leistungsvorteile der Hash-Verknüpfung kann es notwendig sein, den Wert des Konfigurationsparameters sortheap der Datenbank und des Konfigurationsparameters sheapthres des Datenbankmanagers zu ändern.
Bei Entscheidungshilfeabfragen benötigen Zugriffspläne mit Hash-Verknüpfungen mehr Sortierspeicher als Zugriffspläne mit anderen Verknüpfungsarten. Wenn der Wert von sheapthres relativ nahe am Wert von sortheap liegt (d. h. weniger als ein Faktor 2 oder 3 pro gleichzeitiger Abfrage), arbeitet eine Hash-Verknüpfung mit wesentlich weniger Speicher als das Optimierungsprogramm veranschlagt hat. Bei der Ausführung mit begrenztem Hauptspeicher können Hash-Verknüpfungen sehr langsam sein. Das Problem tritt in Abfragen mit mehreren Sortierungen und Hash-Verknüpfungen auf, in denen sich die Sortierungen bzw. Hash-Verknüpfungen den größten Teil des verfügbaren Speichers reservieren.
Die Lösung besteht darin, den Parameter sheapthres ausreichend groß (im Vergleich zu sortheap) zu konfigurieren.
Wie wird bei der Verknüpfung festgelegt, welche Tabelle die äußere und welche die innere ist? Im folgenden wird allgemein erläutert, wie das Optimierungsprogramm festlegt, welche Tabelle die innere und welche die äußere Tabelle sein soll.
Im Fall einer Hash-Verknüpfung wird die innere Tabelle in Speicherpuffern behalten. Wenn nicht genügend Speicherpuffer zur Verfügung stehen, ist die Hash-Verknüpfung gezwungen, einen Überlauf zu verursachen. Das Optimierungsprogramm versucht, dies zu vermeiden, und wählt daher die kleinere der beiden Tabellen als innere Tabelle und die größere als äußere Tabelle.
Die Reihenfolge, in der auf die Tabellen zugegriffen wird, spielt besonders bei einer Verknüpfung über Verschachtelungsschleife eine wichtige Rolle, da auf die äußere Tabelle nur einmal zugegriffen wird, während auf die innere Tabelle einmal für jede Zeile der äußeren Tabelle zugegriffen wird. Das Optimierungsprogramm wählt die äußere und innere Tabelle auf der Grundlage von Aufwandsschätzungen aus. Diese Aufwandsschätzungen werden von folgenden Faktoren beeinflußt:
Die kleinere Tabelle wird häufig als äußere Tabelle festgelegt, um die Anzahl der erneuten Zugriffe auf die innere Tabelle zu verringern. Allerdings kann ein Vorablesezugriff gerade das Gegenteil bewirken. Der Vorablesezugriff kann den Aufwand für den Zugriff auf eine umfangreiche Tabelle erheblich reduzieren. Jedoch ist der Vorablesezugriff nur für die äußere Tabelle einer Verknüpfung effizient. Daher kann auch auf die größere Tabelle zuerst zugegriffen werden. Weitere Informationen finden Sie in Vorablesen von Daten in den Pufferpool.
Eine Tabelle wird mit größerer Wahrscheinlichkeit als äußere Tabelle festgelegt, wenn selektive Vergleichselemente auf sie angewandt werden können, da auf die innere Tabelle nur für Zeilen zugegriffen wird, die die auf die äußere Tabelle angewandten Vergleichselemente erfüllen.
Wenn die gesamte innere Tabelle für jede Zeile der äußeren Tabelle durchsucht werden muß (d. h., es kann keine Indexsuche für die innere Tabelle durchgeführt werden), kann die kleinere der beiden Tabellen als innere Tabelle festgelegt werden, um die Vorteile der Pufferung nutzen zu können. Diese Festlegung wird von der Größe der Tabelle und des Pufferpools beeinflußt. Beachten Sie, daß die Entscheidungen über die Verknüpfung von der Größe des Pufferpools beeinflußt werden und daher der Zugriffsplan für Anwendungen geändert werden kann, wenn nach einer Änderung der Pufferpoolgröße die Anwendungen erneut an die Datenbank gebunden werden.
Die Möglichkeit, mehr als einen Pufferpool zu erstellen und die Größe dieses Pufferpools zu ändern sowie die Tabellenbereiche, die diesen Pufferpool verwenden, zu steuern, kann sich darauf auswirken, wann das Puffern innerhalb innerer und äußerer Tabellen verwendet wird.
Wenn für eine der Tabellen eine Indexsuche durchgeführt werden kann, dann bietet sich diese Tabelle zur Verwendung als innere Tabelle besonders an. Auf sie könnte dann über eine Suche nach einem Indexschlüssel mit Hilfe des Verknüpfungsschlüsselvergleichselements der äußeren Tabelle als eines der Schlüsselwerte zugegriffen werden. Wenn eine Tabelle keinen Index hat, ist sie zur Verwendung als innere Tabelle nicht besonders geeignet, da in diesem Fall die gesamte innere Tabelle für jede Zeile der äußeren Tabelle durchsucht werden müßte.
Auf eine Tabelle, für die eine Reihenfolge der Zeilen angefordert wurde, wird eventuell zuerst zugegriffen. Wenn zum Beispiel die Ausgabe der Verknüpfung zwischen t1 und t2 nach t1.c sortiert werden soll, kann der Zugriff auf t1 als äußere Tabelle mit einem Index für t1.c eine gute Methode sein. Die Ausgabe der Verknüpfung wäre geordnet, so daß keine weitere Sortierung erforderlich würde.
SELECT * FROM t1, t2 WHERE t1.a = t2.b ORDER BY t1.c
Die Reihenfolge, in der auf die Tabellen zugegriffen wird, spielt bei einer Mischverknüpfung eine nicht so wichtige Rolle, weil sowohl die innere als auch die äußere Tabelle nur einmal gelesen werden. Allerdings werden Teile der inneren Tabelle, die mehrfach auftretenden Werten in der Verknüpfungsspalte der äußeren Tabelle entsprechen, in einem speicherinternen Puffer behalten. Der Puffer wird erneut gelesen, wenn die nächste äußere Zeile mit der vorigen äußeren Zeile übereinstimmt. Ansonsten wird der Puffer neu belegt. Wenn die Anzahl der Vorkommen eines mehrfach auftretenden Verknüpfungswerts die Kapazität des speicherinternen Puffers überschreitet, werden nicht alle auftretenden Werte im Puffer behalten. Dies geschieht nur, wenn die Duplizität eines Werts groß ist und der Wert einen übereinstimmenden Wert in der äußeren Tabelle hat.
Bei allen Überlegungen zu diesen mehrfach auftretenden Werten wird doch in den meisten Fällen die Tabelle als äußere Tabelle in einer Verknüpfung ausgewählt, die weniger mehrfach auftretende Werte enthält. Letzten Endes wählt das Optimierungsprogramm die äußeren und inneren Tabellen jedoch auf der Grundlage detaillierter Aufwandsschätzungen aus.
Das Optimierungsprogramm kann die optimalen Methoden zur Verknüpfung mit Hilfe verschiedener Suchstrategien bestimmen. Die verwendete Suchstrategie wird durch der Optimierungsklasse festgelegt (siehe Anpassen der Optimierungsklasse). Die Suchstrategien und ihre Merkmale sind folgende:
Der Algorithmus für die Verknüpfungsaufzählung spielt die entscheidende Rolle bei der Bestimmung der Anzahl von Zugriffsplankombinationen, die vom Optimierungsprogramm geprüft werden.
Im allgemeinen sollten die Tabellen, auf die in einer Abfrage zugegriffen wird, durch Verknüpfungsvergleichselemente miteinander verbunden sein. Wenn zwei Tabellen ohne Verknüpfungsvergleichselement verknüpft werden, wird das kartesische Produkt der beiden Tabellen gebildet. Das heißt, jede ausgewählte Zeile der ersten Tabelle wird mit jeder ausgewählten Zeile der zweiten Tabelle verknüpft. Das Ergebnis ist eine Tabelle, die aus dem Kreuzprodukt in der Größe der beiden Tabellen besteht und in der Regel sehr groß ist. Da ein solcher Plan wahrscheinlich keine gute Leistung zuläßt, vermeidet das Optimierungsprogramm sogar die Aufwandsabschätzung für einen Plan dieser Art. Die einzige Ausnahme dieser Vorgehensweise tritt ein, wenn die Optimierungsklasse 9 definiert wurde oder der folgende Spezialfall eines "Sternschemas" (Star Scheme) vorliegt. Weitere Informationen finden Sie in Anpassen der Optimierungsklasse.
Die Fälle, in denen Zugriffspläne mit kartesischen Produkten eine gute Leistung zeigen, liegen in der Regel dann vor, wenn es sich um umfangreiche Entscheidungshilfedatenbanken handelt, die in der Technik eines Sternschemas (Star Schema) aufgebaut sind. Mit Sternschema wird der Aufbau einer Datenbank bezeichnet, bei dem das Gros der Rohdaten in einer einzigen umfangreichen Tabelle mit zahlreichen Spalten gespeichert wird, die gemeinhin als "Fakttabelle" bezeichnet wird. Viele der Spalten enthalten verschlüsselte Werte, die die Dimensionen eines bestimmten, in der Fakttabelle gespeicherten Faktums charakterisieren. Zur einfachen Ermöglichung der Analyse einer bestimmten Untermenge der Fakten werden die Dimensionstabellen verwendet, um die verschlüsselten Werte zu decodieren. Eine typische Abfrage bestünde aus mehreren lokalen Vergleichselementen, die auf decodierte Werte in den Dimensionstabellen verweisen, und enthielte Verknüpfungsvergleichselemente, die die Dimensionstabellen mit der Fakttabelle verbinden. Für diese Arten von Abfragen kann es vorteilhaft sein, das kartesische Produkt mehrerer kleiner Dimensionstabellen zu bilden und erst anschließend auf die umfangreiche Fakttabelle zuzugreifen. Diese Technik ist dann von Vorteil, wenn mehrere Verknüpfungsvergleichselemente einem mehrspaltigen Index entsprechen.
DB2 kann Abfragen erkennen, die für Datenbanken durchgeführt werden, die mit Sternschemen aufgebaut sind und mindestens zwei Dimensionstabellen haben, und kann den Suchbereich vergrößern, um potentielle Zugriffspläne mit kartesischen Produkten von Dimensionstabellen zu berücksichtigen. Wenn der Plan mit den kartesischen Produkten den niedrigsten geschätzten Aufwand verursacht, wird er vom Optimierungsprogramm ausgewählt.
Bei der bisher behandelten Sternschementechnik wurde angenommen, daß Primärschlüsselindizes in der Verknüpfung verwendet werden. Eine andere Situation liegt vor, wenn Fremdschlüsselindizes verwendet werden. Ausgehend von der Annahme, daß die Fremdschlüsselspalten in der Fakttabelle einspaltige Indizes sind und daß es eine relativ hohe Selektivität über alle Dimensionstabellen hinweg gibt, könnte die folgende Methode der Sternverknüpfung zur Anwendung kommen:
Für diese Methode sind keine mehrspaltigen Indizes erforderlich. Explizite referentielle Integritätsbedingungen zwischen Fakttabelle und Dimensionstabellen müssen für diese Methode nicht ausgewählt werden, obwohl die Beziehung zwischen Fakttabelle und Dimensionstabellen über dieses Merkmal verfügen sollte.
Ein anderer wichtiger Parameter bestimmt die Reihenfolge der Verknüpfungen in einer Abfrage. Das Ergebnis der Verknüpfung zweier Tabellen wird als zusammengesetzte Tabelle bezeichnet. In der Regel wird diese zusammengesetzte Ergebnistabelle als äußere Tabelle in einer Verknüpfung mit einer weiteren inneren Tabelle verwendet. In diesem Fall handelt es sich also um eine "zusammengesetzte äußere Tabelle". In einigen Fällen, besonders bei Verwendung der schnellen Verknüpfungsaufzählung (Greedy Join Enumeration), ist es sinnvoll, das Ergebnis der Verknüpfung zweier Tabellen zur inneren Tabelle einer späteren Verknüpfung zu machen. Wenn die innere Tabelle einer Verknüpfung selbst aus dem Ergebnis einer Verknüpfung zweier oder mehrerer Tabellen besteht, enthält der Plan eine "zusammengesetzte innere Tabelle". Betrachten Sie zum Beispiel die folgende Abfrage:
SELECT COUNT(*) FROM T1, T2, T3, T4 WHERE T1.A = T2.A AND T3.A = T4.A AND T2.Z = T3.Z
Hier könnte es von Vorteil sein, die Tabellen T1 und T2 zu verknüpfen ( T1xT2 ), anschließend die Tabellen T3 und T4 zu verknüpfen ( T3xT4 ) und schließlich das Ergebnis der ersten Verknüpfung als äußere Tabelle und das Ergebnis der zweiten Verknüpfung als innere Tabelle auszuwählen. Im daraus resultierenden Plan ( (T1xT2) x (T3xT4) ) ist das Ergebnis der Verknüpfung (T3xT4) eine zusammengesetzte innere Tabelle. Abhängig von der Abfrageoptimierungsklasse belegt das Optimierungsprogramm die maximale Anzahl von Tabellen, die als innere Tabelle einer Verknüpfung verwendet werden können, mit unterschiedlichen Einschränkungen. Zusammengesetzte innere Tabellen sind bei den Optimierungsklassen 5, 7 und 9 zulässig.
Durch die Verwendung replizierter Übersichtstabellen in einer partitionierten Datenbankumgebung können Sie die Leistung verbessern, indem Sie die Datenbank vorberechnete Werte der Basistabellendaten verwalten lassen. Zum Beispiel würde die im folgenden gezeigte Abfrage von der Erstellung der nachfolgend definierten replizierten Übersichtstabelle profitieren. Dazu gelten folgende Annahmen:
Anschließend erstellen Sie eine replizierte Übersichtstabelle auf der Basis der Informationen in der Tabelle EMPLOYEE.
CREATE TABLE R_EMPLOYEE AS ( SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT FROM EMPLOYEE ) DATA INITIALLY DEFERRED REFRESH IMMEDIATE IN REGIONTABLESPACE REPLICATED;
Wenn die replizierte Übersichtstabelle erstellt ist, wird ihr Inhalt durch Ausführung der folgenden Anweisung aktualisiert:
REFRESH TABLE R_EMPLOYEE;
Im folgenden Beispiel werden der Verkauf nach Mitarbeiter, die Summe für die Abteilung und die Gesamtsumme berechnet:
SELECT d.mgrno, e.empno, SUM(s.sales) FROM department AS d, employee AS e, sales AS s WHERE s.sales_person = e.lastname AND e.workdept = d.deptno GROUP BY ROLLUP(d.mgrno, e.empno) ORDER BY d.mgrno, e.empno;
Anstatt die Tabelle EMPLOYEE, die nur in einer Datenbankpartition ist, zu verwenden, verwendet der Datenbankmanager die Tabelle R_EMPLOYEE , die in jeder der Datenbankpartitionen repliziert wird, in der sich die Tabelle SALES befindet. Der Leistungsvorteil ergibt sich daraus, daß die Mitarbeiterinformationen zur Berechnung der Verknüpfung nicht zu jeder Datenbankpartition über das Netzwerk gesendet werden muß.
In den folgenden Abschnitten werden die Verknüpfungsstrategien beschrieben, die in einer partitionierten Datenbankumgebung möglich sind. Das DB2-Optimierungsprogramm wählt abhängig von den Anforderungen der jeweiligen Anwendung automatisch die beste Verknüpfungsstrategie aus. Die Verknüpfungsstrategien werden hier vorgestellt, um Ihnen einen besseren Einblick in die verschiedenen Verfahren zu geben. Eine "Tabellenwarteschlange" ist ein Mechanismus zur Übertragung von Zeilen zwischen Datenbankpartitionen oder zwischen Prozessoren in einer Datenbank mit einer Einzelpartition.
In den folgenden Ausführungen bezieht sich der Ausdruck übertragene Tabellenwarteschlange auf eine Tabellenwarteschlange, deren Zeilen über ein Hash-Verfahren einer der empfangenden Datenbankpartitionen zugeführt wurden. Eine rundgesendete Tabellenwarteschlange (Broadcast) ist eine Tabellenwarteschlange, deren Zeilen an alle empfangenden Datenbankpartitionen gesendet werden (d. h. ohne Hash-Verfahren). In den Abbildungen dieses Abschnitts beziehen sich q1, q2 und q3 (für "Queue") auf die Tabellenwarteschlangen in den Beispielen. Außerdem sind die Tabellen, auf die zugegriffen wird, für den Zweck dieser Beispielszenarios auf zwei Datenbankpartitionen verteilt. Die Pfeile zeigen die Richtung an, in der die Tabellenwarteschlangen übertragen bzw. gesendet werden. Der Koordinatorknoten ist Partition 0.
Ein Gesichtspunkt bei Tabellen, die häufig an Verknüpfungen in einer partitionierten Datenbank beteiligt sind, ist die Tabellenkollokation, d. h. die physische Zusammenfassung von Tabellen. Die Tabellenkollokation stellt eine Methode in einer partitionierten Datenbank dar, Daten aus einer Tabelle mit Hilfe eines gleichen Partitionierungsschlüssels mit den Daten aus einer anderen Tabelle in derselben Partition zu suchen. Wenn die Daten einmal in dieser Weise zusammengefaßt sind, können zu verknüpfende Daten an einer Abfrage beteiligt sein, ohne als Teil der Aktivität der Abfrage von einer Datenbankpartition in eine andere übertragen werden zu müssen. Nur die Ergebnismenge einer Verknüpfung wird an den Koordinatorknoten übergeben. Weitere Informationen zu diesem Thema finden Sie in "Tabellenkollokation" im Band Systemverwaltung: Konzept.
Informationen zu Verknüpfungsabhängigkeiten finden Sie im Handbuch SQL Reference.
Damit das Optimierungsprogramm eine zusammengefaßte Verknüpfung in Erwägung ziehen kann, müssen die zu verknüpfenden Tabellen in einer Partition zusammengefaßt sein (Kollokation) und alle Paare sich entsprechender Partitionierungsschlüssel müssen in den Gleichheitsvergleichselementen der Verknüpfung vertreten sein. Abbildung 80 zeigt ein Beispiel.
Anmerkung: | Replizierte Übersichtstabellen erhöhen die Wahrscheinlichkeit zusammengefaßter Verknüpfungen. Weitere Informationen finden Sie in Replizierte Übersichtstabellen. |
Abbildung 80. Beispiel für eine zusammengefaßte Verknüpfung
Diese parallele Verknüpfungsstrategie kann angewandt werden, wenn es zwischen den zu verknüpfenden Tabellen keine Gleichheitsvergleichselemente zur Verknüpfung gibt. Sie kann außerdem in solchen Fällen verwendet werden, in denen sie die Methode mit dem geringsten Aufwand darstellt. Solche Fälle liegen typischerweise dann vor, wenn eine sehr umfangreiche und eine sehr kleine Tabelle an der Verknüpfung beteiligt sind, von denen keine über die Spalten, auf die die Vergleichselemente angewandt werden, partitioniert ist. Anstatt beide Tabellen auf Partitionen zu verteilen, kann es "billiger" sein, die kleine Tabelle an alle Partitionen mit der größeren Tabelle rundzusenden. Abbildung 81 zeigt ein Beispiel.
Abbildung 81. Beispiel für eine Verknüpfung mit rundgesendeter äußerer Tabelle
Bei dieser Verknüpfungsstrategie wird jede Zeile der äußeren Tabelle an eine Datenbankpartition der inneren Tabelle (entsprechend den Partitionierungsattributen der inneren Tabelle) übertragen. Die Verknüpfung erfolgt in dieser Datenbankpartition. Abbildung 82 zeigt ein Beispiel.
Abbildung 82. Beispiel für eine Verknüpfung mit übertragener äußerer Tabelle
Bei dieser Strategie werden Zeilen der äußeren und inneren Tabellen entsprechend den Werten der Verknüpfungsspalten an eine Gruppe von Datenbankpartitionen übertragen. Die Verknüpfung erfolgt in diesen Datenbankpartitionen. Abbildung 83 zeigt ein Beispiel.
Abbildung 83. Beispiel für eine Verknüpfung mit übertragener innerer und äußerer Tabelle
Bei dieser Strategie wird die innere Tabelle an alle Datenbankpartitionen der äußeren Tabelle rundgesendet. Abbildung 84 zeigt ein Beispiel.
Abbildung 84. Beispiel für eine Verknüpfung mit rundgesendeter innerer Tabelle
Bei dieser Verknüpfungsstrategie wird jede Zeile der inneren Tabelle an eine Datenbankpartition der äußeren Tabelle (entsprechend den Partitionierungsattributen der äußeren Tabelle) übertragen. Die Verknüpfung erfolgt in dieser Datenbankpartition. Abbildung 85 zeigt ein Beispiel.
Abbildung 85. Beispiel für eine Verknüpfung mit übertragener innerer Tabelle
Eine Tabellenwarteschlange hat folgende Funktionen:
Eine Tabellenwarteschlange dient zur Übergabe der Daten in eine einzige Richtung.
Der Compiler entscheidet, wo Tabellenwarteschlangen erforderlich sind, und nimmt sie in den Plan auf. Bei der Ausführung des Plans werden die Tabellenwarteschlangen durch die Verbindungen zwischen den Datenbankpartitionen initiiert. Die Tabellenwarteschlangen werden am Ende der Prozesse wieder geschlossen.
Es gibt verschiedene Arten von Tabellenwarteschlangen:
Asynchrone Tabellenwarteschlangen werden verwendet, wenn Sie die Klausel FOR FETCH ONLY in der SELECT-Anweisung verwenden. Wenn Sie Zeilen nur mit FETCH abrufen, ist eine asynchrone Tabellenwarteschlange schneller.
Synchrone Tabellenwarteschlangen werden verwendet, wenn Sie die Klausel FOR FETCH ONLY in der SELECT-Anweisung nicht angeben. In einer partitionierten Datenbankumgebung verwendet der Datenbankmanager synchrone Tabellenwarteschlangen, wenn Zeilen aktualisiert werden.
Wenn das Optimierungsprogramm einen Zugriffsplan auswählt, kalkuliert es die Auswirkungen einer Sortierung von Daten auf die Leistung mit ein. Sortieroperationen werden durchgeführt, wenn es keinen Index gibt, über den die angeforderte Reihenfolge der abgerufenen Daten hergestellt werden kann. Eine Sortieroperation kann außerdem erfolgen, wenn sie vom Optimierungsprogramm als weniger aufwendig als eine Indexsuche eingestuft wird. Das Optimierungsprogramm kann beim Sortieren von Daten eine der folgenden Aktionen ausführen:
Wenn nach Beendigung der Sortierung die endgültige sortierte Liste von Daten in einem einzigen sequentiellen Vorgang gelesen werden kann, können die Ergebnisse über eine Pipe geleitet werden. Dieses Piping ist schneller als die Verwendung anderer (nicht über Pipe geleiteter) Mechanismen zur Übertragung der Sortierergebnisse. Das Optimierungsprogramm wählt, wenn möglich, die Pipe zur Übergabe der Sortierergebnisse.
Unabhängig davon, ob eine Sortierung über eine Pipe geleitet wird, ist die Dauer der Sortierung von einer Reihe von Faktoren abhängig, wie z. B. der Anzahl der zu sortierenden Zeilen, der Sortierschlüsselgröße und der Zeilenlänge. Wenn die zu sortierenden Zeilen mehr als den im Zwischenspeicher für Sortierlisten verfügbaren Speicherbereich in Anspruch nehmen, werden mehrere Sortierarbeitsgänge durchgeführt, wobei in jedem Arbeitsgang eine Untermenge der Gesamtmenge von Zeilen sortiert wird. Jeder Arbeitsgang des Sortiervorgangs wird in einer temporären Tabelle im Pufferpool gespeichert. (Im Rahmen der Pufferpoolverwaltung ist es möglich, daß Seiten aus dieser temporären Tabelle auf Platte geschrieben werden.) Wenn alle Arbeitsgänge des Sortiervorgangs abgeschlossen sind, müssen die sortierten Untermengen zu einer einzigen sortierten Menge von Zeilen zusammengefügt werden. Wenn die Sortierung über eine Pipe geleitet wird, werden die Zeilen nach dem Zusammenfügen direkt an die Services für relationale Daten (Relational Data Services) weitergegeben.
Weitere Informationen finden Sie in Anzeichen für Probleme bei der Sortierleistung oder in der Diskussion des Konfigurationsparameters sortheap im Abschnitt Konfigurationsparameter mit Auswirkung auf die Abfrageoptimierung.
In einigen Fällen kann sich das Optimierungsprogramm entscheiden, einen Sortiervorgang oder eine Spaltenberechnung (Aggregation) von der Komponente der Services für relationale Daten an die Komponente der Datenverwaltungsservices zu "verschieben" ("Pushdown"). Die Verschiebung dieser Operationen verbessert die Leistung, da nun die Datenverwaltungsservices Daten direkt an eine Sortier- oder Spaltenberechnungsroutine übergeben können. Ohne diese Verschiebung würden die Datenverwaltungsservices diese Daten zunächst an die Services für relationale Daten übergeben, die anschließend wiederum mit den Sortier- bzw. Spaltenberechnungsroutinen kommunizieren würden. Die folgende Abfrage beispielsweise kann von dieser Art der Optimierung profitieren:
SELECT WORKDEPT, AVG(SALARY) AS AVG_DEPT_SALARY FROM EMPLOYEE GROUP BY WORKDEPT
Wenn das Sortieren dazu dient, die erforderliche Reihenfolge für eine Operation GROUP BY herzustellen, hat das Optimierungsprogramm die Möglichkeit, einige oder alle Spaltenberechnungen (Aggregation) für GROUP BY während des Sortierens durchzuführen. Dies ist vorteilhaft, wenn die Anzahl der Zeilen in jeder Gruppe sehr groß ist. Es wird sogar noch vorteilhafter, wenn die Durchführung eines Teils der Gruppierung während des Sortierens die Notwendigkeit, daß die Sortierung einen Überlauf auf die Festplatte verursacht, verringert oder ausschließt.
Wenn Spaltenberechnung in einer Sortierung verwendet wird, sind bis zu drei Phasen der Spaltenberechnung erforderlich, um sicherzustellen, daß die richtigen Ergebnisse berechnet werden. Die erste Phase der Spaltenberechnung, die "partielle Spaltenberechnung" errechnet die Ergebniswerte, bis der Sortierspeicher voll ist. Die partielle Spaltenberechnung ist der Prozeß, durch den nicht berechnete Daten entgegengenommen und partielle Ergebniswerte erstellt werden. Wenn der Sortierspeicher voll ist, läuft der Rest der Daten auf die Festplatte über und enthält alle partiellen Spaltenberechnungsergebnisse, die während des aktuellen Füllens des Sortierspeichers berechnet wurden. Nach dem Zurücksetzen des Sortierspeichers werden neue Spaltenberechnungen gestartet.
Die zweite Phase der Spaltenberechnung, die "Zwischenberechnung" nimmt alle übergelaufenen Sortierdurchläufe und setzt die Spaltenberechnung für die Gruppierschlüssel fort. Die Spaltenberechnung kann nicht zu Ende geführt werden, weil die Gruppierschlüsselspalten eine Untergruppe der Partitionierungsschlüsselspalten sind. Die Zwischenberechnung nimmt vorhandene partielle Spaltenberechnungsergebnisse und produziert neue partielle Berechnungsergebnisse. Diese Phase ist wahlfrei und wird sowohl für partitionsinterne Parallelität als auch für partitionsübergreifende Parallelität verwendet. Im letzteren Fall ist die Gruppierung beendet, wenn ein globaler Gruppierschlüssel verfügbar ist. Bei partitionsübergreifender Parallelität würde dies eintreten, wenn der Gruppierschlüssel eine Untergruppe des Partitionierungsschlüssels wäre, der Gruppen über Partitionen hinweg teilt und so eine erneute Partitionierung zur Beendigung der Spaltenfunktion erforderlich machte. Ein ähnlicher Fall liegt vor, wenn bei partitionsinterner Parallelität jeder Agent seine übergelaufenen Sortierdurchgänge zusammengefügt hat, bevor auf einen einzigen Agenten reduziert wird, um die Spaltenberechnung zu beenden.
Die letzte Phase der Spaltenberechnung, die "Endberechnung", greift alle partiellen Berechnungsergebnisse auf und führt die Spaltenberechnung zu Ende. Die Endberechnung greift partielle Berechnungsergebnisse auf und produziert Endergebnisse. Dieser Schritt findet immer in einem Operator GROUP BY statt. Das Sortieren kann die Spaltenberechnung nicht bis zu Ende durchführen, weil es keine Garantie gibt, daß die Sortierung nicht geteilt wird. Die Komplettberechnung nimmt nicht berechnete Daten auf und produziert Endergebnisse. Diese Methode der Spaltenberechnung wird in der Regel dann verwendet, wenn die Gruppierdaten bereits in der richtigen Reihenfolge vorliegen und keine Partitionierung ihre Verwendung verhindert.