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 :
Identification des tables et des colonnes qui bénéficient de l’optimisation de la recherche
Identifier les requêtes qui bénéficient de l’optimisation de la recherche
Configuration de l’optimisation des recherches pour une table
Configuration de l’optimisation des recherches pour des colonnes spécifiques
Ajout et gestion de l’optimisation de la recherche pour l’ensemble de la table
Vérifier que la table est configurée pour l’optimisation des recherches
Affichage de la configuration de l’optimisation de la recherche pour une table
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 |
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. |
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>
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;
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 queCOUNT(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 :
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 :
SPLIT_PART (dans les prédicats d’égalité)
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);
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%'
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%'
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înest=CA
,st=AZ
oust=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);
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
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 deconstant
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 deconstant
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
où
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 champsrc: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);
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);
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 GEOGRAPHYgeom
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);
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);
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.
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;
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 de la recherche à une table, vous utilisez la commande ALTER TABLE … ADD 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
Ajout et gestion de l’optimisation de la recherche pour l’ensemble de la table
Vérifier que la table est configurée pour l’optimisation des recherches
Configuration de l’optimisation des recherches pour des colonnes spécifiques¶
Pour configurer l’optimisation de la recherche pour une colonne spécifique, utilisez la commande ALTER TABLE … ADD SEARCH OPTIMIZATION avec la clause ON.
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
Exemple : prise en charge des prédicats d’égalité et de IN pour toutes les colonnes applicables
Exemple : prise en charge de l’égalité et de IN pour un champ dans un VARIANT
Exemple : prise en charge des prédicats d’égalité et de IN pour des colonnes spécifiques¶
Pour optimiser les recherches avec des prédicats d’égalité pour les colonnes c1
, c2
et c3
dans la table t1
, exécutez l’instruction suivante :
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2, c3);
Vous pouvez également spécifier la même méthode de recherche plus d’une fois dans la clause ON :
-- This statement is equivalent to the previous statement.
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1), EQUALITY(c2, c3);
Exemple : prise en charge des prédicats d’égalité et de IN pour toutes les colonnes applicables¶
Pour optimiser les recherches avec des prédicats d’égalité pour toutes les colonnes applicables de la table, exécutez l’instruction suivante :
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(*);
Remarques :
Comme expliqué dans la description de la syntaxe de la méthode de recherche et de la cible, pour une méthode donnée, vous ne pouvez pas spécifier un astérisque et des colonnes spécifiques.
Bien que l’omission de la clause ON configure également l’optimisation de la recherche pour les prédicats d’égalité et de IN sur toutes les colonnes applicables de la table, il existe des différences entre la spécification et l’omission de la clause ON. Voir Ajout et gestion de l’optimisation de la recherche pour l’ensemble de la table.
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);
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);
Ajout et gestion de l’optimisation de la recherche pour l’ensemble de la table¶
Si vous souhaitez simplement spécifier EQUALITY pour toutes les colonnes des types de données pris en charge (à l’exception de VARIANT et GEOGRAPHY), utilisez la commande ALTER TABLE … ADD SEARCH OPTIMIZATION sans la clause ON.
Note
Lors de l’exécution de cette commande, utilisez un rôle qui a les privilèges permettant d’ajouter l’optimisation de la recherche à la table.
Par exemple :
alter table test_table add search optimization;
Pour plus d’informations sur la syntaxe, consultez la section sur l’optimisation de la recherche dans ALTER TABLE.
Après avoir exécuté cette commande, vous pouvez vérifier que les colonnes ont été configurées pour l’optimisation de la recherche.
Effet sur les colonnes ajoutées ultérieurement¶
Après avoir exécuté la commande ALTER TABLE … ADD SEARCH OPTIMIZATION sans la clause ON, toutes les colonnes ajoutées ultérieurement à la table seront également configurées pour 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 :
Affichez la configuration de l’optimisation de la recherche pour la table et ses colonnes.
Exécutez la commande SHOW TABLES pour vérifier que l’optimisation de la recherche a été ajoutée et pour déterminer la proportion de la table qui a été optimisée.
Par exemple :
SHOW TABLES LIKE '%test_table%';
Dans la sortie de cette commande :
Vérifiez que SEARCH_OPTIMIZATION est
ON
, ce qui indique que l’optimisation de la recherche a été ajoutée.Vérifiez la valeur de SEARCH_OPTIMIZATION_PROGRESS. Cela spécifie le pourcentage de la table qui a été optimisé jusqu’à présent.
Lorsque l’optimisation de la recherche est ajoutée pour la première fois à un tableau, les avantages en termes de performances n’apparaissent pas immédiatement. Le service d’optimisation de la recherche commence à remplir les données en arrière-plan. Les avantages apparaissent de plus en plus à mesure que la maintenance rattrape l’état actuel de la table.
Avant d’exécuter une requête pour vérifier que l’optimisation de la recherche fonctionne, attendez que cela indique que la table a été entièrement optimisée.
Exécutez une requête pour vérifier que l’optimisation de la recherche fonctionne.
Notez que l’optimiseur Snowflake choisit automatiquement quand utiliser le service d’optimisation de la recherche pour une requête particulière. Les utilisateurs ne peuvent pas contrôler les requêtes pour lesquelles l’optimisation de la recherche est utilisée.
Choisissez une requête que le service d’optimisation de la recherche est conçu pour optimiser. Voir Identification des tables et des colonnes qui bénéficient de l’optimisation de la recherche.
Sur l’UI Web, affichez le plan de requête pour cette requête et vérifiez que le nœud de requête « Accès optimisation recherche » fait partie du plan de requête.
Affichage de la configuration de l’optimisation de la recherche pour une table¶
Pour afficher la configuration de l’optimisation de la recherche pour une table, utilisez la commande DESCRIBE SEARCH OPTIMIZATION.
Par exemple, supposons que vous exécutez l’instruction suivante pour configurer l’optimisation de la recherche pour une colonne :
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1);
L’exécution de DESCRIBE SEARCH OPTIMIZATION produit la sortie suivante :
DESCRIBE SEARCH OPTIMIZATION ON t1;
+---------------+----------+--------+------------------+--------+
| expression_id | method | target | target_data_type | active |
+---------------+----------+--------+------------------+--------+
| 1 | EQUALITY | C1 | NUMBER(38,0) | true |
+---------------+----------+--------+------------------+--------+
Utilisation de tables optimisées pour la recherche¶
Lorsque vous travaillez avec une table qui utilise l’optimisation de la recherche, vous devez être conscient des effets sur le service d’optimisation de la recherche.
Modification de la table¶
Un chemin d’accès de recherche devient non valide si la valeur par défaut d’une colonne est modifiée.
Pour utiliser à nouveau l’optimisation de la recherche après qu’un chemin d’accès de recherche est devenu non valide, vous devez supprimer la propriété SEARCH OPTIMIZATION et ajouter à nouveau la propriété SEARCH OPTIMIZATION à la table.
Un chemin d’accès à la recherche reste valable si vous ajoutez, détruisez ou renommez une colonne :
Si vous avez activé l’optimisation de la recherche pour une toute une table sans spécifier de colonnes spécifiques, lorsque vous ajoutez une colonne à une table, la nouvelle colonne est automatiquement ajoutée au chemin d’accès à la recherche. Toutefois, si vous avez utilisé la clause ON lors de l’activation de l’optimisation de la recherche pour une colonne, les nouvelles colonnes ne sont pas ajoutées automatiquement.
Lorsque vous détruisez une colonne d’une table, la colonne détruite est automatiquement retirée du chemin d’accès à la recherche.
Le changement de nom d’une colonne ne nécessite aucune modification du chemin d’accès à la recherche.
Si vous détruisez une table, la propriété SEARCH OPTIMIZATION et les chemins d’accès à la recherche sont également détruits. Remarques importantes :
Le rétablissement de la table rétablit immédiatement l’optimisation de la recherche en tant que propriété de la table.
Lorsque vous détruisez une table, le chemin d’accès à la recherche a la même période de conservation des données que la table.
Si vous détruisez la propriété SEARCH OPTIMIZATION de la table, le chemin d’accès à la recherche est supprimé. Lorsque vous rajoutez la propriété SEARCH OPTIMIZATION à la table, le service de maintenance doit recréer le chemin d’accès de recherche. (Il est impossible de rétablir la propriété).
Clonage de la table, du schéma ou de la base de données¶
Si vous clonez une table, un schéma ou une base de données, la propriété SEARCH OPTIMIZATION et les chemins d’accès à la recherche de chaque table sont également clonés. (Le clonage d’une table, d’un schéma ou d’une base de données crée un clone à zéro copie de chaque table et ses chemins d’accès de recherche correspondants.)
Notez que si vous utilisez CREATE TABLE … LIKE pour créer une nouvelle table vide avec les mêmes colonnes que la table d’origine, la propriété SEARCH OPTIMIZATION n’est pas copiée dans la nouvelle table.
Travailler avec des tables dans une base de données secondaire (aide à la réplication d’une base de données)¶
Si la propriété SEARCH OPTIMIZATION est activée dans une table de la base de données principale, la propriété est répliquée dans la table correspondante de la base de données secondaire.
Les chemins d’accès de recherche dans la base de données secondaire ne sont pas répliqués, mais reconstruits automatiquement. Il est à noter que ce processus entraîne les mêmes types de coûts que ceux décrits dans Gestion des coûts du service d’optimisation de la recherche.
Partage de la table¶
Les fournisseurs de données peuvent utiliser Secure Data Sharing pour partager des tables pour lesquelles l’optimisation de la recherche est activée.
Lors de l’interrogation de tables partagées, les consommateurs de données peuvent bénéficier des améliorations de performances apportées par le service d’optimisation de la recherche.
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
, etMERGE
: 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 TABLE … DROP SEARCH OPTIMIZATION avec la clause ON.
Par exemple, supposons que l’exécution de la commande DESCRIBE SEARCH OPTIMIZATION imprime les expressions suivantes :
DESCRIBE SEARCH OPTIMIZATION ON t1;
+---------------+-----------+-----------+-------------------+--------+
| expression_id | method | target | target_data_type | active |
+---------------+-----------+-----------+-------------------+--------+
| 1 | EQUALITY | C1 | NUMBER(38,0) | true |
| 2 | EQUALITY | C2 | VARCHAR(16777216) | true |
| 3 | EQUALITY | C4 | NUMBER(38,0) | true |
| 4 | EQUALITY | C5 | VARCHAR(16777216) | true |
| 5 | EQUALITY | V1 | VARIANT | true |
| 6 | SUBSTRING | C2 | VARCHAR(16777216) | true |
| 7 | SUBSTRING | C5 | VARCHAR(16777216) | true |
| 8 | GEO | G1 | GEOGRAPHY | true |
| 9 | EQUALITY | V1:"key1" | VARIANT | true |
| 10 | EQUALITY | V1:"key2" | VARIANT | true |
+---------------+-----------+-----------+-------------------+--------+
Pour abandonner l’optimisation de la recherche des sous-chaînes sur la colonne c2
, exécutez l’instruction suivante :
ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON SUBSTRING(c2);
Pour détruire toute optimisation de recherche pour toutes les méthodes sur la colonne c5
, exécutez l’instruction suivante :
ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON c5;
Étant donné que la colonne c5
est configurée pour optimiser les recherches d’égalité et de sous-chaînes, l’instruction ci-dessus abandonne la configuration pour les recherches d’égalité et de sous-chaînes pour c5
.
Pour abandonner l’optimisation de la recherche d’égalité sur la colonne c1
et pour abandonner la configuration spécifiée par l’expression IDs 6
et 8
, exécutez l’instruction suivante :
ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON EQUALITY(c1), 6, 8;
Pour plus d’informations sur la syntaxe, consultez la section sur ALTER TABLE … DROP SEARCH OPTIMIZATION.
Suppression de l’optimisation de la recherche de la table¶
Pour supprimer la propriété SEARCH OPTIMIZATION d’une table :
Basculez vers un rôle qui a les privilèges permettant de supprimer l’optimisation de la recherche de la table.
Exécutez la commande ALTER TABLE … DROP SEARCH OPTIMIZATION sans la clause ON :
ALTER TABLE [IF EXISTS] <table_name> DROP SEARCH OPTIMIZATION;
Par exemple :
alter table test_table drop search optimization;
Pour plus d’informations, consultez la section sur ALTER TABLE … DROP SEARCH OPTIMIZATION.
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;