Discussion :

[Mafate]

Bonjour à tous,

Je souhaiterais optimiser le temps d'exécution d'une requête contenant plusieurs sous-requêtes en UNION ALL et j'aurais besoin de savoir où il est préférable de placer ma ou mes clauses WHERE pour restreindre la profondeur de l'historique retourné.

Je suis certain que ma question est très basique, et je pourrais évidemment faire le test en réel, mais comme le temps d'exécution actuel de ma requête est de plus de 3 heures, mes tests risquent d'être très longs... Donc j'apprécierais vraiment votre aide svp.

Donc, est-il plus rapide de faire ceci (une seule clause WHERE dans la requête principale) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT req.champ1, req.annee_creation
FROM (
  SELECT champ1, annee_creation
  FROM table1
  UNION ALL
  SELECT champ1, annee_creation
  FROM table2
) req
WHERE req.annee_creation = 2020;

Ou bien ceci ? (une clause WHERE dans chaque sous-requête) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT req.champ1, req.annee_creation
FROM (
  SELECT champ1, annee_creation
  FROM table1
  WHERE annee_creation = 2020
  UNION ALL
  SELECT champ1, annee_creation
  FROM table2
  WHERE annee_creation = 2020
) req;

Merci d'avance pour votre aide.

Bonne journée.

[McM]

Bonjour,

Moi je conseille le 2, les restrictions au niveau le plus bas (dans chaque Select). Même si l'optimiseur Oracle peut transformer la requête pour le gérer en automatique, rien n'est jamais sûr.

[vanagreg]

Bonjour,

Sur le principe je préfère restreindre la sélection de données dès le plus bas niveau, donc comme McM je partirai pour l'option 2.

[Mafate]

Bonjour,

Merci beaucoup pour vos retours rapides, je vais suivre vos conseils.

Bonne journée.

[SQLpro]

Et pourquoi pas les deux ? Si l'optimiseur est assez intelligent, il se débrouillera au mieux !

mais l'essentiel est de voir le plan de requête dans les deux cas et de comparer.

A +

[McM]

Pourquoi pas les 2 ? parce que c'est inutile et que gaspiller de la mémoire pour rajouter des conditions inutile va à l'encontre de l'Optimisation.

[vanagreg]

Peut-être que l'optimiseur simplifiera en effet et il peut-être intéressant de jeter un oeil aux plans d'exécution, mais sur le principe je préfère appliquer les filtres directement sur les tables sources plutôt qu'au global. Il peut bien sûr avoir des exceptions, et je suppose que la requête présentée par Mafate était simplifiée pour l'exemple.

[pachot]

Je vois que la question est marquée 'résolue' mais j'ai du mal à comprendre toutes ces réponses qui recommandent de dupliquer le prédicat, sans raison, juste au cas où, par principe,...

La question est:

Donc, est-il plus rapide de faire ceci (une seule clause WHERE dans la requête principale) :

et la réponse est: non, il n'est pas plus rapide de faire l'un ou l'autre car une transformation basique de l'optimiser, push-predicate, va de toute façon descendre au plus bas le prédicat.

Par contre, la première requête est:
- plus rapide à écrire
- plus facile à comprendre
- moins sujette à erreur

Donc c'est clairement la bonne solution.

[vanagreg]

Bonjour Frank,

Les arguments que tu avances:

Par contre, la première requête est:
- plus rapide à écrire
- plus facile à comprendre
- moins sujette à erreur

sont vraiment subjectifs. Oui c'est plus rapide à écrire, comme le sont les requêtes qui ne qualifient jamais les colonnes par pure fainéantise, mais par lesquelles on perd un temps fou à retrouver dans quelles tables ces colonnes se trouvent. En quoi mettre le critère au global plutôt qu'au niveau des 2 tables est plus facile à comprendre? Même remarque pour le fait que ce soit plus prône à l'erreur?

Si possible ce sera bien que Mafate nous montres les plans d'exécution.
J’essaierai de faire un test demain sur ma base pour comparer.

[JeitEmgie]

- moins sujette à erreur

Donc c'est clairement la bonne solution.

“Moins sujette à erreur humaine...”
le push-predicate peut toutefois avoir des effets de bord indésirés dans des situations plus complexes où par exemple la clause where externe utilise une fonction qui ne peut s’appliquer que sur les résultats finaux car un ou plusieurs des select de l’union ont d’autres where qui filtrent les résultats pour ne garder que ceux sur lesquels la fonction est garantie fonctionner (exemple un to_number sur un substr) : dans ce pattern vous pourriez avoir une mauvaise surprise car le push-predicate va aller mélanger votre where clause externe avec l’interne mais sans garantir l’ordre d’évaluation... et la fonction peut dès lors être évaluée sur des valeurs normalement exclues si l’on s’en tient à l’ordre apparent des choses à la lecture du query.

Donc oui cela reste la bonne solution mais rester prudent si la ”where” clause externe contient une expression qui dépend des résultats des ”where” internes pour fonctionner dans tous les cas.

[pachot]

sans garantir l’ordre d’évaluation

Mais rien ne garantit l'ordre d'évaluation en SQL. C'est un langage déclaratif. Vraiment, il y a plusieurs manières de déclarer le résultat qu'on veut (pour raison de style, lisibilité,...) mais c'est l'optimiseur qui va choisir le plan d'exécution. Il n'y a que certains hints qui peuvent forcer l'ordre d'exécution (par exemple no_push_pred dans ce cas) mais ce n'est plus du SQL et c'est pour ça qu'ils sont en commentaires.
Le pattern décrit (se baser sur "l’ordre apparent des choses à la lecture") est un bug de l'application. Si la fonction n'est pas valide pour toutes les valeurs possibles de la colonne ou expression, il faut le gérer (par un CASE ou un ON CONVERSION ERROR)

[vanagreg]

Bonjour,

Je viens de tester avec un exemple basique, les plans d'exécution sont identiques:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
 
create table table1(champ1, annee_creation)
as
select level, case when level <= 10000 then 2020 else 2020 - level end
from dual
connect by level <= 100000;
 
create table table2(champ1, annee_creation)
as
select level, case when level <= 20000 then 2020 else 2020 - level end
from dual
connect by level <= 100000;
 
create index table1_i1 on table1(annee_creation);
create index table2_i1 on table2(annee_creation);

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
 
SELECT req.champ1, req.annee_creation
FROM (
  SELECT champ1, annee_creation
  FROM table1
  UNION ALL
  SELECT champ1, annee_creation
  FROM table2
) req
WHERE req.annee_creation = 2020;

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
 
SELECT req.champ1, req.annee_creation
FROM (
  SELECT champ1, annee_creation
  FROM table1
  WHERE annee_creation = 2020
  UNION ALL
  SELECT champ1, annee_creation
  FROM table2
  WHERE annee_creation = 2020
) req

Le souci de préciser le critère au global c'est si on ne choisit plus la colonne mais une expression alors l'index n'est plus utilisé (c'est je pense que JeitEmgie expliquait), alors qu'il le sera toujours si le filtre est au niveau de la table:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT req.champ1, req.annee_creation
FROM (
  SELECT champ1, annee_creation 
  FROM table1
  UNION ALL
  SELECT champ1, annee_creation - 1 
  FROM table2
) req
WHERE req.annee_creation = 2020;

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT req.champ1, req.annee_creation
FROM (
  SELECT champ1, annee_creation
  FROM table1
  WHERE annee_creation = 2020
  UNION ALL
  SELECT champ1, annee_creation - 1
  FROM table2
  WHERE annee_creation = 2020
) req

[JeitEmgie]

Mais rien ne garantit l'ordre d'évaluation en SQL.

certes, mais ici on parle spécifiquement de l’ordre d’évaluation des composants de la nouvelle WHERE clause ré-écrite par le push-predicate : avant la condition étant dans la WHERE clause externe, on pouvait légitement s’attendre à ce qu’elle soit évaluée après les conditions des select internes, or le push-predicate la déplace de telle manière que l’ordre n’est plus garantit, car il se contente de l’ajouter aux conditions du WHERE existant (et si l’on examine le texte du query ré-écrit, il l’a met en tête des clauses existantes suivi de AND... ce qui fait qu’en pratique il la met en tête de l’ordre d’évaluation...). Or la ré-écriture pourrait se faire d’une autre manière qui garantirait que la logique de l’ordre original soit respectée...
car ici on parle de l’ordre d’évaluation des éléments d’une expression booléenne qui lui est quand même encadré par quelques règles.

[pachot]

on pouvait légitement s’attendre à ce qu’elle soit évaluée après les conditions des select internes

C'est justement le point que j'essaie de préciser car c'est très dangereux. Dans un langage procédural, oui, mais pas en SQL. Il n'y a pas d'ordre d'exécution déterminé par la requête.
Les parenthèses de la sous-requête n'impliquent pas que la sous-requête est exécutée en premier. C'est juste une factorisation pour l'écriture, comme en algèbre. Comme en maths où 2(a+b) = 2.a + 2.b