... qui nécessite votre coup de main pour l'améliorer...
 
Toujours le même ERP, toujours la même base Oracle, toujours les mêmes tables  
 
A savoir :
 
Table PRO produits
 
Index unique :
codsoc
codpro
 
Les autres champs :
sigdep dépôt principal
sigfou fournisseur principal
codblocage statut du produit
 
Table MVT mouvements de stock
 
Index unique :
codsoc
codpro
sigdep
numlot numéro de lot (oujours à ' ' ici)
nummvt numéro de mouvement (compteur autoincrément pour le quaruplet ci-dessus)
 
Index que je viens de créer :
codsoc
codpro
sigdep
datmvt date du mouvement
 
Champs utilisés
c01 stock physique
qteope quantité de l'oppération
codosk type d'opération de stock
 
Table OSK oppérations de stock
 
Index unique :
codsoc
codosk
 
Champs utilisés :
opec01 oppération à effectuer sur le compteur physique c01
 
Je dois retrouver :
1) Les quantités en stock avant inventaire
2) Les quantités inventoriées
3) Le cumul des entrées/sorties dans le stock physique depuis l'inventaire
 
Le tout :
- Sur une période donnée
- Il peut y avoir plusieurs inventaires sur la période
- Il peut ne pas y avoir d'inventaire sur la période
- Si le produit n'a pas bougé du tout dans la période, pas la peine de le retourner
- L'inventaire est reconnaissable dans la base par un CODOSK = 'INICPT' (initialisation des compteurs)
- Pour les oppérations de stock, dans la table OSK j'ai '+' ou '-' (ou ' ') dans la colonne OPEC01 afin d'indiquer pour le CODOSK si je dois ajouter ou supprimer le QTEOPE sur mon compteur physique c01
 
 
Voici la requête actuelle, pour tous les produits d'un fournisseur :

 
Ca marche, mais c'est assez lent : 1 minute. Je dois récupérer la même chose, mais pour TOUS les produits de la base, c'est à dire qu'en estimant à 1 minute par fournisseur, y'en a pour une demi-journée (enfin... plus d'une heure en tout cas )
 
Les parties 2 et 3 de ma requête sont rapides (4 secondes).
 
Par contre, c'est la première qui cloche (quand on voit sa tronche c'est un peu normal aussi)
 
Comment je pourrais l'améliorer ?
Pour rappel, cette partie recherche "la quantité dans le compteur c01 lors du dernier mouvement de stock avant le dernier inventaire de la période"

Reuh...
 
J'ai divisé le temps par deux en ajoutant cet index :
 

 
Mais ça m'emmerde... Les deux derniers UNION durent 700ms seulement... Alors que le premier bout dure 16 seconds à lui tout seul

mé heu
 
pkoi y'a jamais personne qui me répond quand c'est chez moi que ça marche pas ?

 
T'as vu la tête de ta première requete?

tu sais montrer l'execution plan?

 
Il n'y en a pas besoin pour voir que 3 requêtes imbriquées donnent de mauvaises perfs (même avec tout les indexs qui vont bien). Il faufrait plutot chercher du côté de la simplification de la requête.

rappel :  
 

 
Equivaut à :

 
Essaye de voir si c'est pas jouable.
 
Sinon, tu peux peut-être t'en sortir avec GREATEST ou la fonction analytique MAX -> http://lalystar.developpez.com/fon [...] ques/#L3.4

ben pas du tout pour le coup des select.
le premier rammène la valeur maximale de col2 pour chaque valeur de col1, tandis que le second ramène la valeur de col1 pour laquelle col2 est maximale, c'est pas du tout pareil.
 
pour le reste, j'essaie de comprendre ces fonctions que je ne connais pas du tout !

J'arrive pas à l'activer avec Toad. Je suis pourtant en SYSTEM et j'ai lancé toadprep, mais pas moyen.

je suis d'accord sur ce point

pffff, j'ai trop bouffé, j'ai le cerveau en compote, j'arrive plus à réfléchir

je ne suis pas d'accord, au moins on saurait pour quelle partie le cout est le plus élevé et voir ou adapter, que ce soit en réécrivant le select ou en créant un index utile.

Tu pourrais pas donner les scripts pour créer les tables ?
Ca permettrait à ceux qui veulet plancher dessus de le faire plus facilement (moi par exemple ).
 
Il faudrait les CREATE TABLE, CREATE INDEX, et puis même si possible quelques données ...
 
edit :
 
à la va-vite (probablement des erreurs ), j'ai mis le dernier sous-select dans le FROM, et j'ai changé le '= SELECT MAX()' en ORDER BY DESC avec ROWNUM = 1.
Etant donné que je n'ai pas accès aux données, c'est pas évident de vérifier si le résultat est correct, en tout cas je pense que ce sont des pistes à explorer, en particulier si tu dois ensuite faire la même chose pour tous les produits (faut factoriser au maximum )  :
 

 
edit 2 : en gros j'ai appliqué une fois ce qu'orafrance proposait ... même si je ne l'ai lu qu'après

 
relis ta phrase... c'est la même chose
 
1°) raméne le col1 et max(col2)
2°) raméne col1 et col2 ou col2=max(col2) -> col1 et max(col2)
 
Fait un test si t'es pas convaincu

orafrance > ton truc ne marche que si on faut un rownum = 1 dans la requête, sinon il me ramène plein de lignes que je ne veux pas.
 
beegee > a vue de nez, ta requête est largement pire que la mienne 1:45 pour retrouver une ligne (normalement je dois avoir 124 lignes )
vais voir ce qui déconne avant de déclarer qu'elle est plus lente, parceque le fait de pas rammener ce qu'il faut fausse pas mal le résultat je pense
 
Sinon, voici la structure des tables : (tu vas voir que tu préférais quand t'avais pas l'info )
 
 

 
Je sais pas si ça t'aide beaucoup
 
Pour les données, impossible pour deux raisons :
1/ Y'en a trop
2/ Vu que ça concerne les stocks, la valorisation et les fournisseurs d'une chaîne de magasins, c'est strictement confidentiel...
 
Mais je peux te donner la volumétrie :
 
PRO : 24464 lignes (3248 pour codsoc = 2)
OSK : 555 lignes (96 pour codsoc = 2)
MSK : (aïe) 664057 lignes (645915 pour codsoc = 2) et (28776 lignes pour codsoc = 2 et datmvt between '20051231' and '20060131')

En fait, j'arrive pas à réécrire à la sauce orafrance/begee.
 
En français, le bout de requête qui merde ça donne :
 
Je veux récupérer, pour chaque produit du fournisseur "heritage" dans la société 2 la quantité de stock physique lors du dernier mouvement de stock avant la réinitialisation du compteur par les valeurs de l'inventaire le plus récent dans la période du 31/12/2005 au 31/01/2006
 
déjà, rien que pour comprendre ce que je veux, ça laisse rêveur  
 
pour chaque produit du fournisseur "heritage" dans la société 2
=> pro.codsoc = 2 and pro.sigfou = 'HERITAGE'
 
quantité de stock physique
=> msk.c01
 
lors du dernier mouvement de stock
=> max(m1.nummsk) where m1.nummsk < m2.nummsk*
 
la réinitialisation du compteur par les valeurs de l'inventaire le plus récent
=> max(nummsk) where codosk = 'INICPT' (c'est le "m2.nummsk" de la ligne ci-dessus)
 
dans la période du 31/12/2005 au 31/01/2006
=> datmvt between '20051231' and '20060131'
 
et du coup j'arrive pas à faire autrement que mes deux sous-requêtes imbriquées

En fait, c'est un emmerdeur à la finance qui a eu peur en voyant un état merdé du stock (problème d'unité de colisage dans la valorisation du stock) et qui a failli avoir une crise cardiaque en voyant 700 000 € de différence ente la valeur du stock avant et après inventaire.
 
Et vu que ce financier est le directeur de la société financière qui a racheté la majorité des parts de la société, ben si il fait un caca nerveurx, faut dire "amen".
 
Et à cause de la pouff qui a fait un lot de "50000" enveloppes qu'elle a colisé par "1" dans la base et à donc multiplié le prix de ses 1000 colis d'enveloppes par 50000, il n'a plus confiance dans l'inventaire et les mouvements de stock qu'il y a eu depuis.
 
Alors le caca nerveux c'est :
 
Pour tous les produits de tous les fournisseurs :
quantité avant inventaire
quantité inventoriée
quantité régulée (réinventaire)
total des sorties depuis
total des entrées depuis
 
afin de comparer avec les stocks de la base, et un recomptage qu'il a ordonné de faire par une société externe
 
perso, bibi il préfèrerait aller re-compter les enveloppes au dépôt plutôt que de se griller ce qui lui reste de neuronnes sur sa requête à la mord-moi-le-noeud

histoire de mieux saisir ce qu'il y a la dedans,
MSK : (aïe) 664057 lignes (645915 pour codsoc = 2) et (28776 lignes pour codsoc = 2 et datmvt between '20051231' and '20060131')
mais
codsoc = 2 et datmvt between '20051231' and '20060131' et codosk = 'INICPT' ca représente combien de lignes?

 
Un HAVING col2 = MAX(col2) devrait suffir non... sinon, je te dis qu'avec le fenêtrage ça va le faire -> fonctions analytiques

1 ligne
 
en fait, pour le moment, il doit y avoir 1 ou deux lignes avec ce codosk dans toute la base, avant ils faisaient pas d'inventaire
 
mais dans un futur très proche, ils vont faire des inventaires souvent (inventaires rotatifs) et du coup je serai pas à l'abris d'avoir le même produit inventorié plusieurs fois dans une période assez courte.

de mémoire, le having ne marche pas ni dans une sous-requête ni dans un union
 
pis tes requêtes analytiques, je panne rien (je comprends les exemple, mais je vois pas comment ils pourraient m'aider dans mon cas. par contre je garde sous le coude, parceque ça va bien me simplifier la vie pour d'autres requêtes. mais là je vois pas)

donc dans le cas présent tu crées l'index qui va bien avec codsoc,datmvt,codosk et ta sousous-requete devrait aller déja mieux.
 
sinon c'est vrai que les fonctions analytiques ca a l'air costaud, je découvre ca aussi, et a mon avis on doit etre sur la meme page, http://lalystar.developpez.com/fonctionsAnalytiques/ , clair que je garde ca aussi sous le coude, mais la je suis en train de potasser le pl/sql, alors chaque chose en son temps lilalaa

Pour info, ton explain plan avec tes 2 sous-requêtes imbriquées est le suivant :
 
SELECT STATEMENT Hint=RULE                                
  FILTER                                  
    TABLE ACCESS BY INDEX ROWID MSK                              
      NESTED LOOPS                                
        TABLE ACCESS BY INDEX ROWID PRO          
          INDEX RANGE SCAN WI_PRO                              
        INDEX RANGE SCAN MSK_IDX1                
    SORT AGGREGATE                                
      FILTER                                  
        INDEX RANGE SCAN MSK_IDX1                
        SORT AGGREGATE                                
          TABLE ACCESS BY INDEX ROWID MSK          
            INDEX RANGE SCAN MSK_WI_IDX2
 
edit : la requête que j'avais écrite au-dessus comprenait quelques erreurs (GROUP BY manquant par exemple), voic la version corrigée (mais sans données c'est pas évident de voir ce qui cloche) :
 

c'est peut-être con... mais c'est vraiment utile le m1.nummsk < (select max(m2.nummsk) ?
 
Parce que j'vois mal comment nummsk ne pourrait pas être inférieur au max
 
Tu peux expliquer en français ce que fait la 1° requête STP ?
 
Les fonctions analytiques permettent d'appliquer une fonction de regroupement sur les lignes ramenées. Donc tu peux sélectionner le max d'une colonne dans un regroupement défini dans la fonction ce qui peut être très utile puisque GROUP BY doit lister toutes les colonnes.

Apparemment il faut récupérer l'info des lignes ayant num_msk qui est l'avant-dernier d'après ce que j'ai compris ...
 
Est-ce que les num_msk sont incrémentés de 1 ? ou est-ce que c'est plus complexe ?

"il sont censés", mais je n'en ai aucune garantie (l'ERP peut être configuré pour avoir un compteur gobale toutes oppérations/prouits/dépots/etc confondu, un ou compteur différent par dépot, par produit pour par les deux à la fois, etc. je ne peux donc pas faire de plans sur la comète à ce sujet. la seule chose que je sais, c'est que si un trou est créé, on ne le bouchera jamais (pas de bouclage du compteur)
 
d'ailleurs en regardant, contrairement à ce qui est indiqué dans l'index unique, j'ai un compteur par société et c'est tout (donc je dois pouvoir faire une clé unique sur (codsoc, nummvt), donc pour un même produit, les valeurs ne sont pas incrémentées de 1 en 1

vu les deux index que j'ai déjà créé, je suis pas sur qu'il change grand chose (déjà un sir datmvt et un sur codosk, mais pas les deux à la fois)
 
mais bon, j'ai lancé la création quand même...
 
t'ain le tablespace des index, il a dû se prendre 2 Go facile dans la journée là

beegee > le souci de ta requête, c'est que le rownum me retourne 1 ligne par fournisseur. moi j'en veux 1 par produit du fournisseur
 
 
Pour orafrance qui me fait répéter
 
Là j'expliquais la première requête :
 

Quand tu parles de la première partie à optimiser, c'est ce boût de requête ?  
 

 
J'essaye de piocher quelques idées ça et là mais le sujet est vraiment vaste
Je ne pense pas avoir les connaissances pour aider mais j'aimerai bien m'initier à l'optimisation des coûts . Si vous avez des liens à fournir, j'ai déjà consulté ça :
 
http://sqlpro.developpez.com/cours/optimiser/
http://sqlpro.developpez.com/cours/arborescence/

Pour info, voici les CREATE TABLE et INDEX simplifiés pour ceux qui veulent jouer avec les requêtes, avec un tout petit peu de données créées à la mano et les EXPLAIN PLAN correspondants :
 

 
Arjuna : pourrais-tu lancer la dernière requête et me dire ce qui cloche ? (si les données retournées ne sont pas les mêmes tu devrais arriver à voir ce qui cloche ...)

mmm pour les explain plan faut quand meme préciser que c'est sur une table non peuplée

Oui, c'est ce morceau (il a légèrement changé depuis ce matin, mais toujours grossomodo le même)
 

 
(avec la requête totale et complètée -j'en ai rajouté encore un peu part rapport à ce matin-)
 

 
PS: Je viens de shooter le nouvel index, comme je pensais, ça change rien à la durée de la requête (la période porte sur trop peux de lignes pour que ce soit intéressant de filtrer par codosk)

c'est ça qui cloche : "1 ligne(s) séléctionnée(s)" en 1 minutes 29 secondes
 
moi j'en ai 124 lignes en 12 secondes

(en fait, ton petit bout de requête tout seul met plus de temps à tourner que ma requête toute entière )
 
ceci dit, je te remercie de passer du temps à m'aider à chercher une solution
 
bon, je vais aller fumer un café dehors, ça va me rafraîchier l'esprit, chuis en train de choper mal à la tête)

Ouaip j'ai vu , j'ai posté en //.
 
Pas évident à 1ère vue de modifier la requête ...

erf, j'ai foutu le serveur à genoux y'a tout le monde qui râle dans les couloirs

Tu as essayé un index sur codsoc, codpro, sigdep ?
 
Voici ce que ça donne sur ta requête, ainsi que sur la requête réécrite avec 2 GROUP BY :
 

 
ben les 3 index de la table tapent contiennent déjà ce triplet en première position, donc "oui"
 
pis je crée plus d'index aujourd'hui, à chaque fois tout le monde est boqué 10 minutes...

Bon, allez, je lance la requête pour tous les fournisseurs confondus... Après tout, avec beaucoup de chance, ce sera pas beaucoup plus lent
(l'espoir fait vivre )

Une petite question bête : est-ce que vous savez pourquoi quand je lance une requête avec TOAD, ce dernier occupe 100% du CPU durant toute la requête
 
Genre on dirait que c'est lui qui execute la requête !