Optimisation d'une requête

**MoscoBlade** · 11/04/2018, 10h14

Bonjour,

j'ai réalisé une requête, je tiens à dire que je suis loin d'être un expert, contenant un UNION ALL.
Voici ce que m'a requête est censé faire :
Dans une table je souhaite récupérer les 10 premiers lignes correspondant à un type (valeur d'un champ) ainsi que les 10 premières lignes correspondant à un autre type, voici la requête que j'ai réalisé :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
	select *	   
	from TABLE_A a, TABLE_B b
	where a.CATEGORY_TYPE = 1
		and a.TERMINAL IS NOT NULL
		and a.CATEGORY <> b.VALEUR
		and ROWNUM <= 10
	UNION ALL
	select *
	from TABLE_A a, TABLE_B b
	where  a.CATEGORY_TYPE = 2
		and a.TERMINAL IS NOT NULL
		and a.CATEGORY <> b.VALEUR
		and ROWNUM <= 10

J'ai simplifié ma requête afin de mettre en lumière le UNION ALL qui me pose problème.

Cependant après analyse je m'en rend compte que cette requête est bien trop coûteuse, et je voudrais savoir s'il y a une manière de réaliser la même chose avec des fonctions oracle. Je voudrais pouvoir me passer de UNION ALL.

N'hésitez pas à me donner des pistes et/ou à me dire s'il vous faut plus d'informations.

Mosco

**skuatamad** · 11/04/2018, 11h30

1/ Quand on parle des 10 premières lignes, ça impliquent un tri, dans l'exemple proposé il n'y a pas de tri donc juste 10 lignes au hazard.
2/ L'union de 2 ensembles de 10 lignes n'est pas couteux, UNION ALL n'est l'origine du problème
3/ Le gros problème vient de votre condition de jointure sur un "différent de".

L'utilisation de NOT EXISTS à la place de la jointure pourraît peut être convenir, ainsi que celle de row_number à la place de rownum pour éviter l'union.
Quelque chose comme ça :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
  with t as (
select a.* --pas étoile, mais la liste des colonnes
     , row_number() over (partition by a.CATEGORY_TYPE order by /*le critère de tri*/) as rn
  from TABLE_A a
 where a.CATEGORY_TYPE in (1, 2)
   and a.TERMINAL IS NOT NULL
   and not exists (select 1 
                     from TABLE_B b 
                    where b.VALEUR = a.CATEGORY)
)
select *
  from t
 where rn <= 10

Sinon, précisez votre besoin, et par ailleurs quels sont les index en présence et la volumétrie des 2 tables ?

**MoscoBlade** · 11/04/2018, 11h57

merci pour le retour je vais regarder les différents points.

Je me rends compte que j'ai un peu trop simplifié ma requête

, voici la requête moins simplifiée :

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
 
	select *	   
	from TABLE_A a, TABLE_B b
	where a.CATEGORY_TYPE = 1
		and a.TERMINAL IS NOT NULL
		and a.CATEGORY <> b.VALEUR
		and ROWNUM <= 100000 -- en fait la vraie valeur est 100000 et non 10
	UNION ALL
	select *
	from TABLE_A a, TABLE_B b
	where  a.CATEGORY_TYPE = 2
		and a.TERMINAL IS NOT NULL
		and a.CATEGORY <> b.VALEUR
		and ROWNUM <= 100000 -- en fait la vraie valeur est 100000 et non 10
	order by CATEGORY_REQ_TIME DESC

Concernant la volumétrie, la table b ne contient que plusieurs dizaines de lignes (table de configuration) alors que la table a peut contenir des centaines de milliers de lignes (ma base de test en contient 90000).
D'où mon remplacement de 10 par 10000 qui correspond à la vraie valeur dans ma requête (je ne pensais pas que cela avait une importance

)

Promis cette fois j'ai juste changer le nom des tables.

Concernant les index utilisés par ma requête :
- pas d'index sur ma table b
- table a :
- un index sur CATEGORY
- un index sur TERMINAL
- je n'ai pas listé les autres index dont les champs ne sont pas utilisés dans ma requête mais il s'agit peut être d'un tord ?

Je peux rajouter d'autres index si nécessaire.

N'hésitez pas si vous pensez qu'il vous faut d'autres informations.

Mosco

**skuatamad** · 11/04/2018, 12h53

100000 n'a de sens que si vous êtes en train de générer un fichier, sinon pour un humain sur un écran, 100000 lignes c'est beaucoup trop.

**MoscoBlade** · 11/04/2018, 13h36

Envoyé par skuatamad

100000 n'a de sens que si vous êtes en train de générer un fichier, sinon pour un humain sur un écran, 100000 lignes c'est beaucoup trop.

C'est pour envoyer à un autre traitement pas pour de l'affichage, j'avais oublié de le préciser.

**Ikebukuro** · 11/04/2018, 13h55

Mosco, quand tu dis que ta requête est trop coûteuse, ça signifie quoi? En temps je suppose mais elle met combien de seconde à s'exécuter?

Tu peux nous afficher le plan d'exécution et les stats en faisant un AUTOTRACE? Ca nous aidera à y voir plus clair.
Au fait, les stats de ton schéma sont à jour?

Est-ce qu'il y a un index sur a.CATEGORY_TYPE? Si non, pourquoi?

**skuatamad** · 11/04/2018, 14h02

Envoyé par MoscoBlade

C'est pour envoyer à un autre traitement pas pour de l'affichage, j'avais oublié de le préciser.

Ok, travaillez en priorité sur la relation avec la table B, voir si NOT EXISTS convient, ou autre, car en l'état la condition de jointure est catastrophique.

**MoscoBlade** · 11/04/2018, 15h32

Les premiers résultats ont l'air concluant. Je vous ferais un retour quand j'aurais fini les différents tests.

Encore merci

**MoscoBlade** · 12/04/2018, 11h57

Alors après différents tests pour m'assurer que le résultat attendu correspondait à ce qui est en place en PROD voici mes différents retours.
Merci à vous pour cette aide qui m'a permis d'apprendre beaucoup de choses.

J'ai pris en compte les différents points par skuatamad à savoir la condition de jointure sur un "différent de" remplacé par un NOT EXIST, et utilisation du row_number() over partition pour ne plus utiliser le UNION ALL.

voici ma requête (complète cette fois ci) :

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
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
 
WITH CONFIG_CAT AS
(
	SELECT a.NOM_PARAMETRE AS CLE, 
		   TO_NUMBER(a.VALEUR_PARAMETRE) AS VALEUR
	FROM   PARAM a
	WHERE  a.CODE_APPLICATION = 'XXX'
	AND    a.NOM_SECTION = 'YYY'
	AND    a.NOM_PARAMETRE = 'CAT'
),
CONFIG_SIZE AS
(
	SELECT a.PARAMETRE AS CLE, 
		   TO_NUMBER(a.VALEUR) AS VALEUR
	FROM   PARAM a
	WHERE  a.APPLICATION = 'XXX'
	AND    a.SECTION = 'YYY'
	AND    a.PARAMETRE = 'SIZE'
),
RESULTAT AS
(
	SELECT a.* -- j'ai mis l'ensemble de mes champs souhaités et je n'ai pas laissé '*'
		, row_number() over (partition by a.CATEGORY_TYPE order by a.REQ_TIME DESC) as rn
	from TABLE_A a
	where a.CATEGORY_TYPE in (1, 2)
		and a.TERMINAL IS NOT NULL
		and not exists (select 1 
                     from CONFIG_CAT b 
                    where b.VALEUR = a.CATEGORY)
)
select r.*
from RESULTAT r, CONFIG_SIZE c
where rn <= C.VALEUR
order by TERMINAL;

Concernant les remarques de Ikebukuro.

quand tu dis que ta requête est trop coûteuse, ça signifie quoi? En temps je suppose mais elle met combien de seconde à s'exécuter?

Exactement en temps, tu trouveras ci-dessous les 2 plans d'exécutions pour te faire une idée.

Au fait, les stats de ton schéma sont à jour?

Normalement oui, comment puis je être sur que c'est le cas ? Désolé pour ma non connaissance mais jusqu'à maintenant mon rôle était de faire des requêtes simples sachant que j'interviens plus code application normalement.

Est-ce qu'il y a un index sur a.CATEGORY_TYPE? Si non, pourquoi?

Il n'y a pas d'index sur ce champ, et le pourquoi je ne sais pas. En fait tout existait déjà en production ...
Après mes dernières modifs (voir la requête ci-dessus), j'ai rajouté un index sur ce champ mais je n'ai pas vu d'impact sur le résultat du plan d'exécution, est il encore nécessaire ?

Voici mon plan d'exécution initial : (ne prenez pas peur je n'avais pas communiqué sur l'ensemble de la requête que j'avais en partie optimiser)

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
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
 
----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                      
| Id  | Operation                      | Name                        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                      
----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                      
|   0 | SELECT STATEMENT               |                             | 59999 |    27M|       |  9805   (1)| 00:01:58 |                                                                                                                                                                                      
|   1 |  VIEW                          | V_VIEW1                     | 59999 |    27M|       |  9805   (1)| 00:01:58 |                                                                                                                                                                                      
|   2 |   TEMP TABLE TRANSFORMATION    |                             |       |       |       |            |          |                                                                                                                                                                                      
|   3 |    LOAD AS SELECT              | SYS_TEMP_0FD9D6604_E7D42F75 |       |       |       |            |          |                                                                                                                                                                                      
|   4 |     TABLE ACCESS BY INDEX ROWID| PARAM                       |     1 |    42 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|*  5 |      INDEX RANGE SCAN          | PK_PARAM                    |     1 |       |       |     1   (0)| 00:00:01 |                                                                                                                                                                                      
|   6 |    SORT ORDER BY               |                             | 59999 |    27M|    31M|  9803   (1)| 00:01:58 |                                                                                                                                                                                      
|   7 |     VIEW                       |                             | 59999 |    27M|       |  3776   (1)| 00:00:46 |                                                                                                                                                                                      
|   8 |      UNION-ALL                 |                             |       |       |       |            |          |                                                                                                                                                                                      
|   9 |       COUNT                    |                             |       |       |       |            |          |                                                                                                                                                                                      
|* 10 |        FILTER                  |                             |       |       |       |            |          |                                                                                                                                                                                      
|  11 |         MERGE JOIN CARTESIAN   |                             | 30816 |    16M|       |  1929   (1)| 00:00:24 |                                                                                                                                                                                      
|* 12 |          VIEW                  |                             |     1 |    95 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  13 |           TABLE ACCESS FULL    | SYS_TEMP_0FD9D6604_E7D42F75 |     1 |    30 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  14 |          BUFFER SORT           |                             | 30816 |    13M|       |  1929   (1)| 00:00:24 |                                                                                                                                                                                      
|  15 |           VIEW                 |                             | 30816 |    13M|       |  1927   (1)| 00:00:24 |                                                                                                                                                                                      
|  16 |            SORT ORDER BY       |                             | 30816 |  6771K|  8512K|  1927   (1)| 00:00:24 |                                                                                                                                                                                      
|  17 |             NESTED LOOPS       |                             | 30816 |  6771K|       |   419   (1)| 00:00:06 |                                                                                                                                                                                      
|* 18 |              VIEW              |                             |     1 |    93 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  19 |               TABLE ACCESS FULL| SYS_TEMP_0FD9D6604_E7D42F75 |     1 |    30 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|* 20 |              TABLE ACCESS FULL | TABLE_A	             | 30816 |  3972K|       |   417   (1)| 00:00:06 |                                                                                                                                                                                      
|  21 |       COUNT                    |                             |       |       |       |            |          |                                                                                                                                                                                      
|* 22 |        FILTER                  |                             |       |       |       |            |          |                                                                                                                                                                                      
|  23 |         MERGE JOIN CARTESIAN   |                             | 29183 |    15M|       |  1847   (1)| 00:00:23 |                                                                                                                                                                                      
|* 24 |          VIEW                  |                             |     1 |    95 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  25 |           TABLE ACCESS FULL    | SYS_TEMP_0FD9D6604_E7D42F75 |     1 |    30 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  26 |          BUFFER SORT           |                             | 29183 |    13M|       |  1847   (1)| 00:00:23 |                                                                                                                                                                                      
|  27 |           VIEW                 |                             | 29183 |    13M|       |  1845   (1)| 00:00:23 |                                                                                                                                                                                      
|  28 |            SORT ORDER BY       |                             | 29183 |  6412K|  8064K|  1845   (1)| 00:00:23 |                                                                                                                                                                                      
|  29 |             NESTED LOOPS       |                             | 29183 |  6412K|       |   419   (1)| 00:00:06 |                                                                                                                                                                                      
|* 30 |              VIEW              |                             |     1 |    93 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|  31 |               TABLE ACCESS FULL| SYS_TEMP_0FD9D6604_E7D42F75 |     1 |    30 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                      
|* 32 |              TABLE ACCESS FULL | TABLE_A	             | 29183 |  3761K|       |   417   (1)| 00:00:06 |                                                                                                                                                                                      
----------------------------------------------------------------------------------------------------------------------                                                                                                                                                                                      
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         
 
   5 - access("A"."CODE_APPLICATION"='XXX' AND "A"."NOM_SECTION"='YYY')                                                                                                                                                                                                                                
       filter("A"."NOM_PARAMETRE"='SIZE' OR "A"."NOM_PARAMETRE"='CAT')                                                                                                                                                                                                                
  10 - filter("CONFIG"."VALEUR">=ROWNUM)                                                                                                                                                                                                                                                                    
  12 - filter("CONFIG"."CLE"='SIZE')                                                                                                                                                                                                                                                               
  18 - filter("CONFIG"."CLE"='CAT')                                                                                                                                                                                                                                                            
  20 - filter("CATEGORY_TYPE"=1 AND "TERMINAL" IS NOT NULL AND                                                                                                                                                                                                                              
              "CATEGORY"<>"CONFIG"."VALEUR")                                                                                                                                                                                                                                                      
  22 - filter("CONFIG"."VALEUR">=ROWNUM)                                                                                                                                                                                                                                                                    
  24 - filter("CONFIG"."CLE"='SIZE')                                                                                                                                                                                                                                                               
  30 - filter("CONFIG"."CLE"='CAT')                                                                                                                                                                                                                                                            
  32 - filter("CATEGORY_TYPE"=2 AND "TERMINAL" IS NOT NULL AND                                                                                                                                                                                                                              
              "CATEGORY"<>"CONFIG"."VALEUR")

Voici mon plan d'exécution final :

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
16
17
18
19
20
21
22
23
24
25
26
27
28
 
------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                
| Id  | Operation                        | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |                                                                                                                                                                                                
------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                
|   0 | SELECT STATEMENT                 |                 |  2267 |  1168K|       |  2414   (1)| 00:00:29 |                                                                                                                                                                                                
|   1 |  SORT ORDER BY                   |                 |  2267 |  1168K|  1304K|  2414   (1)| 00:00:29 |                                                                                                                                                                                                
|   2 |   NESTED LOOPS                   |                 |  2267 |  1168K|       |  2158   (1)| 00:00:26 |                                                                                                                                                                                                
|   3 |    TABLE ACCESS BY INDEX ROWID   | PARAM	   |     1 |    42 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                
|*  4 |     INDEX UNIQUE SCAN            | PK_PARAM	   |     1 |       |       |     0   (0)| 00:00:01 |                                                                                                                                                                                                
|*  5 |    VIEW                          |                 |  2267 |  1075K|       |  2157   (1)| 00:00:26 |                                                                                                                                                                                                
|   6 |     WINDOW SORT                  |                 | 45340 |  7704K|  8448K|  2157   (1)| 00:00:26 |                                                                                                                                                                                                
|*  7 |      HASH JOIN RIGHT ANTI        |                 | 45340 |  7704K|       |   419   (2)| 00:00:06 |                                                                                                                                                                                                
|   8 |       TABLE ACCESS BY INDEX ROWID| PARAM	   |     1 |    42 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                
|*  9 |        INDEX UNIQUE SCAN         | PK_PARAM	   |     1 |       |       |     0   (0)| 00:00:01 |                                                                                                                                                                                                
|* 10 |       TABLE ACCESS FULL          | TABLE_A	   | 59999 |  7734K|       |   418   (2)| 00:00:06 |                                                                                                                                                                                                
------------------------------------------------------------------------------------------------------------                                                                                                                                                                                                
 
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         
 
   4 - access("A"."CODE_APPLICATION"='XXX' AND "A"."NOM_SECTION"='YYY' AND                                                                                                                                                                                                                             
              "A"."NOM_PARAMETRE"='SIZE')                                                                                                                                                                                                                                                          
   5 - filter("RN"<=TO_NUMBER("A"."VALEUR_PARAMETRE"))                                                                                                                                                                                                                                                      
   7 - access("A"."CATEGORY"=TO_NUMBER("A"."VALEUR_PARAMETRE"))                                                                                                                                                                                                                                   
   9 - access("A"."CODE_APPLICATION"='XXX' AND "A"."NOM_SECTION"='YYY' AND                                                                                                                                                                                                                             
              "A"."NOM_PARAMETRE"='CAT')                                                                                                                                                                                                                                                       
  10 - filter(("A"."CATEGORY_TYPE"=1 OR "A"."CATEGORY_TYPE"=2) AND                                                                                                                                                                                                                      
              "A"."TERMINAL" IS NOT NULL)

N'hésitez pas à me faire vos remarques.

Je passerai la discussion à résolue si aucune remarque

Merci pour tout à tous les 2.

Mosco

**Ikebukuro** · 12/04/2018, 13h45

Euh, c'est normal que le SELECT en première ligne des deux plans d'exécutions ne retourne pas le même valeur? Cela indique deux résultats finaux différents.
Plan 1 : 59 999 rows
Plan 2 : 2 267

**MoscoBlade** · 12/04/2018, 14h13

Je ne saurais répondre ... je ne suis pas un spécialiste de la lecture d'un plan d'exécution.
Ce que je sais c'est que le résultat est identique.

Voici la 1ère requête complète si cela peut aider (surement ce que j'aurais du faire depuis le début).

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
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
 
WITH CONFIG AS (
	SELECT a.NOM_PARAMETRE AS CLE, 
		   TO_NUMBER(a.VALEUR_PARAMETRE) AS VALEUR
	FROM   PARAM a
	WHERE  a.CODE_APPLICATION = 'XXX'
	AND    a.NOM_SECTION = 'YYY'
	AND    a.NOM_PARAMETRE in ('CAT', 'SIZE') )
--Sélection des données de la vue
select * -- il y a la liste des champs et non '*'
from   
(
	select *	   
	from
	(
		select *
		from   TABLE_A, CONFIG
		where  CATEGORY_TYPE = 1
			and TERMINAL IS NOT NULL
			and CATEGORY <> CONFIG.VALEUR
			and CONFIG.CLE = 'CAT'
		order by CATEGORY_REQ_TIME DESC
	)
	, CONFIG
	where CONFIG.CLE = 'SIZE'
		and ROWNUM <= CONFIG.VALEUR
	UNION ALL
	select *
	from
	(
		select *
		from   TABLE_A, CONFIG
		where  CATEGORY_TYPE = 2
		and TERMINAL IS NOT NULL
		and CATEGORY <> CONFIG.VALEUR
		and CONFIG.CLE = 'CAT'
		order by REQ_TIME DESC
	)
	, CONFIG
	where CONFIG.CLE = 'SIZE'
	and ROWNUM <= CONFIG.VALEUR
)
order by TERMINAL;

**MoscoBlade** · 12/04/2018, 16h17

Est ce que cela ne viendrait pas du changement radical qui a été apporté à la requête ?
Aussi on voit bien que le nombre de row pour TABLE_A est égale (si on prend en compte les 2 lignes du plan d'exécution initial).

**Ikebukuro** · 12/04/2018, 16h32

Non, pour moi cela n'a rien à voir.
Le SELECT d'id 0 doit, sauf erreur de ma part, donner le nombre de rows final.

Ton plan d'exécution, tu l'as généré avec un EXPLAIN PLAN, un AUTOTRACE, un DBMS_XPLAN.DISPLAY_CURSOR...?

En tout cas si le résultat est OK, c'est le principal, ne te prends pas la tête.

**MoscoBlade** · 12/04/2018, 16h34

J'ai généré mon plan d'exécution avec :

Code :

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

1
2
3
 
explain plan for select * from V_VIEW1;
select * from table(dbms_xplan.display);

V_VIEW1 = la requête globale

Merci.

Optimisation d'une requête

SQL Oracle

Discussions similaires

Partager

Partager