Utilisation du service d’optimisation de la recherche¶

Comment fonctionne le service d’optimisation de la recherche ?¶

Pour améliorer les performances des recherches ponctuelles, le service d’optimisation de la recherche crée et maintient une structure de données persistante appelée chemin d’accès à la recherche. Le chemin d’accès à la recherche permet de savoir quelles valeurs des colonnes de la table peuvent être trouvées dans chacune de ses micro-partitions, ce qui permet d’en ignorer certaines lors de l’analyse de la table.

Un service de maintenance est responsable de la création et de la maintenance du chemin d’accès de recherche :

• Lorsque vous configurez l’optimisation de recherche pour une table, le service de maintenance crée et remplit le chemin d’accès à la recherche avec les données nécessaires pour effectuer les recherches.

  Le processus de remplissage des données peut prendre beaucoup de temps, selon la taille de la table. Le service de maintenance effectue ce travail en arrière-plan et ne bloque aucune opération simultanée sur la table.

• Lorsque les données de la table sont mises à jour (par exemple, en chargeant de nouveaux ensembles de données ou via des opérations DML), le service de maintenance met automatiquement à jour le chemin d’accès de recherche pour refléter les modifications apportées aux données.

  Si les requêtes sont exécutées alors que le chemin d’accès à la recherche n’a pas encore été mis à jour, les requêtes peuvent s’exécuter plus lentement, mais renvoient toujours des résultats à jour.

L’état d’avancement du service de maintenance de chaque table apparaît dans la colonne search_optimization_progress de la sortie de SHOW TABLES. Avant de mesurer l’amélioration des performances de l’optimisation de la recherche sur une table nouvellement optimisée, attendez que cette colonne montre que la table a été entièrement optimisée.

Ce chemin d’accès de recherche et le service de maintenance sont transparents pour l’utilisateur. Vous n’avez pas besoin de créer un entrepôt pour le service qui gère le chemin d’accès à la recherche.

Cependant, notez qu’il y a un coût pour le stockage et les ressources de calcul pour ce service. Pour plus de détails, reportez-vous à Gestion des coûts du service d’optimisation de la recherche (dans cette rubrique).

Prise en compte d’autres solutions pour optimiser les performances des requêtes¶

Le service d’optimisation de la recherche est l’un des nombreux moyens d’optimiser les performances des requêtes. Les techniques liées comprennent :

• Accélération de requête.

• Clustering d’une table.

• La création d’une ou plusieurs vues matérialisées (en cluster ou non).

Chacune d’entre elles présente des avantages différents, comme le montre le tableau suivant :

Le tableau suivant montre lesquelles de ces trois optimisations ont des coûts de stockage ou de calcul :

1

Le processus de reclustering peut augmenter la taille du stockage Fail-safe en raison de la réécriture des partitions existantes en nouvelles partitions. (Notez que ce processus n’introduit pas de nouvelles lignes. Cela ne fait que réorganiser les lignes existantes.) Pour plus de détails, voir Conséquences du reclustering sur les crédits et le stockage.

Quels privilèges de contrôle d’accès sont nécessaires pour le service d’optimisation de la recherche ?¶

Pour ajouter, configurer ou supprimer l’optimisation de la recherche d’une table, vous devez disposer des privilèges suivants :

• Vous devez avoir le privilège OWNERSHIP sur la table.

• Vous devez avoir le privilège ADD SEARCH OPTIMIZATION sur le schéma qui contient la table.

  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.

Égalité ou prédicats IN¶

Le service d’optimisation de la recherche peut améliorer les performances des requêtes qui utilisent les éléments suivants :

• Prédicats d’égalité (par exemple : <nom_colonne> = <constante>).

• Prédicats qui utilisent IN (voir exemple).

Sous-chaînes et expressions régulières¶

Le service d’optimisation de la recherche peut améliorer les performances des requêtes dont les prédicats recherchent des sous-chaînes ou utilisent des expressions régulières dans du texte ou des données semi-structurées. (Pour plus de détails sur le fonctionnement de la recherche par sous-chaîne avec des données semi-structurées, voir Recherche par sous-chaîne dans les types VARIANT.)

Cette capacité inclut les prédicats qui utilisent :

• LIKE

• LIKE ANY

• LIKE ALL

• ILIKE

• ILIKE ANY

• CONTAINS

• ENDSWITH

• STARTSWITH

• SPLIT_PART (dans les prédicats d’égalité)

• RLIKE

• REGEXP

• REGEXP_LIKE

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(mycol);

Le service d’optimisation des recherches peut améliorer les performances lors de la recherche de sous-chaînes de 5 caractères ou plus. (Des sous-chaînes plus sélectives peuvent donner lieu à de meilleures performances). Le service d’optimisation de la recherche n’utilise pas les chemins d’accès à la recherche pour le prédicat suivant, car la sous-chaîne fait moins de cinq caractères :

LIKE '%TEST%'

Copy

Pour le prédicat suivant, le service d’optimisation de la recherche peut optimiser cette requête, en utilisant les chemins d’accès à la recherche pour rechercher les sous-chaînes de SEARCH et OPTIMIZED. Cependant, les chemins d’accès de recherche ne sont pas utilisés pour IS car la sous-chaîne est plus courte que 5 caractères.

LIKE '%SEARCH%IS%OPTIMIZED%'

Copy

Pour les requêtes qui utilisent RLIKE, REGEXP, et REGEXP_LIKE sur du texte :

• L’argument subject doit être une colonne TEXT dans une table pour laquelle l’optimisation de la recherche est activée.

• L’argument pattern doit être une constante de chaîne.

Pour les expressions régulières, le service d’optimisation des recherches fonctionne mieux lorsque :

• Le motif contient au moins une sous-chaîne littérale de 5 caractères ou plus.

• Le motif spécifie que la sous-chaîne doit apparaître au moins une fois.

Par exemple, le motif suivant spécifie que string doit apparaître une ou plusieurs fois dans le sujet :

RLIKE '(string)+'

Copy

Le service d’optimisation des recherches peut améliorer les performances des requêtes comportant les motifs suivants, car chaque prédicat spécifie qu’une sous-chaîne de 5 caractères ou plus doit apparaître au moins une fois. (Notez que le premier exemple utilise une constante de chaîne entre dollars pour éviter l’échappement des caractères de barre oblique inverse).

RLIKE $$.*email=[\w\.]+@snowflake\.com.*$$

Copy

RLIKE '.*country=(Germany|France|Spain).*'

Copy

RLIKE '.*phone=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'

Copy

En revanche, le service d’optimisation de la recherche n’utilise pas les chemins d’accès à la recherche pour les requêtes présentant les schémas suivants :

• Motifs sans aucune sous-chaîne :

  RLIKE '.*[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
  

  Copy

• Motifs qui ne contiennent que des sous-chaînes inférieures à 5 caractères :

  RLIKE '.*tel=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
  

  Copy

• Les motifs qui utilisent l’opérateur d’alternance où une option est une sous-chaîne plus courte que 5 caractères :

  RLIKE '.*(option1|option2|opt3).*'
  

  Copy

• Motifs dans lesquels la sous-chaîne est facultative :

  RLIKE '.*[a-zA-z]+(string)?[0-9]+.*'
  

  Copy

Même lorsque les sous-chaînes littérales sont plus courtes que 5 caractères, le service d’optimisation de la recherche peut encore améliorer les performances de la requête si l’expansion de l’expression régulière produit un littéral de sous-chaîne de 5 caractères ou plus.

Par exemple, considérons le motif suivant :

.*st=(CA|AZ|NV).*(-->){2,4}.*

Copy

Dans cet exemple :

• Bien que les littéraux des sous-chaînes (par exemple, st=, CA, etc.) soient plus courts que 5 caractères, le service d’optimisation des recherches reconnaît que la sous-chaîne st=CA, st=AZ ou st=NV (chacune d’entre elles comptant 5 caractères) doit apparaître dans le texte.

• De même, même si la sous-chaîne littérale --> est plus courte que 5 caractères, le service d’optimisation des recherches détermine que la sous-chaîne -->--> (qui est plus longue que 5 caractères) doit apparaître dans le texte.

Le service d’optimisation de la recherche peut utiliser les chemins d’accès à la recherche pour faire correspondre ces sous-chaînes, ce qui peut améliorer les performances de la requête.

Champs dans les colonnes VARIANT¶

Le service d’optimisation de la recherche peut améliorer les performances des recherches ponctuelles et des requêtes de sous-chaînes sur des données semi-structurées dans des tables Snowflake (données dans les colonnes VARIANT, OBJECT et ARRAY).

Lorsque la prise en charge de VARIANT du service d’optimisation des recherches est configuré pour les colonnes d’une table, le service d’optimisation des recherches inclut automatiquement les colonnes VARIANT, OBJECT et ARRAY dans un chemin d’accès de recherche. Cela s’applique même aux colonnes où la structure est profondément imbriquée et où la structure change fréquemment. Vous pouvez également activer l’optimisation de la recherche pour des champs spécifiques dans une colonne semi-structurée.

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

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON SUBSTRING(c4:user.uuid);

Les sections suivantes fournissent plus de détails sur cette prise en charge :

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

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

• Prédicats pris en charge pour les recherches ponctuelles sur les types VARIANT

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

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

create or replace TABLE test_table
(
    ID INTEGER,
    SRC VARIANT
);

insert into test_table select 1, to_variant('true'::boolean);          -- BOOLEAN
insert into test_table select 2, to_variant('2020-01-09'::date);       -- DATE
insert into test_table select 3, to_variant('01:02:03.899213'::time);  -- TIME

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 = '01:02:03.899213';

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

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

SELECT * FROM test_table WHERE col2:name LIKE '%john%doe%';
SELECT * FROM test_table WHERE col2 LIKE '%hello%world%';

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

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

Fonctions géospatiales¶

Le service d’optimisation des recherches peut améliorer les performances des requêtes dont les prédicats utilisent des fonctions géospatiales avec des objets GEOGRAPHY.

ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON GEO(mygeocol);

Les sections suivantes fournissent plus de détails :

• Prédicats pris en charge avec des fonctions géospatiales

• Autres remarques sur les performances

• Exemples qui utilisent des fonctions géospatiales

CREATE OR REPLACE TABLE geospatial_table (id NUMBER, g1 GEOGRAPHY);
INSERT INTO geospatial_table VALUES
  (1, 'POINT(-122.35 37.55)'),
  (2, 'LINESTRING(-124.20 42.00, -120.01 41.99)'),
  (3, 'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
ALTER TABLE geospatial_table ADD SEARCH OPTIMIZATION ON GEO(g1);

SELECT id FROM geospatial_table WHERE
  ST_INTERSECTS(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'));

...
  ST_INTERSECTS(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)

...
  ST_CONTAINS(
    TO_GEOGRAPHY('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
    g1)

...
  ST_CONTAINS(
    g1,
    TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'))

...
  ST_WITHIN(
   TO_GEOGRAPHY('{"type" : "MultiPoint","coordinates" : [[-122.30, 37.55], [-122.20, 47.61]]}'),
   g1)

...
  ST_WITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))

...
  ST_COVERS(
    TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'),
    g1)

...
  ST_COVERS(
    g1,
    TO_GEOGRAPHY('POINT(0 0)'))

...
  ST_COVEREDBY(
    TO_GEOGRAPHY('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'),
    g1)

...
  ST_COVEREDBY(
    g1,
    TO_GEOGRAPHY('POINT(-122.35 37.55)'))

...
  ST_DWITHIN(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1,
    100000)

...
  ST_DWITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    100000)

...
  ST_INTERSECTS(
    g1,
    ST_GEOGRAPHYFROMWKT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))

...
  ST_INTERSECTS(
    ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)

...
  ST_CONTAINS(
    ST_GEOGRAPHYFROMEWKT('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
    g1)

...
  ST_WITHIN(
    ST_GEOGRAPHYFROMWKB('01010000006666666666965EC06666666666C64240'),
    g1)

...
  ST_COVERS(
    g1,
    ST_MAKEPOINT(0.2, 0.8))

...
  ST_INTERSECTS(
    g1,
    ST_MAKELINE(
      TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'),
      TO_GEOGRAPHY('POINT(0.8 0.2)')))

...
  ST_INTERSECTS(
    ST_POLYGON(
      TO_GEOGRAPHY('SRID=4326;LINESTRING(0.0 0.0, 1.0 0.0, 1.0 2.0, 0.0 2.0, 0.0 0.0)')),
    g1)

...
  ST_WITHIN(
    g1,
    TRY_TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'))

...
  ST_COVERS(
    g1,
    ST_GEOGPOINTFROMGEOHASH('s00'))

Conjonctions de prédicats pris en charge (AND)¶

Pour les requêtes qui utilisent des conjonctions de prédicats (c’est-à-dire AND), les performances des requêtes peuvent être améliorées par l’optimisation de la recherche si l’un des prédicats respecte les conditions ci-dessus.

Par exemple, supposons qu’une requête a :

where condition_x and condition_y

L’optimisation de la recherche peut améliorer les performances si l’une des conditions renvoie séparément quelques lignes (c’est-à-dire que condition_x renvoie quelques lignes ou condition_y renvoie quelques lignes).

Si condition_x renvoie quelques lignes mais que condition_y renvoie de nombreuses lignes, les performances de la requête peuvent bénéficier de l’optimisation de la recherche.

Voir ces exemples supplémentaires.

Disjonctions de prédicats pris en charge (OR)¶

Pour les requêtes qui utilisent des disjonctions de prédicats (c’est-à-dire OR), les performances des requêtes peuvent être améliorées par l’optimisation de la recherche si tous les prédicats respectent les conditions ci-dessus.

Par exemple, supposons qu’une requête a :

where condition_x or condition_y

L’optimisation de la recherche peut améliorer les performances si chaque condition renvoie séparément quelques lignes (par exemple, condition_x renvoie quelques lignes et condition_y renvoie quelques lignes).

Si condition_x renvoie quelques lignes mais que condition_y renvoie de nombreuses lignes, les performances de la requête ne bénéficient pas de l’optimisation de la recherche.

Dans le cas de disjonctions, chaque prédicat isolé n’est pas déterminant dans la requête. Les autres prédicats doivent être évalués avant de pouvoir déterminer si l’optimisation de la recherche peut améliorer les performances.

Jointures¶

Le service d’optimisation de la recherche n’améliore pas directement les performances des jointures. Cependant, il peut améliorer les performances de filtrage des lignes d’une table avant la jointure, si l’optimisation de la recherche est activée dans la table et si le prédicat est sélectif.

L’optimisation de la recherche n’a pas besoin d’être activée dans les deux tables. La décision d’utiliser l’optimisation de la recherche est prise pour chaque table indépendamment.

Vues¶

Le service d’optimisation de la recherche peut indirectement améliorer les performances des vues (y compris les vues sécurisées). Si l’optimisation de la recherche est activée dans une table de base pour une vue et si la requête utilise un prédicat sélectif pour cette table, le service d’optimisation de la recherche peut améliorer les performances lors du filtrage des lignes.

L’optimisation de la recherche n’a pas besoin d’être activée sur toutes les tables de la vue. L’optimisation de la recherche est effectuée sur chaque table indépendamment.

Tables avec politiques de masquage et politiques d’accès aux lignes¶

Le service d’optimisation de la recherche peut améliorer les performances des requêtes pour les tables qui utilisent des politiques de masquage et des politiques d’accès aux lignes.

Requêtes non prises en charge par le service d’optimisation de la recherche¶

Le service d’optimisation de la recherche ne prend pas en charge les éléments suivants :

• Tables externes.

• Vues matérialisées.

• Colonnes définies avec une clause COLLATE.

• Concaténation de colonnes.

• Expressions analytiques.

• Correspond aux colonnes de la table (sauf pour les nombres à virgule fixe convertis en chaînes de caractères).

  Bien que l’optimisation de la recherche prenne en charge les prédicats avec des conversions implicites et explicites sur les valeurs constantes, elle ne prend pas en charge les prédicats qui convertissent des valeurs dans la colonne de table réelle (sauf pour les conversions de INTEGER et NUMBER à VARCHAR).

  Par exemple, les prédicats suivants sont pris en charge, car ils utilisent des conversions implicites et explicites sur des valeurs constantes (et non des valeurs dans la colonne du tableau) :

  -- Supported predicate
  -- (where the string '2020-01-01' is implicitly cast to a date)
  WHERE timestamp1 = '2020-01-01';
  
  -- Supported predicate
  -- (where the string '2020-01-01' is explicitly cast to a date)
  WHERE timestamp1 = '2020-01-01'::date;
  

  Copy

  Le prédicat suivant n’est pas pris en charge, car il utilise une conversion sur des valeurs dans la colonne de table :

  -- Unsupported predicate
  -- (where values in a VARCHAR column are cast to DATE)
  WHERE to_date(varchar_column) = '2020-01-01';
  

  Copy

  Le service d’optimisation de la recherche prend en compte les valeurs de colonne d’origine et non les valeurs après la conversion. Par conséquent, le service d’optimisation de la recherche n’est pas utilisé pour les requêtes avec ces prédicats.

Comme nous l’avons mentionné précédemment, l’exception à cette règle est la conversion de valeurs NUMBER ou INTEGER en des valeurs VARCHAR dans la colonne de la table. Le service d’optimisation de la recherche prend en charge ce type de prédicat :

-- Supported predicate
-- (where values in a numeric column are cast to a string)
WHERE cast(numeric_column as varchar) = '2'

Copy

L’optimisation de la recherche n’améliore pas les performances des requêtes qui utilisent Time Travel, car l’optimisation de la recherche ne fonctionne que sur les données actives.

Configuration de l’optimisation des recherches 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.

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.

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

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

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

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

• Exemple : prise en charge des fonctions géospatiales

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

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

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

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

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

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

Ajout et gestion de l’optimisation de la recherche pour l’ensemble de la table¶

Si vous souhaitez simplement 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.

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.

Vérifier que la table est configurée pour l’optimisation des recherches¶

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 Identification des tables et des colonnes qui bénéficient 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.

Modification de la table¶

Un chemin d’accès de recherche devient non valide si la valeur par défaut d’une colonne est modifiée.

Pour utiliser à nouveau l’optimisation de la recherche après qu’un chemin d’accès de recherche est devenu non valide, vous devez supprimer la propriété SEARCH OPTIMIZATION et ajouter à nouveau la propriété SEARCH OPTIMIZATION à la table.

Un chemin d’accès à la recherche reste valable si vous ajoutez, détruisez ou renommez une colonne :

• Si vous avez activé l’optimisation de la recherche pour une toute une table sans spécifier de colonnes spécifiques, lorsque vous ajoutez une colonne à une table, la nouvelle colonne est automatiquement ajoutée au chemin d’accès à la recherche. Toutefois, si vous avez utilisé la clause ON lors de l’activation de l’optimisation de la recherche pour une colonne, les nouvelles colonnes ne sont pas ajoutées automatiquement.

• Lorsque vous détruisez une colonne d’une table, la colonne détruite est automatiquement retirée du chemin d’accès à la recherche.

• Le changement de nom d’une colonne ne nécessite aucune modification du chemin d’accès à la recherche.

Si vous détruisez une table, la propriété SEARCH OPTIMIZATION et les chemins d’accès à la recherche sont également détruits. Remarques importantes :

• Le rétablissement de la table rétablit immédiatement l’optimisation de la recherche en tant que propriété de la table.

• Lorsque vous détruisez une table, le chemin d’accès à la recherche a la même période de conservation des données que la table.

Si vous détruisez la propriété SEARCH OPTIMIZATION de la table, le chemin d’accès à la recherche est supprimé. Lorsque vous rajoutez la propriété SEARCH OPTIMIZATION à la table, le service de maintenance doit recréer le chemin d’accès de recherche. (Il est impossible de rétablir la propriété).

Clonage de la table, du schéma ou de la base de données¶

Si vous clonez une table, un schéma ou une base de données, la propriété SEARCH OPTIMIZATION et les chemins d’accès à la recherche de chaque table sont également clonés. (Le clonage d’une table, d’un schéma ou d’une base de données crée un clone à zéro copie de chaque table et ses chemins d’accès de recherche correspondants.)

Notez que si vous utilisez CREATE TABLE … LIKE pour créer une nouvelle table vide avec les mêmes colonnes que la table d’origine, la propriété SEARCH OPTIMIZATION n’est pas copiée dans la nouvelle table.

Travailler avec des tables dans une base de données secondaire (aide à la réplication d’une base de données)¶

Si la propriété SEARCH OPTIMIZATION est activée dans une table de la base de données principale, la propriété est répliquée dans la table correspondante de la base de données secondaire.

Les chemins d’accès de recherche dans la base de données secondaire ne sont pas répliqués, mais reconstruits automatiquement. Il est à noter que ce processus entraîne les mêmes types de coûts que ceux décrits dans Gestion des coûts du service d’optimisation de la recherche.

Partage de la table¶

Les fournisseurs de données peuvent utiliser Secure Data Sharing pour partager des tables pour lesquelles l’optimisation de la recherche est activée.

Lors de l’interrogation de tables partagées, les consommateurs de données peuvent bénéficier des améliorations de performances apportées par le service d’optimisation de la recherche.

Estimation des coûts¶

Pour estimer le coût de l’ajout d’une optimisation de recherche à une table et configurer des colonnes spécifiques pour l’optimisation de la recherche, utilisez la fonction SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS.

En général, les coûts sont proportionnels aux facteurs suivants :

• Le nombre de colonnes sur lesquelles la fonction est activée et le nombre de valeurs distinctes dans ces colonnes.

• La quantité de données qui change dans ces tables.

Affichage des coûts¶

Vous pouvez afficher les coûts de facturation du service d’optimisation de la recherche à l’aide de l’interface Web ou de SQL. Voir Découverte des coûts de calcul.

Réduction des coûts¶

Vous pouvez contrôler le coût du service d’optimisation de la recherche en sélectionnant soigneusement les tables et les colonnes pour lesquelles activer l’optimisation de la recherche.

De plus, pour réduire le coût du service d’optimisation de la recherche :

• Snowflake recommande les opérations DML par lots de la table :

  • DELETE: si les tables stockent des données pour la période la plus récente (par exemple, le jour, la semaine ou le mois le plus récent), lorsque vous ajustez votre table en supprimant les anciennes données, le service d’optimisation de la recherche doit prendre en compte ces mises à jour. Dans certains cas, vous pourrez peut-être réduire les coûts en effectuant des suppressions moins fréquemment (par exemple, tous les jours plutôt que toutes les heures).

  • INSERT, UPDATE, et MERGE : le traitement par lots de ces types d’instructions DML sur la table peut réduire le coût de la maintenance par le service d’optimisation de la recherche.

• Si vous réassemblez la table entière, envisagez de supprimer la propriété SEARCH OPTIMIZATION pour cette table avant le reclustering, puis d’ajouter la propriété SEARCH OPTIMIZATION à la table après le reclustering.

• Avant d’activer l’optimisation de la recherche pour les recherches de sous-chaînes (ON SUBSTRING(col)) ou VARIANTs (ON EQUALITY(variant_col)), appelez SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS pour estimer les coûts. La construction et la maintenance initiales de ces méthodes de recherche peuvent être très gourmandes en ressources de calcul, vous devez donc évaluer le compromis entre performances et coût.

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 :

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 TABLE … DROP 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.