Index/Indizes
<< Firebird 3.0 Packages | IBExpert | Firebird 3.0 Stored Funktionen >>
Die deutschsprachige Dokumentation wird seit dem 26. Juli 2016 nicht mehr gepflegt. Aktuelle und vollständige Dokumentation finden Sie auf der englischsprachigen Webseite: IBExpert Documentation
Index/Indizes
Ein Index kann verglichen werden mit einem Buchindex, der eine schnelle Suche ermöglicht.
Indizes sind eine sortierte Zeigerliste in einer Tabelle und dienen der Beschleunigung des Datenzugriffs. Sie können am Besten beschrieben werden, als alphabetisches Verzeichnis mit internen Zeigern, wo was zu finden ist. Wenn das indexierte Feld eindeutig (unique) ist, gibt es nur einen Zeiger.
Ein Index kann aufsteigend oder absteigend ein und kann bei Bedarf auch als unique definiert werden.
Indizes sollten nicht mit Schlüsseln verwechselt werden. In dem relationalen Modell, wird ein Schlüssel verwendet, um Daten logisch zu organisieren, sodass spezifische Zeilen identifiziert werden können. Ein Index ist jedoch Teil der physischen On-Disk Tabellenstruktur und wird zur Leistungssteigerung der Tabellen während der Abfraggen genutzt. Indizes sind daher nicht Teil des relationalen Modells. Trotzdem sind Indizes extrem wichtig für relationale Datenbanksysteme.
Für Spalten, die mit einem Primärschlüssel oder einem Fremdschlüssel in einer Tabelle definiert sind, generiert InterBase/Firebird automatisch einen entsprechenden aufsteigenden Index und erfüllt somit die Eindeutigkeitsbeschränkung, die von einem relationalen Modell gefordert wird.
Ein Index kann im IBExpert Indexeditor definiert werden:
oder im IBExpert Tabelleneditor (beide Editoren werden aus dem DB Explorer geöffnet):
Die Abhängigkeiten-Seite zeigt eventuell vorhandene Index-Abhängigkeiten an.
Die maximale Beschränkung auf 65 Indizes pro Tabelle wurde in der Firebird Version 1.0.3 entfernt und auf einem höheren Niveau von 257 in der Firebird Version 1.5 wiedereingeführt und erneut in der Firebird Version 2.0 entfernt. Obwohl es keine starre Begrenzung mehr nach oben gibt, ist die in der Praxis erreichbare Zahl immer noch durch die Datenbankseitengröße und die Anzahl der Spalten pro Index beschränkt (weiteres finden Sie im Kapitel Firebird 2.0 Language Reference Update, Maximum number of indices in different Firebird versions). Beachten Sie jedoch bitte, das unter normalen Umständen schon 50 Indizes bei weitem zu viel sind und die Bearbeitungsleistung erheblich herabsetzt. Die maximale Obergrenze wurde angehoben, um Daten-Depotanwendungen und ähnliche Anwednungen, die viele Massenoperationen durchführen, währenddessen die Indizes vorübergehend ausgeschaltet werden.
Wenn Sie sich versichern wollen, wie viele Indizes bereits in einzelne Tabellen in einer Datenbank vorhanden sind, führen Sie folgende Abfrage aus der Systemtabelle RDB$INDICES aus:
select * from RDB$INDICES where RDB$INDICES.RDB$RELATION_NAME='MYTABLE'
oder sehen Sie sich die Indexliste unter der Verknüpfung Indizes im DB Explorer an.
Systemtabellen und Indizes können im IBExpert DB Explorer durch Aktivierung der Optionen Zeige Systemtabellen und Zeige Systemindizes eingesehen werden, zu finden in der Datenbankregistrierungsinfo auf der Seite Zusatzeinstellungen.
In der Version Firebird 2.0 wurde das Indexieren auf Expressions eingeführt und die maximale Länge von Indexschlüsseln von 252 Bytes auf 1/4 der Seitengröße erhöht, d.h. es variiert zwischen 256 und 4096. Die maximale indexierbare Stringlänge in Bytes ist geringer, als die Schlüssellänge.
Indizes werden jedes Mal aktualisiert, wenn ein neuer Datensatz eingeben wird, oder vielmehr, wenn ein idexreferenziertes Feld aktualisiert wird. Firebird/InterBase® schreibt eine zusätzliche zweite Miniversion des Datensatzes in jede Indextabelle.
Ein Index hat eine Sequenz, wenn zum Beispiel ein aufsteigender Index einem Feld zugeschrieben wird Feld (Standard) und ein absteigendender select auf diesem Feld abgefragt wird. Firebird/InterBase® verwendet beim Sortieren nicht die aufsteigenden Indizes. Hierfür muss ein zweiter, absteigender Index, für dasselbe Feld festgelegt werden.
Ein Index kann beliebig benannt werden; es können sogar fortlaufende Nummern verwendet werden, da ein Index nur extrem selten in SQL benannt wird.
Ein Index in zwei Feldern gleichzeitig macht nur Sinn, wenn beide Felder durch ORDER BY sortiert werden sollen und das sollte nur bei einer relativ kleinen Ergebnismenge erfolgen.
Firebird/InterBase® entscheidet automatisch, welcher Index verwendet wird, um eine SELECT-Abfrage durchzuführen. Auf der Seite Tabelleneditor / Indizes unter Statistiken kann man sehen, dass der Index mit dem niedrigsten Wert eine höhere Eindeutigkeit hat und daher von Firebird/InterBase® anderen Indizes mit einem niedrigeren Grad an Eindeutigkeit vorgezogen wird. Diese Eigenschaft nennt man Selektivität.
Ein Index sollte nur in Feldern verwendet werden, die wirklich häufig als Sortierkriterium genutzt werden (z.B. sind Felder, wie STREET und MALE/FEMALE im Allgemeinen unwichtig) oder in einer WHERE-Bedingung. Wenn ein Feld oft als Sortierkriterium genutzt wird, sollte die Verwendung eines absteigenden Indexes ebenfalls in Erwägung gezogen werden, z.B. besonders in DATE oder TIMESTAMP Feldern. Es sollte auch darauf geachtet werden, dass indexierte CHAR-Felder nicht länger als ca. 80 Zeichen sind (in der Version Firebird 1.5 ist das Limit um einiges höher).
Indizes können immer, je nach Leistungsanforderung gesetzt werden, wenn die Datenbank tatsächlich in Gebrauch ist. Weitere Details und Beispiele finden Sie unter Leistungsanalyse.
Indexstatistiken und Indexselektivität
Wenn eine Abfrage an den Server geschickt wird, weiß der Optimierer anfänglich nicht, wie dieser verarbeitet werden soll. Er braucht weitere Informationen, um zu entscheiden, wie die Abfrage ausgeführt werden muss. Zu diesem Zweck werden Indizes verwendet und, um zu entscheiden, welcher Index am Besten zuerst verwendet wird, stützt sich der Optimierer auf die Indexselektivität. Die Selektivität eines Indizes ist der beste Hinweis darauf, ob der Abfrageplan einen bestimmten Index verwenden sollte, oder nicht. Wenn mehr als ein Index zur Verfügung steht, hilft dies dem Firebird Server zu entscheiden, welcher Index zuerst verwendet werden sollte. Eine gute Selektivität liegt nache 0 - es ist das Ergebnis aus : 1/eindeutiger Wert.
Also ist das Erste, was ein Optimierer macht, wenn er eine Abfrage erhält, die Ausfürhung vorzubereiten. Er trifft Entscheidungen bezüglich der Indizes einzig auf Grund deren Selektivität. Obwohl der Optimierer nur Indizes mit einer Selektivität < 0.01 verwendet, wenn keine entsprechend anderen zur Verfügung stehen.
Wenn Sie einen Index auf einem Feld mit nur zwei untershiedlichen Werten haben (z.B. yes oder no) , ist die Selektivität 0.5. Wenn Ihr indexiertes Feld den Wert 10 hat, hat es eine Slektivität von 0.1. Je höher die Anzahl der verschiedenen Werte, desto niedriger ist die Selektivität und desto besser kann dieser Wert als Index verwendet werden. Ihr Benchmark ist immer Ihre ID - der Primärschlüssel, weil dieser immer einen absolut eindeutigen Wert enthält und daher die niedrigste Selektivität.
Unter dem Menüpunkt Datenbankstatistik im IBExpert Menü Systemdienste kann die Indexstatistik eingesehen werden.
Die Selektivität wird nur bei der Erzeugung berechnet, oder wenn der IBExpert Menüpunkt Selektivität aller Indizes neu berechnen oder Selektivität neu berechnen'' verwendet wird (dirket zu finden im Indexeditor, IBExpert Menü Systemdienste, Datenbankstatistik, im IBExpert Datenbankmenü, oder im Rechtsklickmenü des DB Explorers). Alternativ kann der Befehl
SET STATISTIC INDEX {INDEX_NAME}
im SQL Editor verwendet werden, um einzelne Indizes neu zu berechnen. Nur der Erzeuger eines Indizes kann SET STATISTICS verwenden.
Bitte beachten Sie: SET STATISTICS erneuert einen Index nicht wieder. Um einen Index zu erneuern, können Sie ALTER INDEX verwenden.
Die erneute Berechnung der Selektivität kann automatisiert werden, um effizienteste Verwendung der Indizes sicherzustellen. Weitere Infos hierzu finden Sie im Kapitel Firebird administration using IBExpert und Automating the recalculation of index statistics.
Dies wird automatisch während eines Datenbank Backups und Datenbank Restores gemacht, da nicht der Index, sondern seine Definition gesichert wird und somit der Index bei einem Restore der Datenbank rekonstruiert wird.
Der vom Firebird/InterBase® Optimierer verwendete SQL-Plan zeigt lediglich, wie der Server die Abfrage ausführen will.
Wenn der Entwickler die automatische Indexauswahl von Firebird/InterBase® überschreiben will und die Indexsuchsequenz selbst bestimmen möchte, muss dies in SQL definiert werden (siehe Using the PLAN operator).
Beispiel: ein Index wird in der EMPLOYEE Datenbank erzeugt:
CREATE INDEX EMPLOYEE_IDX1 ON EMPLOYEE(PHONE_EXT);
Dann:
SELECT * FROM EMPLOYEE
WHERE EMPLOYEE.PHONE_EXT='250'
PLAN (EMPLOYEE INDEX (EMPLOYEE_IDX1));
Jeder Index muss einzeln eingegeben und benannt werden.
Um einen Index aus dem Plan zu entfernen, kann +0 zu der Abfrage hinzugefügt werden in das Feld, wo Sie den Index ignoriert haben möchten, somit wird dem Optimierer die Möglichkeit genommen, den Index für diese spezielle Abfrage zu verwenden. Dies ist effektiver und flexibler, als den Index zu löschen, wodurch jede zukünftige Verwendung durch den Optimierer vermieden werden würde.
Indizes sollten mit Bedacht in einer Datenstruktur definiert werden, da nicht jeder Index automatisch zu einer Beschleunigung der Abfrageleistung führt. Wenn zum Beispiel in einer Tabelle eine Spalte nur Daten mit dem Wert 0 oder 1 enthält, kann ein Index die Gecshwindigkeit sogar herabsetzen. Eine komplexe Indexstruktur kann jedoch einen großen Einfluss haben, auf den Eingabe- und Änderungsprozess in einem langen Ablauf.
Weiteres finden Sie im Kapitel IBExpert Dokumentation Datenbankstatistik, dem Kapitel Firebird 2.0.4 Release Notes, Enhancements to indexing für Verbesserungen und neue Features in Firebird 2.0, im Firebird 2.1 Release Notes Kapitel, Indexing & optimizations und allgemeine Informationen zu Indizes finden Sie in folgenden Kapiteln.
Siehe auch:
deutschsprachig:
Indexeditor
Tabelleneditor / Index-Seite
SQL-Editor / Plananalyse
SQL-Editor / Leistungsanalyse
IBExpert Tabelleneditor / Indizes
Selektivität aller Indizes neu berechnen
englischsprachig:
Firebird administration using IBExpert: Index statistics
Firebird 2.0.4 Release Notes: Enhancements to Indexing
Firebird 2.1 Release Notes: Indexing & optimizations
CREATE INDEX
Firebird for the database expert: Episode 1 - Indexes
Recreating Indices 1
Recreating Indices 2
Aufsteigender Index
Ein sufsteigender Index sucht entsprechend einer aufsteigenden alphabetischen oder numerischen Sequenz, je nach definiertem Zeichensatz (oder, wenn kein Zeichensatz für das indexierte Feld definiert wurde, dem Standardzeichensatz).
Absteigender Index
Ein absteigender Index sucht entsprechend einer absteigenden aplphabetischen oder numerischen Sequenz, je nach definiertem Zeichensatz (oder, wenn kein Zeichensatz für das indexierte Feld definiert wurde, dem Standardzeichensatz).
UNIQUE Indizes lassen NULL zu
Seit Firebird 1.5 werden, in Anlehnung an den SQL-99 Standard, NULL – auch mehrfach – in Spalten, in denen ein UNIQUE-Index definiert ist, zugelassen. Soweit es die NULLs betrifft, sind die Regeln für eindeutige Indizes dieselben, wie für eindeutige Schlüssel.
Indexeditor
Traditionell werden Indizes einzeln für Tabellen im IBExpert Tabelleneditor erzeugt. Jedoch hat das Baumobjekt Indizes den Vorteil, das alle Indizes einer Datenbank darstellt, was Ihnen ermöglicht, schnell und sicher bestimmte Indizes zu aktivieren oder zu deaktivieren, ohne sich die Mühe machen zu müssen jeden Objekteditor zu öffnen und auf den einzelnen Indizes-Seiten suchen zu müssen.
Das Baumobjekt Index im DB Explorer stellt alle Indizes in einer Datenbank dar - sowohl die manuell erzeugten, als auch die Systemindizes. Systemindizes werden rot dargestellt, wenn die Systemoptionen im Datenbank registrieren-Dialog (zu öffnen per Rechtsklick auf Zusatzeinstellungen/DB Explorer). Firebird und InterBase® Systemindizes beginnen immer mit dem Präfix RDB$.
Neuer Index/Erzeuge Index
Ein neuer Index kann für eine verbundene Datenbank mit dem Rechtsklickmenüpunkt des DB Explorers Neuer Index (oder Tastaturkürzel [Strg + N] erzeugt werden, wenn das Baumobjekt Index oder einer der Indizes der enstprechenden Datenbank markiert wurde).
Der Dialog Add Index for erscheint.
Seite Indexfeld
Wählen Sie den Tabellenamen aus der Drop-Down-Liste der Tabelle, in der Sie einen Index setzen wollen, benennen Sie den Index und wählen Sie dan das Feld/ die Felder aus der Liste Available fields (Verfügbare Felder links, mit dem blauen Pfeil in der Mitte des Eingabefeldes, um das Feld auf die rechte Seite nach Included fields zu schieben. Legen Sie dann die Sortierfolge fest, aktivieren Sie die Checkbox Unique, falls benötigt und klicken Sie auf OK und abschließend auf Commit.
Seite Expression
Neu seit Firebird 2.0: Anstatt einer Spalte – oder einer Spaltenliste – können Sie jetzt auch ein COMPUTED BY Expression in eine Indexdefinition festlegen. Expression-Indizes werden in entsprechenden Abfragen verwendet, vorausgesetzt, dass der Expression in der WHERE, ORDER BY oder GROUP BY-Klausel exakt zu dem Expression in der Indexdefinition passt.
Weitere Infos und Beispiele finden Sie unter CREATE INDEX.
Seite Beschreibung
Wie die Mehrzahl der IBExpert Editoren, kann die Seite Beschreibung im Indexeditor zur Eingabe, zum Bearbeiten und Löschen von Texten verwendet werden, was eine einfache und schnelle Dokumentierung der Datenbank ermöglicht.
Vergessen Sie nicht, nach jeder Ergänzung oder Änderungen auf der Seite Indexeditor zu bestätigen und Commit zu klicken!
Wer Handcodierung bevorzugt, kann natürlich auch eigene Indizes im SQL Editor mit folgender Syntax erzeugen:
CREATE [UNIQUE] [ASC[ENDING] | [DESC[ENDING]] INDEX indexname
ON tablename
{ (colname [, colname ...]) | COMPUTED BY (expression) }
Bearbeite Index
Ein Index kann im Indexeditor durch Dopelklick auf den Indexnamen im DB Explorer bearbeitet werden. Alternativ können Sie im Rechtsklickmenü den Menüpunkt Bearbeite Index oder die Tastenkombination [Ctrl + O] verwenden.
Der Name eines Indizes kann nicht geändert werden. Wenn Sie einen Index umbenennen möchten, müssen Sie diesen löschen und neu erzeugen. Attribute, wie Fields included, unique, Sortiere und active können hinzugefügt, gelöscht oder geändert werden.
Ein Index sollte deaktiviert werden, wenn beispielsweise eine große Anzahl an Datensätzen hinzugefügt werden muss, da ein aktiver Index bei jeder Dateneingabe den Index neu berechnen würde. Durch Deaktivierung des Index und Reaktivierung des Index nach der Dateneingabe, wird der Index nur einmal neu berechnet.
Die Funktion Selektivität aller Indizes neu berechnen ermöglicht die schnelle und einfache Neuberechnung aller Indizes und maximiert somit die Leistungsfähigkeit aller vorgenommenen Änderungen. Dieses Feature ist detailliert im Kapitel Selektivität aller Indizes neu berechnen beschrieben.
Dies kann auch einfach direkt im Tabelleneditor / Seite Index erfolgen, durch Aktivierung oder Deaktivierung der entsprechenden Checkboxen in der Spalte Status und anschließendem Kompilieren mit dem entsprechenden Editorsymbol oder [Strg + F9], sowie anschließendem Commit.
Bei Verwendung von SQL ist es nur möglich, den ACTIVE/INACTIVE Status zu ändern.
Die SQL-Syntax lautet:
ALTER INDEX <index_name> ACTIVE | INACTIVE
Wenn Sie den IBExpert Dialog Compile beim Kompilieren Ihrer Indexänderung aufmerksam verfolgt haben, werden Sie bemerkt haben, dass zur Ausführung Ihrer gewünschten Änderungen, IExpert nichts anderes macht, als den Index zu löschen und entsprechend der definierten Eigenschaften neu zu erzeugen.
Wenn ein Index verwendet wird, hat ALTER INDEX keine Auswirkungen, bis der Index nicht mehr verwendet wird.
Ein Index kann von seinem Erzeuger, dem SYSDBA und jedem anderen Benutzer mit Grundrechten des Betriebssystems bearbeitet werden.
Lösche Index
Da Indizes einfach durch Anklicken der Checkbox deaktiviet werden können, ist es selten notwendig, einen Index zu löschen. can be quickly and simply deactivated by simply checking a box, it is hardly ever necessary to drop an index. Sollten Sie jedoch das Bedürfnis haben, Ihre Datenbank gründlich zu bereinigen, ist es durchaus möglich. Nur benutzerdefinierte Indizes können gelösct werden. Da bei Handcodierung die einzigen zugelassenen Änderungen die Aktivierung oder Deaktivierung eines Indizes sind müssen Indizes meistens gelöscht und dann wieder neu erzeugt werden, um bestimmte Indexinformationen, wie Indexspalten, Sortierung oder Eindeutigkeitsbeschränkungen zu ändern. In IBExpert können Änderungen einfach im Indexeditor ausgeführt werden.
Um einen Index zu löschen, können Sie im Rechtsklickmenü des DB Explorers den Menüpunkt Lösche Index oder [Strg + Entf] verwenden. IBExpert bittet um Bestätigung:
Alternativ können Sie im Tabelleneditor / Seite Index einfach den zu löschenden Index markieren und dann im Rechtsklickmenü den Menüpunkt Lösche Index <INDEXNAME> wählen oder die Taste [Entf] verwenden. Finally commit or roll back.
Die SQL-Syntax lautet:
DROP INDEX Index_Name
Ein Index wird nicht gelöscht, solange er in Gebrauch ist. DROP INDEX kann nicht für systemerzeugte Indizes in Primär- oder Fremdschlüsseln, oder in Spalten, die eine Unique-Beschränkung in der Tabellendefinition enthalten verwendet werden.
Ein Index kann von seinem Erzeuger, dem SYSDBA, oder jedem anderen Benutzer mit Grundrechten des Betriebsssystems gelöscht werden.
Siehe auch:
deutschsprachig:
Tabelleneditor / Indizesseite
Indizierte Reads/Nicht-indizierte Reads
Datenbankstatistiken / Indizes
USING INDEX subclause?
Selektivität aller Indizes erneut berechnen
englischsprachig:
CREATE INDEX
Firebird administration using IBExpert: Index statistics
Firebird 2.0 Language Reference Upate
Firebird 2.1 Release Notes: Indexing & optimizations
Firebird 2.0.4. Release Notes: Enhancements to indexing
Firebird for the database expert: Episode 1 - Indexes
Maximum number of indices in different Firebird versions
Recreating Indices 1
Recreating Indices 2
zurück zum Seitenanfang
<< Firebird 3.0 Packages | IBExpert | Firebird 3.0 Stored Funktionen >>
