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

Types de données pris en charge pour les constantes et les conversions dans les prédicats pour les types de VARIANT¶

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

• TEXT

• 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

Prise en charge de la conversion de valeurs VARIANT en TEXT¶

Le service d’optimisation de la recherche peut également améliorer les performances des recherches ponctuelles dans lesquelles les colonnes VARIANT sont converties en TEXT et sont comparées à des constantes converties en TEXT.

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

Pour cette table, le service d’optimisation des recherches peut améliorer les requêtes suivantes, qui convertissent la colonne VARIANT en TEXT et comparent la colonne à des constantes de type chaîne :

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

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 VARIANT, et path_to_variant_field est un chemin d’accès à un champ de la colonne VARIANT.

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

  WHERE path_to_variant_field[::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 :

  • Mise en correspondance d’un élément avec une constante NUMBER sans convertir explicitement l’élément.

    WHERE src:person.age = 42;
    

    Copy

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

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

    Copy

  • Mise en correspondance d’un élément avec une constante TEXT sans convertir explicitement l’élément.

    WHERE src:sender_info.ip_address = '123.123.123.123';
    

    Copy

  • Conversion explicite d’un élément en TEXT.

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

    Copy

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

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

    Copy

  • Conversion explicite d’un élément 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

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

  • WHERE ARRAY_CONTAINS(constant::VARIANT, path_to_variant_field)

    Dans cette syntaxe, constant ne doit pas être NULL, et le type de données de constant doit être l’un des types pris en charge.

    Par exemple :

    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)

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

    Par exemple :

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

    Copy

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

  • WHERE IS_NULL_VALUE(path_to_variant_field)

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

  • WHERE path_to_variant_field IS NOT NULL

  • WHERE variant_column IS NULL

    où variant_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 des recherches prend en charge l’utilisation de la colonne VARIANT src mais pas le chemin d’accès au champ src:person.age dans cette colonne VARIANT.

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

Le service d’optimisation de la recherche peut optimiser les recherches par caractères génériques ou expressions régulières dans les colonnes semi-structurées (y compris les colonnes ARRAY, OBJECT et VARIANT) ou les champs de ces colonnes. Cela inclut les prédicats qui utilisent :

• [ NOT ] LIKE

• LIKE ANY

• LIKE ALL

• [ NOT ] ILIKE

• ILIKE ANY

• CONTAINS

• ENDSWITH

• STARTSWITH

• SPLIT_PART

• [ NOT ] RLIKE

• [ NOT ] REGEXP

• REGEXP_LIKE

Vous pouvez activer l’optimisation de la recherche de sous-chaîne pour une colonne ou pour plusieurs champs individuels au sein d’une colonne. Par exemple, l’instruction suivante permet d’optimiser la recherche de sous-chaîne pour un champ imbriqué dans une colonne.

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

Une fois le chemin à la recherche construit, la requête suivante peut être optimisée :

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

Cependant, les requêtes suivantes ne sont pas optimisées, car les filtres de la clause WHERE ne s’appliquent pas au champ spécifié lors de l’activation de l’optimisation de la recherche (col2:data.search).

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

Vous pouvez spécifier plusieurs champs à optimiser. Ici, l’optimisation de la recherche est activée pour deux champs spécifiques dans la colonne 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 champ donné, elle est activée pour tous les sous-champs. La deuxième instruction ALTER TABLE ci-dessous est redondante, car la première instruction permet d’optimiser la recherche pour l’ensemble du champ data , y compris le champ 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 champs imbriqués à n’importe quelle profondeur à l’intérieur de celle-ci.

Limites actuelles de la prise en charge pour les types de VARIANT¶

Actuellement, la prise en charge des types VARIANT dans le service d’optimisation des recherches présente les limitations suivantes :

• Les prédicats qui utilisent XMLGET ne sont pas pris en charge.

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

• Les prédicats où 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 qui contiennent des sous-éléments ne sont pas pris en charge.

Les limites actuelles du service d’optimisation de la recherche s’appliquent également à cette fonction.