Accélérer les requêtes de données semi-structurées grâce à l’optimisation de la recherche¶

Activation de l’optimisation de la recherche pour les requêtes de données semi-structurées¶

Pour améliorer les performances des requêtes de données semi-structurées dans une table, utilisez la clause ON dans la commande ALTER TABLE … ADD SEARCH OPTIMIZATION pour des colonnes spécifiques ou des éléments dans des colonnes. Les requêtes portant sur les colonnes VARIANT, OBJECT et ARRAY ne sont pas optimisées si vous omettez la clause ON. L’activation de l’optimisation de la recherche au niveau de la table ne permet pas de l’activer pour les colonnes contenant des types de données semi-structurées.

Par exemple :

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

Pour plus d’informations, voir Activation et désactivation de l’optimisation de la recherche.

Types de données pris en charge pour les constantes et les conversions de type dans les prédicats pour les types semi-structurés¶

Le service d’optimisation de la recherche peut améliorer les performances des recherches ponctuelles de données semi-structurées où les types suivants sont utilisés pour la constante et la conversion implicite ou explicite pour l’élément :

• FIXED (y compris des conversions qui spécifient une précision et une échelle valides)

• INTEGER (y compris les types synonymes)

• VARCHAR (y compris les types synonymes)

• DATE (y compris les conversions qui précisent une échelle)

• TIME (y compris les conversions qui précisent une échelle)

• TIMESTAMP, TIMESTAMP_LTZ, TIMESTAMP_NTZ, TIMESTAMP_TZ (y compris les conversions qui spécifient une échelle)

Le service d’optimisation des recherches prend en charge la conversion des types en utilisant :

• CAST et l’opérateur ::

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

Par exemple, supposons que src est une colonne VARIANT contenant des valeurs BOOLEAN, DATE et TIMESTAMP qui ont été converties en VARIANT :

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

Prédicats pris en charge pour les recherches ponctuelles sur les types VARIANT¶

Le service d’optimisation de la recherche peut optimiser les recherches ponctuelles avec les types de prédicats énumérés ci-dessous. Dans les exemples ci-dessous, src est la colonne avec un type de données semi-structurées, et path_to_element est un chemin vers un élément de la colonne avec un type de données semi-structurées.

• Prédicats d’égalité de la forme suivante :

  WHERE path_to_element[::target_data_type] = constant

  Dans cette syntaxe, target_data_type (s’il est spécifié) et le type de données de constant doivent être l’un des types pris en charge.

  Par exemple, le service d’optimisation de la recherche prend en charge les points suivants :

  • Correspondance entre un élément VARIANT et une constante NUMBER sans conversion explicite de l’élément.

    WHERE src:person.age = 42;
    

    Copy

  • Conversion explicite d’un élément VARIANT en NUMBER avec une précision et une échelle spécifiées.

    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

  • Conversion explicite d’un élément de VARIANT en DATE.

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

    Copy

  • Conversion explicite d’un élément VARIANT en TIMESTAMP avec une échelle spécifiée.

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

    Copy

  • Correspondance entre un élément ARRAY et une valeur d’un type pris en charge par <label-search_optimization_service_variant_predicates_types>, avec ou sans conversion explicite vers le type. Par exemple :

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

    Copy

  • Correspondance entre un élément OBJECT et une valeur d’un type pris en charge par <label-search_optimization_service_variant_predicates_types>, avec ou sans conversion explicite vers le type. Par exemple :

    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

• Les prédicats qui utilisent les fonctions ARRAY, tels que :

  • WHERE ARRAY_CONTAINS(value_expr, array)

    Dans cette syntaxe, value_expr ne doit pas être NULL et doit être évalué à VARIANT. Le type de données de la valeur doit être l’un des types pris en charge par.

    Par exemple :

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

    Copy

    Dans cet exemple, la valeur est une constante qui est implicitement convertie en VARIANT :

    WHERE ARRAY_CONTAINS(300, my_array_column)
    

    Copy

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

    Le type de données de chaque constante (constant_1, constant_2, etc.) doit être l’un des types pris en charge par. L’ARRAY construit peut inclure des constantes NULL.

    Dans cet exemple, le tableau se trouve dans une valeur de VARIANT :

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

    Copy

    Dans cet exemple, le tableau est une colonne ARRAY :

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

    Copy

• Les prédicats suivants qui recherchent la présence de valeurs NULL :

  • WHERE IS_NULL_VALUE(path_to_element)

    Notez que IS_NULL_VALUE s’applique aux valeurs nulles JSON et non aux valeurs SQL NULL.

  • WHERE path_to_element IS NOT NULL

  • WHERE semistructured_column IS NULL

    où semistructured_column fait référence à la colonne et non à un chemin vers un élément des données semi-structurées.

    Par exemple, le service d’optimisation de la recherche prend en charge l’utilisation de la colonne VARIANT src mais pas le chemin vers l’élément src:person.age dans cette colonne VARIANT.

Recherche par sous-chaîne dans les types VARIANT¶

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

Si vous activez l’optimisation de la recherche pour un élément donné, elle est activée pour tous les éléments imbriqués. La deuxième instruction ALTER TABLE ci-dessous est redondante car la première instruction permet l’optimisation de la recherche pour l’ensemble de l’élément data, y compris l’élément imbriqué search.

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

De même, l’activation de l’optimisation de la recherche pour une colonne entière permet d’optimiser toutes les recherches de sous-chaînes sur cette colonne, y compris les éléments imbriqués à n’importe quelle profondeur à l’intérieur de celle-ci.

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 Interrogation de données semi-structurées, see Activer l’optimisation de la recherche FULL_TEXT sur une colonne VARIANT.

Limites actuelles de la prise en charge des types semi-structurés¶

La prise en charge des types semi-structurés dans le service d’optimisation de la recherche est limitée de la manière suivante :

• Les prédicats de la forme path_to_element IS NULL ne sont pas pris en charge.

• Les prédicats dont les constantes sont des résultats de sous-requêtes scalaires ne sont pas pris en charge.

• Les prédicats qui spécifient des chemins vers des éléments contenant des sous-éléments ne sont pas pris en charge.

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

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