Beschleunigen von Abfragen auf semistrukturierten Daten durch die Suchoptimierung¶

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

Der Suchoptimierungsdienst kann die Leistung von Punktsuchabfragen auf semistrukturierten 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

• TEXT

• 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 Suchoptimierungsdienst unterstützt die Umwandlung von Typen mit:

• CAST und dem Operator ::

• TRY_CAST

Unterstützung der Umwandlung von VARIANT-Werten in TEXT¶

Der Suchoptimierungsdienst kann auch die Leistung von Punktsuchabfragen verbessern, bei denen VARIANT-Spalten in TEXT umgewandelt und mit in TEXT umgewandelten Konstanten verglichen werden.

Angenommen, src ist eine VARIANT-Spalte, die Werte der Datentypen BOOLEAN, DATE und TIMESTAMP enthält, die in VARIANT-Wert konvertiert worden sind:

CREATE OR REPLACE TABLE test_table
(
  id INTEGER,
  src VARIANT
);

INSERT INTO test_table SELECT 1, TO_VARIANT('true'::BOOLEAN);
INSERT INTO test_table SELECT 2, TO_VARIANT('2020-01-09'::DATE);
INSERT INTO test_table SELECT 3, TO_VARIANT('2020-01-09 01:02:03.899'::TIMESTAMP);

Bei dieser Tabelle kann der Suchoptimierungsdienst die folgenden Abfragen verbessern, bei denen die VARIANT-Spalte in TEXT umgewandelt und die Spalte mit Zeichenfolgenkonstante verglichen wird:

SELECT * FROM test_table WHERE src::TEXT = 'true';
SELECT * FROM test_table WHERE src::TEXT = '2020-01-09';
SELECT * FROM test_table WHERE src::TEXT = '2020-01-09 01:02:03.899';

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

Der Suchoptimierungsdienst kann Punktsuchabfragen mit den unten aufgeführten Typen von Prädikaten verbessern. In den folgenden Beispielen ist src die VARIANT-Spalte und path_to_variant_field ist ein Pfad zu einem Feld in der VARIANT-Spalte.

• Gleichheitsprädikate im folgenden Format:

  WHERE path_to_variant_field[::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 Suchoptimierungsdienst unterstützt beispielsweise Folgendes:

  • Abgleich eines Elements mit einer NUMBER-Konstante ohne explizite Umwandlung des Elements.

    WHERE src:person.age = 42;
    

    Copy

  • Explizite Umwandlung eines Elements in NUMBER mit einer bestimmten Genauigkeit und Skalierung.

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

    Copy

  • Abgleich eines Elements mit einer TEXT-Konstante ohne explizite Umwandlung des Elements.

    WHERE src:sender_info.ip_address = '123.123.123.123';
    

    Copy

  • Explizite Umwandlung eines Elements in TEXT.

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

    Copy

  • Explizite Umwandlung eines Elements in DATE.

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

    Copy

  • Explizite Umwandlung eines Elements in TIMESTAMP mit einer bestimmten Skalierung.

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

    Copy

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

  • WHERE ARRAY_CONTAINS(constant::VARIANT, path_to_variant_field)

    Bei dieser Syntax darf constant nicht NULL sein, und der Datentyp von constant muss zu den oben aufgeführten unterstützten Typen gehören.

    Beispiel:

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

    Copy

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

    Der Datentyp jeder Konstanten (constant_1, constant_2 usw.) muss einer der oben aufgeführten unterstützten Typen sein. Das konstruierte ARRAY kann NULL-Konstanten enthalten.

    Beispiel:

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

    Copy

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

  • WHERE IS_NULL_VALUE(path_to_variant_field)

    Beachten Sie, dass IS_NULL_VALUE für JSON-Nullwerte und nicht für SQL-NULL-Werte gilt.

  • WHERE path_to_variant_field IS NOT NULL

  • WHERE variant_column IS NULL

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

    So unterstützt der Suchoptimierungsdienst beispielsweise die Verwendung der VARIANT-Spalte src, nicht aber den Pfad zum Feld src:person.age in dieser VARIANT-Spalte.

Suche nach Teilzeichenfolgen in VARIANT-Typen¶

Der Suchoptimierungsdienst kann Platzhalter oder reguläre Ausdrücke in semistrukturierten Spalten (einschließlich ARRAY-, OBJECT- und VARIANT-Spalten) oder in Feldern in solchen Spalten optimieren. Dazu zählen Prädikate, die Folgendes verwenden:

• [ NOT ] LIKE

• LIKE ANY

• LIKE ALL

• [ NOT ] ILIKE

• ILIKE ANY

• CONTAINS

• ENDSWITH

• STARTSWITH

• SPLIT_PART

• [ NOT ] RLIKE

• [ NOT ] REGEXP

• REGEXP_LIKE

Sie können die Suchoptimierung nach Teilzeichenfolgen für eine Spalte oder für mehrere einzelne Felder innerhalb einer Spalte aktivieren. Die folgende Anweisung aktiviert beispielsweise die Suchoptimierung nach Teilzeichenfolgen für ein verschachteltes Feld in einer Spalte.

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

Sobald 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 für das Feld gelten, das bei 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 Felder angeben, die optimiert werden sollen. Hier ist die Suchoptimierung für zwei bestimmte Felder 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 Feld aktivieren, wird sie auch für alle Unterfelder aktiviert. Die zweite ALTER TABLE-Anweisung unten ist redundant, da die erste Anweisung die Suchoptimierung für das gesamte data-Feld ermöglicht, einschließlich des verschachtelten search-Feldes.

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

Wenn Sie die Suchoptimierung für eine ganze Spalte aktivieren, werden alle Suchläufe nach Teilzeichenfolgen in dieser Spalte optimiert, einschließlich für Felder, die in beliebiger Tiefe in der Spalte verschachtelt sind.

Aktuelle Beschränkungen bei der Unterstützung von VARIANT-Typen¶

Die Unterstützung von VARIANT-Typen im Suchoptimierungsdienst unterliegt derzeit den folgenden Einschränkungen:

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

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

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

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

Die aktuell für den Suchoptimierungsdienst geltenden Einschränkungen gelten auch für dieses Feature.