Optimiser une requête SQL(Oracle 11) dans une table 65 millions de lignes

Version imprimable

Bonjour à tous,

Je souhaiterais si possible un coup de main pour l’optimisation de cette requete

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
select T.CD_PA, T.AN_MS, T.NO_LIG_FIN , T.CD_TY_PER_FAC, T.NB_JR_DEPSS,T.NO_CNC,T.MT_LIG_FIN,T.CD_GRP_PROD
from INFO_EX.TZDFAC T 
where T.CD_TY_PER_FAC='D' and T.NB_JR_DEPSS>3 
and T.AN_MS=(select max(T.AN_MS) from INFO_EX.TZDFAC where CD_TY_PER_FAC='D' and NB_JR_DEPSS>3 and NO_LIG_FIN=T.NO_LIG_FIN and CD_PA=T.CD_PA and AN_MS between
to_char(add_months(sysdate,-7),'YYYYMM') and
to_char(add_months(sysdate,-1),'YYYYMM')
group by NO_LIG_FIN)
 
UNION
 
select T.CD_PA, T.AN_MS, T.NO_LIG_FIN , T.CD_TY_PER_FAC, T.NB_JR_DEPSS,T.NO_CNC,T.MT_LIG_FIN,T.CD_GRP_PROD
from INFO_EX.TZDFAC T 
where T.CD_TY_PER_FAC='*'
and T.NB_JR_DEPSS>3 
and T.AN_MS=(select max(T.AN_MS) from INFO_EX.TZDFAC where CD_TY_PER_FAC='*' and NB_JR_DEPSS>3 and NO_LIG_FIN=T.NO_LIG_FIN and CD_PA=T.CD_PA and AN_MS between
to_char(add_months(sysdate,-7),'YYYYMM') and
to_char(add_months(sysdate,-1),'YYYYMM')
group by NO_LIG_FIN

Si cela peut vous aider la clé primaire de la table est NO_LIG_FIN
Merci d'avance

Mounir

Bonjour,

Vous pouvez essayer 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
 
WITH CTE AS (
	SELECT 
		T.CD_PA, 
		T.AN_MS, 
		T.NO_LIG_FIN , 
		T.CD_TY_PER_FAC, 
		T.NB_JR_DEPSS,
		T.NO_CNC,
		T.MT_LIG_FIN,
		T.CD_GRP_PROD,
		RANK() OVER(PARTITION BY CD_TY_PER_FAC ORDER BY T.AN_MS DESC) AS R
	FROM INFO_EX.TZDFAC T 
	WHERE 
		T.CD_TY_PER_FAC IN ('D' , '*')
		AND T.NB_JR_DEPSS>3 
		AND AN_MS BETWEEN
			to_char(add_months(sysdate,-7),'YYYYMM') AND
			to_char(add_months(sysdate,-1),'YYYYMM')
)
SELECT 
	T.CD_PA, 
	T.AN_MS, 
	T.NO_LIG_FIN , 
	T.CD_TY_PER_FAC, 
	T.NB_JR_DEPSS,
	T.NO_CNC,
	T.MT_LIG_FIN,
	T.CD_GRP_PROD
FROM CTE
WHERE R = 1

La solution proposée plus haut est super rapide ... Mais elle ne retourne pas le bon résultat.

Votre plan d'exécution pourrait être le suivant :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |           |       |       |  1349K(100)|          |
|   1 |  SORT AGGREGATE                    |           |     1 |       |            |          |
|   2 |   VIEW                             |           |     2 |       |  1349K  (1)| 04:29:49 |
|   3 |    SORT UNIQUE                     |           |     2 |  4070 |  1349K (51)| 04:29:49 |
|   4 |     UNION-ALL                      |           |       |       |            |          |
|*  5 |      FILTER                        |           |       |       |            |          |
|*  6 |       TABLE ACCESS FULL            | TZDFAC    |   224K|   434M|   948   (1)| 00:00:12 |
|   7 |       SORT GROUP BY NOSORT         |           |     1 |  2035 |     3   (0)| 00:00:01 |
|*  8 |        FILTER                      |           |       |       |            |          |
|*  9 |         TABLE ACCESS BY INDEX ROWID| TZDFAC    |     1 |  2035 |     3   (0)| 00:00:01 |
|* 10 |          INDEX UNIQUE SCAN         | PK_TZDFAC |     1 |       |     2   (0)| 00:00:01 |
|* 11 |      FILTER                        |           |       |       |            |          |
|* 12 |       TABLE ACCESS FULL            | TZDFAC    |   224K|   436M|   948   (1)| 00:00:12 |
|  13 |       SORT GROUP BY NOSORT         |           |     1 |  2035 |     3   (0)| 00:00:01 |
|* 14 |        FILTER                      |           |       |       |            |          |
|* 15 |         TABLE ACCESS BY INDEX ROWID| TZDFAC    |     1 |  2035 |     3   (0)| 00:00:01 |
|* 16 |          INDEX UNIQUE SCAN         | PK_TZDFAC |     1 |       |     2   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Je vous suggère plutôt cette réécriture :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
with t as ( SELECT NO_LIG_FIN, CD_TY_PER_FAC, CD_PA, max(AN_MS) AN_MS 
            FROM TZDFAC 
            WHERE CD_TY_PER_FAC in ('*','D') 
              AND NB_JR_DEPSS>3 
              AND AN_MS BETWEEN to_char(add_months(sysdate,-7),'YYYYMM') AND to_char(add_months(sysdate,-1),'YYYYMM')
            GROUP BY NO_LIG_FIN, CD_TY_PER_FAC, CD_PA
          )
select CD_PA
     , AN_MS
     , NO_LIG_FIN 
     , CD_TY_PER_FAC
     , NB_JR_DEPSS
     , NO_CNC
     , MT_LIG_FIN
     , CD_GRP_PROD
FROM TZDFAC natural join t 
WHERE NB_JR_DEPSS > 3

plan :

Code:

1
2
3
4
5
6
7
8
9
10
11
------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |       |       |   954 (100)|          |
|   1 |  SORT AGGREGATE        |           |     1 |       |            |          |
|   2 |   VIEW                 | VM_NWVW_1 |   249K|       |   954   (2)| 00:00:12 |
|*  3 |    FILTER              |           |       |       |            |          |
|   4 |     HASH GROUP BY      |           |   249K|   486M|   954   (2)| 00:00:12 |
|*  5 |      FILTER            |           |       |       |            |          |
|*  6 |       TABLE ACCESS FULL| TZDFAC    |   249K|   486M|   947   (1)| 00:00:12 |
------------------------------------------------------------------------------------

Cependant, cette solution repose sur une réartition uniforme des données et le fait que seul l'index de PK existe sur votre table. A prendre avec une certaine distance donc.

05/08/2014, 10h32
aieeeuuuuu
Citation:

Envoyé par ojo77

La solution proposée plus haut est super rapide ... Mais elle ne retourne pas le bon résultat.

En effet, la clause de fenêtrage n'est pas bonne, il faudrait plutôt :
Code:

1 2 RANK() OVER(PARTITION BY CD_TY_PER_FAC,NO_LIG_FIN ,CD_PA ORDER BY T.AN_MS DESC) AS R

Citation:

Si cela peut vous aider la clé primaire de la table est NO_LIG_FIN

Donc déjà pas de UNION dans la requête origine (qui fait un distinct sur toutes les lignes). A remplacer par un UNION ALL

Ensuite.. je pense qu'il y a une incohérence dans la requête. Si NO_LIG_FIN est la PK, alors le SELECT max(T.AN_MS) ne sert pas à grand chose. Surtout que c'est une référence à la table parente (T).

Code:

1
2
3
AND T.AN_MS= (SELECT max(T.AN_MS) FROM INFO_EX.TZDFAC WHERE CD_TY_PER_FAC='D' AND NB_JR_DEPSS>3 AND NO_LIG_FIN=T.NO_LIG_FIN AND CD_PA=T.CD_PA 
AND AN_MS BETWEEN to_char(add_months(sysdate,-7),'YYYYMM') AND to_char(add_months(sysdate,-1),'YYYYMM')
GROUP BY NO_LIG_FIN)

Quel est le but de la requête ?
Est-ce que ce ne serait pas ça ?

Code:

1
2
3
4
5
6
7
8
9
SELECT cd_pa, an_ms, no_lig_fin, cd_ty_per_fac, nb_jr_depss, no_cnc, mt_lig_fin, cd_grp_prod
FROM TZDFAC
WHERE nb_jr_depss > 3
AND (cd_ty_per_fac, an_ms) IN ( SELECT cd_ty_per_fac, MAX(an_ms) an_ms 
                                FROM TZDFAC 
                                WHERE cd_ty_per_fac IN ('*','D') 
                                AND nb_jr_depss > 3 
                                AND an_ms BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE,-7),'YYYYMM') AND TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')
                                GROUP BY cd_ty_per_fac)

Enfin, quels sont les index sur la table