Discussion :

[yAnSoLo82]

Bonjour,

j'ai une table contenant des contrats avec des montants associés par date

j'aimerais sélectionné chaque contrat une seule fois avec le montant de la date la plus élevé

je sais le faire mais j'aimerais votre avis pour le faire relativement simplement et peut-être réduire le cout de la requete en terme de consommation

merci

[lallio]

Poste déjà la requete que tu as fait avec son plan d'execution, ca donera une base à la discussion.

Laurent

[yAnSoLo82]

alors en fait j'avais pensé à deux manières différentes mais que je trouve relativement compliqué

je vous colle les deux requetes et leur explain plan (qui sont à prendre avec des pincettes car il me manque un index sur la table principale)

considérez les 3 premiers champs comme l'identifiant contrat

la première avec une sous requête:

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
SELECT   hp.id_cta,
         hp.cd_part,
         hp.nm_avn,
         hp.mt_ht
    FROM siddwh.dwh_contrats c,
         siddwh.dwh_avenants avn,
         siddwh.dwh_histo_primes hp
   WHERE c.id_cta = avn.id_cta
     AND c.cd_part = avn.cd_part
     AND avn.id_cta = hp.id_cta
     AND avn.cd_part = hp.cd_part
     AND avn.nm_avn = hp.nm_avn
     AND c.cd_part IN (18, 20)
     AND (hp.id_cta, hp.cd_part, hp.nm_avn, hp.dt_debchrono) IN (
            SELECT   hp2.id_cta,
                     hp2.cd_part,
                     hp2.nm_avn,
                     MAX (hp2.dt_debchrono)
                FROM siddwh.dwh_contrats c2,
                     siddwh.dwh_avenants avn2,
                     siddwh.dwh_histo_primes hp2
               WHERE c2.id_cta = avn2.id_cta
                 AND c2.cd_part = avn2.cd_part
                 AND avn2.id_cta = hp2.id_cta
                 AND avn2.cd_part = hp2.cd_part
                 AND avn2.nm_avn = hp2.nm_avn
                 AND c2.cd_part IN (18, 20)
            GROUP BY hp2.id_cta,
                     hp2.cd_part,
                     hp2.nm_avn)
ORDER BY 2,
         1;

explain plan :

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
Plan
SELECT STATEMENT  CHOOSECost: 2,864  Bytes: 1,771  Cardinality: 23  								
	16 SORT ORDER BY  Cost: 2,864  Bytes: 1,771  Cardinality: 23  							
		15 HASH JOIN SEMI  Cost: 2,862  Bytes: 1,771  Cardinality: 23  						
			6 HASH JOIN  Cost: 1,719  Bytes: 2,566,728  Cardinality: 47,532  					
				4 NESTED LOOPS  Cost: 69  Bytes: 930,842  Cardinality: 32,098  				
					1 INDEX FAST FULL SCAN UNIQUE SIDDWH.PK_DWH_AVENANTS Cost: 69  Bytes: 1,281,664  Cardinality: 80,104  			
					3 INLIST ITERATOR  			
						2 INDEX UNIQUE SCAN UNIQUE SIDDWH.PK_DWH_CONTRATS Bytes: 756,886  Cardinality: 58,222  		
				5 TABLE ACCESS FULL SIDDWH.DWH_HISTO_PRIMES Cost: 1,463  Bytes: 7,400,875  Cardinality: 296,035  				
			14 VIEW SYS.VW_NSO_1 Cost: 1,069  Bytes: 1,093,236  Cardinality: 47,532  					
				13 SORT GROUP BY  Cost: 1,069  Bytes: 2,376,600  Cardinality: 47,532  				
					12 HASH JOIN  Cost: 662  Bytes: 2,376,600  Cardinality: 47,532  			
						10 NESTED LOOPS  Cost: 69  Bytes: 930,842  Cardinality: 32,098  		
							7 INDEX FAST FULL SCAN UNIQUE SIDDWH.PK_DWH_AVENANTS Cost: 69  Bytes: 1,281,664  Cardinality: 80,104  	
							9 INLIST ITERATOR  	
								8 INDEX UNIQUE SCAN UNIQUE SIDDWH.PK_DWH_CONTRATS Bytes: 756,886  Cardinality: 58,222  
						11 INDEX FAST FULL SCAN UNIQUE SIDDWH.PK_DWH_HISTO_PRIMES Cost: 424  Bytes: 6,216,735  Cardinality: 296,035

la deuxième avec des fonctions analytique:

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
SELECT DISTINCT hp.id_cta,
                hp.cd_part,
                hp.nm_avn,
                LAST_VALUE (hp.mt_ht) OVER (PARTITION BY hp.id_cta, hp.cd_part, hp.nm_avn ORDER BY hp.dt_debchrono ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)
                                                                     AS mt_ht
           FROM siddwh.dwh_contrats c,
                siddwh.dwh_avenants avn,
                siddwh.dwh_histo_primes hp
          WHERE c.id_cta = avn.id_cta
            AND c.cd_part = avn.cd_part
            AND avn.id_cta = hp.id_cta
            AND avn.cd_part = hp.cd_part
            AND avn.nm_avn = hp.nm_avn
            AND c.cd_part IN (18, 20)
       ORDER BY 2,
                1;

Code :

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

1
2
3
4
5
6
7
8
9
10
11
Plan
SELECT STATEMENT  CHOOSECost: 3,033  Bytes: 2,566,728  Cardinality: 47,532  							
	9 SORT ORDER BY  Cost: 3,033  Bytes: 2,566,728  Cardinality: 47,532  						
		8 SORT UNIQUE  Cost: 2,595  Bytes: 2,566,728  Cardinality: 47,532  					
			7 WINDOW SORT  Cost: 3,033  Bytes: 2,566,728  Cardinality: 47,532  				
				6 HASH JOIN  Cost: 1,719  Bytes: 2,566,728  Cardinality: 47,532  			
					4 NESTED LOOPS  Cost: 69  Bytes: 930,842  Cardinality: 32,098  		
						1 INDEX FAST FULL SCAN UNIQUE SIDDWH.PK_DWH_AVENANTS Cost: 69  Bytes: 1,281,664  Cardinality: 80,104  	
						3 INLIST ITERATOR  	
							2 INDEX UNIQUE SCAN UNIQUE SIDDWH.PK_DWH_CONTRATS Bytes: 13  Cardinality: 1  
					5 TABLE ACCESS FULL SIDDWH.DWH_HISTO_PRIMES Cost: 1,463  Bytes: 7,400,875  Cardinality: 296,035

mais je trouve ces 2 solutions un peu lourdingues alors que la problématique est relativement simple : sélectionner les derniers montants pour chaque contrat

[Delwyn]

Salut =)

Pourquoi refaire les jointures dans requete 1 ?

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
 
SELECT   hp.id_cta,
         hp.cd_part,
         hp.nm_avn,
         hp.mt_ht
    FROM          
         siddwh.dwh_histo_primes hp
   WHERE                          
        (hp.id_cta, hp.cd_part, hp.nm_avn, hp.dt_debchrono) IN (
            SELECT   hp2.id_cta,
                     hp2.cd_part,
                     hp2.nm_avn,
                     MAX (hp2.dt_debchrono)
                FROM siddwh.dwh_contrats c2,
                     siddwh.dwh_avenants avn2,
                     siddwh.dwh_histo_primes hp2
               WHERE c2.id_cta = avn2.id_cta
                 AND c2.cd_part = avn2.cd_part
                 AND avn2.id_cta = hp2.id_cta
                 AND avn2.cd_part = hp2.cd_part
                 AND avn2.nm_avn = hp2.nm_avn
                 AND c2.cd_part IN (18, 20)
            GROUP BY hp2.id_cta,
                     hp2.cd_part,
                     hp2.nm_avn)
ORDER BY 2,
         1;

Sinon j'aime bien l'analytique