Optimisation d'une requête

Version imprimable

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:

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

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:

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 ?

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 :aie:, voici la requête moins simplifiée :
Code:

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 :aie:)

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

11/04/2018, 12h53
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.
11/04/2018, 13h36
MoscoBlade

Citation:

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.
11/04/2018, 13h55
Ikebukuro

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?
11/04/2018, 14h02
skuatamad

Citation:

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.
11/04/2018, 15h32
MoscoBlade

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

Encore merci

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:

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.

Citation:

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.

Citation:

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.

Citation:

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:

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:

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 :aie:

Merci pour tout à tous les 2.

Mosco

12/04/2018, 13h45
Ikebukuro

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

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:

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;

12/04/2018, 16h17
MoscoBlade

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).
12/04/2018, 16h32
Ikebukuro

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.
12/04/2018, 16h34
MoscoBlade
J'ai généré mon plan d'exécution avec :
Code:

1 2 3 explain plan for select * from V_VIEW1; select * from table(dbms_xplan.display);
V_VIEW1 = la requête globale

Merci.