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 TABLE… ADD 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>
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 besoin d’aucun privilège supplémentaire. 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 TABLE… ADD 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 :
Configuration de l’optimisation de la recherche pour des colonnes spécifiques
Activation de l’optimisation de la recherche pour une table entière
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 TABLE … ADD 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 : optimisation de la recherche en texte intégral sur des colonnes spécifiques
Exemple : prise en charge des prédicats d’égalité et de IN pour des colonnes spécifiques
Exemple : prise en charge des prédicats d’égalité et de IN pour toutes les colonnes applicables
Exemple : prise en charge de différents prédicats sur la même colonne
Exemple : prise en charge de l’égalité et de IN pour un champ dans un VARIANT
Exemple : optimisation de la recherche en texte intégral sur des colonnes spécifiques¶
Vous pouvez effectuer des recherches de texte en utilisant les fonctions SEARCH et SEARCH_IP. Pour améliorer les performances d’exécution des requêtes lorsque ces fonctions sont utilisées, activez l’optimisation de la recherche FULL_TEXT. Vous pouvez activer l’optimisation de la recherche FULL_TEXT sur une table en utilisant différents sous-ensembles de colonnes de la table et différents analyseurs de texte. Pour plus d’informations sur le comportement des différents analyseurs, voir Comment les termes de recherche sont tokenisés.
Activez l’optimisation de la recherche FULL_TEXT sur un ensemble de colonnes dans une table en utilisant la syntaxe suivante.
ALTER TABLE <name> ADD SEARCH OPTIMIZATION
ON FULL_TEXT( { * | <col1> [ , <col2>, ... ] } [ , ANALYZER => '<analyzer_name>' ]);
Les colonnes que vous spécifiez doivent être les colonnes VARCHAR ou VARIANT. Les colonnes avec d’autres types de données ne sont pas prises en charge. En plus de l’ensemble de colonnes VARIANT, vous pouvez spécifier des chemins individuels <label-traversing_semistructured_data> aux champs dans ces colonnes.
Vous pouvez spécifier le caractère astérisque générique (*) au lieu d’une liste de colonnes. Dans ce cas, l’optimisation est automatiquement activée sur toutes les colonnes VARCHAR et VARIANT dans la table. Si des colonnes ARRAY ou OBJECT existent dans la table, l’optimisation n’est pas activée sur ces colonnes.
Si spécifié, l’argument ANALYZER => 'analyzer_name' doit être l’un des choix documentés pour la fonction SEARCH. Si vous ne spécifiez pas d’analyseur, DEFAULT_ANALYZER est utilisé.
Note
Pour l’exécution de la requête avec la fonction SEARCH à optimiser, l’analyseur spécifié pour l’optimisation de la recherche dans la commande ALTER TABLE doit être la même que l’analyseur spécifié dans l’appel de fonction SEARCH. Si les analyseurs ne correspondent pas, le chemin d’accès à la recherche ne sera pas sélectionné.
Cet exemple permet une optimisation de la recherche FULL_TEXT sur trois colonnes VARCHAR qui pourraient être les cibles d’une requête SEARCH.
ALTER TABLE lines ADD SEARCH OPTIMIZATION
ON FULL_TEXT(play, character, line);
Pour décrire la configuration d’optimisation de la recherche pour cette table, exécutez la commande suivante :
DESCRIBE SEARCH OPTIMIZATION ON lines;
+---------------+----------------------------+-----------+------------------+--------+
| expression_id | method | target | target_data_type | active |
|---------------+----------------------------+-----------+------------------+--------|
| 1 | FULL_TEXT DEFAULT_ANALYZER | PLAY | VARCHAR(50) | true |
| 2 | FULL_TEXT DEFAULT_ANALYZER | CHARACTER | VARCHAR(30) | true |
| 3 | FULL_TEXT DEFAULT_ANALYZER | LINE | VARCHAR(2000) | true |
+---------------+----------------------------+-----------+------------------+--------+
Pour plus d’informations, voir Affichage de la configuration de l’optimisation de la recherche pour une table.
Cet exemple permet l’optimisation de la recherche FULL_TEXT sur une colonne VARCHAR qui pourrait être la cible d’une requête SEARCH_IP.
ALTER TABLE ipt ADD SEARCH OPTIMIZATION ON FULL_TEXT(ip1, ANALYZER => 'ENTITY_ANALYZER');
Pour supprimer la configuration d’optimisation de la recherche, exécutez l’une des commandes suivantes :
ALTER TABLE lines DROP SEARCH OPTIMIZATION
ON FULL_TEXT(play, character, line);
ALTER TABLE lines DROP SEARCH OPTIMIZATION
ON play, character, line;
ALTER TABLE lines DROP SEARCH OPTIMIZATION
ON 1, 2, 3;
Dans la troisième commande ALTER TABLE. .. DROP SEARCH OPTIMIZATION, 1, 2, 3 fait référence aux IDs de l’expression retournés par la commande DESCRIBE.
Vous pouvez également modifier une configuration de l’optimisation de la recherche FULL_TEXT en supprimant un sous-ensemble des colonnes (par ID de nom ou d’expression). Pour plus d’informations, voir Suppression de l’optimisation de la recherche dans des colonnes spécifiques ou dans l’ensemble de la table.
Pour plus d’exemples qui activent et suppriment l’optimisation de la recherche FULL_TEXT, voir Exemples d’optimisation de la recherche ADD (et DROP) FULL_TEXT.
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);
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);
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(*);
Remarques :
Comme expliqué dans la description de la syntaxe de la méthode de recherche et de la cible, pour une méthode donnée, vous ne pouvez pas spécifier à la fois un astérisque et des colonnes spécifiques.
Bien que l’omission de la clause ON configure également l’optimisation de la recherche pour les prédicats d’égalité et de IN sur toutes les colonnes applicables de la table, il existe des différences entre la spécification et l’omission de la clause ON. Voir Activation de l’optimisation de la recherche pour une table entière.
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);
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);
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);
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);
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 TABLE … ADD 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;
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 :
Affichez la configuration de l’optimisation de la recherche pour la table et ses colonnes.
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%';
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.
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.
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);
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 |
+---------------+----------+--------+------------------+--------+
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 TABLE … DROP 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 |
+---------------+-----------+-----------+-------------------+--------+
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);
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;
É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;
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 :
Basculez vers un rôle qui a les privilèges permettant de supprimer l’optimisation de la recherche de la table.
Exécutez la commande ALTER TABLE … DROP SEARCH OPTIMIZATION sans la clause ON :
ALTER TABLE [IF EXISTS] <table_name> DROP SEARCH OPTIMIZATION;
Par exemple :
alter table test_table drop search optimization;
Pour plus d’informations, consultez la section sur ALTER TABLE … DROP SEARCH OPTIMIZATION.