Ein Index ist eine Liste der Speicherpositionen von Zeilen, die nach dem Inhalt einer oder mehrerer angegebener Spalten sortiert sind. In der Regel dienen Indizes zur Beschleunigung des Zugriffs auf eine Tabelle. Sie können aber auch Zwecke des logischen Datenentwurfs erfüllen. Zum Beispiel läßt ein eindeutiger Index nicht zu, daß in die Spalten ein Wert mehrfach eingegeben wird, wodurch gewährleistet wird, daß nicht zwei Zeilen in einer Tabelle identisch sind. Indizes können auch erstellt werden, um eine aufsteigende oder absteigende Reihenfolge der Werte in einer Spalte anzugeben.
Eine Indexerweiterung ist ein Indexobjekt, das bei Indizes verwendet wird, die über Spalten für strukturierte oder einzigartige Datentypen verfügen.
Eine Indexspezifikation ist ein Konstrukt aus Metadaten. Sie teilt dem Optimierungsprogramm mit, daß ein Index für ein Datenquellenobjekt (Tabelle oder Sicht) vorhanden ist, auf das ein Kurzname verweist. Eine Indexspezifikation enthält keine Listen mit Zeilenpositionen, sie ist nur eine Beschreibung für einen Index. Das Optimierungsprogramm verwendet die Indexspezifikation dazu, den Zugriff auf das Objekt zu verbessern, auf das der Kurzname verweist. Bei der Ersterstellung eines Kurznamens wird eine Indexspezifikation generiert, wenn für die zugrundeliegende Tabelle ein Index in einem für DB2 erkennbaren Format in der Datenquelle vorhanden ist.
Anmerkung: | Erstellen Sie, falls erforderlich, Indexspezifikationen zu Tabellenkurznamen oder Sichtkurznamen, wenn sich die Sicht auf nur eine Tabelle bezieht. |
Erstellen Sie in folgenden Situationen manuell einen Index oder eine Indexspezifikation:
Indexspezifikationen können erstellt werden, wenn kein Index für die Basistabelle vorhanden ist (DB2 sucht nicht nach dem fernen Index, wenn Sie die Anweisung CREATE INDEX ausführen). Eine Indexspezifikation erzwingt auch dann nicht die Eindeutigkeit der Zeilen, wenn das Schüsselwort UNIQUE angegeben wird.
DB2 Index Advisor ist ein Assistent, der Sie bei der Auswahl einer optimalen Gruppe von Indizes unterstützt. Auf diesen Assistenten kann über die Steuerzentrale zugegriffen werden. Das vergleichbare Dienstprogramm heißt db2advis.
Ein Index wird mit Hilfe von Spalten der Basistabelle definiert. Er kann vom Ersteller einer Tabelle oder von einem Benutzer, der weiß, daß für bestimmte Spalten ein direkter Zugriff erforderlich ist, definiert werden. Ein Primärindexschlüssel wird automatisch anhand des Primärschlüssels erstellt, sofern kein benutzerdefinierter Index bereits existiert.
Für eine bestimmte Basistabelle kann eine beliebige Anzahl von Indizes definiert werden, und diese Indizes können sich positiv auf die Verarbeitung von Abfragen auswirken. Je mehr Indizes jedoch vorhanden sind, desto mehr Änderungen muß der Datenbankmanager während der Aktualisierungs-, Einfüge- und Löschoperationen durchführen. Daher kann die Erstellung einer großen Anzahl von Indizes für eine Tabelle, die häufig aktualisiert wird, die Verarbeitung von Anforderungen verlangsamen. Die Verwendung von Indizes ist also nur dann sinnvoll, wenn sich klare Vorteile für den häufigen Zugriff ergeben.
Alle Spalten, die Teil eines Indexschlüssels sind, sind auf 255 Byte begrenzt. Die maximale Anzahl der Spalten in einem Index beträgt 16. Beim Indexieren einer typisierten Tabelle beträgt die maximale Anzahl an Spalten 15. Die maximale Länge eines Indexschlüssels beträgt 1024 Byte. Wie zuvor erwähnt, kann es durch viele Indexschlüssel in einer Tabelle zu einer Verlangsamung der Anforderungsverarbeitung kommen. Durch umfangreiche Indexschlüssel kann es ebenfalls zur Reduzierung der Verarbeitungsgeschwindigkeit bei Anforderungen kommen.
Ein Indexschlüssel ist eine Spalte oder eine Gruppe von Spalten, mit denen ein Index definiert wird. Die Zweckmäßigkeit eines Index hängt von seinem Schlüssel ab. Obwohl die Reihenfolge der Spalten, die einen Indexschlüssel bilden, bei der Erstellung des Indexschlüssel keine Rolle spielt, kann sie für das Optimierungsprogramm bei der Entscheidung von Bedeutung sein, ob ein Index verwendet werden soll oder nicht.
Wenn die Tabelle, für die ein Index erstellt wird, leer ist, wird der Index zwar erstellt, aber es werden erst Indexeinträge erstellt, wenn die Tabelle geladen oder Zeilen eingefügt werden. Ist die Tabelle nicht leer, erstellt der Datenbankmanager die Indexeinträge während der Verarbeitung der Anweisung CREATE INDEX.
Bei einem Clustering-Index werden neue Zeilen physisch nahe bei vorhandenen Zeilen mit ähnlichen Schlüsselwerten eingefügt. Dies führt zu einer Leistungssteigerung während der Ausführung von Abfragen, weil sich eine linearere Zugriffsweise auf die Daten und ein effektiverer Vorablesezugriff ergeben.
Wenn Sie einen Primärschlüsselindex als Clustering-Index haben wollen, sollte der Primärschlüssel nicht bei der Erstellung mit CREATE TABLE angegeben werden. Wenn der Primärschlüssel einmal erstellt ist, kann der zugehörige Index nicht geändert werden. Führen Sie statt dessen die Anweisung CREATE TABLE ohne Primärschlüsselklausel (Primary Key) aus. Führen Sie anschließend die Anweisung CREATE INDEX aus, in der Sie die Clustering-Attribute angeben. Verwenden Sie schließlich die Anweisung ALTER TABLE, um einen Primärschlüssel hinzuzufügen, der dem gerade erstellten Index entspricht. Dieser Index wird dann als der Primärschlüsselindex verwendet.
Im allgemeinen bleibt die Clusterbildung effektiver erhalten, wenn der Clustering-Index eindeutig ist.
Spaltendaten, die nicht als Teil des eindeutigen Indexschlüssels, aber im Index gespeichert/gepflegt werden, werden als INCLUDE-Spalten bezeichnet. INCLUDE-Spalten können nur für eindeutige Indizes angegeben werden. Bei der Erstellung eines Index mit INCLUDE-Spalten werden nur die eindeutigen Schlüsselspalten sortiert und im Hinblick auf Eindeutigkeit berücksichtigt. Die Verwendung von INCLUDE-Spalten verbessert die Leistung beim Abrufen von Daten, wenn es darum geht, ob ein reiner Indexzugriff erfolgen kann.
Der Datenbankmanager verwendet zum Speichern von Indizes eine B+-Baumstruktur, deren unterste Ebene aus Blattknoten besteht. Auf den Blattknoten oder -seiten sind die eigentlichen Indexschlüsselwerte gespeichert. Bei der Indexerstellung können Sie festlegen, daß diese Indexseiten online zusammengefügt oder reorganisiert werden. Durch diese Online-Reorganisation des Index wird verhindert, daß nach zahlreichen Lösch- und Aktualisierungsvorgängen auf vielen Blattseiten eines Index nur noch wenige Indexschlüssel vorhanden sind. In einem solchen Fall könnte ohne die Online-Reorganisation der freie Speicherplatz nur durch eine Offline-Reorganisation der Daten und des Index zurückgewonnen werden. Bei der Entscheidung, ob ein Index so erstellt werden soll, daß das Reorganisieren der Indexseiten im Online-Modus möglich ist, sollte die folgende Frage berücksichtigt werden. Übersteigen die zusätzlichen Leistungsaufwände für die Prüfung auf Speicherplatz zur Ausführung von Mischoperationen beim Löschen von Schlüsseln und die tatsächlichen Aufwände zum Abschluß der Mischoperation (wenn genügend Speicherplatz verfügbar ist) die Vorteile der besseren Speicherplatznutzung für den Index. Sind diese Aufwände andererseits geringer als die reduzierten Anforderungen für die Offline-Reorganisation, um diesen Speicherplatz wieder zurückzufordern?
Anmerkung: | Die durch eine Online-Reorganisation zurückgewonnenen Seiten können nur für andere Indizes in der gleichen Tabelle verwendet werden. Bei einer umfassenden Reorganisation stehen die zurückgewonnenen Seiten für andere Objekte zur Verfügung (bei Speicherplatzverwaltung durch die Datenbank) oder als Plattenspeicherplatz (bei Speicherplatzverwaltung durch das System). Außerdem werden durch die Online-Reorganisation keine Nichtblattseiten des Index zurückgewonnen, während die umfassende Reorganisation den Index auf Minimalgröße bringt, indem Nichtblattseiten und Blattseiten sowie die Anzahl der Indexstufen reduziert werden. |
Weitere Informationen zum Implementieren eines Index mit Online-Reorganisation finden Sie in Verwenden der Anweisung CREATE INDEX.
Indizes für Tabellen in einer partitionierten Datenbank werden mit Hilfe derselben Anweisung CREATE INDEX erstellt. Sie werden anhand des Partitionierungsschlüssels der Tabelle partitioniert. Ein Index für eine Tabelle besteht aus den lokalen Indizes für die Tabelle auf dem jeweiligen Knoten in der Knotengruppe. Beachten Sie, daß in einer Mehrpartitionsumgebung der Indexschlüssel definierter eindeutiger Indizes eine Obermenge des Partitionierungsschlüssels sein muß.
Hinweis zur Leistung: Erstellen Sie die Indizes, bevor Sie das Dienstprogramm LOAD verwenden, wenn Sie die folgende Reihe von Operationen durchführen wollen:
Sie sollten die folgende Reihenfolge zur Ausführung der Operationen in Betracht ziehen:
Weitere Informationen zur Optimierung der Leistung von LOAD finden Sie in Systemkatalogtabellen.
Indizes werden nach ihrer Erstellung gepflegt. Wenn in der Folge ein Anwendungsprogramm Zeilen in einer Tabelle aufgrund eines Schlüsselwerts ermittelt und verarbeitet, kann ein Index, der auf diesem Schlüssel basiert, verwendet werden, um direkt auf diese Zeile zuzugreifen. Dies ist wichtig, da die physische Speicherung der Zeilen einer Basistabelle nicht geordnet erfolgt. Wenn eine Zeile eingefügt wird, wird sie an der bequemsten passenden Speicherposition untergebracht, sofern nicht ein Clustering-Index definiert wurde. Wenn nach Zeilen einer Tabelle, die bestimmte Auswahlkriterien erfüllen sollen, gesucht wird und die Tabelle keinen Index hat, wird immer die gesamte Tabelle durchsucht. Ein Index optimiert dieses Abrufen von Daten, ohne daß langwierige sequentielle Suchoperationen durchgeführt werden müssen.
Die Daten für die Indizes können im selben Tabellenbereich wie die Tabellendaten oder in einem getrennten Tabellenbereich, der nur Indexdaten enthält, gespeichert werden. Der Tabellenbereich, der zur Speicherung der Indexdaten verwendet wird, wird bei der Erstellung der Tabelle (siehe Erstellen einer Tabelle in mehreren Tabellenbereichen) festgelegt.
Gehen Sie wie folgt vor, um mit der Steuerzentrale einen Index zu
erstellen:
|
Geben Sie in der Befehlszeile folgendes ein, um einen Index zu erstellen:
CREATE INDEX <name> ON <tabellenname> (<spaltenname>)
Die beiden folgenden Abschnitte Verwenden eines Index und Verwenden der Anweisung CREATE INDEX enthalten weitere Informationen zur Indexerstellung.
Ein Index wird nie direkt von einem Anwendungsprogramm verwendet. Die Entscheidung, ob ein Index verwendet wird und welcher der potentiell verfügbaren Indizes verwendet wird, liegt in der Zuständigkeit des Optimierungsprogramms.
Der beste Index für eine Tabelle hat folgende Merkmale:
Eine detaillierte Erläuterung zu den Vorteilen von Indizes finden Sie in Konzepte der Indexsuche.
Sie können einen Index erstellen, der mehrfach auftretende Werte zuläßt (nichteindeutiger Index), um ein effizientes Abrufen über Spalten, die nicht zum Primärschlüssel gehören, zu ermöglichen, und es also erlaubt, daß doppelte Werte in der Indexspalte oder den Indexspalten vorhanden sind.
Mit der folgenden SQL-Anweisung wird ein nichteindeutiger Index namens LNAME für die Spalte LASTNAME der Tabelle LASTNAME erstellt und in aufsteigender Reihenfolge sortiert:
CREATE INDEX LNAME ON EMPLOYEE (LASTNAME ASC)
Mit der folgenden SQL-Anweisung wird ein eindeutiger Index für die Spalte mit den Telefonnummern erstellt:
CREATE UNIQUE INDEX PH ON EMPLOYEE (PHONENO DESC)
Ein eindeutiger Index stellt sicher, daß nicht zwei identische Werte in der Indexspalte oder den Indexspalten auftreten. Diese Integritätsbedingung wird am Ende der SQL-Anweisung, mit der Zeilen aktualisiert oder neue Zeilen eingefügt werden, in Kraft gesetzt. Diese Art von Index kann nicht erstellt werden, wenn die Gruppe aus einer oder mehrerer Spalten bereits doppelte Werte enthält.
Das Schlüsselwort ASC ordnet die Indexeinträge in aufsteigender Reihenfolge gemäß der Spaltenwerte an, während das Schlüsselwort DESC die Werte in absteigender Reihenfolge anordnet. Standardmäßig wird in aufsteigender Reihenfolge sortiert.
Beim Arbeiten mit einem strukturierten Typ kann es erforderlich sein, benutzerdefinierte Indexarten zu erstellen. Dazu ist eine Möglichkeit zum Definieren von Funktionen für Indexpflege, Indexsuche und Indexausnutzung erforderlich. Im Handbuch SQL Reference finden Sie Informationen zu den Voraussetzungen zum Erstellen einer Indexart.
Mit der folgenden SQL-Anweisung wird ein Clustering-Index namens INDEX1 für die Spalte LASTNAME der Tabelle EMPLOYEE erstellt:
CREATE INDEX INDEX1 ON EMPLOYEE (LASTNAME) CLUSTER
Verwenden Sie für größte Effektivität Clustering-Indizes mit dem Parameter PCTFREE der Anweisung ALTER TABLE, so daß neue Daten auf den richtigen Seiten eingefügt werden können und dadurch die Clustering-Reihenfolge erhalten bleibt. Je höher das Aufkommen an INSERT-Aktivitäten für die Tabelle ist, desto größer muß der PCTFREE-Wert (in der Tabelle) sein, der zur Erhaltung des Clusterings benötigt wird. Da dieser Index die Reihenfolge bestimmt, nach der die Daten auf den physischen Seiten gespeichert werden, kann für jede Tabelle nur ein Clustering-Index definiert werden.
Wenn andererseits die Indexschlüsselwerte dieser neuen Zeilen zum Beispiel stets neue HIGHKEY-Werte (d. h. neue Höchstwerte) sind, versucht das CLUSTERING-Attribut der Tabelle, diese Zeilen an das Ende der Tabelle zu setzen. In diesem Fall sind freie Speicherbereiche auf anderen Seiten der Erhaltung des Clusterings wenig dienlich. Daher ist es in einem solchen Fall besser, die Tabelle in den Anfügemodus (Append) zu setzen, als einen Clustering-Index zu haben und die Tabelle zum Erzielen eines großen Werts für PCTFREE zu ändern. Mit dem folgenden Befehl können Sie die Tabelle in den Anhängemodus versetzen: ALTER TABLE APPEND ON. Zusätzliche Übersichtsinformationen zu ALTER TABLE finden Sie in Ändern von Tabellenattributen. Zusätzliche detaillierte Informationen zu ALTER TABLE finden Sie im Handbuch SQL Reference.
Die obige Darstellung gilt auf für neue "Überlaufzeilen", die aus UPDATE-Operationen resultieren, die eine Zeile vergrößern.
Mit der Klausel MINPCTUSED der Anweisung CREATE INDEX wird die Schwelle für die Mindestgröße des genutzten Speicherbereichs auf einer Indexseite angegeben. Bei Verwendung dieser Klausel wird die Online-Reorganisation für diesen Index aktiviert. In diesem Fall wird anhand der folgenden Kriterien entschieden, wann die Online-Reorganisation stattfindet: Wenn nach dem Löschen eines Schlüssels auf einer Indexblattseite ein Prozentwert für benutzten Speicherplatz auf der Seite unter den angegebenen Schwellenwert absinkt, werden die angrenzenden Blattseiten daraufhin überprüft, ob die Schlüssel beider Blattseiten zu einer Indexblattseite zusammengefaßt werden können.
Mit der folgenden SQL-Anweisung wird ein Index mit aktivierter Online-Reorganisation erstellt:
CREATE INDEX LASTN ON EMPLOYEE (LASTNAME) MINPCTUSED=20
Wenn ein Schlüssel aus diesem Index gelöscht wird und die verbleibenden Schlüssel auf dieser Indexseite 20 Prozent oder weniger der Indexseite belegen, wird versucht, eine Indexseite frei zu machen, indem die Schlüssel dieser Indexseite mit den Schlüsseln der angrenzenden Indexseite zusammengeführt werden. Wenn die zusammengeführten Schlüssel auf eine einzige Seite passen, wird die Reorganisation durchgeführt und eine der beiden Indexseiten gelöscht.
Die Klausel PCTFREE der Anweisung CREATE INDEX gibt den Prozentsatz jeder Indexseite an, die beim Erstellen des Index als Speicherbereich frei gelassen werden soll. Je mehr Speicherbereich Sie auf den Indexseiten frei lassen, desto weniger Seitenteilungen werden erforderlich. Dadurch brauchen Sie die Tabelle nicht mehr zu reorganisieren, um sequentielle Indexseiten wiederzugewinnen, was den Vorablesezugriff (Prefetching) erhöht. Vorablesezugriff ist eine wichtige Komponente, die zu Leistungssteigerung führen kann. Wenn Sie immer mit HIGHKEY-Werten zu tun haben, erwägen Sie das Senken des Werts für die Klausel PCTFREE der Anweisung CREATE INDEX. Auf diese Art wird auf jeder Indexseite so wenig Speicherbereich verschwendet wie möglich.
In Mehrpartitionsumgebungen müssen eindeutige Indizes als Obermenge des Partitionierungsschlüssels definiert werden.
Wenn Sie eine replizierte Übersichtstabelle haben, muß bzw. müssen ihre Basistabelle(n) über einen eindeutigen Index verfügen, und die Spalten des Indexschlüssels müssen in der Abfrage verwendet werden, die die replizierte Übersichtstabelle definiert. Weitere Informationen finden Sie in Replizierte Übersichtstabellen.
Bei partitionsinterner Parallelität kann die Leistung von index create verbessert werden, indem mehrere Prozessoren zum Suchen und Sortieren von Daten während der Indexerstellung verwendet werden. Die Verwendung mehrerer Prozessoren wird durch Setzen des Parameter intra_parallel auf YES(1) oder ANY(-1) aktiviert. Die Anzahl von Prozessoren, die während index create verwendet werden, wird vom System festgelegt und wird von den Konfigurationsparametern dft_degree oder max_querydegree, vom Grad der Parallelität von Anwendungen zur Laufzeit oder vom angegebenen Grad der Parallelität bei der Kompilierung der SQL-Anweisung nicht beeinflußt. Wenn der Datenbankkonfigurationsparameter index sort den Wert NO hat, verwendet index create nicht mehrere Prozessoren.