Die Entscheidung, wann ein Index verwendet werden sollte, wird nicht von Ihnen getroffen, sondern der Datenbankmanager wählt auf der Grundlage der verfügbaren Informationen über Tabellen und Indizes das am besten geeignete Verfahren. Allerdings obliegt Ihnen die wichtige Aufgabe der Erstellung der erforderlichen Indizes, die die Leistung verbessern können. Darüber hinaus haben Sie die Aufgabe, nach der Erstellung der Indizes Statistikdaten zu diesen Indizes (mit dem Dienstprogramm RUNSTATS) zu sammeln und den Wert für PREFETCHSIZE (wie oben erwähnt) zu ändern sowie die Statistikdaten fortlaufend auf dem neuesten Stand zu halten. Dies setzt voraus, daß Sie mit den verschiedenen Arten von Indizes, die Sie erstellen können, und den Erstellungsmethoden vertraut sind.
Wenn für eine der Tabellen, auf die in einer Datenbankabfrage verwiesen wird, kein Index existiert, muß für diese Tabelle eine Tabellensuche durchgeführt werden. Je umfangreicher die Tabelle ist, desto länger dauert die Tabellensuche. Als Tabellensuche wird der Vorgang bezeichnet, bei dem der Datenbankmanager sequentiell auf jede Zeile einer Tabelle zugreift. Die Tabellensuche steht einer Indexsuche gegenüber, bei der der Datenbankmanager auf die Daten über einen Index zugreift. (Siehe Konzepte der Indexsuche.)
Das Optimierungsprogramm entscheidet sich für die Verwendung eines Index, wenn aufgrund der Schätzungen zu erwarten ist, daß eine Indexsuche schneller als eine Tabellensuche durchgeführt werden kann. Indexdateien sind im allgemeinen kleiner und erfordern weniger Zeit zum Lesen als eine ganze Tabelle, besonders wenn die Tabellen umfangreicher werden. Darüber hinaus braucht eventuell nicht der gesamte Index durchsucht zu werden. Vergleichselemente, die auf einen Index angewandt werden, verringern die Anzahl der Zeilen, die aus den Datenseiten gelesen werden müssen.
Jeder Indexeintrag besteht aus einem Suchschlüsselwert und einem Zeiger auf die Zeile, die den entsprechenden Wert enthält. Die Werte können in umgekehrter Richtung nur dann durchsucht werden, wenn der Parameter ALLOW REVERSE SCANS in der Anweisung CREATE INDEX angegeben wurde. Es ist also möglich, die Suche unter Angabe geeigneter Vergleichselemente zu begrenzen. Ein Index kann auch dazu verwendet werden, Zeilen in einer geordneten Reihenfolge abzurufen und somit zu vermeiden, daß der Datenbankmanager die Zeilen nach dem Lesen aus der Tabelle in einem weiteren Arbeitsgang sortieren muß. Durch Angeben des Parameters ALLOW REVERSE SCANS kann der Index zum direkten Abrufen von Zeilen in Folge vorwärts und rückwärts verwendet werden. Weitere Einzelheiten finden Sie im Handbuch SQL Reference.
Ein eindeutiger Index kann außer dem Suchschlüssel und dem Zeilenzeiger auch INCLUDE-Spalten enthalten.
Anmerkung: | Es kann nicht gesteuert werden, ob der Datenbankmanager einen Index verwendet oder nicht. Beispielsweise kann durch die bloße Existenz eines Index für die abgefragte Datei nicht garantiert werden, daß das Abfrageergebnis in einer geordneten Reihenfolge erstellt wird. Der Datenbankmanager kann diesen Index während der Verarbeitung der Abfrage verwenden, er muß jedoch nicht. Nur durch die Angabe der Klausel ORDER BY kann die Reihenfolge der Ergebnismenge "garantiert" werden. |
Durch Indizes können die Zugriffszeiten erheblich verringert werden. Aber Indizes können auch nachteilige Auswirkungen auf die Leistung haben. Vor der Erstellung von Indizes sind daher die Auswirkungen mehrerer Indizes auf den Plattenspeicherplatz und die Verarbeitungszeit zu bedenken:
Indizes sollten daher mit Umsicht gewählt werden, um den Anforderungen des Anwendungsprogramms gerecht zu werden.
Um festzustellen, ob ein Index in einem bestimmten Paket verwendet wird, können Sie die SQL-EXPLAIN-Einrichtung verwenden, die in Kapitel 26, Die SQL-EXPLAIN-Einrichtung, beschrieben wird.
DB2 Index Advisor ist ein Tool, das Sie bei der Auswahl einer optimalen Gruppe von Indizes für Ihre Tabellendaten unterstützt. Sie haben verschiedene Möglichkeiten, auf dieses Tool zuzugreifen:
Weitere Informationen über DB2 Index Advisor finden Sie in SQL-Advise-Einrichtung.
Welche Indizes erstellt werden sollten, hängt von den Daten und ihrer beabsichtigten Verwendung ab. Die folgenden Richtlinien enthalten Anhaltspunkte zur Bestimmung, welche Indizes am sinnvollsten wären:
Weitere Informationen zu INCLUDE-Spalten finden Sie im Kapitel "Erstellen eines Index oder einer Indexspezifikation" im Band Systemverwaltung: Konzept.
Anmerkung: | Wenn ein Clusterungsindex definiert wird, sollte die Tabelle mit einem auf jeder Datenseite reservierten freien Speicherbereich geladen werden, um Einfügungen auf diesen Seiten zu ermöglichen. (Freier Speicherbereich wird durch das Schlüsselwort PCTFREE in der Anweisung ALTER TABLE oder durch die Klausel pagefreespace MODIFIED BY im Befehl LOAD reserviert.) |
Anmerkung: | Indizes werden nicht für deklarierte temporäre Tabellen unterstützt. |
Im folgenden werden typische Fälle beschrieben, in denen die Erstellung eines Index die Leistung verbessern kann:
Betrachten Sie zum Beispiel die folgende Klausel WHERE:
WHERE WORKDEPT='A01' OR WORKDEPT='E21'
Diese Klausel wird im allgemeinen von einem Index für die Spalte WORKDEPT profitieren, sofern diese Werte nicht häufig auftreten.
Im folgenden Beispiel wird die Klausel DISTINCT verwendet:
SELECT DISTINCT WORKDEPT FROM EMPLOYEE
Der Datenbankmanager kann einen Index verwenden, der in aufsteigender oder absteigender Reihenfolge für die Spalte WORKDEPT definiert ist, um doppelte Werte zu eliminieren. Derselbe Index könnte auch verwendet werden, um Werte wie in folgendem Beispiel mit einer Klausel GROUP BY zu gruppieren:
SELECT WORKDEPT, AVERAGE(SALARY) FROM EMPLOYEE GROUP BY WORKDEPT
Betrachten Sie zum Beispiel die folgende SQL-Anweisung:
SELECT LASTNAME FROM EMPLOYEE WHERE WORKDEPT IN ('A00','D11','D21')
Wenn ein Index für die Spalten WORKDEPT und LASTNAME der Tabelle EMPLOYEE definiert ist, kann die Anweisung eventuell effizienter verarbeitet werden, indem nur der Index und nicht die gesamte Tabelle durchsucht wird. Beachten Sie, daß hier die Spalte WORKDEPT die erste Spalte des Index sein sollte, da sich das Vergleichselement auf diese Spalte bezieht.
CREATE UNIQUE INDEX x ON employee (workdept) INCLUDE (lastname)
Die Angabe von lastname als INCLUDE-Spalte und nicht als Teil des Indexschlüssels bedeutet, daß lastname nur auf den äußeren Seiten (Blattseiten) des Index gespeichert wird.
Die folgenden Erläuterungen geben Hinweise, wie die Leistung durch eine geeignete Verwendung und Verwaltung von Indizes beeinflußt werden kann:
Bei der Erstellung von Indizes für umfangreiche Tabellen und unter Verwendung einer SMP-Maschine sollten Sie in Betracht ziehen, den Parameter intra_parallel auf 1 (YES) bzw. -1 (SYSTEM) zu setzen, um die Leistungsvorteile der Parallelverarbeitung zu nutzen.
Zum Suchen und Sortieren von Daten können mehrere Prozessoren verwendet werden. Der einzige Fall, in dem die Verwendung mehrerer Prozessoren während der Indexerstellung nicht von Vorteil ist, liegt vor, wenn der Konfigurationsparameter indexsort der Datenbank den Wert NO (inaktiv) hat. (Der Standardwert ist YES.) Mit diesem Parameter wird gesteuert, ob die Sortierung von Indexschlüsseln während der Indexerstellung erfolgt oder nicht.
Indizes können in einem anderen Tabellenbereich als dem, der zur Speicherung der anderen Tabellendaten verwendet wird, gespeichert werden. Dadurch kann Plattenspeicher eventuell besser genutzt werden, indem die Bewegungen der Schreib-/Leseköpfe reduziert werden. Sie können Indextabellenbereiche auch so erstellen, daß die Indizes auf schnelleren physischen Einheiten gespeichert werden.
Einem Indextabellenbereich kann außerdem ein getrennter Pufferpool zugeordnet werden, wodurch verhindert werden kann, daß die Indexseiten durch Einlesen zahlreicher Datenseiten aus dem Puffer verdrängt werden.
Wenn Indizes nicht in getrennten Tabellenbereichen gespeichert werden, verwenden sowohl die Datenseiten als auch die Indexseiten dieselben Werte für EXTENTSIZE und PREFETCHSIZE. Wenn Sie für Indizes einen anderen Tabellenbereich verwenden, haben Sie die Möglichkeit, verschiedene Werte für alle Merkmale eines Tabellenbereichs zu wählen. Da Indizes in der Regel kleiner als Tabellen sind und sich über weniger Behälter erstrecken, können in der Regel auch kleinere Werte für EXTENTSIZE (z. B. 8 und 16) verwendet werden. Weitere Informationen finden Sie in Vorablesezugriff auf Indexseiten. Die Verwendung schnellerer Einheiten für Tabellenbereiche wird vom SQL-Optimierungsprogramm berücksichtigt, wie im Abschnitt Auswirkung des Tabellenbereichs auf die Abfrageoptimierung beschrieben ist. Im Band Systemverwaltung: Konzept finden Sie weitere Informationen zu Tabellenbereichen.
Wenn eine SQL-Anweisung eine Sortierung erfordert (z. B. durch die Klauseln ORDER BY, GROUP BY, DISTINCT) und ein geeigneter Index zur Erfüllung der Reihenfolge existiert, kann es dennoch Fälle geben, in denen der Datenbankmanager den Index nicht verwendet. Solche Fälle liegen zum Beispiel vor:
Es wird empfohlen, REORG oder eine Sortierung und LOAD nach der Erstellung eines Clusterungsindex auszuführen. Im allgemeinen kann eine Tabelle nur in bezug auf einen einzigen Index geclustert werden. Ihre Tabellen und Indizes sollten in der Reihenfolge des Clusterungsindex für diese Tabelle generiert werden. Ein Clusterungsindex versucht, eine bestimmte Reihenfolge der Daten zu erhalten, wodurch die vom Dienstprogramm RUNSTATS gesammelten statistischen Werte für CLUSTERRATIO bzw. CLUSTERFACTOR verbessert werden.
Sie sollten auch die Verwendung des Schlüsselworts PCTFREE beim Ändern einer Tabelle in Betracht ziehen, bevor Sie die Tabelle laden oder reorganisieren. Damit die Clusterung beibehalten werden kann, benötigt jede Tabelle für weitere Einfügungen verfügbaren Speicherbereich auf jeder Datenseite. Wenn der Speicherbereich verfügbar ist, können weitere Einfügungen mit den vorhandenen Daten geclustert werden. Infolgedessen ist es sinnvoll, die Daten erst dann in die Tabelle zu laden, wenn ein Prozentsatz an freiem Speicherbereich auf jeder Seite zur Clusterung weiterer Daten reserviert wurde. Sie können dies erreichen, indem Sie zuerst die Tabelle erstellen und anschließend die Tabelle mit dem Parameter PCTFREE ändern (ALTER TABLE). In ähnlicher Weise sollten Sie auch eine Änderung der Tabelle mit dem Parameter PCTFREE in Betracht ziehen, bevor Sie Ihre Daten reorganisieren. Andernfalls geht durch die Reorganisation jeder reservierte Speicherbereich verloren, wenn PCTFREE nicht definiert wurde.
Die Clusterung wird zur Zeit bei Aktualisierungen nicht beibehalten. Das heißt, wenn jemand einen Datensatz aktualisiert (UPDATE), so daß sich der Schlüsselwert im Clusterungsindex ändert, wird dieser Datensatz nicht unbedingt auf eine entsprechend andere Seite versetzt, um die Clusterungsreihenfolge zu erhalten. Zum Erhalt der Clusterung können anstelle der Anweisung UPDATE die Anweisung DELETE und anschließend INSERT verwendet werden.
Nach der Erstellung eines neuen Index sollten Sie das Dienstprogramm RUNSTATS ausführen, um Indexstatistikdaten zu sammeln. Anhand dieser Statistikdaten kann das Optimierungsprogramm bestimmen, ob durch die Verwendung des Index die Zugriffsleistung verbessert werden kann. Weitere Informationen finden Sie in Erfassen statistischer Daten mit dem Dienstprogramm RUNSTATS.
Um optimale Leistungsvorteile aus den Indizes zu ziehen, sollten Sie eine regelmäßige Reorganisation der Indizes in Betracht ziehen. Durch Aktualisierungen an den Tabellen kann der Vorablesezugriff auf Indexseiten an Effektivität verlieren. Zur Erhaltung der Effektivität des Vorablesezugriffs auf Indexseiten müssen Sie den Index reorganisieren.
Sie können den Index entweder durch Löschen und Neuerstellen oder mit Hilfe des Dienstprogramms REORG reorganisieren. Weitere Informationen finden Sie in Reorganisieren von Katalogen und Benutzertabellen.
Wenn häufige Reorganisationen vermieden werden sollen, können Sie den Parameter PCTFREE beim Erstellen eines Index angeben. Das Angeben des Parameters PCTFREE bei der Indexerstellung bewirkt, daß auf jeder äußeren Seite (Blattseite) des Index bei ihrer Erstellung freier Speicherbereich verfügbar gehalten wird. Dadurch können bei zukünftigen Aktivitäten mit dem Index Datensätze in den Index mit geringerer Wahrscheinlichkeit, Indexseitentrennungen zu verursachen, eingefügt werden. Indexseitentrennungen bewirken, daß Indexseiten nicht mehr direkt aufeinanderfolgen oder sequentiell sind. Dadurch vermindert sich die Möglichkeit, einen Vorablesezugriff auf Indexseiten durchzuführen. Bei einem geeigneten Wert für PCTFREE für einen Index lassen sich Indexreoganisierungen vermeiden bzw. ihre Häufigkeit herabsetzen.
Anmerkung: | Der Wert für PCTFREE, der bei der Erstellung des Index angegeben wurde, wird während der Reorganisation bei Neuerstellung des Index verwendet. |
Durch Löschen und erneutes Erstellen des Index wird eine neue Menge von Seiten erstellt, die in etwa zusammenhängend und sequentiell sind. Dadurch verbessert sich der Vorablesezugriff auf die Indexseiten, wenn er verwendet wird.
Obwohl das Dienstprogramm REORG bei der Ausführung mehr Aufwand verursacht, stellt es sicher, daß die Datenseiten geclustert werden. Diese Clusterbildung bietet größere Vorteile bei Indexsuchen, durch die auf eine bedeutende Anzahl von Datenseiten zugegriffen wird.
Bei der Ausführung in einer symmetrischen Mehrprozessorumgebung (SMP-Umgebung) nutzt das Dienstprogramm REORG mehrere Prozessoren, wenn der Parameter intra_parallel auf YES oder ANY gesetzt ist.
Führen Sie in regelmäßigen Abständen EXPLAIN für Ihre am häufigsten verwendeten Abfragen aus, und überprüfen Sie, ob jeder Ihrer Indizes wenigstens einmal verwendet wird. Wenn ein Index in keiner Abfrage verwendet wird, empfiehlt es sich, diesen Index zu löschen.
Verwenden Sie EXPLAIN auch, um festzustellen, ob Tabellensuchen für große Tabellen als innere Tabellen von Verknüpfungen mit Verschachtelungsschleife verarbeitet werden. Wäre dies der Fall, würde dies bedeuten, daß ein Index für die Spalte im Verknüpfungsvergleichselement entweder fehlt oder bei der Anwendung des Verknüpfungsvergleichselements als nicht effizient eingestuft wird. Eine weitere Möglichkeit wäre, daß vielleicht das Verknüpfungsvergleichselement fehlt.
Eine flüchtige Tabelle ist als Tabelle definiert, deren Inhalt zwischen leer und sehr umfangreich schwanken kann. Die Erstellung eines Zugriffsplans, der mit einer flüchtigen Tabelle arbeitet, kann dazu führen, daß das Optimierungsprogramm für den Zugriff auf die flüchtige Tabelle die Verwendung einer Tabellensuche einer Indexsuche vorzieht.
Durch die Deklaration einer Tabelle als "flüchtig" in der Anweisung ALTER TABLE...VOLATILE kann das Optimierungsprogramm in die Lage versetzt werden, eine Indexsuche auf der flüchtigen Tabelle auszuführen. Weitere Informationen zu diesem Thema finden Sie im Band Systemverwaltung: Konzept oder im Handbuch SQL Reference.