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. Un ensemble étendu de prédicats de filtrage sont pris en charge (voir Identifier les requêtes qui peuvent bénéficier de l’optimisation de la recherche).

Note

Pour commencer avec un didacticiel qui compare le temps d’exécution avec et sans l’optimisation de la recherche, consultez Premiers pas avec l’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 :

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

Le service d’optimisation de la recherche est généralement transparent pour les utilisateurs. Les requêtes fonctionnent de la même manière que sans optimisation de la recherche ; certaines sont simplement plus rapides. Cependant, l’optimisation de la recherche a des effets sur certaines autres opérations de la table. Pour plus d’informations, consultez Utilisation de tables optimisées pour la recherche.

Comment fonctionne le service d’optimisation de la recherche ?

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

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

  • Lorsque vous activez l’optimisation de la recherche, 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.

    La construction du chemin d’accès à la recherche peut prendre beaucoup de temps, en fonction de la taille de la table. Le service de maintenance effectue ce travail en arrière-plan et ne bloque aucune opération sur la table. Les requêtes ne sont pas accélérées tant que le chemin d’accès à la recherche n’a pas été entièrement construit.

  • 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 est encore en cours de mise à jour, les requêtes peuvent s’exécuter plus lentement, mais elles renverront toujours des résultats corrects.

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

La maintenance des chemins d’accès à la recherche est transparente. Vous n’avez pas besoin de créer un entrepôt virtuel pour gérer le service de maintenance. Cependant, les ressources de stockage et de calcul de la maintenance ont un coût. Pour plus de détails sur les coûts, consultez Optimisation de la recherche - Estimation et gestion des coûts.

Autres possibilités d’optimisation des 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 autres techniques comprennent :

  • Accélération de requête.

  • Clustering d’une table.

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

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

Fonctionnalité

Types de requêtes pris en charge

Remarques

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.

Service Query Acceleration

Requêtes avec filtres ou agrégation. Si la requête comprend LIMIT, elle doit également comprendre ORDER BY.
Les filtres doivent être très sélectifs et la clause ORDER BY doit avoir une faible cardinalité.

L’accélération des requêtes fonctionne bien avec les analyses ad hoc et les requêtes dont le volume de données est imprévisible,
et les requêtes avec de grandes analyses et des filtres sélectifs.

L’accélération des requêtes et l’optimisation de la recherche sont complémentaires. Les deux peuvent accélérer la même requête. Voir Compatibilité avec l’accélération des requêtes.

Vue matérialisée

  • Recherches d’égalité.

  • Recherches de plages.

  • Opérations de tri.

Vous pouvez également utiliser les vues matérialisées pour définir différentes clés de clustering sur la même table source (ou un sous-ensemble de cette table), ou pour stocker des données JSON aplaties ou des données de variantes afin qu’elles ne doivent être aplaties qu’une seule fois.

Les vues matérialisées améliorent les performances uniquement pour le sous-ensemble de lignes et de colonnes inclus dans la vue matérialisée.

Clustering de la table

  • Recherches d’égalité.

  • Recherches de plages.

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

La table suivante montre lesquelles de ces optimisations ont des coûts de stockage ou de calcul :

Coût de stockage

Coût de calcul

Service d’optimisation de la recherche

Service Query Acceleration

Vue matérialisée

Clustering de la table.

[1]

Compatibilité avec l’accélération des requêtes

L’optimisation de la recherche et l”accélération des requêtes peuvent fonctionner ensemble pour optimiser les performances des requêtes. Tout d’abord, l’optimisation de la recherche permet d’assainir les micro-partitions qui ne sont pas nécessaires pour une requête. Ensuite, pour les requêtes éligibles, l’accélération des requêtes peut décharger une partie du reste du travail sur des ressources de calcul partagées fournies par le service.

Les performances des requêtes accélérées par les deux services varient en fonction de la charge de travail et des ressources disponibles.

Exemples

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

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

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

alter table test_table add search optimization;
Copy

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

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

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

select * from test_table where c2 = 2;
Copy

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

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

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

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

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

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

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

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