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.

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

Chacun de ces moyens présente différents avantages :

  • Le clustering d’une table peut améliorer les performances de l’un des éléments suivants, tant qu’ils se trouvent sur la clé de clustering :

    • Recherches de plages.

    • Recherches d’égalité.

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

  • Le service d’optimisation des recherches peut améliorer les performances des recherches d’égalité ainsi que d’autres types de recherches pour les types de données pris en charge.

  • Une vue matérialisée peut améliorer les performances des recherches d’égalité et les recherches de plage, ainsi que certaines opérations de tri, mais uniquement pour le sous-ensemble de lignes et de colonnes incluses 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/variables.

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.

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

Pour utiliser le service d’optimisation de la recherche pour une requête, vous avez juste besoin de privilèges 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 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.

Déterminer les types de données pris en charge par le service d’optimisation de la recherche

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

  • 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;
      
    • Utilisez COUNT(DISTINCT <nom_colonne>) pour obtenir le nombre réel de valeurs distinctes :

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

    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 :

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

Par exemple, le service d’optimisation des recherches n’utilise pas les chemins d’accès à la recherche pour le prédicat suivant, car la sous-chaîne est plus courte que 5 caractères :

LIKE '%TEST%'

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.

LIKE '%SEARCH%IS%OPTIMIZED%'

Dans cet exemple, le service d’optimisation de la recherche n’utilise pas les chemins d’accès à la recherche pour IS car la sous-chaîne est plus courte que 5 caractères.

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)+'

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.*$$
RLIKE '.*country=(Germany|France|Spain).*'
RLIKE '.*phone=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'

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}.*'
    
  • 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}.*'
    
  • 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).*'
    
  • Motifs dans lesquels la sous-chaîne est facultative :

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

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

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.

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

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

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;
      
    • 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;
      
    • 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';
      
    • Conversion explicite d’un élément en TEXT.

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

      where src:events.date::DATE = '2021-03-26';
      
    • 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';
      
    • 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';
      
  • 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)
      
    • 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)
      
  • 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. 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.

É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. Par exemple, vous pouvez regrouper les valeurs GEOGRAPHY par leurs valeurs ST_GEOHASH ou par une combinaison des coordonnées du centroïde ou de la boîte englobante des objets.

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

Exemples qui utilisent des fonctions géospatiales

Les instructions suivantes créent et configurent la table utilisée dans les exemples de cette section :

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;
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))'));

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)
...
  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)
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))'))
...
  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 peut améliorer les performances des requêtes qui font la jointure entre une petite table et une grande table. Pour plus de détails, voir Permettre au service d’optimisation de la recherche d’améliorer les performances des jointures.

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.

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.

  • Les tables et les vues protégées par les politiques d’accès aux lignes ne peuvent pas être utilisées avec le service d’optimisation des recherches.

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

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

    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'

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 des recherches à une table, vous utilisez la commande ALTER TABLEADD SEARCH OPTIMIZATION. Cela configure les chemins d’accès à la recherche pour améliorer les performances des requêtes de prédicats d’égalité et IN pour toutes les colonnes qui utilisent les types de données pris en charge.

Si vous souhaitez améliorer les performances pour d’autres types de requêtes ou si vous avez besoin de plus de contrôle sur les colonnes configurées pour l’optimisation de la recherche, 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 d’interrogation (par exemple, les recherches par égalité et IN, etc.).

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.

Enfin, comme le service d’optimisation des recherches prend en charge des types supplémentaires de prédicats et de types de données (par exemple, des sous-chaînes et des expressions régulières, GEOGRAPHY, des champs dans des VARIANTs, etc.), vous pouvez spécifier quelles colonnes doivent être configurées pour bénéficier de cette prise en charge.

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 Ajouter l’optimisation des recherches 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);

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 :

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

Ajouter l’optimisation des recherches 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), 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;

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.

Remarques :

  • Après avoir exécuté cette commande, toutes les colonnes ajoutées ultérieurement à la table seront également configurées pour EQUALITY.

  • 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. Imprimez 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%';
    

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

Permettre au service d’optimisation de la recherche d’améliorer les performances des jointures

Note

Cette fonctionnalité est en cours de déploiement dans différentes régions. Pour une liste des régions dans lesquelles cette fonctionnalité est activée, voir Déploiement de la fonctionnalité : Prise en charge des jointures dans le service d’optimisation de la recherche.

Le service d’optimisation de la recherche peut améliorer les performances des requêtes qui associent une grande table à une ou plusieurs petites tables (par exemple, une table de faits et plusieurs tables de dimensions).

Par exemple, supposons que product soit une table contenant une ligne pour chaque produit et que sales soit une table contenant une ligne pour chaque vente d’un produit. product contient moins de lignes et est plus petite que sales. Pour trouver toutes les ventes d’un produit spécifique, vous joignez la table sales (la plus grande table) à la table product (la plus petite table). Le service d’optimisation de la recherche peut améliorer les performances de ce type de jointure.

Note

Dans l’entreposage de données, la grande table est souvent appelée la table des faits. La petite table est appelée la table dimensionnelle. Dans la suite de cette rubrique, ces termes sont utilisés pour désigner la grande table et la petite table dans la jointure.

Pour permettre au service d’optimisation de la recherche d’améliorer les performances des jointures, ajoutez l’optimisation de la recherche à la table de faits (la plus volumineuse des deux tables).

Pour tirer parti de l’optimisation de la recherche, la table de dimension (la moins volumineuse des deux tables) doit comporter peu de valeurs distinctes. Les coûts d’optimisation de la recherche d’une requête sont proportionnels au nombre de valeurs distinctes qui doivent être recherchées dans la table de faits. Si le nombre de valeurs distinctes dans la table de dimension est trop important, Snowflake peut décider de ne pas utiliser le chemin d’accès de recherche et d’utiliser plutôt le chemin d’accès de table ordinaire.

Prédicats de jointure pris en charge

Le service d’optimisation de la recherche peut améliorer les performances des requêtes avec les types de prédicats de jointure suivants :

  • Prédicats d’égalité de la forme dimension_table.column = fact_table.column.

  • Transformations du côté du prédicat avec la dimension (par exemple, concaténation de chaînes, addition, etc.).

  • Conjonctions (AND) de prédicats d’égalité multiples.

Exemples de requêtes prises en charge avec des jointures

Cette section présente des exemples de requêtes qui peuvent bénéficier d’une optimisation de la recherche avec des jointures.

Exemple : prédicat d’égalité simple comme prédicat de jointure

Voici un exemple de requête prise en charge qui utilise un prédicat d’égalité simple comme prédicat de jointure. Cette requête joint une table de faits nommée sales à une table de dimension nommée product. La table des faits est volumineuse et l’optimisation de la recherche est activée. L’entrée de la table de dimension est faible, en raison du filtre sélectif sur la colonne category.

SELECT sales.date, product.name
FROM sales JOIN product ON (sales.product_id = product.id)
WHERE product.category = 'Cutlery';

Exemple : prédicat de jointure transformé du côté de la dimension

Les requêtes qui transforment le côté du prédicat avec la dimension (par exemple, multiplie) peuvent également bénéficier de l’optimisation de la recherche :

SELECT sales.date, product.name
FROM sales JOIN product ON (sales.product_id = product.old_id * 100)
WHERE product.category = 'Cutlery';

Exemple : prédicat de jointure englobant plusieurs colonnes

Les requêtes dans lesquelles un prédicat de jointure couvre plusieurs colonnes sont également prises en charge :

SELECT sales.date, product.name
FROM sales JOIN product ON (sales.product_id = product.id and sales.location = product.place_of_production)
WHERE product.category = 'Cutlery';

Exemple : requête utilisant des filtres ponctuels et des prédicats de jointure

Dans une requête qui utilise à la fois des filtres ponctuels classiques et des prédicats de jointure, le service d’optimisation de la recherche peut améliorer les performances des deux. Dans la requête suivante, le service d’optimisation de la recherche améliore le prédicat ponctuel sales.location ainsi que le prédicat de jointure product_id.

SELECT sales.date, product.name
FROM sales JOIN product ON (sales.product_id = product.id)
WHERE product.category = 'Cutlery'
AND sales.location = 'Buenos Aires';

Limites de la prise en charge des jointures

  • Les disjonctions (OR) dans les prédicats de jointure ne sont actuellement pas prises en charge.

  • Les prédicats de jointure LIKE/ILIKE/RLIKE ne sont actuellement pas pris en charge.

  • Les prédicats de jointure sur les colonnes de variantes ne sont actuellement pas pris en charge.

  • Les prédicats d’égalité EQUAL_NULL ne seront pas pris en charge.

  • Les limites actuelles du service d’optimisation de la recherche s’appliquent également à cette fonction.

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

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 d’une optimisation de recherche à une table consomme des ressources.

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

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

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

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

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

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

alter table test_table add search optimization;

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

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

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;

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;

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;

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;

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

delete from test_table where id = 3;
update test_table set c1 = 99 where id = 4;
Revenir au début