Beschleunigen von Abfragen auf strukturierten Daten durch die Suchoptimierung¶

Aktivieren der Suchoptimierung für Abfragen von strukturierten Daten¶

Um die Leistung bei Abfragen von strukturierten Datentypen auf einer Tabelle zu verbessern, verwenden Sie die ON-Klausel im Befehl ALTER TABLE … ADD SEARCH OPTIMIZATION für bestimmte Spalten oder Elemente darin. Abfragen für die strukturierten ARRAY-, OBJECT- und MAP-Spalten werden nicht optimiert, wenn Sie die Klausel ON weglassen. Wenn Sie die Suchoptimierung auf Tabellenebene aktivieren, wird sie nicht für Spalten mit strukturierten Daten aktiviert.

Beispiel:

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(array_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(array_column[1]);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(object_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(object_column:key);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(map_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(map_column:user.uuid);

Die folgenden Regeln gelten für die Schlüsselwörter, die Sie in diesen ALTER TABLE … ADD SEARCH OPTIMIZATION-Befehlen verwenden:

• Sie können das EQUALITY-Schlüsselwort mit einem beliebigen inneren Element oder der Spalte selbst verwenden.

• Sie können das SUBSTRING-Schlüsselwort nur mit inneren Elementen verwenden, die Textzeichenfolge-Datentypen haben.

Weitere Informationen dazu finden Sie unter Aktivieren und Deaktivieren der Suchoptimierung.

Unterstützte Datentypen für Konstanten und Umwandlungen in Prädikaten für strukturierte Typen¶

Der Suchoptimierungsservice kann die Leistung von Punktsuchabfragen auf strukturierten Daten verbessern, bei denen die folgenden Typen für Konstanten und für die implizite oder explizite Umwandlung von Elementen verwendet werden:

• FIXED (einschließlich Umwandlungen, die eine gültige Genauigkeit und Skalierung angeben)

• INTEGER (einschließlich synonymer Typen)

• VARCHAR (einschließlich synonymer Typen)

• DATE (einschließlich Umwandlungen, die eine Skalierung angeben)

• TIME (einschließlich Umwandlungen, die eine Skalierung angeben)

• TIMESTAMP TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ (einschließlich Umwandlungen, die eine Skalierung angeben)

Der Suchoptimierungsservice unterstützt die Umwandlung von Typen mithilfe der folgenden Konvertierungsfunktionen:

• CAST und dem Operator ::

• TRY_CAST

Unterstützte Prädikate für Punktsuche auf strukturierten Typen¶

Der Suchoptimierungsservice kann Punktsuchabfragen mit den in der folgenden Liste aufgeführten Typen von Prädikaten verbessern. In den Beispielen ist src die Spalte mit einem strukturierten Datentyp, und path_to_element ist ein Pfad zu einem Element in der Spalte mit einem strukturierten Datentyp.

• Gleichheitsprädikate im folgenden Format:

  WHERE path_to_element[::target_data_type] = constant

  Bei dieser Syntax müssen target_data_type (falls angegeben) und der Datentyp von constant zu den oben aufgeführten unterstützten Typen gehören.

  Der Suchoptimierungsservice unterstützt beispielsweise die folgenden Prädikate:

  • Abgleich eines OBJECT- oder MAP-Elements mit einer NUMBER-Konstante ohne explizite Umwandlung des Elements:

    WHERE src:person.age = 42;
    

    Copy

  • Explizite Umwandlung eines OBJECT- oder MAP-Elements in NUMBER mit einer bestimmten Genauigkeit und Skalierung:

    WHERE src:location.temperature::NUMBER(8, 6) = 23.456789;
    

    Copy

  • Abgleich eines OBJECT- oder MAP-Elements mit einer VARCHAR-Konstante ohne explizite Umwandlung des Elements:

    WHERE src:sender_info.ip_address = '123.123.123.123';
    

    Copy

  • Explizite Umwandlung eines OBJECT- oder MAP-Elements in VARCHAR:

    WHERE src:salesperson.name::VARCHAR = 'John Appleseed';
    

    Copy

  • Explizite Umwandlung eines OBJECT- oder MAP-Elements in DATE:

    WHERE src:events.date::DATE = '2021-03-26';
    

    Copy

  • Explizite Umwandlung eines OBJECT- oder MAP-Elements in TIMESTAMP mit einer bestimmten Skalierung:

    WHERE src:event_logs.exceptions.timestamp_info(3) = '2021-03-26 15:00:00.123 -0800';
    

    Copy

  • Abgleich eines ARRAY-Elements mit dem Wert eines unterstützten Typs, mit oder ohne explizite Umwandlung:

    WHERE my_array_column[2] = 5;
    
    WHERE my_array_column[2]::NUMBER(4, 1) = 5;
    

    Copy

  • Abgleich eines OBJECT- oder MAP-Elements mit dem Wert eines unterstützten Typs, mit oder ohne explizite Umwandlung:

    WHERE object_column['mykey'] = 3;
    
    WHERE object_column:mykey = 3;
    
    WHERE object_column['mykey']::NUMBER(4, 1) = 3;
    
    WHERE object_column:mykey::NUMBER(4, 1) = 3;
    

    Copy

• Prädikate, die die ARRAY-Funktionen verwenden, wie z. B. die folgenden Prädikate:

  • WHERE ARRAY_CONTAINS(value_expr, array)

    In dieser Syntax darf value_expr nicht NULL sein und muss VARIANT ergeben. Der Datentyp des Wertes muss einer der unterstützten Typen sein.

    WHERE ARRAY_CONTAINS('77.146.211.88'::VARIANT, src:logs.ip_addresses)
    

    Copy

    In diesem Beispiel ist der Wert eine Konstante, die implizit in ein OBJECT umgewandelt wird:

    WHERE ARRAY_CONTAINS(300, my_array_column)
    

    Copy

  • WHERE ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), array)

    Der Datentyp der einzelnen Konstanten (constant_1, constant_2 und so weiter) muss einer der unterstützten Typen sein. Das konstruierte ARRAY kann NULL-Konstanten enthalten.

    In diesem Beispiel befindet sich das Array in einem OBJECT-Wert:

    WHERE ARRAYS_OVERLAP(
      ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
    

    Copy

    In diesem Beispiel befindet sich das Array in einer ARRAY-Spalte:

    WHERE ARRAYS_OVERLAP(
      ARRAY_CONSTRUCT('a', 'b'), my_array_column)
    

    Copy

• Die folgenden Prädikate prüfen auf NULL-Werte:

  • WHERE IS_NULL_VALUE(path_to_element)

    Bemerkung

    IS_NULL_VALUE gilt für JSON-Null-Werte und nicht für SQL-NULL-Werte.

  • WHERE path_to_element IS NOT NULL

  • WHERE structured_column IS NULL

    wobei sich structured_column auf die Spalte und nicht auf einen Pfad zu einem Element in den strukturierten Daten bezieht.

    Zum Beispiel unterstützt der Suchoptimierungsdienst die Verwendung der Spalte OBJECT src, aber nicht den Pfad zum Element src:person.age in dieser Spalte OBJECT.

Suche nach Teilzeichenfolgen in strukturierten Typen¶

Sie können die Suche nach Teilzeichenfolgen nur aktivieren, wenn das strukturierte Zielelement den Datentyp Textzeichenfolge aufweist.

Betrachten Sie beispielsweise die folgende Tabelle:

CREATE TABLE t(
  col OBJECT(
    a INTEGER,
    b STRING,
    c MAP(INTEGER, STRING),
    d ARRAY(STRING)
  )
);

Für diese Tabelle kann die Optimierung für die SUBSTRING-Suche zu den folgenden strukturierten Zielelementen hinzugefügt werden:

• col:b, weil sein Typ STRING lautet.

• col:c[value] – z. B. col:c[0], col:c[100] –, wenn es ich bei den Werten um Textzeichenfolgen handelt.

Für diese Tabelle kann die Optimierung für die SUBSTRING-Suche nicht zu den folgenden strukturierten Zielelementen hinzugefügt werden:

• col, weil sein Typ strukturiertes OBJECT lautet.

• col:a, weil sein Typ INTEGER lautet.

• col:c, weil sein Typ MAP lautet.

• col:d, weil sein Typ ARRAY lautet.

Der Suchoptimierungsservice kann Prädikate optimieren, die die folgenden Funktionen verwenden:

• LIKE

• LIKE ANY

• LIKE ALL

• ILIKE

• ILIKE ANY

• CONTAINS

• ENDSWITH

• STARTSWITH

• SPLIT_PART

• RLIKE

• REGEXP

• REGEXP_LIKE

Sie können die Optimierung der Suche nach Teilzeichenfolgen für eine Spalte oder für mehrere einzelne Elemente innerhalb einer Spalte aktivieren. Die folgende Anweisung aktiviert zum Beispiel die Optimierung der Suche nach Teilzeichenfolgen für ein verschachteltes Element in einer Spalte:

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);

Nachdem der Suchzugriffspfad erstellt wurde, kann die folgende Abfrage optimiert werden:

SELECT * FROM test_table WHERE col2:data.search LIKE '%optimization%';

Die folgenden Abfragen werden jedoch nicht optimiert, da die Filter der WHERE-Klausel nicht auf das Element angewendet werden, das bei der Aktivierung der Suchoptimierung angegeben wurde (col2:data.search):

SELECT * FROM test_table WHERE col2:name LIKE '%simon%parker%';
SELECT * FROM test_table WHERE col2 LIKE '%hello%world%';

Sie können mehrere Elemente angeben, die optimiert werden sollen. Im folgenden Beispiel ist die Suchoptimierung für zwei bestimmte Elemente in der Spalte col2 aktiviert:

ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:name);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);

Wenn Sie die Suchoptimierung für ein bestimmtes Element aktivieren, wird sie auch für alle nicht verschachtelten Elemente eines Textzeichenfolgentyps aktiviert. Die Suchoptimierung wird nicht für verschachtelte Elemente oder Elemente von Nicht-Text-Zeichenfolgentypen aktiviert.

Unterstützung der Schemaentwicklung¶

Das Schema strukturierter Spalten kann sich im Laufe der Zeit weiterentwickeln. Weitere Informationen zur Schemaentwicklung finden Sie unter ALTER ICEBERG TABLE … ALTER COLUMN … SET DATA TYPE (strukturierte Typen).

Im Rahmen einer einzelnen Schemaentwicklungsvorgänge können die folgenden Änderungen auftreten:

• Typerweiterung

• Neuordnen der Elemente

• Hinzufügen von Elementen

• Entfernen von Elementen

• Umbenennen von Elementen

Der Suchoptimierungsservice wird als Teil des Schemaentwicklungsvorgangs nicht ungültig gemacht. Stattdessen behandelt der Suchoptimierungsservice die Vorgänge auf folgende Weise:

Typerweiterung (z. B. INT zu NUMBER)

Die Zugriffspfade für die Suchoptimierung sind nicht betroffen.

Hinzufügen von Elementen

Die neu hinzugefügten Elemente werden automatisch in den bestehenden Zugriffspfaden für die Suchoptimierung berücksichtigt.

Entfernen von Elementen

Wenn Elemente aus einer strukturierten Spalte entfernt werden, löscht der Suchoptimierungsservice automatisch Zugriffspfade, die über das Präfix des entfernten Elements verfügen.

Erstellen Sie zum Beispiel eine Tabelle mit einer Spalte des Typs OBJECT, und fügen Sie dann Daten ein:

CREATE OR REPLACE TABLE test_struct (
  a OBJECT(
    b INTEGER,
    c OBJECT(
      d STRING,
      e VARIANT
      )
  )
);

INSERT INTO test_struct (a) SELECT
  {
    'b': 100,
    'c': {
        'd': 'value1',
        'e': 'value2'
  }
  }::OBJECT(
    b INTEGER,
    c OBJECT(
        d STRING,
        e VARIANT
    )
);

Copy

Fragen Sie die Tabelle ab, um die Daten anzuzeigen:

SELECT * FROM test_struct;

Copy

+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

Die folgende Anweisung entfernt das Element c aus dem Objekt:

ALTER TABLE test_struct ALTER COLUMN a
  SET DATA TYPE OBJECT(
    b INTEGER);

Copy

Wenn diese Anweisung ausgeführt wird, werden die Zugriffspfade unter a, a:c, a:c:d und a:c:e gelöscht.

Umbenennen von Elementen

Wenn ein Element umbenannt wird, löscht der Suchoptimierungsservice automatisch die Zugriffspfade, denen das umbenannte Element vorangestellt ist, und fügt sie mit dem neu benannten Pfad wieder hinzu. Bei diesem Vorgang fallen zusätzliche Wartungskosten für die Verarbeitung des neu hinzugefügten Pfads im Suchoptimierungsservice an.

Erstellen Sie zum Beispiel eine Tabelle mit einer Spalte des Typs OBJECT, und fügen Sie dann Daten ein:

CREATE OR REPLACE TABLE test_struct (
  a OBJECT(
    b INTEGER,
    c OBJECT(
      d STRING,
      e VARIANT
      )
  )
);

INSERT INTO test_struct (a) SELECT
  {
    'b': 100,
    'c': {
        'd': 'value1',
        'e': 'value2'
  }
  }::OBJECT(
    b INTEGER,
    c OBJECT(
        d STRING,
        e VARIANT
    )
);

Copy

Fragen Sie die Tabelle ab, um die Daten anzuzeigen:

SELECT * FROM test_struct;

Copy

+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

Die folgende Anweisung benennt das Element c im Objekt in c_new um:

ALTER TABLE test_struct ALTER COLUMN a
  SET DATA TYPE OBJECT(
    b INTEGER,
    c_new OBJECT(
      d STRING,
      e VARIANT
    )
  ) RENAME FIELDS;

Copy

Die Zugriffspfade unter a, a:c, a:c:d und a:c:e werden gelöscht und als a, a:c_new, a:c_new:d und a:c_new:e wieder hinzugefügt.

Neuordnen der Elemente

Die Zugriffspfade für die Suchoptimierung sind nicht betroffen.

Derzeitige Einschränkungen bei der Unterstützung von strukturierten Typen¶

Die Unterstützung für strukturierte Typen im Suchoptimierungsservice ist auf folgende Weise eingeschränkt:

• Prädikate der Form path_to_element IS NULL werden nicht unterstützt.

• Prädikate, bei denen die Konstanten Ergebnisse von skalaren Unterabfragen sind, werden nicht unterstützt.

• Prädikate, die Pfade zu Elementen angeben, die Unterelemente enthalten, werden nicht unterstützt.

• Prädikate, die die XMLGET-Funktion verwenden, werden nicht unterstützt.

• Prädikate, die die MAP_CONTAINS_KEY-Funktion verwenden, werden nicht unterstützt.

Die aktuellen Einschränkungen des Suchoptimierungsservice gelten auch für strukturierte Typen.