Bonjour à tous,
 
Je vais essayer de schématiser mon problème de la manière la plus simple possible :
J'ai une table VENTE qui contient les champs NOM_VENDEUR, NOM_CLIENT, DATE_VENTE, QUANTITE, PRIX
Mon souhait est de récupérer pour chaque couple NOM_VENDEUR, NOM_CLIENT, la dernière date de vente et le max du prix des vente réalisées pour cette dernière date.
J'ai donc fait une requête du style :
 
select A.NOM_VENDEUR, A.NOM_CLIENT, A.DATE_VENTE, max(A.QUANTITE, PRIX)
from MA_TABLE A
where
A.DATE_VENTE = (select max(B.DATE_VENTE) from MA_TABLE B where B.NOM_VENDEUR=A.NOM_VENDEUR and B.NOM_CLIENT=A.NOM_CLIENT)
group by
A.NOM_VENDEUR, A.NOM_CLIENT, A.DATE_VENTE
 
Le résultat est OK, pas de soucis la dessus. Le gros problème, c'est les perf car j'ai plusieurs millions d'enregistrements dans cette table et je dois la parcourir en entier (c'est pour du reporting commercial).
 
Bref, ma question est la suivante (je suppose que c'est un cas plus ou moins classique d'optim) : comment optimiser cette(ces) requete(s). Peut-on éviter les sous-requêtes ? Pour info, si j'exécute le select simple (sans la sous-requête), ca met quelques secondes (moins de 5) par contre dès que je met la sous-requête, a dure 5 à 10 minutes suivant mon périmètre d'extraction.
 
Il faut dire que la requête que j'ai est beaucoup plus complexe que celle que je vous présente (pour simplifier), j'ai donc déjà créé une vue qui me simplifie ma requête et que l'on peut considérer comme l'équivalent de MA_TABLE dans mon exemple.
 
Merci à tous de votre aide et à bientôt
 
Vegaelce

...et la sous-requête seule?
Tu as regardé le plan d'exécution de la requête?

 
La sous requête met environ 30sec à s'exécuter.
 
Pour ce qui est du plan d'exécution, j'avoue que je ne suis pas expert dans le déchiffrage de la log mais si vous pouvez m'aider à la déchiffrer (ce qui me permettra d'apprendre par la même occasion), la voici :
 
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
 
SELECT STATEMENT Optimizer Mode=CHOOSE  1     46                            
  SORT GROUP BY  1   207   14                            
    FILTER                                  
      NESTED LOOPS  1   207   12                            
        NESTED LOOPS  1   191   11                            
          NESTED LOOPS  1   163   10                            
            NESTED LOOPS  1   141   8                            
              NESTED LOOPS  1   120   6                            
                TABLE ACCESS BY GLOBAL INDEX ROWID DSTG_JDE.EVETBL 1   66   4                        ROWID ROW L
                  INDEX RANGE SCAN DSTG_JDE.EVETBL_AAAAMM 1     3                            
                TABLE ACCESS BY INDEX ROWID DSTG_JDE.SALAFF_DET 1   54   2                            
                  INDEX RANGE SCAN DSTG_JDE.SALAFF_DET_EVETBL 1     1                            
              TABLE ACCESS BY INDEX ROWID DSTG_JDE.PROTBL 1   21   2                            
                INDEX RANGE SCAN DSTG_JDE.I_PROTBL_CODE 1     1                            
            TABLE ACCESS BY INDEX ROWID DSTG_JDE.CINTBL 1   22   2                            
              INDEX RANGE SCAN DSTG_JDE.PK_CINTBL 1     1                            
          TABLE ACCESS BY INDEX ROWID DSTG_JDE.TIETBL 1   28   2                            
            INDEX RANGE SCAN DSTG_JDE.PK_TIETBL 1     1                            
        INDEX UNIQUE SCAN DSTG_JDE.PK_CODTBL 1   16                              
      SORT AGGREGATE  1   151                              
        FILTER                                  
          MERGE JOIN CARTESIAN  1   151   32                            
            NESTED LOOPS  1   144   31                            
              NESTED LOOPS  1   128   30                            
                NESTED LOOPS  1   107   28                            
                  MERGE JOIN CARTESIAN  1   45   4                            
                    INDEX RANGE SCAN DSTG_JDE.PK_CINTBL 1   6   2                            
                    BUFFER SORT  1   39   2                            
                      TABLE ACCESS BY INDEX ROWID DSTG_JDE.SALAFF_DET 1   39   2                            
                        INDEX RANGE SCAN DSTG_JDE.SALAFF_DET_EVETBL 1     1                            
                  TABLE ACCESS BY GLOBAL INDEX ROWID DSTG_JDE.EVETBL 1   62   25                        ROWID ROW L
                    INDEX RANGE SCAN DSTG_JDE.EVETBL_TIEPROD 1     2                            
                TABLE ACCESS BY INDEX ROWID DSTG_JDE.PROTBL 1   21   2                            
                  INDEX RANGE SCAN DSTG_JDE.I_PROTBL_CODE 1     1                            
              INDEX UNIQUE SCAN DSTG_JDE.PK_CODTBL 1   16                              
            BUFFER SORT  1   7   30                            
              INDEX RANGE SCAN DSTG_JDE.PK_TIETBL 1   7   1                            
 
 
Merci d'avance
 
Cordialement
 
Vegaelce

toute facon ton plan d'exécution est incompréhensible car les noms de tables ne sont pas les mêmes par rapport à ce que tu énonces
 
Donc ce qu'il faut vérifier/faire :
1) vérifier que les statistisques sont à jour : tu utilises le mode CHOOSE donc vérifier que NUM_ROWS n'a pas un trop grand écart par rapport à la réalité et que LAST_ANALYZED est récent (vue USER_TABLES)
 
2) à vue de nez je dirais que ton problème se situe sur la recherche du max dans la sous-requête car il y a manipulations de pas mal de lignes => à vérifier
si il s'avère que c'est effectivement un problème sur le calcul du max avec les critères de recherches, essayes de créer un index calculé  :

tu auras un index dédié à cette sous-requête
 
que ce soit 1) ou 2) à chaque action effectuée il faut comparer le plan d'exécution et les gains/pertes qu'a engendré les modifs. par rapport au fonctionnement initial

et surtout si tu met en place le 2) tu vérifies si tu n'as pas de pertes importantes lors des INSERT et des UPDATE car vu la tronche de l'index que j'ai pondu, y'a de grandes chances que tu perdes en perf. sur ces opérations là

effectivement tu as un peu trop simplifié ton query d'exemple par rapport a ce que l'execution plan nous montre.
 
sinon pour ma part je dirais:
1)teste ton sous-select en y mettant un jeu de valeur litérale pour voir si tu tapes directement dans sur un index suffisament précis que pour ne pas scanner des données qui ne t'intéressent pas. Et créér un index composite qui va bien si ce n'est pas le cas.
 
2) dans certains cas les fonctions analytiques avec un filtre par dessus seront plus performantes, car il n'y aura qu'un seul full scan de la table
 
une truc du style:
l'avantage c'est que c'est très peu dépendant des index
 

 
Bonjour à vous et merci pour ces réponse.
Je n'ai pas beaucoup de degré de liberté sur la table initiale (concernant la création d'index surtout) car je ne suis pas le seul (et loin d'être le prioritaire !!!) à taper dedans.
Cependant, je vais faire d'autre tests aujourd'hui (dont celui de casimimir).
J'ai aussi pensé à la solution suivante (que je vais essayer de tester aujourd'hui), qu'en pensez-vous :
Je crée une vue matérialisée basée sur la sous-requête et indexé (entre autre) sur la date.
Ensuite je crée une autre vue (ou requête directe, je verrai) qui fait une jointure entre ma table initiale et ma vue matérialisée afin de me retourner le résultat obtenu.
Quels sont les inconvénients/avantages de cette solution (surtout en terme de dégradation possible sur les perfs de la table initiale lors du refresh de la vue matérialisée que je mettrai en refresh autmatique).
 
Merci d'avance,
 
cordialement,
 
vegaelce