Utilisation du service d’optimisation de la recherche

Le service d’optimisation de la recherche peut améliorer considérablement les performances de certains types de requêtes de recherche et d’analyse qui utilisent un ensemble étendu de prédicats pour le filtrage.

Pour commencer avec un didacticiel qui compare le temps d’exécution avec et sans l’optimisation de la recherche, accédez à Premiers pas avec l’optimisation de la recherche

Dans ce chapitre :

Comprendre le service d’optimisation de la recherche

Le service d’optimisation de la recherche vise à améliorer considérablement les performances de certains types de requêtes sur des tables, et notamment :

  • Requêtes sélectives de recherche de points sur des tables. Une requête de recherche ponctuelle renvoie uniquement une ligne ou un petit nombre de lignes distinctes. Les exemples de cas d’utilisation incluent :

    • Utilisateurs professionnels qui ont besoin de temps de réponse rapides pour les tableaux de bord critiques avec des filtres hautement sélectifs.

    • Scientifiques de données qui explorent de grands volumes de données et recherchent des sous-ensembles spécifiques de données.

    • Applications de données récupérant un petit ensemble de résultats sur la base d’un ensemble étendu de prédicats de filtrage.

  • Recherches par sous-chaînes et expressions régulières (par exemple LIKE, ILIKE, RLIKE, etc.).

  • Requêtes sur les champs des colonnes VARIANT, OBJECT et ARRAY qui utilisent certains types de prédicats (prédicats d’égalité, prédicats IN, prédicats qui utilisent ARRAY_CONTAINS et ARRAYS_OVERLAP, et prédicats qui vérifient les valeurs NULL).

  • Requêtes qui utilisent des fonctions géospatiales sélectionnées avec des valeurs GEOGRAPHY.

Une fois que vous avez identifié les requêtes qui peuvent bénéficier du service d’optimisation des recherches, vous pouvez configurer l’optimisation des recherches pour les colonnes et les tables utilisées dans ces requêtes.

Comment fonctionne le service d’optimisation de la recherche ?

Pour améliorer les performances des recherches ponctuelles, le service d’optimisation de la recherche s’appuie sur une structure de données persistante qui sert de chemin d’accès de recherche optimisé.

Un service de maintenance qui s’exécute en arrière-plan 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 du temps, selon la taille de la table. Le service 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.

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, voir Gestion des coûts du service d’optimisation de la recherche (dans ce chapitre).

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 :

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

Fonctionnalité

Types de requêtes pris en charge

Autres cas d’utilisation

Service d’optimisation de la recherche

Le service d’optimisation des recherches peut améliorer les performances de ces types de recherches pour les types de données pris en charge.

Vue matérialisée

  • Recherches d’égalité.

  • Recherches de plages.

  • Opérations de tri.

Remarque : les performances ne peuvent être améliorées que pour le sous-ensemble de lignes et de colonnes inclus dans la vue matérialisée.

Les vues matérialisées peuvent également être utilisées afin de définir différentes clés de clustering sur la même table source (ou un sous-ensemble de cette table), ou conjointement avec l’aplatissement des données JSON/variantes.

Clustering de la table.

  • Recherches d’égalité.

  • Recherches de plages.

Remarque : une table peut être mise en cluster sur une seule clé uniquement (qui peut contenir une ou plusieurs colonnes ou expressions).

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

Coût de stockage

Coût de calcul

Service d’optimisation de la recherche

Vue matérialisée

Clustering de la table.

1

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.

Identification des tables et des colonnes qui bénéficient de l’optimisation de la recherche

L’optimisation de la recherche fonctionne mieux pour améliorer les performances d’une requête lorsque la table est fréquemment interrogée sur des colonnes autres que la clé primaire du cluster.

Le service d’optimisation de la recherche prend actuellement en charge des types de requêtes spécifiques pour les types de données suivants :

  • Nombres à virgule fixe (par exemple INTEGER, NUMERIC).

  • DATE, TIME et TIMESTAMP.

  • VARCHAR.

  • BINARY.

  • VARIANT, OBJECT et ARRAY. (Il s’agit d’une fonctionnalité en avant-première).

  • GEOGRAPHY. (Il s’agit d’une fonctionnalité en avant-première).

Actuellement, le service d’optimisation de la recherche ne prend pas en charge les types de données à virgule flottante, GEOMETRY ou d’autres types de données non répertoriés ci-dessus. Snowflake pourrait prendre en charge d’autres types de données à l’avenir.

Le service d’optimisation de la recherche ne prend pas non plus en charge les classements.

Identifier les requêtes qui bénéficient de l’optimisation de la recherche

L’optimisation des recherches permet d’améliorer les performances des types de requêtes suivants :

  • Une requête qui s’exécute généralement pendant quelques secondes ou plus (avant l’application de l’optimisation de la recherche). Dans la plupart des cas, l’optimisation de la recherche n’améliorera pas sensiblement les performances d’une requête dont le temps d’exécution est inférieur à la seconde.

  • Une requête dans laquelle au moins une des colonnes accessibles par le biais de l’opération de filtrage de requête a au moins 100 000 à 200 000 valeurs distinctes.

    Pour déterminer le nombre de valeurs distinctes, vous pouvez utiliser l’une des méthodes suivantes :

    • Utilisez APPROX_COUNT_DISTINCT pour obtenir le nombre approximatif de valeurs distinctes :

      select approx_count_distinct(column1) from table1;
      
      Copy
    • Utilisez COUNT(DISTINCT <nom_colonne>) pour obtenir le nombre réel de valeurs distinctes :

      select count(distinct c1), count (distinct c2)  from test_table;
      
      Copy

    Etant donné que vous n’avez besoin que d’une approximation du nombre de valeurs distinctes, pensez à utiliser APPROX_COUNT_DISTINCT, qui est généralement plus rapide et plus économique que COUNT(DISTINCT <nom_colonne>).

L’optimisation des recherches peut améliorer les performances de ces types de requêtes.

É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 des recherches peut améliorer les performances des requêtes dont les prédicats recherchent des sous-chaînes ou utilisent des expressions régulières. Cela inclut les prédicats qui utilisent :

Note

Vous devez activer cette fonctionnalité pour des colonnes spécifiques à l’aide de la clause ON dans la commande ALTER TABLE … ADD SEARCH OPTIMIZATION. Par exemple :

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

Les recherches de sous-chaînes ne sont pas améliorées si vous omettez la clause ON.

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

En revanche, 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 :

  • 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 des recherches peut améliorer les performances des requêtes de recherche ponctuelle 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.

Note

Vous devez activer cette fonctionnalité pour des colonnes ou des champs spécifiques dans des colonnes à l’aide de la clause ON dans la commande ALTER TABLE … ADD SEARCH OPTIMIZATION. Par exemple :

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

Les requêtes sur une colonne VARIANT ne sont pas améliorées si vous omettez la clause ON.

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

Le service d’optimisation des recherches peut améliorer les performances des requêtes 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 :

Prise en charge de la conversion de valeurs VARIANT en TEXT

Le service d’optimisation de la recherche peut également améliorer les performances des requêtes dans lesquelles les colonnes VARIANT sont converties en TEXT et sont comparées à des constantes converties en TEXT.

Par exemple, supposons que src est une colonne VARIANT contenant des valeurs booléennes, de date et d’heure 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);          -- 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
Copy

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

Prédicats pris en charge pour les types de VARIANT

Le service d’optimisation des recherches peut améliorer les requêtes avec les types de prédicats énumérés ci-dessous. Dans les exemples ci-dessous, src est la colonne VARIANT, et path_to_variant_field est un chemin d’accès à un champ de la colonne VARIANT.

  • Prédicats d’égalité de la forme suivante :

    where path_to_variant_field[::target_data_type] = constant


    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 énumérés au-dessus de.

    Notez que :: n’est qu’un exemple de l’une des façons prises en charge de convertir la valeur en un type spécifique.

    Par exemple, le service d’optimisation de la recherche prend en charge les points suivants :

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

      where src:person.age = 42;
      
      Copy
    • Conversion explicite d’un élément 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 avec une constante TEXT sans convertir explicitement l’élément.

      where src:sender_info.ip_address = '123.123.123.123';
      
      Copy
    • Conversion explicite d’un élément en TEXT.

      where src:salesperson.name::TEXT = 'John Appleseed';
      
      Copy
    • Conversion explicite d’un élément en DATE.

      where src:events.date::DATE = '2021-03-26';
      
      Copy
    • Conversion explicite d’un élément en TIME avec une échelle spécifiée.

      where src:events.time_info::TIME(6) = '01:02:03.456789';
      
      Copy
    • Conversion explicite d’un élément 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
  • Les prédicats qui utilisent les fonctions ARRAY, tels que :


    • where ARRAY_CONTAINS(constant::VARIANT, path_to_variant_field)

      constant ne doit pas être NULL, et le type de données de constant doit être l’un des types pris en charge énumérés au-dessus de.

      Notez que :: n’est qu’un exemple de l’une des façons prises en charge de convertir la valeur en un type spécifique.

      Par exemple :

      where ARRAY_CONTAINS('77.146.211.88'::VARIANT, src:logs.ip_addresses)
      
      Copy
    • where ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), path_to_variant_field)

      Le type de données de chaque constante (constant_1, constant_2, etc.) doit être l’un des types pris en charge énumérés au-dessus de. L’ARRAY construit peut inclure des constantes NULL.

      Par exemple :

      where ARRAYS_OVERLAP(
          ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
      
      Copy
  • Les prédicats suivants qui recherchent la présence de valeurs NULL :

    • where IS_NULL_VALUE(path_to_variant_field)

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

    • where path_to_variant_field IS NOT NULL

    • where variant_column IS NULL

      variant_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 des recherches prend en charge l’utilisation de la colonne VARIANT src mais pas le chemin d’accès au champ src:person:age dans cette colonne VARIANT.

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

Actuellement, la prise en charge des types VARIANT dans le service d’optimisation des recherches présente les limitations suivantes :

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

  • Les prédicats de la forme variant_field IS NULL ne sont pas pris en charge.

  • Les prédicats où 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 qui contiennent 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.

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.

Note

Vous devez activer cette fonctionnalité pour des colonnes spécifiques à l’aide de la clause ON dans la commande ALTER TABLE … ADD SEARCH OPTIMIZATION. Par exemple :

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

Les requêtes qui utilisent des fonctions géospatiales ne sont pas améliorées si vous omettez la clause ON.

Les sections suivantes fournissent plus de détails :

Note

Les objets GEOMETRY ne sont pas encore pris en charge.

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

Pour les requêtes dont les prédicats utilisent les fonctions suivantes :

Le service d’optimisation des recherches peut améliorer les performances si :

  • Une expression d’entrée est une colonne GEOGRAPHY dans une table, et

  • L’autre expression en entrée est une constante GEOGRAPHY (créée par une conversion ou une fonction constructrice).

  • Pour ST_DWITHIN, l’argument de distance est une constante REAL non négative.

Notez que cette fonctionnalité présente les mêmes limitations que celles qui s’appliquent au service d’optimisation des recherches.

Autres remarques sur les performances

Étant donné que le service d’optimisation de la recherche est conçu pour des prédicats très sélectifs et que les prédicats filtrent par proximité entre les objets géospatiaux, le clustering des objets géospatiaux par proximité dans la table peut entraîner de meilleures performances. Vos données peuvent faire l’objet d’un clustering en spécifiant l’ordre de tri lors du chargement des données ou en utilisant le clustering automatique, selon que la table de base change fréquemment :

Chargement de données pré-triées

Si les données de votre table de base ne changent pas souvent, vous pouvez spécifier l’ordre de tri lors du chargement des données. Vous pouvez ensuite activer l’optimisation de la recherche sur la colonne GEOGRAPHY. Par exemple :

CREATE TABLE new_table AS SELECT * FROM source_table ORDER BY st_geohash(geom);
ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Copy

Après chaque modification importante apportée à vos données de base, vous pouvez trier manuellement les données.

Clustering automatique

S’il y a des mises à jour fréquentes de votre table de base, vous pouvez utiliser la commande ALTER TABLE … CLUSTER BY … pour activer le clustering automatique afin que la table fasse automatiquement l’objet d’un reclustering à mesure qu’elle change.

L’exemple suivant ajoute une nouvelle colonne geom_geohash de type VARCHAR et stocke le géohachage ou l’index H3 de la colonne GEOGRAPHY geom dans cette nouvelle colonne. Il active ensuite le clustering automatique avec la nouvelle colonne comme clé de cluster. Avec cette approche, les parties du tableau qui changent feront l’objet d’un reclustering automatique.

CREATE TABLE new_table AS SELECT *, ST_GEOHASH(geom) AS geom_geohash FROM source_table;
ALTER TABLE new_table CLUSTER BY (geom_geohash);
ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
Copy

Exemples qui utilisent des fonctions géospatiales

Les instructions suivantes créent et configurent la table utilisée dans les exemples de cette section. La dernière instruction utilise la clause ON dans la commande ALTER TABLE … ADD SEARCH OPTIMIZATION pour ajouter une optimisation de la recherche pour la colonne GEOGRAPHY g1.

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);
Copy
Exemples de prédicats pris en charge

La requête suivante est un exemple de requête prise en charge par le service d’optimisation des recherches. Le service d’optimisation de la recherche peut utiliser les chemins d’accès à la recherche pour améliorer les performances de cette requête :

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

Voici des exemples de prédicats supplémentaires pris en charge par le service d’optimisation des recherches :

...
  ST_INTERSECTS(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)
Copy
...
  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)
Copy
...
  ST_CONTAINS(
    g1,
    TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'))
Copy
...
  ST_WITHIN(
   TO_GEOGRAPHY('{"type" : "MultiPoint","coordinates" : [[-122.30, 37.55], [-122.20, 47.61]]}'),
   g1)
Copy
...
  ST_WITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
Copy
...
  ST_COVERS(
    TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'),
    g1)
Copy
...
  ST_COVERS(
    g1,
    TO_GEOGRAPHY('POINT(0 0)'))
Copy
...
  ST_COVEREDBY(
    TO_GEOGRAPHY('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'),
    g1)
Copy
...
  ST_COVEREDBY(
    g1,
    TO_GEOGRAPHY('POINT(-122.35 37.55)'))
Copy
...
  ST_DWITHIN(
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1,
    100000)
Copy
...
  ST_DWITHIN(
    g1,
    TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    100000)
Copy
Exemples de construction de constantes GEOGRAPHY

Voici des exemples de prédicats qui utilisent différentes fonctions de conversion et de construction pour la constante GEOGRAPHY.

...
  ST_INTERSECTS(
    g1,
    ST_GEOGRAPHYFROMWKT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
Copy
...
  ST_INTERSECTS(
    ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
    g1)
Copy
...
  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)
Copy
...
  ST_WITHIN(
    ST_GEOGRAPHYFROMWKB('01010000006666666666965EC06666666666C64240'),
    g1)
Copy
...
  ST_COVERS(
    g1,
    ST_MAKEPOINT(0.2, 0.8))
Copy
...
  ST_INTERSECTS(
    g1,
    ST_MAKELINE(
      TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'),
      TO_GEOGRAPHY('POINT(0.8 0.2)')))
Copy
...
  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)
Copy
...
  ST_WITHIN(
    g1,
    TRY_TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'))
Copy
...
  ST_COVERS(
    g1,
    ST_GEOGPOINTFROMGEOHASH('s00'))
Copy

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.

Note

Lorsque l’optimisation de la recherche est activée, un utilisateur qui ne peut pas voir une valeur en raison d’une politique de masquage ou d’une politique d’accès aux lignes peut être en mesure de déduire avec plus de certitude si cette valeur existe. Avec ou sans optimisation de la recherche, les différences de latence des requêtes peuvent fournir des indications sur la présence ou l’absence de données restreintes par une politique, mais cet effet peut être amplifié par l’optimisation de la recherche dans certaines situations.

Par exemple, supposons qu’une politique d’accès aux lignes empêche un utilisateur d’accéder à des lignes avec country = US, mais que les données n’incluent pas de lignes avec country = US. Supposons maintenant que l’optimisation de la recherche est activée pour la colonne country et que l’utilisateur exécute une requête avec WHERE country = US. La requête renvoie des résultats vides comme prévu, mais la requête peut s’exécuter plus rapidement avec l’optimisation de la recherche que sans. Dans ce cas, l’utilisateur peut déduire que les données ne contiennent pas de ligne où country = US en fonction du temps nécessaire pour exécuter la requête.

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.

Le service Query Acceleration n’accélère pas les requêtes sur les tables pour lesquelles le service d’optimisation des recherches est activé.

Configuration de l’optimisation des recherches pour une table

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.

Pour ajouter l’optimisation de la recherche à une table, vous utilisez la commande ALTER TABLEADD SEARCH OPTIMIZATION avec ou sans la clause ON.

L’utilisation de la commande sans la clause ON configure les chemins d’accès à la recherche pour améliorer les performances des requêtes à l’aide de la méthode de recherche EQUALITY pour toutes les colonnes qui utilisent les types de données pris en charge.

Si vous avez besoin de plus de contrôle sur les colonnes qui sont configurées pour l’optimisation de la recherche ou si vous souhaitez utiliser d’autres méthodes de recherche que EQUALITY, 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 et SUBSTRING pour les recherches de sous-chaînes).

Pour gérer le coût de l’optimisation de la recherche, vous pouvez supprimer l’optimisation de la recherche de colonnes spécifiques où l’optimisation de la recherche n’est pas nécessaire.

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

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 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 Ajout et gestion de l’optimisation de la recherche pour l’ensemble de la 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.

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

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

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

Gestion des coûts du service d’optimisation de la recherche

Le service d’optimisation de la recherche a un impact sur les coûts des ressources de stockage et de calcul :

  • Ressources de stockage : le service d’optimisation de la recherche crée une structure de données de chemin d’accès à la recherche qui nécessite de l’espace pour chaque table sur laquelle l’optimisation de la recherche est activée. Le coût de stockage du chemin d’accès de recherche dépend de plusieurs facteurs, notamment :

    • Le nombre de valeurs distinctes (NDVs) dans la table. Dans le cas extrême où toutes les colonnes contiennent des types de données qui utilisent le chemin d’accès de recherche et où toutes les valeurs de données dans chaque colonne sont uniques, le stockage requis peut être autant important que la taille de la table d’origine.

      Cependant, la taille correspond généralement à environ 1/4 de la taille de la table d’origine.

  • Ressources de calcul :

    • L’ajout de l’optimisation de la recherche à une table consomme des ressources lors de la phase initiale de construction.

    • La maintenance du service d’optimisation de la recherche nécessite également des ressources. La consommation de ressources est plus élevée en cas de roulement élevé (c’est-à-dire lorsque de grands volumes de données changent dans la table). Ces coûts sont à peu près proportionnels à la quantité de données ingérées (ajoutées ou modifiées). Les suppressions ont également un certain coût.

      Le clustering automatique, tout en améliorant la latence des requêtes dans des tables avec l’optimisation de la recherche, peut encore augmenter les coûts de maintenance de l’optimisation de la recherche. Si une table a un taux de rotation élevé, l’activation du clustering automatique et la configuration de l’optimisation de la recherche pour la table peuvent entraîner des coûts de maintenance plus élevés que si la table est simplement configurée pour l’optimisation de la recherche.

      Snowflake garantit une utilisation efficace du crédit en ne facturant à votre compte que les ressources réellement utilisées. La facturation est calculée par incréments d’une seconde.

      Reportez-vous au « Tableau des crédits de fonctionnalité sans serveur » dans le Tableau de consommation du service Snowflake pour les coûts par heure de calcul.

      Une fois que vous avez activé le service d’optimisation de la recherche, vous pouvez afficher les coûts liés à votre utilisation du service.

Astuce

Snowflake recommande de commencer lentement avec cette fonctionnalité (c’est-à-dire d’ajouter l’optimisation de la recherche à seulement quelques tables au début) et de surveiller attentivement les coûts et les avantages.

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 tables sur lesquelles la fonction est activée et le nombre de valeurs distinctes dans ces tables.

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

Important

Les valeurs renvoyées par la fonction SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS sont des estimations au mieux basées sur l’échantillonnage d’un ensemble de données partiel dans la table. Les coûts estimés peuvent varier considérablement (jusqu’à 50 % ou, dans de rares cas, de plusieurs fois) par rapport aux coûts réels réalisés.

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

Exemples

Le code suivant montre la création et l’utilisation d’un service d’optimisation de la recherche.

Commencez par créer une table avec des données :

create or replace table test_table (id int, c1 int, c2 string, c3 date) as
select * from values
  (1, 3, '4',  '1985-05-11'),
  (2, 4, '3',  '1996-12-20'),
  (3, 2, '1',  '1974-02-03'),
  (4, 1, '2',  '2004-03-09'),
  (5, null, null,  null);
Copy

Ajoutez la propriété SEARCH OPTIMIZATION à la table :

alter table test_table add search optimization;
Copy

Les requêtes suivantes peuvent utiliser le service d’optimisation de la recherche :

select * from test_table where id = 2;
Copy
select * from test_table where c2 = '1';
Copy
select * from test_table where c3 = '1985-05-11';
Copy
select * from test_table where c1 is null;
Copy
select * from test_table where c1 = 4 and c3 = '1996-12-20';
Copy

La requête suivante peut utiliser le service d’optimisation de la recherche car la conversion implicite se trouve sur la constante, pas sur la colonne :

select * from test_table where c2 = 2;
Copy

Ce qui suit ne peut pas utiliser le service d’optimisation de la recherche car la conversion se fait sur la colonne de la table :

select * from test_table where cast(c2 as number) = 2;
Copy

Une clause IN est prise en charge par le service d’optimisation de la recherche :

select id, c1, c2, c3
    from test_table
    where id IN (2, 3)
    order by id;
Copy

Si les prédicats sont pris en charge individuellement par le service d’optimisation de la recherche, ils peuvent être joints par la conjonction AND et être toujours pris en charge par le service d’optimisation de la recherche :

select id, c1, c2, c3
    from test_table
    where c1 = 1
       and
          c3 = TO_DATE('2004-03-09')
    order by id;
Copy

DELETE et UPDATE (et MERGE) peuvent également utiliser le service d’optimisation de la recherche :

delete from test_table where id = 3;
Copy
update test_table set c1 = 99 where id = 4;
Copy