Remplacer une union par une jointure

Version imprimable

Bonjour,

je me posais une question, j'ai une grosse requête, dans lequel je fais une union et un in et j'ai constaté, après plusieurs recherche sur le net, que l'on pouvais faire une jointure à la place d'une union et que c'était mieux niveau optimisation, seulement j'ai un peu de mal à le faire, voici ceux à quoi ressemble ma requête :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
 
(
select nom,
prenom,
identifiant,
matricule,
nomsociete,
date_debut_contrat,
date_fin_contrat,
date_reglement
from table1
inner join table2...
inner join table3
inner join table 4
.....
where matricule='0123456'
and date_fin_contrat >= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')
and date_reglement >= to_date('2016/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss')
and date_reglement <= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')
and date_reglement <= date_fin_contrat
 
)
union
(
select nom,
prenom,
identifiant,
nomsociete,
date_debut_contrat,
date_fin_contrat,
date_reglement
from table1
inner join table2...
inner join table3
inner join table 4
.....
 
WHERE  date_reglement >= to_date('2016/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss')
and date_reglement <= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')
and date_reglement <= date_fin_contrat
AND date_fin_contrat <> to_date('2999/12/31','yyyy/mm/dd') -- SANS DATE DE FIN 
 
and identifiant in (
 
	select 
	identifiant
	from table1
	inner join table2...
	inner join table3
	inner join table 4
	.....
 
	where matricule='0123456'
	and date_reglement >= to_date('2016/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss')
	and date_reglement <= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')
	and date_reglement <= date_fin_contrat
 
	)
)

En gros, je me demandais et je pense que oui à tout hasard, si je peux optimisé ce genre de requête , pour évité l'union et le In?

merci à vous

25/09/2018, 14h45
McM

Première chose : Attention avec les UNION.
UNION fait un distinct des lignes (donc gourmand en Tri et en CPU) , donc il faut s'assurer qu'il est nécessaire. Sinon utiliser UNION ALL (qui ne fait pas de distinct et sera donc plus rapide)

Seconde chose : Mieux vaut éviter le IN et préférer plutôt le EXISTS

Et enfin, c'est compliquer pour nous d'optimiser une requête sans connaitre le modèle de données et le résultat attendu.
Mais entre un UNION de 2 requêtes rapides et une Jointure, je pense que je préfère l'UNION (mais ça dépend de la requête)
26/09/2018, 14h14
SQLpro

Il n'est jamais sûr que l'UNION soit moins optimal qu'une jointure. Ce genre de racontar, y compris avec une pauvre preuve sur un jeu de données bidon, n'a rien à voir avec ce que vous aurez en production.

UNION (par rapport à une jointure) permet de séparer une grosse requête en deux plus petites et compte tenu des règles de fonctionnement d'un optimiseur, et notamment de la nécessaire corrélation des statistiques, a plus de chance d'obtenir un bon plan que la même requête en jointure, en particulier lorsque la jointure est complexe (nombreuses tables) et qu'il y a de nombreuses opérations de restriction (clause WHERE).

En conclusion, il faut tester !

A +
26/09/2018, 14h30
android59

Citation:

Envoyé par McM

Première chose : Attention avec les UNION.
UNION fait un distinct des lignes (donc gourmand en Tri et en CPU) , donc il faut s'assurer qu'il est nécessaire. Sinon utiliser UNION ALL (qui ne fait pas de distinct et sera donc plus rapide)

Seconde chose : Mieux vaut éviter le IN et préférer plutôt le EXISTS

Et enfin, c'est compliquer pour nous d'optimiser une requête sans connaitre le modèle de données et le résultat attendu.
Mais entre un UNION de 2 requêtes rapides et une Jointure, je pense que je préfère l'UNION (mais ça dépend de la requête)

Bonjour,
j'imagine, toutefois ne pourriez vous pas utilisez le modèle de code ci joint ( en utilisant table 1 et table 2 etc ..) ? Car je ne sais pas si ça vient de moi, mais je trouve un peu lourd de faire une union alors que les requêtes sont exactement les même, sauf que dans l'autre il y a une ou deux condition qui diffère, vous voyez où je veux en venir ?

Citation:

Il n'est jamais sûr que l'UNION soit moins optimal qu'une jointure. Ce genre de racontar, y compris avec une pauvre preuve sur un jeu de données bidon, n'a rien à voir avec ce que vous aurez en production.

UNION (par rapport à une jointure) permet de séparer une grosse requête en deux plus petites et compte tenu des règles de fonctionnement d'un optimiseur, et notamment de la nécessaire corrélation des statistiques, a plus de chance d'obtenir un bon plan que la même requête en jointure, en particulier lorsque la jointure est complexe (nombreuses tables) et qu'il y a de nombreuses opérations de restriction (clause WHERE).

En conclusion, il faut tester !

Et je me demandais, en matière de temps d'éxecution de la requête sous sqldeveloper,( car au final c'est mon objectif c'est d'optimiser de grosse requête pour un temps d'execution moins long, si possible) est ce mieux que de préféré une union pour des grosses requêtes que des jointures ?

J'ai lu également par exemple qu'il fallait préféré les between plutot que de prendre une tranche de date ( par ex t1.date>to_date(....) and t1.date<=to_date(....) ou ça ne change rien au final ?
Merci :)

Pas facile d'analyser une requête avec des table1,2,3...

Je ne vois pas trop l'intérêt du IN, le filtre sur le matricule devrait pouvoir être remonté dans le 2eme SELECT.

Dans la requête 1 on ne prend que les DATE_FIN_CONTRAT à partir de 2017
Dans la requête 2, on prend les DATE_FIN_CONTRAT à partir de 2016, mais pas ceux sans date de fin (mais qui sont remontés dans la requête 1)
Donc je ne vois pas trop l'intérêt de l'UNION non plus, on dirait que la requête pourrait être simplement :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 select nom, prenom, identifiant, matricule, nomsociete, date_debut_contrat, date_fin_contrat, date_reglement from table1 inner join table2... inner join table3 inner join table 4 ..... where matricule='0123456' and date_reglement >= to_date('2016/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss') and date_reglement <= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss') and date_reglement <= date_fin_contrat
A voir si la requête n'a pas été trop simplifiée, ou si je suis passé à côté de quelque chose.

26/09/2018, 14h46
skuatamad
Citation:

Envoyé par android59

Car je ne sais pas si ça vient de moi, mais je trouve un peu lourd de faire une union alors que les requêtes sont exactement les même, sauf que dans l'autre il y a une ou deux condition qui diffère

Pour ce point vous pouvez utiliser une Common Table Expression avec le mot clé WITH, pour factoriser du code au sein de la requête :
Code:

1 2 3 4 5 6 with t as ( select 1 as c from dual ) select c from t union all select c from t
Citation:

Envoyé par android59

J'ai lu également par exemple qu'il fallait préféré les between plutot que de prendre une tranche de date ( par ex t1.date>to_date(....) and t1.date<=to_date(....) ou ça ne change rien au final ?

Ça ne change rien, c'est n'importe quoi.
26/09/2018, 15h49
McM

Citation:

Envoyé par skuatamad

Je ne vois pas trop l'intérêt du IN, le filtre sur le matricule devrait pouvoir être remonté dans le 2eme SELECT.

Comment peux-tu affirmer ça ? Tu ne sais pas quel rôle a IDENTIFIANT. C'est peut être un identifiant qui regrouperait plusieurs matricules.
C'est pour ça que j'ai dit "compliquer pour nous d'optimiser une requête sans connaitre le modèle de données et le résultat attendu." avec une faute sur "compliqué" d'ailleurs :)

PS : Si le but est d'avoir tous les règlements d'une année, moi je préfère utiliser un Extract(year from date_reglement) = 2016 qu'un between entre le 1er janvier et le 31 décembre

Imzginons que ta requête soit en fait la meilleure solution. Hypothèse d'école. Trop galère pour le diagnostiquer en l'état.

Je te conseille cependant de la modifier un tout petit peu ; ça ne changera strictement rien en terme de performance, mais ça rendra ta requête plus lisible par un humain. Pourcela, il faut utiliser des alias :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
 
(
select t1.nom,
t1.prenom,
t1.identifiant,
t1.matricule,
t1.nomsociete,
t1.date_debut_contrat,
t2.date_fin_contrat,
t3.date_reglement
from table1 t2
inner join table2  t2 
inner join table3 t3
inner join table4 t4
.....
where t1.matricule='0123456'
and t2.date_fin_contrat >= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')
and t3.date_reglement >= to_date('2016/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss')
and t3.date_reglement <= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')
and t3.date_reglement <= t2.date_fin_contrat
 
)
union
(
select t1.nom,
t1.prenom,
t1.identifiant,
t1.matricule,
t1.nomsociete,
t1.date_debut_contrat,
t2.date_fin_contrat,
t3.date_reglement
from table1 t1
inner join table2 t2
inner join table3 t3
inner join table4 t4
.....
 
WHERE  t3.date_reglement >= to_date('2016/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss')
and t3.date_reglement <= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')
and t3.date_reglement <= date_fin_contrat
AND t2.date_fin_contrat <> to_date('2999/12/31','yyyy/mm/dd') -- SANS DATE DE FIN 
 
and t1.identifiant in (
 
	select 
	t01.identifiant
	from table1 t01
	inner join table2 t02
	inner join table3 t03
	inner join table4 t04
	.....
 
	where t01.matricule='0123456'
	and t03.date_reglement >= to_date('2016/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss')
	and t03.date_reglement <= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')
	and t03.date_reglement <= t02.date_fin_contrat
 
	)
)

Si tu dois relire cette requête dans 3 mois, tu seras très content d'avoir rendu ta requête un peu plus lisible.
Et si tu veux obtenir de l'aide sur ce forum par exemple, c'est indispensable de rendre ta requête compréhensible.

08/10/2018, 21h55
SQLpro

Citation:

Envoyé par android59

J'ai lu également par exemple qu'il fallait préféré les between plutot que de prendre une tranche de date ( par ex t1.date>to_date(....) and t1.date<=to_date(....) ou ça ne change rien au final ?
Merci :)

Vous avez sans doute lu les racontars d'un crétin. BETWEEN ou > AND < est strictement équivalent. La plupart des SGBDR remplacent BETWEEN par des > AND < au niveau de l'algébriseur afin de faciliter la vie de l'optimiseur.

A +
08/10/2018, 21h58
SQLpro

Citation:

Envoyé par McM

PS : Si le but est d'avoir tous les règlements d'une année, moi je préfère utiliser un Extract(year from date_reglement) = 2016 qu'un between entre le 1er janvier et le 31 décembre

ATTENTION : tout dépend du moteur SQL… À nouveau certains algébriseurs transformeront le EXTRACT(YEAR...) en BETWEEN afin de rendre la requête "sargable" (à me lire). D'autres pas ! Dans ce dernier cas aucun index ne pourra être utilisé !

A +

Citation:

Envoyé par tbc92

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
 
 
(
select t1.nom,
t1.prenom,
t1.identifiant,
t1.matricule,
t1.nomsociete,
t1.date_debut_contrat,
t2.date_fin_contrat,
t3.date_reglement
from table1 t2
inner join table2  t2 
inner join table3 t3
inner join table4 t4
.....
where t1.matricule='0123456'
and t2.date_fin_contrat >= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')
and t3.date_reglement >= to_date('2016/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss')
and t3.date_reglement <= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')
and t3.date_reglement <= t2.date_fin_contrat
 
)
union
(
select t1.nom,
t1.prenom,
t1.identifiant,
t1.matricule,
t1.nomsociete,
t1.date_debut_contrat,
t2.date_fin_contrat,
t3.date_reglement
from table1 t1
inner join table2 t2
inner join table3 t3
inner join table4 t4
.....
 
WHERE  t3.date_reglement >= to_date('2016/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss')
and t3.date_reglement <= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')
and t3.date_reglement <= date_fin_contrat
AND t2.date_fin_contrat <> to_date('2999/12/31','yyyy/mm/dd') -- SANS DATE DE FIN 
 
and t1.identifiant in (
 
	select 
	t01.identifiant
	from table1 t01
	inner join table2 t02
	inner join table3 t03
	inner join table4 t04
	.....
 
	where t01.matricule='0123456'
	and t03.date_reglement >= to_date('2016/01/01 00:00:00','yyyy/mm/dd hh24:mi:ss')
	and t03.date_reglement <= to_date('2016/12/31 23:59:59','yyyy/mm/dd hh24:mi:ss')
	and t03.date_reglement <= t02.date_fin_contrat
 
	)
)

Bonjour et désolé pour le retour tardif. J'ai lu les différents postes et j'ai du mal à saisir les différents commentaires.

Ma requête actuelle a évolué et je rencontre le même soucis, sauf que là j'ai du rajouter une union, car on s'est aperçu que certaines personnes manqué à l'appel, nous obtenons donc ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
 
(
	select t1.nom,t2.prenom,....  --REQUETE 1
	from ...... t1
	inner join ...
	inner join ...
	inner join ...
	where t1.date1 >= to_date...
	and t1.date1 <= to_date...
	and identifiant =....
	and numeroentreprise='123456'
)
union
(
	select t1.nom,t2.prenom,....  --REQUETE 2
	from ...... t1
	inner join ...
	inner join ...
	inner join ...
	where t1.date1 >= to_date...
	and t1.date1 <= to_date...
	and numcontrat in 
		( select numcontrat
			from ...
			where ...
			inner join ...
			inner join ...
			inner join ...
			inner join ...
 
		)
)
union
(
	select t1.nom,t2.prenom,....  --REQUETE 3
	from ...... t1
	inner join ...
	inner join ...
	inner join ...
	where t1.date1 >= to_date...
	and t1.date1 <= to_date...
	and numcontrat in 
		( select numcontrat
			from ...
			where ...
			inner join ...
			inner join ...
			inner join ...
			inner join ...
		)
	and datepaiement >= ( 
			 select to_char(datepaiement ....)
			from ...
			where ...
			inner join ...
			inner join ...
			inner join ...
			inner join ...
		)
)
 
union
(
	select t1.nom,t2.prenom,....  --REQUETE 4 nouvellement rajoute
	from ...... t1
	inner join ...
	inner join ...
	inner join ...
	where t1.date1 >= to_date...
	and t1.date1 <= to_date...
	and numcontrat in 
		( select numcontrat
			from ...
			where ...
			inner join ...
			inner join ...
			inner join ...
			inner join ...
			and numeroentreprise='123456'
		)
	and datepaiement >= ( 
			 select to_char(datepaiement ....)
			from ...
			where ...
			inner join ...
			inner join ...
			inner join ...
			inner join ...
		)
    and  ( 
			 select numentreprise
			from ...
			where ...
			inner join ...
			inner join ...
			inner join ...
			inner join ...
		)	='123456'	
)

bref je vais pouvoir bientot inaugurer les 1000 lignes de ma requete sql, et je ne savais pas le terme excat, mais j'aimerais factoriser ma requete.
J'ai regarder sur internet et pour ça il faudrait créé une fonction, je n'y avais pas pensé avant car ........ je n'ai pas les privilèges suffisants pour le faire et autant dire que je ne les aurait jamais lol.

Y a til un moyen pour factoriser tout ça ou rendre ça plus lisible ?

UN grand merci à vous

Pour factoriser , skuatamad avait donné une bonne piste : la commande WITH

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
 
WITH jointure1 as (
select t1.id_commande  , cl.nom_client, pr.nom_produit , cm.annee , pm.mode_paiement , liv.date_livraison
 from commande cm
 inner join client cl on cl.id_client = cm.id_client 
 inner join produit pr on pr.id_produit = cm.id_produit 
 inner join paiement pm on pm.id_commande = cm.id_commande 
 inner join livraison  liv on liv.id_commande = cm.id_commande 
 where cm.annee >= 2015
)
select * from jointure1  j
  inner join xxx on ( ... ) 
  where annee  = 2018
union
select  * from jointure1 j
  inner join  yyy on (...) 
  where j.nom_client = 'DUPONT'

Si tu as beaucoup d'endroits où tu dois accéder à commande /Produit /client, avec normalement les mêmes règles de jointure , tu commences par faire cette requête, et lui attribuer un nom (ici Jointure1).
Ensuite, tu peux accéder à 'Jointure1' autant de fois que tu veux.

Et tu peux utiliser plusieurs With dans une même requête , en cascade :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
WITH jointure1 as ( un select bien compliqué )
, 
 jointure2 as 
( un autre select bien compliqué , qui utilise éventuellement jointure1 )
,
jointure3 as
( un autre select bien compliqué , qui utilise éventuellement jointure1 et jointure2 )  
 
select * from jointure1  j 
  inner join xxx on ( ... ) 
  where annee  = 2018
union
select  * from jointure2 j
  inner join  jointure3 j3 on (...) 
  where j.nom_client = 'DUPONT'

Merci pour ton retour :)

J'ai eu une idée également est il possible de faire la chose suivante, exemple :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
 
	select t1.nom,t2.prenom,....,numero_matricule  --REQUETE 1
	from ...... t1
	inner join ...
	inner join ...
	inner join ...
	where t1.date1 >= to_date...
	and t1.date1 <= to_date...
	and identifiant =....
	and numeroentreprise='123456'
and numero_matricule  in (
                                      select numero_matricule 
									  from .....
									  where condition 1
									  and condition 2
									  )
and numero_matricule in (
                                      select numero_matricule 
									  from .....
									  where autre_condition 3
									  and autre_condition 4
									  )
and numero_matricule in (
                                      select numero_matricule 
									  from .....
									  where autre_condition 5
									  and autre_condition 5
									  )

vous voyez où je veux en venir ? J'ai pensé à ça en pensant à la factorisation

merci

Au départ, tu parlais d'une union. Donc tu voulais la réunion du résultat de plusieurs requetes.
Donc ce serait plutôt :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
 
	select t1.nom,t2.prenom,....,numero_matricule  --REQUETE 1
	from ...... t1
	inner join ...
	inner join ...
	inner join ...
	where t1.date1 >= to_date...
	and t1.date1 <= to_date...
	and identifiant =....
	and numeroentreprise='123456'
and (   numero_matricule  in (
                                      select numero_matricule 
									  from .....
									  where condition 1
									  and condition 2
									  )
  or numero_matricule in (
                                      select numero_matricule 
									  from .....
									  where autre_condition 3
									  and autre_condition 4
									  )
  or numero_matricule in (
                                      select numero_matricule 
									  from .....
									  where autre_condition 5
									  and autre_condition 5
									  )
  )

Attention : ne pas oublier les parenthèses supplémentaires dès qu'on utilise OR.

11/06/2020, 10h36
android59

je ferme le sujet, celui ci netant plus d'actualité
11/06/2020, 19h40
pachot

Bonjour, just un conseil générique. Avant de chercher à réécrire les requêtes pour des idées de performance, jetez un coup d'oeuil au plan d'exécution. Il y a de grandes chances que si une transformation est possible et utile, l'optimiseur la fasse. Et du coup vous écrivez vos requêtes pour qu'elles soient le plus comprehensible pour vous, plutôt que sur des hypothèses d'exécution.
12/06/2020, 15h46
android59

coucou
merci pour l'information