Index für Hybridtabellen

In diesem Thema wird erklärt, wie Sie Hybridtabellen indizieren.

Arten von Indizes

Hybridtabellen unterstützen zwei Arten von Indizes:

  • Indizes, die automatisch erstellt werden, wenn Sie Einschränkungen für Spalten von Hybridtabellen deklarieren.

    • Indizes für PRIMARY KEY-Einschränkungen

    • Indizes für FOREIGN KEY-Einschränkungen

    • Indizes für UNIQUE-Einschränkungen

  • Benutzerdefinierte Indizes, bekannt als sekundäre Indizes, die Sie bei Bedarf auch für andere Spalten definieren können. Ein einzelner Index kann eine oder mehrere Spalten abdecken. Sie können CREATE HYBRID TABLE oder CREATE INDEX zum Definieren sekundärer Indizes verwenden.

    Wenn Sie sekundäre Indizes erstellen, können Sie Spalten „einschließen“, die nicht Teil des Indexschlüssels sind, aber mit dem Index selbst verbunden und gespeichert werden. Siehe INCLUDE-Spalten.

    Achtung

    Um einen sekundären Index hinzuzufügen, müssen Sie eine Rolle verwenden, die über die SELECT-Berechtigung für die Hybridtabelle verfügt. Wenn Sie Zugriff auf eine Ansicht der Daten in der Hybridtabelle haben, aber nicht auf Tabelle selbst, können Sie keinen sekundären Index hinzufügen.

Sekundäre Indizes hinzufügen

Alle Hybridtabellen benötigen einen eindeutigen Primärschlüssel. Die Daten in einer Hybridtabelle sind nach diesem Primärschlüssel geordnet. Sie können zusätzliche sekundäre Indizes für nicht primäre Schlüsselattribute erstellen, um Lookups bei diesen Attributen zu beschleunigen. Indizes können möglicherweise die Anzahl der gescannten Datensätze reduzieren, wenn ein Abfrageprädikat eine der folgenden Bedingungen erfüllt:

Wenn Sie häufige, wiederholte Abfragen mit Prädikaten auf einem bestimmten Attribut oder eine zusammengesetzte Gruppe von Attributen haben, sollten Sie in Erwägung ziehen, einen Index für dieses Attribut oder diese Gruppe von Attributen hinzuzufügen, um die Leistung zu verbessern. Beachten Sie die folgenden Aspekte bei der Verwendung von Indizes:

  • Erhöhter Speicherbedarf bei der Speicherung zusätzlicher Kopien der Teilmenge von Daten im Index.

  • Hinzufügen von Overhead zu DMLs, da Indizes synchron verwaltet werden.

Sie können sekundäre Indizes zu einer Hybridtabelle hinzufügen, wenn Sie sie erstellen. Sie können diese aber auch später mithilfe des Befehls CREATE INDEX hinzufügen. Die folgende CREATE HYBRID TABLE-Anweisung erstellt beispielsweise automatisch zwei Indizes (in der Spalte PRIMARY KEY und UNIQUE, col1 und col2) und einen benutzerdefinierten sekundären Index (in col3):

CREATE OR REPLACE HYBRID TABLE target_hybrid_table (
    col1 VARCHAR(32) PRIMARY KEY,
    col2 NUMBER(38,0) UNIQUE,
    col3 NUMBER(38,0),
    INDEX index_col3 (col3)
    )
  AS SELECT col1, col2, col3 FROM source_table;
Copy

Alternativ können Sie einen sekundären Index für eine bestehende Hybridtabelle erstellen, indem Sie den Befehl CREATE INDEX verwenden. Verwenden Sie diesen Befehl, um einen Index zu einer Hybridtabelle hinzuzufügen, die aktiv für einen Workload verwendet wird und Abfragen bedient oder Fremdschlüssel hat. Der CREATE INDEX-Befehl erstellt Indizes gleichzeitig, ohne die Tabelle während des Vorgangs zu sperren.

Tipp

Überprüfen Sie den Status der Indexerstellung mit dem Befehl SHOW INDEXES. Es wird jeweils nur eine Indexerstellung unterstützt.

Wenn sich Ihre Hybridtabellenanwendung jedoch im Entwicklungs- oder Testmodus befindet und eine gewisse Ausfallzeit für die Tabelle kein Problem darstellt, ist es effizienter, die Hybridtabelle neu zu erstellen und die Indizes durch Ausführen eines optimierten Massenladens zu erstellen. Diese Methode ist effizienter als die Online-Indexerstellung mit dem Befehl CREATE INDEX.

Optimiertes Massenladen wird unterstützt für CTAS, COPY und INSERT INTO. … SELECT, aber Sie können CTAS nicht verwenden, wenn Ihre Tabelle eine FOREIGN KEY-Einschränkung hat. Die zweite in diesem Beispiel erstellte Tabelle, fk_hybrid_table, müsste mit COPY oder INSERT INTO. … SELECT gefüllt werden:

CREATE OR REPLACE HYBRID TABLE ref_hybrid_table (
    col1 VARCHAR(32) PRIMARY KEY,
    col2 NUMBER(38,0) UNIQUE
);

CREATE OR REPLACE HYBRID TABLE fk_hybrid_table (
    col1 VARCHAR(32) PRIMARY KEY,
    col2 NUMBER(38,0),
    col3 NUMBER(38,0),
    FOREIGN KEY (col2) REFERENCES ref_hybrid_table(col2),
    INDEX index_col3 (col3)
);
Copy

INCLUDE-Spalten

Obwohl sie nicht Teil des sekundären Indexschlüssels sind, werden INCLUDE-Spalten mit den Indexdatensätzen gespeichert. Aufgrund dieser Zuordnung zwischen den tatsächlich indizierten Spalten und den Daten in den enthaltenen Spalten können bestimmte Abfragen Tabellenscans vermeiden und von kostengünstigeren Scans unter Verwendung des Index profitieren. Die Verwendung von enthaltenen Spalten in Indizes kann jedoch zu einem erhöhten Speicherbedarf führen, da zusätzliche Spalten mit den indizierten Spalten gespeichert werden.

Betrachten Sie beispielsweise die folgende Tabelle und den Index: Der Index könnte in diesem Fall entweder in der CREATE TABLE-Anweisung oder in der CREATE INDEX-Anweisung deklariert werden.

CREATE OR REPLACE HYBRID TABLE sensor_data_device1 (
  device_id VARCHAR(10),
  timestamp TIMESTAMP PRIMARY KEY,
  temperature DECIMAL(6,4),
  vibration DECIMAL(6,4),
  motor_rpm INT
  );

CREATE INDEX sec_sensor_idx
  ON TABLE sensor_data_device1(temperature)
    INCLUDE (vibration, motor_rpm);
Copy

Da dieser sekundäre Index eine Spalte direkt abdeckt (temperature) und zwei Spalten indirekt (vibration, motor_rpm) kann der Index verwendet werden, um bestimmte Abfragen zu optimieren, die temperature einschränken und Daten aus den enthaltenen Spalten auswählen.

Um diese Verhaltensweise zu testen, generieren Sie zunächst einige Zeilen für die Tabelle:

INSERT INTO sensor_data_device1 (device_id, timestamp, temperature, vibration, motor_rpm)
  SELECT 'DEVICE1', timestamp,
    UNIFORM(25.1111, 40.2222, RANDOM()), -- Temperature range in °C
    UNIFORM(0.2985, 0.3412, RANDOM()), -- Vibration range in mm/s
    UNIFORM(1400, 1495, RANDOM()) -- Motor RPM range
  FROM (
    SELECT DATEADD(SECOND, SEQ4(), '2024-03-01') AS timestamp
      FROM TABLE(GENERATOR(ROWCOUNT => 2678400)) -- seconds in 31 days
  );
Copy

Führen Sie nun die folgende Abfrage aus:

SELECT temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature = 25.6;
Copy

Diese Abfrage verwendet den sekundären Index namens sec_sensor_idx. Sie können dieses Verhalten überprüfen, indem Sie den Befehl EXPLAIN für die Abfrage ausführen oder durch Überprüfen des Abfrageprofils in Snowsight. Sie werden einen Indexscan für den sekundären Index und keinen „Testscan“ für die Hybridtabelle selbst sehen.

Die folgenden Abfragen, die andere unterstützte WHERE-Klauselbedingungen verwenden, würden ebenfalls von demselben sekundären Index profitieren:

SELECT temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature IN (25.6, 31.2, 35.8);

SELECT temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature BETWEEN 25.0 AND 26.0;
Copy

Ändern Sie nun die erste Abfrage, indem Sie die device_id-Spalte zur Auswahlliste hinzufügen. Diese Spalte wird nicht vom sec_sensor_idx-Index abgedeckt.

SELECT device_id, temperature, vibration, motor_rpm
  FROM sensor_data_device1
  WHERE temperature = 25.6;
Copy

Diese Abfrage kann nicht vollständig vom sekundären Index abhängen. Ein Testscan der Hybridtabelle ist erforderlich, um die korrekten device_id-Werte zurückzugeben.