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

• 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 des valeurs de types de données semi-structurées converties en TEXT¶

Le service d’optimisation de la recherche peut également améliorer les performances des recherches de points dans lesquelles les colonnes avec des types de données semi-structurées sont converties en TEXT et sont comparées à des constantes qui sont 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 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

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

    WHERE src:sender_info.ip_address = '123.123.123.123';
    

    Copy

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

    WHERE src:salesperson.name::TEXT = '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¶

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 (c’est-à-dire les colonnes VARIANT, OBJECT et ARRAY) ou les éléments de ces colonnes. Cela inclut les prédicats qui utilisent :

• LIKE

• LIKE ANY

• LIKE ALL

• ILIKE

• ILIKE ANY

• CONTAINS

• ENDSWITH

• STARTSWITH

• SPLIT_PART

• RLIKE

• REGEXP

• REGEXP_LIKE

Vous pouvez activer l’optimisation de la recherche par sous-chaînes pour une colonne ou pour plusieurs éléments individuels au sein d’une colonne. Par exemple, l’instruction suivante permet l’optimisation de la recherche de sous-chaînes pour un élément 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 à l’élément 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 éléments à optimiser. Ici, l’optimisation de la recherche est activée pour deux éléments spécifiques de 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 é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.

Pour un exemple permettant l’optimisation de la recherche FULL_TEXT sur une colonne VARIANT de la table car_sales et ses données (décrites dans Interrogation de données semi-structurées), voir 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 qui utilisent XMLGET ne sont pas pris en charge.

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

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