Activation et désactivation de l’optimisation de la recherche

Pour activer l’optimisation de la recherche, utilisez un rôle disposant des privilèges nécessaires, puis activez-la pour une table entière ou des colonnes spécifiques à l’aide de la commande ALTER TABLEADD SEARCH OPTIMIZATION.

Privilèges de contrôle d’accès requis

Pour ajouter, configurer ou supprimer l’optimisation de la recherche d’une table, vous devez :

  • Avoir le privilège OWNERSHIP sur la table.

  • Avoir le privilège ADD SEARCH OPTIMIZATION sur le schéma qui contient la table. Pour accorder ce privilège :

    GRANT ADD SEARCH OPTIMIZATION ON SCHEMA <schema_name> TO ROLE <role>
    
    Copy

Pour utiliser le service d’optimisation de la recherche pour une requête, vous avez juste besoin du privilège SELECT sur la table.

Vous n’avez pas besoin de privilèges supplémentaires. Car SEARCH OPTIMIZATION est une propriété de table, elle est automatiquement détectée et utilisée (le cas échéant) lors de l’interrogation d’une table.

Configuration de l’optimisation de la recherche

Note

L’ajout d’une optimisation de la recherche à une table volumineuse (une table contenant au moins des téraoctets [TB] de données) peut entraîner une augmentation immédiate de la consommation de crédit sur une courte période.

Lorsque vous ajoutez une optimisation de la recherche à une table, le service de maintenance commence immédiatement à construire les chemins de recherche pour la table en arrière-plan. Si la table est volumineuse, le service de maintenance peut paralléliser massivement ce travail, ce qui peut entraîner une augmentation des coûts sur une courte période.

Avant d’ajouter l’optimisation de la recherche à une table volumineuse, obtenez une estimation de ces coûts afin de savoir à quoi vous attendre.

Lorsque vous activez l’optimisation de la recherche, vous avez le choix de l’activer pour une table entière ou pour des colonnes spécifiques du tableau.

  • L’activation de l’optimisation de la recherche pour une table entière permet d’effectuer des requêtes ponctuelles sur toutes les colonnes éligibles.

    Pour activer l’optimisation de la recherche pour une table entière, utilisez la commande ALTER TABLEADD SEARCH OPTIMIZATION sans la clause ON.

  • L’activation de l’optimisation de la recherche pour des colonnes spécifiques évite de consacrer des crédits à la création de chemins d’accès à la recherche pour des colonnes que vous n’utilisez pas souvent dans vos requêtes. Elle vous permet également de sélectionner d’autres types de requêtes à optimiser pour chaque colonne, ce qui peut encore améliorer les performances.

    Pour activer l’optimisation de la recherche pour des colonnes spécifiques, en spécifiant les types de requêtes à optimiser, utilisez la clause ON dans la commande ALTER TABLE… ADD SEARCH OPTIMIZATION. Dans la clause ON de ADD SEARCH OPTIMIZATION, vous indiquez quelles colonnes doivent être activées pour l’optimisation de la recherche. Lorsque vous activez l’optimisation de la recherche pour une colonne donnée, vous pouvez également spécifier une méthode de recherche (par exemple, EQUALITY pour les recherches d’égalité et IN, GEO pour les recherches GEOGRAPHY ou SUBSTRING pour les recherches de sous-chaînes). Vous pouvez activer plusieurs méthodes de recherche sur la même colonne.

En général, la meilleure pratique consiste à n’activer l’optimisation de la recherche que pour des colonnes spécifiques.

Les sections suivantes expliquent comment configurer l’optimisation de la recherche pour une table :

Après avoir configuré l’optimisation de la recherche, vous pouvez inspecter votre configuration pour vous assurer qu’elle est correcte.

Vous pouvez supprimer l’optimisation de la recherche pour des colonnes spécifiques ou des tables entières lorsque vous avez découvert que l’optimisation de la recherche n’apporte pas suffisamment d’avantages.

Configuration de l’optimisation de la recherche pour des colonnes spécifiques

Pour configurer l’optimisation de la recherche pour une colonne spécifique, utilisez la commande ALTER TABLEADD SEARCH OPTIMIZATION avec la clause ON.

Note

Lors de l’exécution de cette commande, utilisez un rôle qui a les privilèges permettant d’ajouter l’optimisation de la recherche à la table.

La clause ON spécifie que vous voulez configurer l’optimisation de la recherche pour des colonnes spécifiques. Pour plus de détails sur la syntaxe, voir la section sur ALTER TABLE … ADD SEARCH OPTIMIZATION.

Note

Si vous souhaitez simplement appliquer l’optimisation de la recherche pour l’égalité et les prédicats IN à toutes les colonnes applicables de la table, voir Activation de l’optimisation de la recherche pour une table entière.

Après avoir exécuté cette commande, vous pouvez vérifier que les colonnes ont été configurées pour l’optimisation de la recherche.

Les sections suivantes contiennent des exemples qui démontrent comment spécifier la configuration pour l’optimisation de la recherche :

Exemple : prise en charge des prédicats d’égalité et de IN pour des colonnes spécifiques

Pour optimiser les recherches avec des prédicats d’égalité pour les colonnes c1, c2 et c3 dans la table t1, exécutez l’instruction suivante :

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2, c3);
Copy

Vous pouvez également spécifier la même méthode de recherche plus d’une fois dans la clause ON :

-- This statement is equivalent to the previous statement.
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1), EQUALITY(c2, c3);
Copy

Exemple : prise en charge des prédicats d’égalité et de IN pour toutes les colonnes applicables

Pour optimiser les recherches avec des prédicats d’égalité pour toutes les colonnes applicables de la table, exécutez l’instruction suivante :

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(*);
Copy

Remarques :

Exemple : prise en charge de différents types de prédicats

Pour optimiser les recherches avec des prédicats d’égalité pour la colonne c1 et c2 et les recherches de sous-chaîne pour la colonne c3, exécutez l’instruction suivante :

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2), SUBSTRING(c3);
Copy

Exemple : prise en charge de différents prédicats sur la même colonne

Pour optimiser les recherches des prédicats d’égalité et des prédicats de sous-chaîne sur la même colonne, c1, exécutez l’instruction suivante :

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1), SUBSTRING(c1);
Copy

Exemple : prise en charge de l’égalité et de IN pour un champ dans un VARIANT

Pour optimiser les recherches avec des prédicats d’égalité sur le champ VARIANT uuid imbriqué dans le champ user de la colonne VARIANT c4, exécutez l’instruction suivante :

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

Exemple : prise en charge des fonctions géospatiales

Pour optimiser les recherches avec des prédicats qui utilisent des fonctions géospatiales avec des objets GEOGRAPHY dans la colonne c1 , exécutez l’instruction suivante :

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON GEO(c1);
Copy

Activation de l’optimisation de la recherche pour une table entière

Pour spécifier EQUALITY pour toutes les colonnes des types de données pris en charge (à l’exception de VARIANT et GEOGRAPHY), utilisez la commande ALTER TABLEADD SEARCH OPTIMIZATION sans la clause ON.

Note

Lors de l’exécution de cette commande, utilisez un rôle qui a les privilèges permettant d’ajouter l’optimisation de la recherche à la table.

Par exemple :

alter table test_table add search optimization;
Copy

Pour plus d’informations sur la syntaxe, consultez la section sur l’optimisation de la recherche dans ALTER TABLE.

Après avoir exécuté cette commande, vous pouvez vérifier que les colonnes ont été configurées pour l’optimisation de la recherche.

Effet sur les colonnes ajoutées ultérieurement

Après avoir exécuté la commande ALTER TABLE … ADD SEARCH OPTIMIZATION sans la clause ON, toutes les colonnes ajoutées ultérieurement à la table seront également configurées pour optimisation sur EQUALITY.

Cependant, si vous exécutez ALTER TABLE … { ADD | DROP } SEARCH OPTIMIZATION avec la clause ON sur la même table, toute colonne ajoutée ultérieurement à la table ne sera pas configurée automatiquement pour EQUALITY. Vous devez exécuter ALTER TABLE … ADD SEARCH OPTIMIZATION ON … pour configurer ces colonnes nouvellement ajoutées pour EQUALITY.

Vérifier qu’une table est configurée pour l’optimisation de la recherche

Pour vérifier que la table et ses colonnes ont été configurées pour l’optimisation de la recherche :

  1. Affichez la configuration de l’optimisation de la recherche pour la table et ses colonnes.

  2. Exécutez la commande SHOW TABLES pour vérifier que l’optimisation de la recherche a été ajoutée et pour déterminer la proportion de la table qui a été optimisée.

    Par exemple :

    SHOW TABLES LIKE '%test_table%';
    
    Copy

    Dans la sortie de cette commande :

    • Vérifiez que SEARCH_OPTIMIZATION est ON, ce qui indique que l’optimisation de la recherche a été ajoutée.

    • Vérifiez la valeur de SEARCH_OPTIMIZATION_PROGRESS. Cela spécifie le pourcentage de la table qui a été optimisé jusqu’à présent.

      Lorsque l’optimisation de la recherche est ajoutée pour la première fois à un tableau, les avantages en termes de performances n’apparaissent pas immédiatement. Le service d’optimisation de la recherche commence à remplir les données en arrière-plan. Les avantages apparaissent de plus en plus à mesure que la maintenance rattrape l’état actuel de la table.

      Avant d’exécuter une requête pour vérifier que l’optimisation de la recherche fonctionne, attendez que cela indique que la table a été entièrement optimisée.

  3. Exécutez une requête pour vérifier que l’optimisation de la recherche fonctionne.

    Notez que l’optimiseur Snowflake choisit automatiquement quand utiliser le service d’optimisation de la recherche pour une requête particulière. Les utilisateurs ne peuvent pas contrôler les requêtes pour lesquelles l’optimisation de la recherche est utilisée.

    Choisissez une requête que le service d’optimisation de la recherche est conçu pour optimiser. Voir Identifier les requêtes qui peuvent bénéficier de l’optimisation de la recherche.

  4. Sur l’UI Web, affichez le plan de requête pour cette requête et vérifiez que le nœud de requête « Accès optimisation recherche » fait partie du plan de requête.

Affichage de la configuration de l’optimisation de la recherche pour une table

Pour afficher la configuration de l’optimisation de la recherche pour une table, utilisez la commande DESCRIBE SEARCH OPTIMIZATION.

Par exemple, supposons que vous exécutez l’instruction suivante pour configurer l’optimisation de la recherche pour une colonne :

ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1);
Copy

L’exécution de DESCRIBE SEARCH OPTIMIZATION produit la sortie suivante :

DESCRIBE SEARCH OPTIMIZATION ON t1;

+---------------+----------+--------+------------------+--------+
| expression_id |  method  | target | target_data_type | active |
+---------------+----------+--------+------------------+--------+
| 1             | EQUALITY | C1     | NUMBER(38,0)     | true   |
+---------------+----------+--------+------------------+--------+
Copy

Suppression de l’optimisation de la recherche dans des colonnes spécifiques ou dans l’ensemble de la table

Vous pouvez supprimer la configuration de l’optimisation de la recherche pour des colonnes spécifiques, ou bien supprimer la propriété SEARCH OPTIMIZATION de l’ensemble de la table.

Suppression de l’optimisation de la recherche pour des colonnes spécifiques

Pour détruire la configuration de l’optimisation de la recherche pour des colonnes spécifiques, utilisez la commande suivante : commande ALTER TABLEDROP SEARCH OPTIMIZATION avec la clause ON.

Par exemple, supposons que l’exécution de la commande DESCRIBE SEARCH OPTIMIZATION imprime les expressions suivantes :

DESCRIBE SEARCH OPTIMIZATION ON t1;

+---------------+-----------+-----------+-------------------+--------+
| expression_id |  method   | target    | target_data_type  | active |
+---------------+-----------+-----------+-------------------+--------+
|             1 | EQUALITY  | C1        | NUMBER(38,0)      | true   |
|             2 | EQUALITY  | C2        | VARCHAR(16777216) | true   |
|             3 | EQUALITY  | C4        | NUMBER(38,0)      | true   |
|             4 | EQUALITY  | C5        | VARCHAR(16777216) | true   |
|             5 | EQUALITY  | V1        | VARIANT           | true   |
|             6 | SUBSTRING | C2        | VARCHAR(16777216) | true   |
|             7 | SUBSTRING | C5        | VARCHAR(16777216) | true   |
|             8 | GEO       | G1        | GEOGRAPHY         | true   |
|             9 | EQUALITY  | V1:"key1" | VARIANT           | true   |
|            10 | EQUALITY  | V1:"key2" | VARIANT           | true   |
+---------------+-----------+-----------+-------------------+--------+
Copy

Pour abandonner l’optimisation de la recherche des sous-chaînes sur la colonne c2, exécutez l’instruction suivante :

ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON SUBSTRING(c2);
Copy

Pour détruire toute optimisation de recherche pour toutes les méthodes sur la colonne c5, exécutez l’instruction suivante :

ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON c5;
Copy

Étant donné que la colonne c5 est configurée pour optimiser les recherches d’égalité et de sous-chaînes, l’instruction ci-dessus abandonne la configuration pour les recherches d’égalité et de sous-chaînes pour c5.

Pour abandonner l’optimisation de la recherche d’égalité sur la colonne c1 et pour abandonner la configuration spécifiée par l’expression IDs 6 et 8, exécutez l’instruction suivante :

ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON EQUALITY(c1), 6, 8;
Copy

Pour plus d’informations sur la syntaxe, consultez la section sur ALTER TABLE … DROP SEARCH OPTIMIZATION.

Suppression de l’optimisation de la recherche de la table

Pour supprimer la propriété SEARCH OPTIMIZATION d’une table :

  1. Basculez vers un rôle qui a les privilèges permettant de supprimer l’optimisation de la recherche de la table.

  2. Exécutez la commande ALTER TABLEDROP SEARCH OPTIMIZATION sans la clause ON :

    ALTER TABLE [IF EXISTS] <table_name> DROP SEARCH OPTIMIZATION;
    
    Copy

    Par exemple :

    alter table test_table drop search optimization;
    
    Copy

Pour plus d’informations, consultez la section sur ALTER TABLE … DROP SEARCH OPTIMIZATION.