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

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

Pour améliorer les performances des requêtes de types de données structurées sur une table, utilisez la clause ON dans la commande ALTER TABLE … ADD SEARCH OPTIMIZATION pour des colonnes spécifiques ou des éléments de colonnes. Les requêtes portant sur les colonnes structurées ARRAY, OBJECT et MAP 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 structurées.

Par exemple :

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

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

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(map_column);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(map_column:user.uuid);

Les règles suivantes s’appliquent aux mots-clés que vous utilisez dans ces commandes ALTER TABLE … ADD SEARCH OPTIMIZATION :

• Vous pouvez utiliser le mot-clé EQUALITY avec un élément interne ou la colonne elle-même.

• Vous pouvez utiliser le mot clé SUBSTRING uniquement avec des éléments internes qui ont des types de données chaîne de texte.

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 structurés¶

Le service d’optimisation de la recherche peut améliorer les performances des recherches ponctuelles de données 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 les fonctions de conversion suivantes :

• CAST et l’opérateur ::

• TRY_CAST

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

Le service d’optimisation de la recherche peut optimiser les recherches ponctuelles avec les types de prédicats indiqués dans la liste suivante. Dans les exemples, src est la colonne avec un type de données structurées, et path_to_element est un chemin vers un élément de la colonne avec un type de données 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 prédicats suivants :

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

    WHERE src:person.age = 42;
    

    Copy

  • Conversion explicite d’un élément OBJECT ou MAP 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 OBJECT ou MAP avec une constante VARCHAR sans convertir explicitement l’élément.

    WHERE src:sender_info.ip_address = '123.123.123.123';
    

    Copy

  • Conversion explicite d’un élément OBJECT ou MAP en VARCHAR :

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

    Copy

  • Conversion explicite d’un élément OBJECT ou MAP en DATE :

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

    Copy

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

  • Mise en correspondance d’un élément ARRAY avec une valeur d’un type pris en charge, avec ou sans conversion explicite :

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

    Copy

  • Mise en correspondance avec un élément OBJECT ou MAP avec une valeur d’un type pris en charge, avec ou sans conversion explicite :

    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édicats qui utilisent les fonctions ARRAY, telles que les prédicats suivants :

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

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

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

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

    Copy

    Dans cet exemple, le tableau se trouve dans une colonne ARRAY :

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

    Copy

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

  • WHERE IS_NULL_VALUE(path_to_element)

    Note

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

  • WHERE path_to_element IS NOT NULL

  • WHERE structured_column IS NULL

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

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

Recherche par sous-chaîne dans des types structurés¶

Vous ne pouvez activer la recherche de sous-chaînes que si l’élément structuré cible est un type de données chaîne de texte.

Par exemple, considérez la table suivante :

CREATE TABLE t(
  col OBJECT(
    a INTEGER,
    b STRING,
    c MAP(INTEGER, STRING),
    d ARRAY(STRING)
  )
);

Pour cette table, optimisation de la recherche pour la recherche SUBSTRING peut être ajoutée sur les éléments structurés cibles suivants :

• col:b car son type est STRING.

• col:c[value] — par exemple, col:c[0], col:c[100] — si les valeurs sont des types de chaînes de texte.

Pour cette table, l’optimisation de la recherche pour la recherche SUBSTRING ne peut pas être ajoutée sur les éléments structurés cibles suivants :

• col car son type est OBJECT structuré.

• col:a car son type est INTEGER.

• col:c car son type est MAP.

• col:d car son type est ARRAY.

Le service d’optimisation de la recherche peut optimiser les prédicats qui utilisent les fonctions suivantes :

• 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 d’accès à 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. Dans l’exemple suivant, 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 non imbriqués d’un type de chaîne de texte. L’optimisation de la recherche n’est pas activée pour les éléments imbriqués ou les éléments de types de chaînes non texte.

Prise en charge de l’évolution du schéma¶

Le schéma des colonnes structurées peut évoluer au fil du temps. Pour plus d’informations sur l’évolution des schémas, voir ALTER ICEBERG TABLE … ALTER COLUMN … SET DATA TYPE (types structurés).

Dans le cadre d’une seule opération d’évolution du schéma, les modifications suivantes peuvent survenir :

• Élargissement du type

• Réorganisation des éléments

• Ajout d’éléments

• Suppression d’éléments

• Renommage d’éléments

Le service d’optimisation de la recherche n’est pas invalidé dans le cadre de l’opération d’évolution du schéma. Au lieu de cela, le service d’optimisation de la recherche gère les opérations de la manière suivante :

Élargissement du type (par exemple, INT en NUMBER)

Les chemins d’accès à l’optimisation de la recherche ne sont pas affectés.

Ajout d’éléments

Les éléments nouvellement ajoutés sont automatiquement reflétés dans les chemins d’accès à l’optimisation de la recherche existants.

Suppression d’éléments

Lorsque des éléments sont supprimés d’une colonne structurée, le service d’optimisation de la recherche supprime automatiquement les chemins d’accès qui sont préfixés par l’élément supprimé.

Par exemple, créez une table avec une colonne de type OBJECT, puis insérez les données :

CREATE OR REPLACE TABLE test_struct (
  a OBJECT(
    b INTEGER,
    c OBJECT(
      d STRING,
      e VARIANT
      )
  )
);

INSERT INTO test_struct (a) SELECT
  {
    'b': 100,
    'c': {
        'd': 'value1',
        'e': 'value2'
  }
  }::OBJECT(
    b INTEGER,
    c OBJECT(
        d STRING,
        e VARIANT
    )
);

Copy

Pour afficher les données, interrogez la table :

SELECT * FROM test_struct;

Copy

+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

L’instruction suivante supprime l’élément c de l’objet :

ALTER TABLE test_struct ALTER COLUMN a
  SET DATA TYPE OBJECT(
    b INTEGER);

Copy

Lorsque cette instruction est exécutée, les chemins d’accès à a, a:c, a:c:d et a:c:e sont supprimés.

Renommage d’éléments

Lorsqu’un élément est renommé, le service d’optimisation de la recherche supprime automatiquement les chemins d’accès préfixés par l’élément renommé et les rajoute avec le chemin nouvellement nommé. Cette opération entraîne des coûts de maintenance supplémentaires pour traiter le chemin nouvellement ajouté dans le service d’optimisation de la recherche.

Par exemple, créez une table avec une colonne de type OBJECT, puis insérez les données :

CREATE OR REPLACE TABLE test_struct (
  a OBJECT(
    b INTEGER,
    c OBJECT(
      d STRING,
      e VARIANT
      )
  )
);

INSERT INTO test_struct (a) SELECT
  {
    'b': 100,
    'c': {
        'd': 'value1',
        'e': 'value2'
  }
  }::OBJECT(
    b INTEGER,
    c OBJECT(
        d STRING,
        e VARIANT
    )
);

Copy

Pour afficher les données, interrogez la table :

SELECT * FROM test_struct;

Copy

+--------------------+
| A                  |
|--------------------|
| {                  |
|   "b": 100,        |
|   "c": {           |
|     "d": "value1", |
|     "e": "value2"  |
|   }                |
| }                  |
+--------------------+

L’instruction suivante renomme l’élément c, c_new, dans l’objet :

ALTER TABLE test_struct ALTER COLUMN a
  SET DATA TYPE OBJECT(
    b INTEGER,
    c_new OBJECT(
      d STRING,
      e VARIANT
    )
  ) RENAME FIELDS;

Copy

Les chemins d’accès à a, a:c, a:c:d, a:c:e sont supprimés et rajoutés comme a, a:c_new, a:c_new:d, a:c_new:e.

Réorganisation des éléments

Les chemins d’accès à l’optimisation de la recherche ne sont pas affectés.

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

La prise en charge des types 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.

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

• Les prédicats qui utilisent la fonction MAP_CONTAINS_KEY ne sont pas pris en charge.

Les limites actuelles du service d’optimisation de la recherche s’appliquent également aux types structurés.