FirebirdSQL logo
 TABLEVIEW 

Ein Index ist ein Datenbankobjekt, das zum schnelleren Abrufen von Daten aus einer Tabelle oder zum schnelleren Sortieren in einer Abfrage verwendet wird.Indizes werden auch verwendet, um die referenziellen Integritätsbedingungen PRIMARY KEY, FOREIGN KEY und UNIQUE zu erzwingen.

In diesem Abschnitt wird beschrieben, wie Sie Indizes erstellen, aktivieren und deaktivieren, löschen und Statistiken dazu sammeln (Selektivität neu berechnen).

CREATE INDEX

Verwendet für

Erstellen eines Index für eine Tabelle

Verfügbar in

DSQL, ESQL

Syntax
CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
  INDEX indexname ON tablename
  {(col [, col …]) | COMPUTED BY (<expression>)}
Table 1. CREATE INDEX-Anweisungsparameter
Parameter Beschreibung

indexname

Indexname.Dieser kann aus bis zu 63 Zeichen bestehen

tablename

Der Name der Tabelle, für die der Index erstellt werden soll

col

Name einer Spalte in der Tabelle.Spalten der Typen BLOB und ARRAY sowie berechnete Felder können nicht in einem Index verwendet werden

expression

Der Ausdruck, der die Werte für einen berechneten Index berechnet, auch als “Ausdrucksindex” bekannt

Die CREATE INDEX-Anweisung erstellt einen Index für eine Tabelle, mit dem das Suchen, Sortieren und Gruppieren beschleunigt werden kann.Indizes werden beim Definieren von Constraints wie Primärschlüssel-, Fremdschlüssel- oder Unique-Constraints automatisch erstellt.

Ein Index kann auf dem Inhalt von Spalten jedes Datentyps mit Ausnahme von 'BLOB' und Arrays erstellt werden.Der Name (Bezeichner) eines Index muss unter allen Indexnamen eindeutig sein.

Note
Schlüsselindizes

Wenn einer Tabelle oder Spalte ein Primärschlüssel, Fremdschlüssel oder eine eindeutige Einschränkung hinzugefügt wird, wird automatisch ein Index mit demselben Namen ohne ausdrückliche Anweisung des Designers erstellt.Der Index PK_COUNTRY wird beispielsweise automatisch erstellt, wenn Sie die folgende Anweisung ausführen und festschreiben:

ALTER TABLE COUNTRY ADD CONSTRAINT PK_COUNTRY
  PRIMARY KEY (ID);

docnext count = 19

Wer kann einen Index erstellen?

Die CREATE INDEX-Anweisung kann ausgeführt werden durch:

  • Administratoren

  • Der Besitzer der Tabelle

  • Benutzer mit dem ALTER ANY TABLE-Privileg

Eindeutige Indizes

Die Angabe des Schlüsselworts UNIQUE in der Anweisung zur Indexerstellung erstellt einen Index, in dem die Eindeutigkeit in der gesamten Tabelle erzwungen wird.Der Index wird als “eindeutiger Index” bezeichnet.Ein eindeutiger Index ist keine Einschränkung.

Eindeutige Indizes dürfen keine doppelten Schlüsselwerte (oder doppelte Schlüsselwertkombinationen im Fall von zusammengesetzten oder mehrspaltigen oder mehrsegmentigen) Indizes enthalten.Doppelte NULLs sind gemäß dem SQL:99-Standard sowohl in Einzelsegment- als auch in Mehrfachsegment-Indizes erlaubt.

Indexrichtung

Alle Indizes in Firebird sind unidirektional.Ein Index kann vom niedrigsten Wert zum höchsten (aufsteigende Reihenfolge) oder vom höchsten zum niedrigsten Wert (absteigende Reihenfolge) aufgebaut werden. Die Schlüsselwörter ASC[ENDING] und DESC[ENDING] werden verwendet, um die Richtung des Index anzugeben.Die Standardindexreihenfolge ist ASC[ENDING].Es ist durchaus zulässig, sowohl einen aufsteigenden als auch einen absteigenden Index für dieselbe Spalte oder denselben Schlüsselsatz zu definieren.

Tip

Ein absteigender Index kann für eine Spalte nützlich sein, die nach den hohen Werten (""neuest", Maximum usw.) gesucht wird.
Note

Firebird verwendet B-tree-Indizes, die bidirektional sind.Aufgrund technischer Einschränkungen verwendet Firebird jedoch einen Index nur in eine Richtung.

Siehe auch Firebird für den Datenbankexperten: Episode 3 – Auf Datenträgerkonsistenz

Berechnete (Ausdrucks-)Indizes

Beim Erstellen eines Index können Sie die COMPUTED BY-Klausel verwenden, um einen Ausdruck anstelle einer oder mehrerer Spalten anzugeben.Berechnete Indizes werden in Abfragen verwendet, bei denen die Bedingung in einer WHERE-, ORDER BY- oder GROUP BY-Klausel genau mit dem Ausdruck in der Indexdefinition übereinstimmt.Der Ausdruck in einem berechneten Index kann mehrere Spalten in der Tabelle umfassen.

Sie können tatsächlich einen berechneten Index für ein berechnetes Feld erstellen, aber ein solcher Index wird niemals verwendet.

Beschränkungen für Indizes

Für Indizes gelten bestimmte Grenzen.

Die maximale Länge eines Schlüssels in einem Index ist auf ¼ der Seitengröße begrenzt.

Maximale Indizes pro Tabelle

Die Anzahl der Indizes, die für jede Tabelle untergebracht werden können, ist begrenzt.Das tatsächliche Maximum für eine bestimmte Tabelle hängt von der Seitengröße und der Anzahl der Spalten in den Indizes ab.

Table 1. Maximale Indizes pro Tabelle

Seitengröße (Page size)

Anzahl der Indizes abhängig von der Spaltenanzahl

Einspaltig

Zweispaltig

Dreispaltig

4096

203

145

113

8192

408

291

227

16384

818

584

454

32768

1637

1169

909

Zeichenindexbeschränkungen

Die maximale Länge der indizierten Zeichenfolge beträgt 9 Byte weniger als die maximale Schlüssellänge.Die maximale Länge der indexierbaren Zeichenfolge hängt von der Seitengröße und dem Zeichensatz ab.

Table 1. Maximale indexierbare (VAR)CHAR-Länge

Seitengröße (Page size)

Maximale Länge der indizierbaren Zeichenfolge nach Zeichensatztyp

1 Byte/Zeichen

2 Bytes/Zeichen

3 Bytes/Zeichen

4 Bytes/Zeichen

4096

1015

507

338

253

8192

2039

1019

679

509

16384

4087

2043

1362

1021

32768

8183

4091

2727

2045
Note

Abhängig von der Sortierung kann die maximale Größe weiter reduziert werden, da Sortierungen ohne Unterscheidung zwischen Groß- und Kleinschreibung mehr Bytes pro Zeichen in einem Index benötigen.Siehe auch Zeichen-Indizes im Kapitel Datentypen und Untertypen.

Beispiele für die Verwendung von CREATE INDEX

  1. Erstellen eines Index für die Spalte UPDATER_ID in der Tabelle SALARY_HISTORY

    CREATE INDEX IDX_UPDATER
  ON SALARY_HISTORY (UPDATER_ID);

  2. Erstellen eines Index mit in absteigender Reihenfolge sortierten Schlüsseln für die Spalte CHANGE_DATE in der Tabelle SALARY_HISTORY

    CREATE DESCENDING INDEX IDX_CHANGE
  ON SALARY_HISTORY (CHANGE_DATE);

  3. Erstellen eines Multi-Segment-Index für die Spalten ORDER_STATUS, PAID in der Tabelle SALES

    CREATE INDEX IDX_SALESTAT
  ON SALES (ORDER_STATUS, PAID);

  4. Erstellen eines Index, der keine doppelten Werte für die Spalte NAME in der Tabelle COUNTRY zulässt

    CREATE UNIQUE INDEX UNQ_COUNTRY_NAME
  ON COUNTRY (NAME);

  5. Erstellen eines berechneten Index für die Tabelle PERSONS

    CREATE INDEX IDX_NAME_UPPER ON PERSONS
  COMPUTED BY (UPPER (NAME));

    Ein Index wie dieser kann für eine Suche ohne Beachtung der Groß-/Kleinschreibung verwendet werden:

    SELECT *
FROM PERSONS
WHERE UPPER(NAME) STARTING WITH UPPER('Iv');
Siehe auch

[fblangref40-ddl-idx-altridx-de], [fblangref40-ddl-idx-dropidx-de]

ALTER INDEX

Verwendet für

Aktivieren oder Deaktivieren eines Indexes;Neuerstellung eines Index

Verfügbar in

DSQL, ESQL

Syntax
ALTER INDEX indexname {ACTIVE | INACTIVE}
Table 1. ALTER INDEX-Anweisungsparameter
Parameter Beschreibung

indexname

Indexname

Die ALTER INDEX-Anweisung aktiviert oder deaktiviert einen Index.Diese Anweisung bietet keine Möglichkeit, irgendwelche Attribute des Indexes zu ändern.

INAKTIV

Mit der Option INACTIVE wird der Index vom aktiven in den inaktiven Zustand geschaltet.Die Wirkung ist ähnlich wie bei der DROP INDEX-Anweisung, außer dass die Indexdefinition in der Datenbank verbleibt.Das Ändern eines Einschränkungsindex in den inaktiven Zustand ist nicht zulässig.

Ein aktiver Index kann deaktiviert werden, wenn keine Abfragen mit diesem Index vorbereitet sind;andernfalls wird ein Fehler “object in use” zurückgegeben.

Die Aktivierung eines inaktiven Index ist ebenfalls sicher.Wenn jedoch aktive Transaktionen vorhanden sind, die die Tabelle ändern, schlägt die Transaktion mit der Anweisung ALTER INDEX fehl, wenn sie das Attribut NOWAIT besitzt.Wenn sich die Transaktion im WAIT-Modus befindet, wartet sie auf den Abschluss gleichzeitiger Transaktionen.

Auf der anderen Seite der Medaille, wenn unser ALTER INDEX erfolgreich ist und beginnt, den Index bei COMMIT neu aufzubauen, werden andere Transaktionen, die diese Tabelle ändern, fehlschlagen oder warten, entsprechend ihren WAIT/NO WAIT-Attributen.Genauso verhält es sich mit CREATE INDEX.

Note
Wofür ist es nützlich?

Es kann sinnvoll sein, einen Index in den inaktiven Zustand zu versetzen, während ein großer Satz von Datensätzen in der Tabelle, die den Index besitzt, eingefügt, aktualisiert oder gelöscht wird.
AKTIV

Mit der Option 'ACTIVE' wird der Index, wenn er sich im inaktiven Zustand befindet, in den aktiven Zustand geschaltet und das System baut den Index neu auf.

Note
Wofür ist es nützlich?

Auch wenn der Index active ist, wenn ALTER INDEX …​ ACTIVE ausgeführt wird, wird der Index neu aufgebaut.Das Neuerstellen von Indizes kann ein nützliches Stück Haushaltsführung sein, um gelegentlich die Indizes einer großen Tabelle in einer Datenbank zu verwalten, die häufig eingefügt, aktualisiert oder gelöscht wird, aber selten wiederhergestellt wird.

Wer kann einen Index ändern?

Die ALTER INDEX-Anweisung kann ausgeführt werden durch:

  • Administratoren

  • Der Besitzer der Tabelle

  • Benutzer mit dem ALTER ANY TABLE-Privileg

Verwendung von ALTER INDEX für einen Einschränkungsindex

Das Ändern des Index eines PRIMARY KEY, FOREIGN KEY oder UNIQUE Constraints in INACTIVE ist nicht erlaubt.ALTER INDEX …​ ACTIVE funktioniert jedoch bei Constraint-Indizes genauso gut wie bei anderen, als Werkzeug zum Neuaufbau von Indizes.

ALTER INDEX-Beispiele

  1. Deaktivieren des IDX_UPDATER-Index

    ALTER INDEX IDX_UPDATER INACTIVE;

  2. Den IDX_UPDATER-Index zurück in den aktiven Zustand schalten und neu aufbauen

    ALTER INDEX IDX_UPDATER ACTIVE;
Siehe auch

[fblangref40-ddl-idx-create-de], [fblangref40-ddl-idx-dropidx-de], [fblangref40-ddl-idx-setsttstcs-de]

DROP INDEX

Verwendet für

Einen Index löschen (löschen)

Verfügbar in

DSQL, ESQL

Syntax
DROP INDEX indexname
Table 1. DROP INDEX-Anweisungsparameter
Parameter Beschreibung

indexname

Indexname

Die DROP INDEX-Anweisung löscht (löscht) den benannten Index aus der Datenbank.

Note

Ein Einschränkungsindex kann nicht mit DROP INDEX gelöscht werden.Constraint-Indizes werden während der Ausführung des Befehls ALTER TABLE …​ DROP CONSTRAINT …​ gelöscht.

Wer kann einen Index löschen?

Die DROP INDEX-Anweisung kann ausgeführt werden durch:

  • Administratoren

  • Der Besitzer der Tabelle

  • Benutzer mit dem ALTER ANY TABLE-Privileg

DROP INDEX-Beispiel

Löschen des IDX_UPDATER-Index
DROP INDEX IDX_UPDATER;
Siehe auch

[fblangref40-ddl-idx-create-de], [fblangref40-ddl-idx-altridx-de]

SET STATISTICS

Verwendet für

Neuberechnung der Selektivität eines Index

Verfügbar in

DSQL, ESQL

Syntax
SET STATISTICS INDEX indexname
Table 1. SET STATISTICS-Anweisungsparameter
Parameter Beschreibung

indexname

Indexname

Die Anweisung SET STATISTICS berechnet die Selektivität des angegebenen Index neu.

Wer kann Indexstatistiken aktualisieren?

Die Anweisung SET STATISTICS kann ausgeführt werden durch:

  • Administratoren

  • Der Besitzer der Tabelle

  • Benutzer mit dem ALTER ANY TABLE-Privileg

Indexselektivität

Die Selektivität eines Index ergibt sich aus der Auswertung der Anzahl der Zeilen, die bei einer Suche nach jedem Indexwert ausgewählt werden können.Ein eindeutiger Index hat die maximale Selektivität, da es unmöglich ist, mehr als eine Zeile für jeden Wert eines Indexschlüssels auszuwählen, wenn dieser verwendet wird.Die Selektivität eines Index auf dem neuesten Stand zu halten ist wichtig für die Auswahl des Optimierers bei der Suche nach dem optimalsten Abfrageplan.

Indexstatistiken in Firebird werden als Reaktion auf große Mengen von Einfügungen, Aktualisierungen oder Löschungen nicht automatisch neu berechnet.Es kann von Vorteil sein, die Selektivität eines Index nach solchen Operationen neu zu berechnen, da die Selektivität dazu neigt, veraltet zu werden.

Note

Die Anweisungen CREATE INDEX und ALTER INDEX ACTIVE speichern beide Indexstatistiken, die vollständig dem Inhalt des neu erstellten Index entsprechen.

Es kann unter gleichzeitiger Last ohne Beschädigungsrisiko ausgeführt werden.Beachten Sie jedoch, dass die neu berechneten Statistiken bei gleichzeitiger Belastung veraltet sein können, sobald SET STATISTICS beendet ist.

Beispiel für die Verwendung von SET STATISTICS

Neuberechnung der Selektivität des Indexes IDX_UPDATER
SET STATISTICS INDEX IDX_UPDATER;
Siehe auch

[fblangref40-ddl-idx-create-de], [fblangref40-ddl-idx-altridx-de]