Problème Requête SQL

Version imprimable

Bonjour,
je suis sous ORACLE V10.2 et j'ai un problème avec une requête SQL qui s'execute indéfiniment.
En fait, si j'execute la requête sans les union (en rouge dans le code), elle passe normalement.
Mais les affaires se corsent avec l'ajout des union dans le exists.
Auriez-vous une idée ?
Merci

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
SELECT DISTINCT
       bod_sim_operation.OPE_CODE14   Code_14, 
       bod_sim_operation.OPE_NOMGPNOEUD    Nom, 
       bod_sim_operation.OPE_TYPEOPERATION    Type_Operation_BODEGA , 
       bod_sim_operation.OPE_ETATOPERATION    Etat_BODEGA
FROM     bod_sim_operation INNER JOIN
         bod_sim_candidat ON bod_sim_operation.OPE_ID = bod_sim_candidat.OPE_ID
WHERE    (bod_sim_operation.code_ur = 'PA')
AND    NOT EXISTS ( SELECT * 
                    FROM   ado_t_dr INNER JOIN
         		   ado_t_base_pgm ON ado_t_dr.N = ado_t_base_pgm.SITE_DR INNER JOIN
			   bde_sim_base_dr ON ado_t_dr.LIBELLE_DR = bde_sim_base_dr.dr_code
                   WHERE   (bde_sim_base_dr.urm_code = 'PA')  
                   AND     ado_t_base_pgm.SITE_ETAT <> 'ANNULE' 
                   AND     ado_t_base_pgm.PURGE = 0 
                   AND     bod_sim_operation.OPE_CODE14 = ado_t_base_pgm.SITE_CODE_PG
                   )
AND    EXISTS (
	SELECT DISTINCT 
		OPE.OPE_CODE14
	FROM	bod_sim_operation OPE INNER JOIN
		bod_sim_candidat CAN ON OPE.OPE_ID = CAN.OPE_ID
	WHERE   OPE.OPE_TYPEOPERATION IN ('Aménagement BTS', 'Réaménagement BTS','Aménagement MIXTE', 'Réaménagement MIXTE')
	AND    (OPE.OPE_ETATOPERATION IN ('EN COURS', 'GELEE') ) 
        AND    CAN.CAN_RANKING = 'Principal'
	AND    OPE.code_ur = 'PA'
	AND (
  		( (CAN.RECETTECONSTRUCTEUR_900_R IS NOT NULL) AND (CAN.RECETTECONSTRUCTEUR_900_R > '31/12/' || to_char(extract(YEAR from sysdate) - 1)) )
		OR ((CAN.RECETTECONSTRUCTEUR_1800_R IS NOT NULL) AND (CAN.RECETTECONSTRUCTEUR_1800_R > '31/12/' || to_char(extract(YEAR from sysdate) - 1)) )
		OR ( (CAN.RECETTECONSTRUCTEUR_900_R IS NULL) AND (CAN.RECETTECONSTRUCTEUR_1800_R IS NULL)
		AND (CAN.EDITIONFN73_900_R is NULL) AND (CAN.EDITIONFN73_1800_R IS NULL) )
	)
        AND    bod_sim_operation.OPE_CODE14 = OPE.OPE_CODE14    

	UNION

	SELECT DISTINCT 
		OPE.OPE_CODE14
	FROM	bod_sim_operation OPE INNER JOIN
		bod_sim_candidat CAN ON OPE.OPE_ID = CAN.OPE_ID
	WHERE  OPE.OPE_TYPEOPERATION IN ('Aménagement UMTS', 'Réaménagement UMTS','Aménagement MIXTE', 'Réaménagement MIXTE')
	AND    (OPE.OPE_ETATOPERATION IN ('EN COURS', 'GELEE')) 
        AND    CAN.CAN_RANKING = 'Principal'
	AND    OPE.code_ur = 'PA'
	AND (
		((CAN.RECETTECONSTRUCTEUR_UMTS_R IS NOT NULL) AND (CAN.RECETTECONSTRUCTEUR_UMTS_R > '31/12/' || to_char(extract(YEAR from sysdate) - 1)) )
		OR ( (CAN.RECETTECONSTRUCTEUR_UMTS_R IS NULL) AND (CAN.EDITIONFN73_UMTS_R IS NULL) )
	)
        AND    bod_sim_operation.OPE_CODE14 = OPE.OPE_CODE14
        
	UNION

	SELECT DISTINCT 
		OPE.OPE_CODE14
	FROM	bod_sim_operation OPE INNER JOIN
		bod_sim_candidat CAN ON OPE.OPE_ID = CAN.OPE_ID
	WHERE  OPE.OPE_TYPEOPERATION IN ('Répéteurs UMTS', 'Répéteurs GSM')
	AND    (OPE.OPE_ETATOPERATION  IN ('EN COURS', 'GELEE')) 
        AND    bod_sim_candidat.CAN_RANKING = 'Principal'
	AND    OPE.code_ur = 'PA'
	AND   (OPE.OPE_FN1 > '01/12/2005')
        AND    bod_sim_operation.OPE_CODE14 = OPE.OPE_CODE14
)
ORDER BY bod_sim_operation.OPE_NOMGPNOEUD;

06/07/2007, 09h43
McM

2-3 choses importantes à mémoriser :
1/ Le EXISTS : Ne pas mettre de nom de colonne dans le sous select
2/ Un UNION fait un distinct

Donc un UNION dans un EXISTS est complètement inutile et coûteux

Tu peux remplacer les union par des OR
06/07/2007, 11h08
guappi

merci pour ces infos.
J'ai supprimé les distinct des sous requêtes exists et j'ai mis des OR à la place des union. La requête tourne toujours indéfiniment.:(
06/07/2007, 12h03
dyvim

Qu'est ce que tu appelles tourner indéfiniment? (ca fait une semaine qu'elle tourne? ;) )
Pour t'aider, il nous faut une idée du volume de tes tables bod_sim_operation, bod_sim_candidat,... et si possible un EXPLAIN PLAN.
Peux tu nous montrer la version corrigée de ta requete?
06/07/2007, 12h38
McM
Je regarde la requete :
1/ y'a un pb d'alias dans le sous select n° 2
Code:

1 2 3 4 5 FROM bod_sim_operation AND EXISTS (SELECT 1 FROM bod_sim_operation OPE .. AND bod_sim_operation.OPE_CODE14 = OPE.OPE_CODE14
Il faut aliaser toutes les tables de la requete principale pour pouvoir les réutiliser dans les sous requetes sans risque.

En simplifiant, ça reste toujours brouillon comme requete.
A quoi sert BOD_SIM_CANDIDAT dans la req principale ?
La dernière requete, quel est la table liée ) à BOD_SIM_CANDIDAT.can_ranking = 'Principal' : Celle de la requete principale ou la sousrequete aliasée "can" ?

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
SELECT DISTINCT
       b_ope.ope_code14   code_14, 
       b_ope.ope_nomgpnoeud    nom, 
       b_ope.ope_typeoperation    type_operation_bodega , 
       b_ope.ope_etatoperation    etat_bodega
FROM     BOD_SIM_OPERATION b_ope
INNER JOIN BOD_SIM_CANDIDAT b_can ON b_ope.ope_id = bod_sim_candidat.ope_id
WHERE  b_ope.code_ur = 'PA'
AND    NOT EXISTS ( SELECT 1
		FROM   	ADO_T_DR dr,
			ADO_T_BASE_PGM  pgm,
			BDE_SIM_BASE_DR sim
		WHERE   dr.n = pgm.site_dr 
		AND 	sim.urm_code = 'PA'
		AND 	dr.libelle_dr = sim.dr_code  
		AND     pgm.site_etat <> 'ANNULE' 
		AND     pgm.PURGE = 0 
		AND     pgm.site_code_pg = bod_sim_operation.ope_code14
                   )
AND  (  
	EXISTS (
		SELECT 1
		FROM	BOD_SIM_OPERATION ope, BOD_SIM_CANDIDAT can 
		WHERE   ope.ope_typeoperation IN ('Aménagement BTS', 'Réaménagement BTS','Aménagement MIXTE', 'Réaménagement MIXTE')
		AND    	ope.ope_etatoperation IN ('EN COURS', 'GELEE')
		AND    	ope.code_ur = 'PA'
		AND 	ope.ope_id = can.ope_id
		AND    	can.can_ranking = 'Principal'
		AND (
				(can.recetteconstructeur_900_r IS NOT NULL AND can.recetteconstructeur_900_r > '31/12/' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) )
			OR 	(can.recetteconstructeur_1800_r IS NOT NULL AND can.recetteconstructeur_1800_r > '31/12/' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) )
			OR 	(can.recetteconstructeur_900_r IS NULL AND can.recetteconstructeur_1800_r IS NULL
					AND can.editionfn73_900_r IS NULL AND can.editionfn73_1800_r IS NULL )
			)
		AND    ope.ope_code14 = b_ope.ope_code14 
		)
	OR EXISTS (   
		SELECT 1
		FROM	BOD_SIM_OPERATION ope, BOD_SIM_CANDIDAT can
		WHERE  ope.ope_typeoperation IN ('Aménagement UMTS', 'Réaménagement UMTS','Aménagement MIXTE', 'Réaménagement MIXTE')
		AND    ope.ope_etatoperation IN ('EN COURS', 'GELEE')
		AND    ope.code_ur = 'PA'
		AND    ope.ope_id = can.ope_id
		AND    can.can_ranking = 'Principal'
		AND (
				(can.recetteconstructeur_umts_r IS NOT NULL AND can.recetteconstructeur_umts_r > '31/12/' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) )
			OR 	(can.recetteconstructeur_umts_r IS NULL AND can.editionfn73_umts_r IS NULL )
			)
		AND ope.ope_code14 = b_ope.ope_code14
		)
	OR EXISTS (
		SELECT 1
		FROM	BOD_SIM_OPERATION ope, BOD_SIM_CANDIDAT can
		WHERE  ope.ope_typeoperation IN ('Répéteurs UMTS', 'Répéteurs GSM')
		AND    ope.ope_etatoperation IN ('EN COURS', 'GELEE')
		AND    ope.ope_id 	= can.ope_id
		AND    BOD_SIM_CANDIDAT.can_ranking 	= 'Principal'
		AND    ope.code_ur 			= 'PA'
		AND    ope.ope_fn1 		> '01/12/2005'
		AND    ope.ope_code14 		= b_ope.ope_code14
		)
)
ORDER BY b_ope.ope_nomgpnoeud;

06/07/2007, 13h11
dyvim

Citation:

Envoyé par McM

A quoi sert BOD_SIM_CANDIDAT dans la req principale ?

A première vue à rien ;) (elle n'est pas utilisée dans les clauses et pas non plus pour ramener un champ)
Donc une jointure inutile..
06/07/2007, 13h49
McM

Ouais, sauf peut peut être pour tester une existence.
Je soupçonne fortement le premier DISTINCT d'en être la conséquence directe .. J'ai une jointure pourrie, ça me ramène des doublons => DISTINCT et hop ça marche, Et en exploit, ohhhh, ça rame ! :roll:
06/07/2007, 14h07
guappi

en réponse à dyvim
Combien de temps d'execution : la requete de midi tourne toujours ...
La 1ere jointure avec bod_sim_candidat me permet de récupérer les sites qui sont en opérations et uniquement ceux-ci.

A McM,

la dernière jointure BOD_SIM_CANDIDAT.can_ranking = 'Principal' référence la sous-requête dans le dernier union., pas la principal.

Essaye comme ça (et si le DISTINCT n'est plus nécessaire, supprime le).

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
SELECT DISTINCT
       b_ope.ope_code14   code_14, 
       b_ope.ope_nomgpnoeud    nom, 
       b_ope.ope_typeoperation    type_operation_bodega , 
       b_ope.ope_etatoperation    etat_bodega
FROM     BOD_SIM_OPERATION b_ope
WHERE  b_ope.code_ur = 'PA'
AND EXISTS (SELECT 1 FROM BOD_SIM_CANDIDAT b_can WHERE b_can.ope_id = b_ope.ope_id)
AND NOT EXISTS ( SELECT 1
		FROM   	ADO_T_DR dr,
			ADO_T_BASE_PGM  pgm,
			BDE_SIM_BASE_DR sim
		WHERE   dr.n = pgm.site_dr 
		AND 	sim.urm_code = 'PA'
		AND 	dr.libelle_dr = sim.dr_code  
		AND     pgm.site_etat <> 'ANNULE' 
		AND     pgm.PURGE = 0 
		AND     pgm.site_code_pg = bod_sim_operation.ope_code14
                   )
AND  (  
	EXISTS (
		SELECT 1
		FROM	BOD_SIM_OPERATION ope, BOD_SIM_CANDIDAT can 
		WHERE   ope.ope_typeoperation IN ('Aménagement BTS', 'Réaménagement BTS','Aménagement MIXTE', 'Réaménagement MIXTE')
		AND    	ope.ope_etatoperation IN ('EN COURS', 'GELEE')
		AND    	ope.code_ur = 'PA'
		AND 	ope.ope_id = can.ope_id
		AND    	can.can_ranking = 'Principal'
		AND (
				(can.recetteconstructeur_900_r IS NOT NULL AND can.recetteconstructeur_900_r > '31/12/' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) )
			OR 	(can.recetteconstructeur_1800_r IS NOT NULL AND can.recetteconstructeur_1800_r > '31/12/' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) )
			OR 	(can.recetteconstructeur_900_r IS NULL AND can.recetteconstructeur_1800_r IS NULL
					AND can.editionfn73_900_r IS NULL AND can.editionfn73_1800_r IS NULL )
			)
		AND    ope.ope_code14 = b_ope.ope_code14 
		)
	OR EXISTS (   
		SELECT 1
		FROM	BOD_SIM_OPERATION ope, BOD_SIM_CANDIDAT can
		WHERE  ope.ope_typeoperation IN ('Aménagement UMTS', 'Réaménagement UMTS','Aménagement MIXTE', 'Réaménagement MIXTE')
		AND    ope.ope_etatoperation IN ('EN COURS', 'GELEE')
		AND    ope.code_ur = 'PA'
		AND    ope.ope_id = can.ope_id
		AND    can.can_ranking = 'Principal'
		AND (
				(can.recetteconstructeur_umts_r IS NOT NULL AND can.recetteconstructeur_umts_r > '31/12/' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) )
			OR 	(can.recetteconstructeur_umts_r IS NULL AND can.editionfn73_umts_r IS NULL )
			)
		AND ope.ope_code14 = b_ope.ope_code14
		)
	OR EXISTS (
		SELECT 1
		FROM	BOD_SIM_OPERATION ope, BOD_SIM_CANDIDAT can
		WHERE  ope.ope_typeoperation IN ('Répéteurs UMTS', 'Répéteurs GSM')
		AND    ope.ope_etatoperation IN ('EN COURS', 'GELEE')
		AND    ope.ope_id 	= can.ope_id
		AND    can.can_ranking 	= 'Principal'
		AND    ope.code_ur 			= 'PA'
		AND    ope.ope_fn1 		> '01/12/2005'
		AND    ope.ope_code14 		= b_ope.ope_code14
		)
)
ORDER BY b_ope.ope_nomgpnoeud;

06/07/2007, 14h21
orafrance

les 3 EXISTS pourraient aussi être réécrit avec un seul en ajoutant des valeurs au IN et en maniant le OR subtilement :)
06/07/2007, 14h37
guappi

Mcm,
ta requete subit le même sort que ma requête initale, elle rame dès le premier
OR EXISTS.

Citation:

ORAFRANCE
les 3 EXISTS pourraient aussi être réécrit avec un seul en ajoutant des valeurs au IN et en maniant le OR subtilement

La requête fonctionne avec cette réécriture.:P

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
 
SELECT DISTINCT
       OPE1.OPE_CODE14   Code_14, 
       OPE1.OPE_NOMGPNOEUD    Nom, 
       OPE1.OPE_TYPEOPERATION    Type_Operation_BODEGA , 
       OPE1.OPE_ETATOPERATION    Etat_BODEGA
FROM     bod_sim_operation OPE1 INNER JOIN
         bod_sim_candidat CAN1 ON OPE1.OPE_ID = CAN1.OPE_ID
WHERE    (OPE1.code_ur = 'PA')
AND    NOT EXISTS ( SELECT * 
                    FROM   ado_t_dr A_DR INNER JOIN
         		   ado_t_base_pgm  A_PGM ON A_DR.N = A_PGM.SITE_DR INNER JOIN
			   bde_sim_base_dr BDE ON A_DR.LIBELLE_DR = BDE.dr_code
                   WHERE   (bde.urm_code = 'PA')  
                   AND     A_PGM.SITE_ETAT <> 'ANNULE' 
                   AND     A_PGM.PURGE = 0 
                   AND     OPE1.OPE_CODE14 = A_PGM.SITE_CODE_PG
                   )
AND    EXISTS (
	SELECT DISTINCT OPE_CODE14
	FROM	bod_sim_operation OPE2 INNER JOIN
		bod_sim_candidat CAN2 ON OPE2.OPE_ID = CAN2.OPE_ID
	WHERE   OPE2.OPE_TYPEOPERATION IN ('Aménagement BTS', 'Réaménagement BTS','Aménagement MIXTE', 'Réaménagement MIXTE')
	AND    (OPE2.OPE_ETATOPERATION IN ('EN COURS', 'GELEE') ) 
        AND    CAN2.CAN_RANKING = 'Principal'
        AND    OPE2.ope_code14 = OPE1.ope_code14
	AND    OPE2.code_ur = 'PA'
	AND (( 
		(CAN2.recetteconstructeur_900_r IS NOT NULL AND CAN2.recetteconstructeur_900_r > '31/12/' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) )
		OR 	(CAN2.recetteconstructeur_1800_r IS NOT NULL AND CAN2.recetteconstructeur_1800_r > '31/12/' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) )
		OR 	(CAN2.recetteconstructeur_900_r IS NULL AND CAN2.recetteconstructeur_1800_r IS NULL
				AND CAN2.editionfn73_900_r IS NULL AND CAN2.editionfn73_1800_r IS NULL )
               )
               OR (
			(CAN2.recetteconstructeur_umts_r IS NOT NULL AND CAN2.recetteconstructeur_umts_r > '31/12/' || TO_CHAR(EXTRACT(YEAR FROM SYSDATE) - 1) )
		          OR 	(CAN2.recetteconstructeur_umts_r IS NULL AND CAN2.editionfn73_umts_r IS NULL )
		  )
	       OR  (
                      OPE2.ope_fn1 > '01/12/2005'
                    )
              )
)
ORDER BY OPE1.OPE_NOMGPNOEUD;

Merci à tous ceux qui ont participé à cette discussion