Beschleunigen von Abfragen auf semistrukturierten Daten durch die Suchoptimierung¶

Suchoptimierung für Abfragen von semistrukturierten Daten¶

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

Beispiel:

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user.uuid);

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON SUBSTRING(c4:user.uuid);

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

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

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

Unterstützte Datentypen für Konstanten und Umwandlungen in Prädikaten für semistrukturierte 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 (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 Suchoptimierungsdienst unterstützt die Umwandlung von Typen mit:

• CAST und dem Operator ::

• TRY_CAST

Support for semi-structured data type values cast to VARCHAR¶

The search optimization service can also improve the performance of point lookups in which columns with semi-structured data types are cast to VARCHAR and are compared to constants that are cast to VARCHAR.

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);

For this table, the search optimization service can improve the following queries, which cast the VARIANT column to VARCHAR and compare the column to string constants:

SELECT * FROM test_table WHERE src::VARCHAR = 'true';
SELECT * FROM test_table WHERE src::VARCHAR = '2020-01-09';
SELECT * FROM test_table WHERE src::VARCHAR = '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 Spalte mit einem semistrukturierten Datentyp, und path_to_element ist ein Pfad zu einem Element in der Spalte mit einem semistrukturierten 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 Suchoptimierungsdienst unterstützt beispielsweise Folgendes:

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

    WHERE src:person.age = 42;
    

    Copy

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

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

    Copy

  • Matching a VARIANT element against a VARCHAR constant without explicitly casting the element.

    WHERE src:sender_info.ip_address = '123.123.123.123';
    

    Copy

  • Explicitly casting a VARIANT element to VARCHAR.

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

    Copy

  • Explizite Umwandlung eines VARIANT-Elements in DATE.

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

    Copy

  • Explizite Umwandlung eines VARIANT-Elements in TIMESTAMP mit einem bestimmten Maßstab.

    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 in den Typ. Beispiel:

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

    Copy

  • Abgleich eines OBJECT-Elements mit dem Wert eines unterstützten Typs, mit oder ohne explizite Umwandlung in den Typ. Beispiel:

    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 ARRAY-Funktionen verwenden, wie z. B.:

  • 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.

    Beispiel:

    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 VARIANT umgewandelt wird:

    WHERE ARRAY_CONTAINS(300, my_array_column)
    

    Copy

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

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

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

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

    Copy

    In diesem Beispiel ist das Array eine ARRAY-Spalte:

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

    Copy

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

  • WHERE IS_NULL_VALUE(path_to_element)

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

  • WHERE path_to_element IS NOT NULL

  • WHERE semistructured_column IS NULL

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

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

Suche nach Teilzeichenfolgen in VARIANT-Typen¶

The search optimization service can optimize wildcard or regular expression searches in semi-structured columns — that is, VARIANT, OBJECT, and ARRAY columns — or elements in such columns.

The search optimization service can optimize predicates that use the following functions:

• LIKE

• LIKE ANY

• LIKE ALL

• ILIKE

• ILIKE ANY

• CONTAINS

• ENDSWITH

• STARTSWITH

• SPLIT_PART

• RLIKE

• REGEXP

• REGEXP_LIKE

You can enable substring search optimization for a column or for multiple individual elements within a column. For example, the following statement enables substring search optimization for a nested element in a column:

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

After the search access path has been built, the following query can be optimized:

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

However, the following queries aren’t optimized because the WHERE clause filters don’t apply to the element that was specified when search optimization was enabled (col2:data.search):

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

You can specify multiple elements to be optimized. In the following example, search optimization is enabled for two specific elements in the column col2:

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 verschachtelten Elemente aktiviert. Die zweite Anweisung ALTER TABLE unten ist redundant, da die erste Anweisung die Suchoptimierung für das gesamte Element data ermöglicht, einschließlich des verschachtelten Elements search.

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 Suchen nach Teilzeichenfolgen in dieser Spalte optimiert, einschließlich der Elemente, die in beliebiger Tiefe in der Spalte verschachtelt sind.

For an example that enables FULL_TEXT search optimization on a VARIANT column in the car_sales table and its data, which is described in Abfragen von semistrukturierten Daten, see FULL_TEXT-Suchoptimierung in einer VARIANT-Spalte aktivieren.

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

Die Unterstützung für semistrukturierte Typen im Suchoptimierungsdienst 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.

• Predicates that use the XMLGET function aren’t supported.

The current limitations of the search optimization service also apply to semi-structured types.