Firebird Documentation IndexFirebird 2.5 SprachreferenzStatements der Data Definition (DDL) → INDEX
Firebird Home Firebird Home Zurück: TABLEFirebird Documentation IndexNach oben: Statements der Data Definition (DDL)Weiter: VIEW

INDEX

Inhaltsverzeichnis

CREATE INDEX
ALTER INDEX
DROP INDEX
SET STATISTICS

Ein Index ist ein Datenbankobjekt, das für eine schnellere Datenabfrage aus einer Tabelle oder zur Beschleunigung der Sortierung der Abfrage verwendet wird. Indizes werden auch verwendet, um die referenziellen Integritätsbedingungen PRIMARY KEY, FOREIGN KEY und UNIQUE sicherzustellen.

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

CREATE INDEX

Benutzt für: Einen Index für eine Tabelle erstellen

Verfügbar in: DSQL, ESQL

Syntax: 

CREATE [UNIQUE] [ASC[ENDING] | DESC[ENDING]]
INDEX indexname ON tablename
{(col [, col …]) | COMPUTED BY (<expression>)};
        

Tabelle 5.10. CREATE INDEX Statement-Parameter

Parameter Beschreibung
indexname Indexname. Es kann aus bis zu 31 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 sowie ARRAY und berechnete Felder können nicht in einem Index verwendet werden
expression Der Ausdruck, der die Werte für einen berechneten Index berechnet, auch bekannt als „Ausdrucksindex


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

Ein Index kann auf den Inhalt von Spalten eines beliebigen Datentyps mit Ausnahme von BLOB und Arrays aufgebaut werden. Der Name (Bezeichner) eines Index muss unter allen Indexnamen eindeutig sein.

Schlüssel-Indizes

Wenn ein Primärschlüssel, ein Fremdschlüssel oder eine eindeutige Einschränkung zu einer Tabelle oder Spalte hinzugefügt wird, wird automatisch ein Index mit demselben Namen erstellt, ohne explizite Anweisung vom Designer. Beispielsweise wird der PK_COUNTRY-Index automatisch erstellt, wenn Sie die folgende Anweisung ausführen und übergeben:

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

Unique-Indizes

Wenn Sie in der Indexerstellungsanweisung das Schlüsselwort UNIQUE angeben, wird ein Index erstellt, in dem die Eindeutigkeit in der gesamten Tabelle durchgesetzt wird. Der Index wird als „eindeutiger Index“ bezeichnet. Ein eindeutiger Index ist keine Einschränkung.

Eindeutige Indizes dürfen keine doppelten Schlüsselwerte (oder Duplikatschlüsselwertkombinationen im Falle von berechnet Indizes oder multi-column oder multi-segment) enthalten. Duplizierte NULLs sind gemäß dem SQL: 99-Standard sowohl in Indizes mit einem einzelnen Segment als auch mit mehreren Segmenten zulässig.

Index-Sortierung

Alle Indizes in Firebird sind unidirektional. Ein Index kann vom niedrigsten Wert zum höchsten (aufsteigend) oder vom höchsten zum niedrigsten (absteigend) erstellt 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 sinnvoll, sowohl einen auf- als auch einen absteigenden Index für dieselbe Spalte oder denselben Schlüsselsatz zu definieren.

Tipp

Ein absteigender Index kann für eine Spalte nützlich sein, die Suchanfragen auf den hohen Werten unterzogen wird („neuestes“, Maximum usw.)

Berechnete (Ausdrucks-) Indizes

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

Anmerkung

Sie können tatsächlich einen berechneten Index für ein berechnetes Feld erstellen, der Index wird jedoch nie verwendet.

Indexgrenzen

Bestimmte Beschränkungen gelten für Indizes.

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

Maximale Anzahl 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.

Tabelle 5.11. Maximale Anzahl Indizes pro Tabelle

Seitengröße Anzahl der Indizes in Abhängigkeit von der Spaltenanzahl
einspaltig zweispaltig dreispaltig
4096 203 145 113
8192 408 291 227
16384 818 584 454


Zeichenindexgrenzen

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

Tabelle 5.12. Maximale indizierbare (VAR)CHAR-Länge

Seitengröße Maximale indexierbare Zeichenfolgenlänge nach Zeichensatz
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


Nur der Tabelleneigentümer und Administratoren besitzen die notwendigen Rechte für die Verwendung von CREATE INDEX.

Beispiel für die Verwendung von CREATE INDEX

  1. Erstellen eines Index für die UPDATER_ID in der Tabelle SALARY_HISTORY
    CREATE INDEX IDX_UPDATER
        ON SALARY_HISTORY (UPDATER_ID);
                
  2. Erstellen eines Index mit Schlüsseln in absteigender Reihenfolge für die CHANGE_DATE-Spalte in der Tabelle SALARY_HISTORY.
    CREATE DESCENDING INDEX IDX_CHANGE
       ON SALARY_HISTORY (CHANGE_DATE);
                
  3. Erstellen eines Multisegment-Index für die Spalten ORDER_STATUS sowie 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 PERSONS-Tabelle
    CREATE INDEX IDX_NAME_UPPER ON PERSONS
        COMPUTED BY (UPPER (NAME));
                
    Ein solcher Index kann für eine Groß- / Kleinschreibungs-sensitive Suche verwendet werden:
    SELECT *
        FROM PERSONS
        WHERE UPPER(NAME) STARTING WITH UPPER('Iv');
                

Siehe auch:  ALTER INDEX, DROP INDEX

ALTER INDEX

Benutzt für: Aktivieren oder Deaktivieren eines Indexes; einen Index neu aufbauen

Verfügbar in: DSQL, ESQL

Syntax: 

ALTER INDEX indexname {ACTIVE | INACTIVE};
        

Tabelle 5.13. ALTER INDEX Statement-Parameter

Parameter Beschreibung
indexname Indexname


Die ALTER INDEX-Anweisung aktiviert oder deaktiviert einen Index. In dieser Anweisung gibt es keine Möglichkeit, Attribute des Index zu ändern.

  • Mit der Option INACTIVE wird der Index vom aktiven in den inaktiven Zustand geschaltet. Der Effekt ähnelt der Anweisung DROP INDEX, mit der Ausnahme, dass die Indexdefinition in der Datenbank verbleibt. Das Ändern eines Beschränkungsindex in den inaktiven Zustand ist nicht zulässig.

    Ein aktiver Index kann deaktiviert werden, wenn keine Abfragen mit diesem Index vorhanden sind. Andernfalls wird ein „Objekt in Verwendung“-Fehler zurückgegeben.

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

    Auf der anderen Seite der Münze werden andere Transaktionen, die diese Tabelle modifizieren den Index nach einem COMMIT neu erstellen oder fehlschlagen je nach Status der WAIT / NO WAIT-Attribute. Die Situation ist genau dieselbe für CREATE INDEX.

    Wie sinnvoll ist dies?

    Es kann sinnvoll sein, einen Index in den inaktiven Zustand zu wechseln, während Sie einen großen Stapel von Datensätzen in der Tabelle, in der sich der Index befindet, einfügen, aktualisieren oder löschen.

  • Wenn sich der Index im Status INAKTIV befindet, wird es mit der Option ACTIVE in den aktiven Status umgeschaltet, und das System erstellt den Index neu.

    Wie sinnvoll ist dies?

    Auch wenn der Index active ist, wenn ALTER INDEX ... ACTIVE ausgeführt wird, wird der Index neu erstellt. Die Wiederherstellung von Indizes kann eine nützliche Haushaltshilfe sein, gelegentlich auch für die Indizes einer großen Tabelle in einer Datenbank, die häufige Neuaufnahmen Aktualisierungen oder Löschungen aufweist, aber selten wiederhergestellt wird.

Verwendung von ALTER INDEX in einem Constraint-Index

Das Ändern des Erzwingungsindex für eine PRIMARY KEY, FOREIGN KEY- oder UNIQUE-Einschränkung auf INACTIVE ist nicht zulässig. der oder UNIQUE ist nicht zulässig. Jedoch funktionert ALTER INDEX ... ACTIVE genauso gut wie andere als Indexwiederherstellungstool.

Nur der Tabelleneigentümer und Administratoren haben die Berechtigungen für die Anweisung ALTER INDEX.

Beispiele für ALTER INDEX: 

  1. Deaktivieren des IDX_UPDATER-Index
    ALTER INDEX IDX_UPDATER INACTIVE;
                
  2. Den IDX_UPDATER-Index in den aktiven Zustand zurückschalten und neu erstellen
    ALTER INDEX IDX_UPDATER ACTIVE;
                

Siehe auch:  CREATE INDEX, DROP INDEX, SET STATISTICS

DROP INDEX

Benutzt für: Deleting an index

Verfügbar in: DSQL, ESQL

Syntax: 

DROP INDEX indexname;
        

Tabelle 5.14. DROP INDEX Statement-Parameter

Parameter Beschreibung
indexname Indexname


Das Statement DROP INDEX löscht den angegebenen Index aus der Datenbank.

Anmerkung

Ein Constraint-Index kann nicht mittels DROP INDEX gelöscht werden. Constraint-Indizes werden während des Ausführens des Befehls ALTER TABLE ... DROP CONSTRAINT ... gelöscht.

Nur die Tabelleneigentümer und Administratoren besitzen die Berechtigungen die Anweisung DROP INDEX auszuführen.

DROP INDEX Example:  Löschen des Index IDX_UPDATER

DROP INDEX IDX_UPDATER;
           

Siehe auch:  CREATE INDEX, ALTER INDEX

SET STATISTICS

Inhaltsverzeichnis

Index-Selektivität

Benutzt für: Neuberechnung der Selektivität eines Index

Verfügbar in: DSQL, ESQL

Syntax: 

SET STATISTICS INDEX indexname
        

Tabelle 5.15. SET STATISTICS Statement-Parameter

Parameter Beschreibung
indexname Indexname


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

Index-Selektivität

Die Selektivität eines Index ergibt sich aus der Auswertung der Anzahl der Zeilen, die bei einer Suche für jeden Indexwert ausgewählt werden können. Ein eindeutiger Index hat die maximale Selektivität, da es nicht mö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 Wahl des Optimierers bei der Suche nach dem optimalen Abfrageplan.

Indexstatistiken in Firebird werden nicht automatisch als Reaktion auf große Stapel von Neuaufnahmen, Aktualisierungen oder Löschungen neu berechnet. Es kann vorteilhaft sein, die Selektivität eines Index nach solchen Operationen neu zu berechnen, da die Selektivität dazu neigt, zu veralten.

Anmerkung

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

Die Selektivität eines Index kann vom Besitzer der Tabelle oder einem Administrator neu berechnet werden. Es kann unter gleichzeitiger Belastung ohne Korruptionsrisiko durchgeführt werden. Beachten Sie jedoch, dass die neu berechnete Statistik bei gleichzeitiger Auslastung veraltet sein kann, sobald SET STATISTICS beendet ist.

Beispiele für die Verwendung von SET STATISTICS:  Neuberechnung der Selektivität des Index IDX_UPDATER

SET STATISTICS INDEX IDX_UPDATER;
           

Siehe auch:  CREATE INDEX, ALTER INDEX

Zurück: TABLEFirebird Documentation IndexNach oben: Statements der Data Definition (DDL)Weiter: VIEW
Firebird Documentation IndexFirebird 2.5 SprachreferenzStatements der Data Definition (DDL) → INDEX