Forum |  HardWare.fr | News | Articles | PC | S'identifier | S'inscrire | Shop Recherche
1259 connectés 

  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [SGBD] Encore une requête à décorner les boeufs...

 
 


 Mot :   Pseudo :  
 
 Page :   1  2
Page Suivante
Page Précédente
Bas de page 
Auteur Sujet :

[SGBD] Encore une requête à décorner les boeufs...

n°1297261
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 11:57:35  profilanswer
 

... 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 [:atari]  
 
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 :

Code :
  1. select '1' ord, pro.sigfou, msk.sigdep, pro.codpro, pro.codblocage, 0 entrees, 0 sorties, 0 initinventaire, msk.c01 avantinv
  2. from msk, pro
  3. where pro.codsoc = 2
  4. and pro.sigfou = 'HERITAGE'
  5. and msk.codsoc = pro.codsoc
  6. and msk.codpro = pro.codpro
  7. and msk.sigdep = pro.sigdep
  8. and msk.numlot = ' '
  9. and msk.nummsk =
  10. (select max(m1.nummsk)
  11. from msk m1
  12. where m1.codsoc = msk.codsoc
  13. and m1.codpro = msk.codpro
  14. and m1.sigdep = msk.sigdep
  15. and m1.numlot = ' '
  16. and m1.nummsk < (
  17. select max(m2.nummsk)
  18. from msk m2
  19. where m2.codsoc = m1.codsoc
  20. and m2.codpro = m1.codpro
  21. and m2.sigdep = m1.sigdep
  22. and m2.datmvt between '20051231' and '20060131'
  23. and m2.codosk = 'INICPT'
  24. ))
  25. union all
  26. select '2' ord, pro.sigfou, msk.sigdep, pro.codpro, pro.codblocage, 0 entreesapres, 0 sortiesapres, msk.c01 initinventaire, 0 avantinv
  27. from msk, pro
  28. where pro.codsoc = 2
  29. and pro.sigfou = 'HERITAGE'
  30. and msk.codsoc = pro.codsoc
  31. and msk.sigdep = pro.sigdep
  32. and msk.codpro = pro.codpro
  33. and msk.datmvt between '20051231' and '20060131'
  34. and msk.codosk = 'INICPT'
  35. union all
  36. select '3' ord, pro.sigfou, msk.sigdep, pro.codpro, pro.codblocage, sum(msk.qteope * decode(osk.opec01, '+', 1, 0)) entreesapres, sum(msk.qteope * decode(osk.opec01, '-', 1, 0)) sortiesapres, 0 initinventaire, 0 avantinv
  37. from osk, msk, pro
  38. where pro.codsoc = 2
  39. and pro.sigfou = 'HERITAGE'
  40. and msk.codsoc = pro.codsoc
  41. and msk.sigdep = pro.sigdep
  42. and msk.codpro = pro.codpro
  43. and msk.datmvt between '20051231' and '20060131'
  44. and osk.codsoc = msk.codsoc
  45. and osk.codosk = msk.codosk
  46. and
  47. (
  48.   msk.nummsk > (select max(nummsk) from msk m where m.codsoc = msk.codsoc and m.sigdep = msk.sigdep and m.codpro = msk.codpro and m.datmvt between '20051231' and '20060131' and m.codosk = 'INICPT')
  49.   or
  50.   not exists (select null from msk m where m.codsoc = msk.codsoc and m.sigdep = msk.sigdep and m.codpro = msk.codpro and m.datmvt between '20051231' and '20060131' and m.codosk = 'INICPT')
  51. )
  52. group by pro.sigfou, pro.codpro, pro.codblocage, msk.sigdep
  53. order by 2, 3, 4, 1


 
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 :D (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"

Code :
  1. select '1' ord, pro.sigfou, msk.sigdep, pro.codpro, pro.codblocage, 0 entrees, 0 sorties, 0 initinventaire, msk.c01 avantinv
  2. from msk, pro
  3. where pro.codsoc = 2
  4. and pro.sigfou = 'HERITAGE'
  5. and msk.codsoc = pro.codsoc
  6. and msk.codpro = pro.codpro
  7. and msk.sigdep = pro.sigdep
  8. and msk.numlot = ' '
  9. and msk.nummsk =
  10. (select max(m1.nummsk)
  11. from msk m1
  12. where m1.codsoc = msk.codsoc
  13. and m1.codpro = msk.codpro
  14. and m1.sigdep = msk.sigdep
  15. and m1.numlot = ' '
  16. and m1.nummsk < (
  17. select max(m2.nummsk)
  18. from msk m2
  19. where m2.codsoc = m1.codsoc
  20. and m2.codpro = m1.codpro
  21. and m2.sigdep = m1.sigdep
  22. and m2.datmvt between '20051231' and '20060131'
  23. and m2.codosk = 'INICPT'
  24. ))


 
:sweat:


Message édité par Arjuna le 02-02-2006 à 11:58:56
mood
Publicité
Posté le 02-02-2006 à 11:57:35  profilanswer
 

n°1297270
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 12:08:41  profilanswer
 

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

Code :
  1. CREATE INDEX MSK_WI_IDX2 ON MSK
  2. (CODSOC, CODPRO, SIGDEP, CODOSK)
  3. LOGGING
  4. TABLESPACE IDXGNX1
  5. PCTFREE    10
  6. INITRANS   2
  7. MAXTRANS   255
  8. STORAGE    (
  9.             INITIAL          24K
  10.             NEXT             7000K
  11.             MINEXTENTS       1
  12.             MAXEXTENTS       120
  13.             PCTINCREASE      10
  14.             FREELISTS        1
  15.             FREELIST GROUPS  1
  16.             BUFFER_POOL      DEFAULT
  17.            )
  18. NOPARALLEL;


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

n°1297302
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 12:42:51  profilanswer
 

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

Message cité 1 fois
n°1297306
thecoin
Chasseur de chasseur de canard
Posté le 02-02-2006 à 12:48:13  profilanswer
 

Arjuna a écrit :

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


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

n°1297314
casimimir
Posté le 02-02-2006 à 12:59:05  profilanswer
 

tu sais montrer l'execution plan?

Message cité 2 fois
n°1297322
thecoin
Chasseur de chasseur de canard
Posté le 02-02-2006 à 13:14:29  profilanswer
 

casimimir a écrit :

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.

Message cité 1 fois
Message édité par thecoin le 02-02-2006 à 13:14:40
n°1297355
orafrance
Posté le 02-02-2006 à 13:58:33  profilanswer
 

rappel :  
 

Code :
  1. SELECT col1,max(col2)
  2. FROM...
  3. GROUP BY col1


 
Equivaut à :

Code :
  1. SELECT col1,col2
  2. FROM...
  3. WHERE col2 = (SELECT max(col2) FROM ... WHERE col2=col2


 
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

n°1297385
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 14:24:32  profilanswer
 

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 !

Message cité 1 fois
n°1297395
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 14:36:54  profilanswer
 

casimimir a écrit :

tu sais montrer l'execution plan?


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

n°1297397
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 14:37:17  profilanswer
 

thecoin a écrit :

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.


je suis d'accord sur ce point

mood
Publicité
Posté le 02-02-2006 à 14:37:17  profilanswer
 

n°1297404
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 14:43:15  profilanswer
 

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

n°1297408
casimimir
Posté le 02-02-2006 à 14:48:25  profilanswer
 

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.

n°1297410
Beegee
Posté le 02-02-2006 à 14:50:04  profilanswer
 

Tu pourrais pas donner les scripts pour créer les tables ?
Ca permettrait à ceux qui veulet plancher dessus de le faire plus facilement :D (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 :D ), 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 ;) )  :
 

Code :
  1. SELECT * FROM (
  2. SELECT '1' ord, pro.sigfou, msk.sigdep, pro.codpro, pro.codblocage, 0 entrees, 0 sorties, 0 initinventaire, msk.c01 avantinv
  3. FROM msk, pro,
  4.      (SELECT codsoc, codpro, sigdep, MAX(m2.nummsk) AS max_nummsk
  5.       FROM msk m2
  6.       WHERE m2.datmvt BETWEEN '20051231' AND '20060131'
  7.       AND m2.codosk = 'INICPT') sub
  8. WHERE pro.codsoc = 2
  9. AND pro.sigfou = 'HERITAGE'
  10. AND msk.codsoc = pro.codsoc
  11. AND msk.codpro = pro.codpro
  12. AND msk.sigdep = pro.sigdep
  13. AND msk.numlot = ' '
  14. AND sub.codsoc = msk.codsoc
  15. AND sub.codpro = msk.codpro
  16. AND sub.sigdep = msk.sigdep
  17. AND msk.nummsk < sub.max_nummsk
  18. ORDER BY msk.nummsk DESC)
  19. WHERE ROWNUM = 1;


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


Message édité par Beegee le 02-02-2006 à 15:03:34
n°1297425
orafrance
Posté le 02-02-2006 à 15:01:19  profilanswer
 

Arjuna a écrit :


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.


 
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 ;)

n°1297482
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 15:32:03  profilanswer
 

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 :D 1:45 pour retrouver une ligne (normalement je dois avoir 124 lignes :D) :sweat:
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 ;))
 
 

Code :
  1. CREATE TABLE PRO
  2. (
  3.   CODPRO      VARCHAR2(16),
  4.   MOTCLE      VARCHAR2(16),
  5.   FAMPRO      VARCHAR2(6),
  6.   SFAPRO      VARCHAR2(6),
  7.   SSFPRO      VARCHAR2(6),
  8.   NOMPRO      VARCHAR2(30),
  9.   GENCOD      VARCHAR2(13),
  10.   CODDOU      VARCHAR2(16),
  11.   TYPPRO      VARCHAR2(3),
  12.   EXICOT      VARCHAR2(1),
  13.   CODTVA      VARCHAR2(1),
  14.   CODUNI      VARCHAR2(3),
  15.   QTEMIN      NUMBER,
  16.   CODTAX      VARCHAR2(1),
  17.   CODTAX2     VARCHAR2(1),
  18.   SIGDEP      VARCHAR2(12),
  19.   CODREA      VARCHAR2(6),
  20.   SUISTK      VARCHAR2(1),
  21.   PROSTK      VARCHAR2(16),
  22.   DELPRE      INTEGER,
  23.   SUISTT      VARCHAR2(1),
  24.   CLAABC      VARCHAR2(1),
  25.   DATMAR      VARCHAR2(8),
  26.   POIDS       NUMBER,
  27.   HAUTE       NUMBER,
  28.   LARGE       NUMBER,
  29.   LONGUE      NUMBER,
  30.   VOLUME      NUMBER,
  31.   CODTAR      VARCHAR2(16),
  32.   DESIGN1     VARCHAR2(40),
  33.   DESIGN2     VARCHAR2(40),
  34.   DESIGN3     VARCHAR2(40),
  35.   TYPTAR      VARCHAR2(3),
  36.   SIGFOU      VARCHAR2(12),
  37.   DATMOD      VARCHAR2(8),
  38.   UTIMOD      VARCHAR2(8),
  39.   REFPRO      VARCHAR2(16),
  40.   NUMVAR      INTEGER,
  41.   TPSMON      INTEGER,
  42.   INDCOM      VARCHAR2(1),
  43.   VA          VARCHAR2(2),
  44.   VL          VARCHAR2(2),
  45.   CODUVC      VARCHAR2(3),
  46.   CODUE       VARCHAR2(3),
  47.   NBUCUE      INTEGER,
  48.   PCB         INTEGER,
  49.   UNIPCB      VARCHAR2(3),
  50.   SPCB        INTEGER,
  51.   UNISPCB     VARCHAR2(3),
  52.   CODZN1      VARCHAR2(12),
  53.   CODZN2      VARCHAR2(12),
  54.   CODZN3      VARCHAR2(12),
  55.   CODZN4      VARCHAR2(12),
  56.   CODZN5      VARCHAR2(12),
  57.   CODZN6      VARCHAR2(12),
  58.   CODZN7      VARCHAR2(12),
  59.   CODZN8      VARCHAR2(12),
  60.   CODZN9      VARCHAR2(12),
  61.   CODZN10     VARCHAR2(12),
  62.   CODZN11     VARCHAR2(12),
  63.   CODZN12     VARCHAR2(12),
  64.   CODZN13     VARCHAR2(12),
  65.   CODZN14     VARCHAR2(12),
  66.   CODZN15     VARCHAR2(12),
  67.   CODZN16     VARCHAR2(12),
  68.   UNICOL      VARCHAR2(3),
  69.   COLEC1      VARCHAR2(1),
  70.   STCK1       INTEGER,
  71.   COLEC2      VARCHAR2(1),
  72.   STCK2       INTEGER,
  73.   COLEC3      VARCHAR2(1),
  74.   STCK3       INTEGER,
  75.   COLEC4      VARCHAR2(1),
  76.   STCK4       INTEGER,
  77.   COLEC5      VARCHAR2(1),
  78.   STCK5       INTEGER,
  79.   COLEC6      VARCHAR2(1),
  80.   STCK6       INTEGER,
  81.   COLEC7      VARCHAR2(1),
  82.   STCK7       INTEGER,
  83.   COLEC8      VARCHAR2(1),
  84.   STCK8       INTEGER,
  85.   COLEC9      VARCHAR2(1),
  86.   STCK9       INTEGER,
  87.   DESIGN4     VARCHAR2(40),
  88.   DESIGN5     VARCHAR2(40),
  89.   DESIGN6     VARCHAR2(40),
  90.   DESIGN7     VARCHAR2(40),
  91.   DESIGN8     VARCHAR2(40),
  92.   DESIGN9     VARCHAR2(40),
  93.   DESIGN10    VARCHAR2(40),
  94.   DESIGN11    VARCHAR2(40),
  95.   DESIGN12    VARCHAR2(40),
  96.   CODDEV      VARCHAR2(3),
  97.   CODBLOCAGE  VARCHAR2(3),
  98.   POIDSNET    NUMBER,
  99.   TYPAFF      VARCHAR2(6),
  100.   QOTITE      NUMBER,
  101.   UNISTA      VARCHAR2(3),
  102.   PROSTA      VARCHAR2(16),
  103.   INDCTS      VARCHAR2(1),
  104.   INDRIS      VARCHAR2(1),
  105.   VALPVR      NUMBER,
  106.   PDSPVR      NUMBER,
  107.   VOLPVR      NUMBER,
  108.   NUMLSK      INTEGER,
  109.   CODSOC      INTEGER,
  110.   DELREA      INTEGER,
  111.   DATCRE      VARCHAR2(8),
  112.   ETBCOD      VARCHAR2(3),
  113.   ETSSTATUT   VARCHAR2(1),
  114.   CODCONV     VARCHAR2(3),
  115.   CLAABCP     VARCHAR2(1),
  116.   DELPER      INTEGER,
  117.   SUIINS      VARCHAR2(1),
  118.   SUIICE      VARCHAR2(1),
  119.   NOMISE      VARCHAR2(1),
  120.   UNIICE      VARCHAR2(3),
  121.   TYPIFA      VARCHAR2(1),
  122.   UNIPER      VARCHAR2(3),
  123.   DELGAR      INTEGER,
  124.   UNIGAR      VARCHAR2(3),
  125.   ACHICE      VARCHAR2(1),
  126.   TYPICE      VARCHAR2(3),
  127.   SIGICE      VARCHAR2(12),
  128.   VERICE      INTEGER,
  129.   PRORUB      VARCHAR2(16),
  130.   TEECOD      VARCHAR2(1),
  131.   TEEUNI      VARCHAR2(3),
  132.   TEEPRX      NUMBER,
  133.   MODLOT      VARCHAR2(1),
  134.   CODPAY      VARCHAR2(3),
  135.   TTAICE      VARCHAR2(3),
  136.   CODMDL      VARCHAR2(6),
  137.   PROREF      VARCHAR2(16),
  138.   SUIREF      VARCHAR2(1),
  139.   REPART      NUMBER,
  140.   CODUNIC     VARCHAR2(60),
  141.   TYPREF      VARCHAR2(6),
  142.   DATFINMAR   VARCHAR2(8)
  143. )
  144. TABLESPACE DATGNX1
  145. PCTUSED    40
  146. PCTFREE    10
  147. INITRANS   1
  148. MAXTRANS   255
  149. STORAGE    (
  150.             INITIAL          32K
  151.             NEXT             1816K
  152.             MINEXTENTS       1
  153.             MAXEXTENTS       120
  154.             PCTINCREASE      10
  155.             FREELISTS        1
  156.             FREELIST GROUPS  1
  157.             BUFFER_POOL      DEFAULT
  158.            )
  159. LOGGING
  160. NOCACHE
  161. NOPARALLEL;
  164. CREATE UNIQUE INDEX PRO_IDX1 ON PRO
  165. (CODSOC, CODPRO)
  166. LOGGING
  167. TABLESPACE IDXGNX1
  168. PCTFREE    10
  169. INITRANS   2
  170. MAXTRANS   255
  171. STORAGE    (
  172.             INITIAL          24K
  173.             NEXT             168K
  174.             MINEXTENTS       1
  175.             MAXEXTENTS       120
  176.             PCTINCREASE      10
  177.             FREELISTS        1
  178.             FREELIST GROUPS  1
  179.             BUFFER_POOL      DEFAULT
  180.            )
  181. NOPARALLEL;
  184. CREATE INDEX PRO_IDX9 ON PRO
  185. (CODSOC, GENCOD)
  186. LOGGING
  187. TABLESPACE IDXGNX1
  188. PCTFREE    10
  189. INITRANS   2
  190. MAXTRANS   255
  191. STORAGE    (
  192.             INITIAL          24K
  193.             NEXT             240K
  194.             MINEXTENTS       1
  195.             MAXEXTENTS       120
  196.             PCTINCREASE      10
  197.             FREELISTS        1
  198.             FREELIST GROUPS  1
  199.             BUFFER_POOL      DEFAULT
  200.            )
  201. NOPARALLEL;
  204. CREATE INDEX PRO_WEB_IX1 ON PRO
  205. (CODSOC, SIGFOU, FAMPRO, SSFPRO, SUISTK,
  206. CODBLOCAGE)
  207. LOGGING
  208. TABLESPACE IDXGNX1
  209. PCTFREE    10
  210. INITRANS   2
  211. MAXTRANS   255
  212. STORAGE    (
  213.             INITIAL          24K
  214.             NEXT             55M
  215.             MINEXTENTS       1
  216.             MAXEXTENTS       2147483645
  217.             PCTINCREASE      10
  218.             FREELISTS        1
  219.             FREELIST GROUPS  1
  220.             BUFFER_POOL      DEFAULT
  221.            )
  222. NOPARALLEL;
  225. CREATE INDEX WI_PRO ON PRO
  226. (CODSOC, SIGFOU)
  227. LOGGING
  228. TABLESPACE IDXGNX1
  229. PCTFREE    10
  230. INITRANS   2
  231. MAXTRANS   255
  232. STORAGE    (
  233.             INITIAL          24K
  234.             NEXT             192K
  235.             MINEXTENTS       1
  236.             MAXEXTENTS       120
  237.             PCTINCREASE      10
  238.             FREELISTS        1
  239.             FREELIST GROUPS  1
  240.             BUFFER_POOL      DEFAULT
  241.            )
  242. NOPARALLEL;
  245. CREATE INDEX WI_PROZN15 ON PRO
  246. (CODSOC, CODZN15)
  247. LOGGING
  248. TABLESPACE IDXGNX1
  249. PCTFREE    10
  250. INITRANS   2
  251. MAXTRANS   255
  252. STORAGE    (
  253.             INITIAL          24K
  254.             NEXT             264K
  255.             MINEXTENTS       1
  256.             MAXEXTENTS       120
  257.             PCTINCREASE      10
  258.             FREELISTS        1
  259.             FREELIST GROUPS  1
  260.             BUFFER_POOL      DEFAULT
  261.            )
  262. NOPARALLEL;
  265. CREATE OR REPLACE TRIGGER "MAJ_PRO" AFTER INSERT OR UPDATE OF "CODPRO", "CODSOC", "CODZN2", "SIGFOU", "VOLUME" ON "SOC1"."PRO" FOR EACH ROW
  266. WHEN (
  267. nvl(old.codzn2,0)<>new.codzn2 or old.volume<>new.volume and new.codsoc<>3
  268.       )
  269. declare
  270. cursor frn is
  271.   select coddev,decode(codzn1,' ','0',codzn1) codzn1,
  272.          replace(decode(codzn2,' ','0',codzn2),',','.') codzn2
  273.     from tie
  274.    where sigtie=:new.sigfou and typtie='FOU' and codsoc=:new.codsoc;
  275. codev     varchar2(6);
  276. dadev     varchar2(30);
  277. txdev     number(18,6);
  278. cdzn      varchar2(12);
  279. pxach     number;
  280. frapp     number;
  281. pxrev     number;
  282. coefmg    number:=2.2;
  283. pxvenc    number;
  284. pxven     number;
  285. mrgval    number;
  286. mrgpct    number;
  287. dev_abs   varchar2(2):='NK';
  288. begin
  289.   for f in frn loop
  290.   begin
  291.     select cours into txdev from dem
  292.      where codsoc=:new.codsoc and coddev=f.coddev and natdev='PRE' and
  293.              (datdeb,datfin) = (select max(datdeb), datfin from dem
  294.                                  where codsoc=:new.codsoc and coddev=f.coddev and
  295.                                        natdev='PRE' and datdeb<=to_char(sysdate,'yyyymmdd') and
  296.                                        nvl(datfin,'29991231')<=to_char(sysdate,'yyyymmdd')
  297.                                  group by datfin
  298.                                  having max(datdeb)<=to_char(sysdate,'yyyymmdd'));
  299.   exception
  300.     when no_data_found then txdev:=1;
  301.   end;
  302.    if :new.codzn2=' ' /* px achat obligatoire */
  303.     then cdzn:='1';
  304.     else cdzn:=:new.codzn2;
  305.    end if;
  306.     pxach:=to_number(replace(cdzn,',','.'))*to_number(txdev);
  307.       if (f.codzn1=0 and f.codzn2=0)
  308.        then frapp:=0;
  309.        else if f.codzn2=0    /* val au m3 par defaut sinon %VM */
  310.              then frapp:=to_number(f.codzn1)*pxach/100;
  311.              else frapp:=to_number(f.codzn2)*:new.volume;
  312.             end if;
  313.        end if;
  314.     pxrev:=pxach+frapp;
  315.     pxvenc:=pxrev*coefmg; /* que pour insertion */
  316.     mrgval:=pxvenc-pxrev;
  317.     if pxvenc<>0
  318.      then mrgpct:=mrgval/pxvenc; /*/100;*/
  319.      else mrgpct:=mrgval;
  320.     end if;
  321.     begin
  322.       select px_vent into pxven
  323.         from wt_prdwr_art
  324.        where co_art=:new.codpro and co_soc=:new.codsoc and co_dev=f.coddev;
  325. /*      mrgval:=pxvenc-pxrev;
  326.       if pxvenc<>0
  327.        then mrgpct:=mrgval/pxvenc;  
  328.        else mrgpct:=mrgval;
  329.       end if;
  330.       if pxrev<>0
  331.        then coefmg:=pxvenc/pxrev;
  332.        else coefmg:=pxvenc;
  333.       end if;
  334.       coef_mrg=coefmg, */
  335.       update wt_prdwr_art set px_acht=pxach, fr_apprt=frapp, px_revt=pxrev,
  336.                               mrg_valt=mrgval, mrg_prctgt=mrgpct,
  337.                               da_modif=sysdate, tx_dev=txdev, px_vent=pxvenc
  338.        where co_art=:new.codpro and co_soc=:new.codsoc and co_dev=f.coddev;
  339.     exception
  340.       when no_data_found then
  341.         insert into wt_prdwr_art
  342.          values (:new.codsoc, :new.codpro, f.coddev, txdev,  pxach, frapp, pxrev, coefmg,
  343.                  pxvenc, mrgval, mrgpct, sysdate,sysdate);
  344.     end;
  345.   end loop;
  346. exception
  347.   when no_data_found then null;
  348. end;
  349. /
  350. SHOW ERRORS;
  355. CREATE TABLE MSK
  356. (
  357.   SIGDEP  VARCHAR2(12),
  358.   CODPRO  VARCHAR2(16),
  359.   NUMLOT  VARCHAR2(7),
  360.   NUMMSK  INTEGER,
  361.   NUMPER  INTEGER,
  362.   DATMVT  VARCHAR2(8),
  363.   HEUMVT  NUMBER,
  364.   DATPRV  VARCHAR2(8),
  365.   CODOSK  VARCHAR2(6),
  366.   CODEMP  VARCHAR2(10),
  367.   ACHVTE  VARCHAR2(1),
  368.   TYPEVE  VARCHAR2(3),
  369.   NUMEVE  INTEGER,
  370.   POSEVE  INTEGER,
  371.   LIGEVE  INTEGER,
  372.   QTEOPE  NUMBER,
  373.   QTEMVT  NUMBER,
  374.   PRXVTE  NUMBER,
  375.   DPA     NUMBER,
  376.   PUMP    NUMBER,
  377.   DECOTE  NUMBER,
  378.   C01     NUMBER,
  379.   C02     NUMBER,
  380.   C03     NUMBER,
  381.   C04     NUMBER,
  382.   C05     NUMBER,
  383.   C06     NUMBER,
  384.   C07     NUMBER,
  385.   C08     NUMBER,
  386.   C09     NUMBER,
  387.   C10     NUMBER,
  388.   C11     NUMBER,
  389.   C12     NUMBER,
  390.   C13     NUMBER,
  391.   C14     NUMBER,
  392.   CODUNI  VARCHAR2(3),
  393.   UNIFAC  VARCHAR2(3),
  394.   DATMOD  VARCHAR2(8),
  395.   UTIMOD  VARCHAR2(8),
  396.   TYPOSK  VARCHAR2(3),
  397.   NUMDEB  VARCHAR2(12),
  398.   NUMFIN  VARCHAR2(12),
  399.   PRXREV  NUMBER,
  400.   CODSOC  INTEGER,
  401.   NUMOF   VARCHAR2(10),
  402.   LIBMSK  VARCHAR2(30),
  403.   INDTRT  VARCHAR2(1),
  404.   INDCES  VARCHAR2(1),
  405.   CODFCT  VARCHAR2(8),
  406.   CODPEV  VARCHAR2(8)
  407. )
  408. TABLESPACE DATGNX1
  409. PCTUSED    40
  410. PCTFREE    10
  411. INITRANS   1
  412. MAXTRANS   255
  413. STORAGE    (
  414.             INITIAL          32K
  415.             NEXT             15048K
  416.             MINEXTENTS       1
  417.             MAXEXTENTS       120
  418.             PCTINCREASE      10
  419.             FREELISTS        1
  420.             FREELIST GROUPS  1
  421.             BUFFER_POOL      DEFAULT
  422.            )
  423. LOGGING
  424. NOCACHE
  425. NOPARALLEL;
  428. CREATE UNIQUE INDEX MSK_IDX1 ON MSK
  429. (CODSOC, CODPRO, SIGDEP, NUMLOT, NUMMSK)
  430. LOGGING
  431. TABLESPACE IDXGNX1
  432. PCTFREE    10
  433. INITRANS   2
  434. MAXTRANS   255
  435. STORAGE    (
  436.             INITIAL          24K
  437.             NEXT             3928K
  438.             MINEXTENTS       1
  439.             MAXEXTENTS       120
  440.             PCTINCREASE      10
  441.             FREELISTS        1
  442.             FREELIST GROUPS  1
  443.             BUFFER_POOL      DEFAULT
  444.            )
  445. NOPARALLEL;
  448. CREATE INDEX MSK_WI_IDX1 ON MSK
  449. (CODSOC, CODPRO, SIGDEP, DATMVT)
  450. LOGGING
  451. TABLESPACE IDXGNX1
  452. PCTFREE    10
  453. INITRANS   2
  454. MAXTRANS   255
  455. STORAGE    (
  456.             INITIAL          24K
  457.             NEXT             7000K
  458.             MINEXTENTS       1
  459.             MAXEXTENTS       120
  460.             PCTINCREASE      10
  461.             FREELISTS        1
  462.             FREELIST GROUPS  1
  463.             BUFFER_POOL      DEFAULT
  464.            )
  465. NOPARALLEL;
  468. CREATE INDEX MSK_WI_IDX2 ON MSK
  469. (CODSOC, CODPRO, SIGDEP, CODOSK)
  470. LOGGING
  471. TABLESPACE IDXGNX1
  472. PCTFREE    10
  473. INITRANS   2
  474. MAXTRANS   255
  475. STORAGE    (
  476.             INITIAL          24K
  477.             NEXT             10264K
  478.             MINEXTENTS       1
  479.             MAXEXTENTS       120
  480.             PCTINCREASE      10
  481.             FREELISTS        1
  482.             FREELIST GROUPS  1
  483.             BUFFER_POOL      DEFAULT
  484.            )
  485. NOPARALLEL;
  489. CREATE TABLE OSK
  490. (
  491.   CODOSK     VARCHAR2(6),
  492.   LIBOSK     VARCHAR2(30),
  493.   LIROSK     VARCHAR2(10),
  494.   OPEC01     VARCHAR2(1),
  495.   OPEC02     VARCHAR2(1),
  496.   OPEC03     VARCHAR2(1),
  497.   OPEC04     VARCHAR2(1),
  498.   OPEC05     VARCHAR2(1),
  499.   OPEC06     VARCHAR2(1),
  500.   OPEC07     VARCHAR2(1),
  501.   OPEC08     VARCHAR2(1),
  502.   OPEC09     VARCHAR2(1),
  503.   OPEC10     VARCHAR2(1),
  504.   OPEC11     VARCHAR2(1),
  505.   OPEC12     VARCHAR2(1),
  506.   OPEC13     VARCHAR2(1),
  507.   OPEC14     VARCHAR2(1),
  508.   PRVC01     VARCHAR2(1),
  509.   PRVC02     VARCHAR2(1),
  510.   PRVC03     VARCHAR2(1),
  511.   PRVC04     VARCHAR2(1),
  512.   PRVC05     VARCHAR2(1),
  513.   PRVC06     VARCHAR2(1),
  514.   PRVC07     VARCHAR2(1),
  515.   PRVC08     VARCHAR2(1),
  516.   PRVC09     VARCHAR2(1),
  517.   PRVC10     VARCHAR2(1),
  518.   PRVC11     VARCHAR2(1),
  519.   PRVC12     VARCHAR2(1),
  520.   PRVC13     VARCHAR2(1),
  521.   PRVC14     VARCHAR2(1),
  522.   LOTOBL     VARCHAR2(1),
  523.   CALPMP     VARCHAR2(1),
  524.   OPEMAN     VARCHAR2(1),
  525.   TYPEVE     VARCHAR2(3),
  526.   CSKTE1     VARCHAR2(6),
  527.   OPETST     VARCHAR2(2),
  528.   CSKTE2     VARCHAR2(6),
  529.   OPEQT1     VARCHAR2(1),
  530.   CSKQT1     VARCHAR2(6),
  531.   OPEQT2     VARCHAR2(1),
  532.   CSKQT2     VARCHAR2(6),
  533.   OPEQT3     VARCHAR2(1),
  534.   CSKQT3     VARCHAR2(6),
  535.   CSKSUI     VARCHAR2(6),
  536.   OPESU1     VARCHAR2(1),
  537.   CSKSU1     VARCHAR2(6),
  538.   OPESU2     VARCHAR2(1),
  539.   CSKSU2     VARCHAR2(6),
  540.   OPESU3     VARCHAR2(1),
  541.   CSKSU3     VARCHAR2(6),
  542.   TYPOSK     VARCHAR2(3),
  543.   DATMOD     VARCHAR2(8),
  544.   UTIMOD     VARCHAR2(8),
  545.   TYPCTR     VARCHAR2(1),
  546.   EMPLOBL    VARCHAR2(1),
  547.   MAJINV     VARCHAR2(1),
  548.   GENMSK     VARCHAR2(1),
  549.   SEROBL     VARCHAR2(1),
  550.   CODSOC     INTEGER,
  551.   CODCSK     VARCHAR2(6),
  552.   OPECSK     VARCHAR2(1),
  553.   CODUT_MES  VARCHAR2(10),
  554.   PROPRIETE  VARCHAR2(1)
  555. )
  556. TABLESPACE DATGNX1
  557. PCTUSED    40
  558. PCTFREE    10
  559. INITRANS   1
  560. MAXTRANS   255
  561. STORAGE    (
  562.             INITIAL          32K
  563.             NEXT             560K
  564.             MINEXTENTS       1
  565.             MAXEXTENTS       120
  566.             PCTINCREASE      10
  567.             FREELISTS        1
  568.             FREELIST GROUPS  1
  569.             BUFFER_POOL      DEFAULT
  570.            )
  571. LOGGING
  572. NOCACHE
  573. NOPARALLEL;
  576. CREATE UNIQUE INDEX OSK_IDX1 ON OSK
  577. (CODSOC, CODOSK)
  578. LOGGING
  579. TABLESPACE IDXGNX1
  580. PCTFREE    10
  581. INITRANS   2
  582. MAXTRANS   255
  583. STORAGE    (
  584.             INITIAL          24K
  585.             NEXT             64K
  586.             MINEXTENTS       1
  587.             MAXEXTENTS       120
  588.             PCTINCREASE      10
  589.             FREELISTS        1
  590.             FREELIST GROUPS  1
  591.             BUFFER_POOL      DEFAULT
  592.            )
  593. NOPARALLEL;


 
Je sais pas si ça t'aide beaucoup :ange:
 
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')

Message cité 1 fois
n°1297490
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 15:43:57  profilanswer
 

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 [:atari]  
 
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

Message cité 1 fois
Message édité par Arjuna le 02-02-2006 à 15:47:30
n°1297504
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 15:54:41  profilanswer
 

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 :o
 
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 :sweat:


Message édité par Arjuna le 02-02-2006 à 15:55:30
n°1297506
casimimir
Posté le 02-02-2006 à 15:55:33  profilanswer
 

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?

Message cité 1 fois
n°1297510
orafrance
Posté le 02-02-2006 à 15:58:47  profilanswer
 

Arjuna a écrit :

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.


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

Message cité 1 fois
n°1297521
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 16:04:20  profilanswer
 

casimimir a écrit :

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?


1 ligne :sol:
 
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 :sol:
 
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.

n°1297524
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 16:05:59  profilanswer
 

orafrance a écrit :

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


de mémoire, le having ne marche pas ni dans une sous-requête ni dans un union :spamafote:
 
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)

n°1297533
casimimir
Posté le 02-02-2006 à 16:10:09  profilanswer
 

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

Message cité 1 fois
n°1297537
Beegee
Posté le 02-02-2006 à 16:12:15  profilanswer
 

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

Code :
  1. SELECT * FROM (
  2. SELECT '1' ord, pro.sigfou, msk.sigdep, pro.codpro, pro.codblocage, 0 entrees, 0 sorties, 0 initinventaire, msk.c01 avantinv
  3. FROM msk, pro,
  4.      (SELECT codsoc, codpro, sigdep, MAX(m2.nummsk) AS max_nummsk
  5.       FROM msk m2
  6.       WHERE m2.datmvt BETWEEN '20051231' AND '20060131'
  7.       AND m2.codosk = 'INICPT'
  8.       GROUP BY codsoc, codpro, sigdep) sub
  9. WHERE pro.codsoc = 2
  10. AND pro.sigfou = 'HERITAGE'
  11. AND msk.codsoc = pro.codsoc
  12. AND msk.codpro = pro.codpro
  13. AND msk.sigdep = pro.sigdep
  14. AND msk.numlot = ' '
  15. AND sub.codsoc = msk.codsoc
  16. AND sub.codpro = msk.codpro
  17. AND sub.sigdep = msk.sigdep
  18. AND msk.nummsk < sub.max_nummsk
  19. ORDER BY msk.nummsk DESC)
  20. WHERE ROWNUM = 1;


Message édité par Beegee le 02-02-2006 à 16:15:55
n°1297542
orafrance
Posté le 02-02-2006 à 16:15:30  profilanswer
 

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.

n°1297544
Beegee
Posté le 02-02-2006 à 16:17:24  profilanswer
 

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 ?

Message cité 1 fois
n°1297555
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 16:22:53  profilanswer
 

Beegee a écrit :

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


Message édité par Arjuna le 02-02-2006 à 16:24:22
n°1297560
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 16:27:50  profilanswer
 

casimimir a écrit :

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.


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à :o

n°1297564
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 16:29:22  profilanswer
 

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 :o :p
 
Là j'expliquais la première requête :
 

Arjuna a écrit :

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 [:atari]  
 
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



Message édité par Arjuna le 02-02-2006 à 16:30:52
n°1297567
jeoff
Posté le 02-02-2006 à 16:31:25  profilanswer
 

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

Code :
  1. select '1' ord, pro.sigfou, msk.sigdep, pro.codpro, pro.codblocage, 0 entrees, 0 sorties, 0 initinventaire, msk.c01 avantinv
  2. from msk, pro
  3. where pro.codsoc = 2
  4. and pro.sigfou = 'HERITAGE'
  5. and msk.codsoc = pro.codsoc
  6. and msk.codpro = pro.codpro
  7. and msk.sigdep = pro.sigdep
  8. and msk.numlot = ' '
  9. and msk.nummsk =
  10. (select max(m1.nummsk)
  11. from msk m1
  12. where m1.codsoc = msk.codsoc
  13. and m1.codpro = msk.codpro
  14. and m1.sigdep = msk.sigdep
  15. and m1.numlot = ' '
  16. and m1.nummsk < (
  17. select max(m2.nummsk)
  18. from msk m2
  19. where m2.codsoc = m1.codsoc
  20. and m2.codpro = m1.codpro
  21. and m2.sigdep = m1.sigdep
  22. and m2.datmvt between '20051231' and '20060131'
  23. and m2.codosk = 'INICPT'
  24. ))


 
J'essaye de piocher quelques idées ça et là mais le sujet est vraiment vaste :whistle:
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/


Message édité par jeoff le 02-02-2006 à 16:36:11
n°1297568
Beegee
Posté le 02-02-2006 à 16:32:37  profilanswer
 

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 :
 

Code :
  1. DROP TABLE msk;
  3. DROP TABLE pro;
  5. DROP TABLE osk;
  7. CREATE TABLE PRO
  8. (
  9.   CODPRO      VARCHAR2(16),
  10.   MOTCLE      VARCHAR2(16),
  11.   FAMPRO      VARCHAR2(6),
  12.   SFAPRO      VARCHAR2(6),
  13.   SSFPRO      VARCHAR2(6),
  14.   NOMPRO      VARCHAR2(30),
  15.   GENCOD      VARCHAR2(13),
  16.   CODDOU      VARCHAR2(16),
  17.   TYPPRO      VARCHAR2(3),
  18.   EXICOT      VARCHAR2(1),
  19.   CODTVA      VARCHAR2(1),
  20.   CODUNI      VARCHAR2(3),
  21.   QTEMIN      NUMBER,
  22.   CODTAX      VARCHAR2(1),
  23.   CODTAX2     VARCHAR2(1),
  24.   SIGDEP      VARCHAR2(12),
  25.   CODREA      VARCHAR2(6),
  26.   SUISTK      VARCHAR2(1),
  27.   PROSTK      VARCHAR2(16),
  28.   DELPRE      INTEGER,
  29.   SUISTT      VARCHAR2(1),
  30.   CLAABC      VARCHAR2(1),
  31.   DATMAR      VARCHAR2(8),
  32.   POIDS       NUMBER,
  33.   HAUTE       NUMBER,
  34.   LARGE       NUMBER,
  35.   LONGUE      NUMBER,
  36.   VOLUME      NUMBER,
  37.   CODTAR      VARCHAR2(16),
  38.   DESIGN1     VARCHAR2(40),
  39.   DESIGN2     VARCHAR2(40),
  40.   DESIGN3     VARCHAR2(40),
  41.   TYPTAR      VARCHAR2(3),
  42.   SIGFOU      VARCHAR2(12),
  43.   DATMOD      VARCHAR2(8),
  44.   UTIMOD      VARCHAR2(8),
  45.   REFPRO      VARCHAR2(16),
  46.   NUMVAR      INTEGER,
  47.   TPSMON      INTEGER,
  48.   INDCOM      VARCHAR2(1),
  49.   VA          VARCHAR2(2),
  50.   VL          VARCHAR2(2),
  51.   CODUVC      VARCHAR2(3),
  52.   CODUE       VARCHAR2(3),
  53.   NBUCUE      INTEGER,
  54.   PCB         INTEGER,
  55.   UNIPCB      VARCHAR2(3),
  56.   SPCB        INTEGER,
  57.   UNISPCB     VARCHAR2(3),
  58.   CODZN1      VARCHAR2(12),
  59.   CODZN2      VARCHAR2(12),
  60.   CODZN3      VARCHAR2(12),
  61.   CODZN4      VARCHAR2(12),
  62.   CODZN5      VARCHAR2(12),
  63.   CODZN6      VARCHAR2(12),
  64.   CODZN7      VARCHAR2(12),
  65.   CODZN8      VARCHAR2(12),
  66.   CODZN9      VARCHAR2(12),
  67.   CODZN10     VARCHAR2(12),
  68.   CODZN11     VARCHAR2(12),
  69.   CODZN12     VARCHAR2(12),
  70.   CODZN13     VARCHAR2(12),
  71.   CODZN14     VARCHAR2(12),
  72.   CODZN15     VARCHAR2(12),
  73.   CODZN16     VARCHAR2(12),
  74.   UNICOL      VARCHAR2(3),
  75.   COLEC1      VARCHAR2(1),
  76.   STCK1       INTEGER,
  77.   COLEC2      VARCHAR2(1),
  78.   STCK2       INTEGER,
  79.   COLEC3      VARCHAR2(1),
  80.   STCK3       INTEGER,
  81.   COLEC4      VARCHAR2(1),
  82.   STCK4       INTEGER,
  83.   COLEC5      VARCHAR2(1),
  84.   STCK5       INTEGER,
  85.   COLEC6      VARCHAR2(1),
  86.   STCK6       INTEGER,
  87.   COLEC7      VARCHAR2(1),
  88.   STCK7       INTEGER,
  89.   COLEC8      VARCHAR2(1),
  90.   STCK8       INTEGER,
  91.   COLEC9      VARCHAR2(1),
  92.   STCK9       INTEGER,
  93.   DESIGN4     VARCHAR2(40),
  94.   DESIGN5     VARCHAR2(40),
  95.   DESIGN6     VARCHAR2(40),
  96.   DESIGN7     VARCHAR2(40),
  97.   DESIGN8     VARCHAR2(40),
  98.   DESIGN9     VARCHAR2(40),
  99.   DESIGN10    VARCHAR2(40),
  100.   DESIGN11    VARCHAR2(40),
  101.   DESIGN12    VARCHAR2(40),
  102.   CODDEV      VARCHAR2(3),
  103.   CODBLOCAGE  VARCHAR2(3),
  104.   POIDSNET    NUMBER,
  105.   TYPAFF      VARCHAR2(6),
  106.   QOTITE      NUMBER,
  107.   UNISTA      VARCHAR2(3),
  108.   PROSTA      VARCHAR2(16),
  109.   INDCTS      VARCHAR2(1),
  110.   INDRIS      VARCHAR2(1),
  111.   VALPVR      NUMBER,
  112.   PDSPVR      NUMBER,
  113.   VOLPVR      NUMBER,
  114.   NUMLSK      INTEGER,
  115.   CODSOC      INTEGER,
  116.   DELREA      INTEGER,
  117.   DATCRE      VARCHAR2(8),
  118.   ETBCOD      VARCHAR2(3),
  119.   ETSSTATUT   VARCHAR2(1),
  120.   CODCONV     VARCHAR2(3),
  121.   CLAABCP     VARCHAR2(1),
  122.   DELPER      INTEGER,
  123.   SUIINS      VARCHAR2(1),
  124.   SUIICE      VARCHAR2(1),
  125.   NOMISE      VARCHAR2(1),
  126.   UNIICE      VARCHAR2(3),
  127.   TYPIFA      VARCHAR2(1),
  128.   UNIPER      VARCHAR2(3),
  129.   DELGAR      INTEGER,
  130.   UNIGAR      VARCHAR2(3),
  131.   ACHICE      VARCHAR2(1),
  132.   TYPICE      VARCHAR2(3),
  133.   SIGICE      VARCHAR2(12),
  134.   VERICE      INTEGER,
  135.   PRORUB      VARCHAR2(16),
  136.   TEECOD      VARCHAR2(1),
  137.   TEEUNI      VARCHAR2(3),
  138.   TEEPRX      NUMBER,
  139.   MODLOT      VARCHAR2(1),
  140.   CODPAY      VARCHAR2(3),
  141.   TTAICE      VARCHAR2(3),
  142.   CODMDL      VARCHAR2(6),
  143.   PROREF      VARCHAR2(16),
  144.   SUIREF      VARCHAR2(1),
  145.   REPART      NUMBER,
  146.   CODUNIC     VARCHAR2(60),
  147.   TYPREF      VARCHAR2(6),
  148.   DATFINMAR   VARCHAR2(8)
  149. );
  151. CREATE UNIQUE INDEX PRO_IDX1 ON PRO
  152. (CODSOC, CODPRO);
  154. CREATE INDEX PRO_IDX9 ON PRO
  155. (CODSOC, GENCOD);
  157. CREATE INDEX PRO_WEB_IX1 ON PRO
  158. (CODSOC, SIGFOU, FAMPRO, SSFPRO, SUISTK, CODBLOCAGE);
  160. CREATE INDEX WI_PRO ON PRO
  161. (CODSOC, SIGFOU);
  163. CREATE INDEX WI_PROZN15 ON PRO
  164. (CODSOC, CODZN15);
  166. CREATE TABLE MSK
  167. (
  168.   SIGDEP  VARCHAR2(12),
  169.   CODPRO  VARCHAR2(16),
  170.   NUMLOT  VARCHAR2(7),
  171.   NUMMSK  INTEGER,
  172.   NUMPER  INTEGER,
  173.   DATMVT  VARCHAR2(8),
  174.   HEUMVT  NUMBER,
  175.   DATPRV  VARCHAR2(8),
  176.   CODOSK  VARCHAR2(6),
  177.   CODEMP  VARCHAR2(10),
  178.   ACHVTE  VARCHAR2(1),
  179.   TYPEVE  VARCHAR2(3),
  180.   NUMEVE  INTEGER,
  181.   POSEVE  INTEGER,
  182.   LIGEVE  INTEGER,
  183.   QTEOPE  NUMBER,
  184.   QTEMVT  NUMBER,
  185.   PRXVTE  NUMBER,
  186.   DPA     NUMBER,
  187.   PUMP    NUMBER,
  188.   DECOTE  NUMBER,
  189.   C01     NUMBER,
  190.   C02     NUMBER,
  191.   C03     NUMBER,
  192.   C04     NUMBER,
  193.   C05     NUMBER,
  194.   C06     NUMBER,
  195.   C07     NUMBER,
  196.   C08     NUMBER,
  197.   C09     NUMBER,
  198.   C10     NUMBER,
  199.   C11     NUMBER,
  200.   C12     NUMBER,
  201.   C13     NUMBER,
  202.   C14     NUMBER,
  203.   CODUNI  VARCHAR2(3),
  204.   UNIFAC  VARCHAR2(3),
  205.   DATMOD  VARCHAR2(8),
  206.   UTIMOD  VARCHAR2(8),
  207.   TYPOSK  VARCHAR2(3),
  208.   NUMDEB  VARCHAR2(12),
  209.   NUMFIN  VARCHAR2(12),
  210.   PRXREV  NUMBER,
  211.   CODSOC  INTEGER,
  212.   NUMOF   VARCHAR2(10),
  213.   LIBMSK  VARCHAR2(30),
  214.   INDTRT  VARCHAR2(1),
  215.   INDCES  VARCHAR2(1),
  216.   CODFCT  VARCHAR2(8),
  217.   CODPEV  VARCHAR2(8)
  218. );
  220. CREATE UNIQUE INDEX MSK_IDX1 ON MSK
  221. (CODSOC, CODPRO, SIGDEP, NUMLOT, NUMMSK);
  223. CREATE INDEX MSK_WI_IDX1 ON MSK
  224. (CODSOC, CODPRO, SIGDEP, DATMVT);
  226. CREATE INDEX MSK_WI_IDX2 ON MSK
  227. (CODSOC, CODPRO, SIGDEP, CODOSK);
  229. CREATE TABLE OSK
  230. (
  231.   CODOSK     VARCHAR2(6),
  232.   LIBOSK     VARCHAR2(30),
  233.   LIROSK     VARCHAR2(10),
  234.   OPEC01     VARCHAR2(1),
  235.   OPEC02     VARCHAR2(1),
  236.   OPEC03     VARCHAR2(1),
  237.   OPEC04     VARCHAR2(1),
  238.   OPEC05     VARCHAR2(1),
  239.   OPEC06     VARCHAR2(1),
  240.   OPEC07     VARCHAR2(1),
  241.   OPEC08     VARCHAR2(1),
  242.   OPEC09     VARCHAR2(1),
  243.   OPEC10     VARCHAR2(1),
  244.   OPEC11     VARCHAR2(1),
  245.   OPEC12     VARCHAR2(1),
  246.   OPEC13     VARCHAR2(1),
  247.   OPEC14     VARCHAR2(1),
  248.   PRVC01     VARCHAR2(1),
  249.   PRVC02     VARCHAR2(1),
  250.   PRVC03     VARCHAR2(1),
  251.   PRVC04     VARCHAR2(1),
  252.   PRVC05     VARCHAR2(1),
  253.   PRVC06     VARCHAR2(1),
  254.   PRVC07     VARCHAR2(1),
  255.   PRVC08     VARCHAR2(1),
  256.   PRVC09     VARCHAR2(1),
  257.   PRVC10     VARCHAR2(1),
  258.   PRVC11     VARCHAR2(1),
  259.   PRVC12     VARCHAR2(1),
  260.   PRVC13     VARCHAR2(1),
  261.   PRVC14     VARCHAR2(1),
  262.   LOTOBL     VARCHAR2(1),
  263.   CALPMP     VARCHAR2(1),
  264.   OPEMAN     VARCHAR2(1),
  265.   TYPEVE     VARCHAR2(3),
  266.   CSKTE1     VARCHAR2(6),
  267.   OPETST     VARCHAR2(2),
  268.   CSKTE2     VARCHAR2(6),
  269.   OPEQT1     VARCHAR2(1),
  270.   CSKQT1     VARCHAR2(6),
  271.   OPEQT2     VARCHAR2(1),
  272.   CSKQT2     VARCHAR2(6),
  273.   OPEQT3     VARCHAR2(1),
  274.   CSKQT3     VARCHAR2(6),
  275.   CSKSUI     VARCHAR2(6),
  276.   OPESU1     VARCHAR2(1),
  277.   CSKSU1     VARCHAR2(6),
  278.   OPESU2     VARCHAR2(1),
  279.   CSKSU2     VARCHAR2(6),
  280.   OPESU3     VARCHAR2(1),
  281.   CSKSU3     VARCHAR2(6),
  282.   TYPOSK     VARCHAR2(3),
  283.   DATMOD     VARCHAR2(8),
  284.   UTIMOD     VARCHAR2(8),
  285.   TYPCTR     VARCHAR2(1),
  286.   EMPLOBL    VARCHAR2(1),
  287.   MAJINV     VARCHAR2(1),
  288.   GENMSK     VARCHAR2(1),
  289.   SEROBL     VARCHAR2(1),
  290.   CODSOC     INTEGER,
  291.   CODCSK     VARCHAR2(6),
  292.   OPECSK     VARCHAR2(1),
  293.   CODUT_MES  VARCHAR2(10),
  294.   PROPRIETE  VARCHAR2(1)
  295. );
  297. CREATE UNIQUE INDEX OSK_IDX1 ON OSK
  298. (CODSOC, CODOSK);
  300. INSERT INTO msk
  301. (codsoc, codpro, sigdep, c01, numlot, nummsk, datmvt, codosk)
  302. VALUES
  303. (2, 1, 1, 1, ' ', 1, '20060101', 'INICPT');
  305. INSERT INTO msk
  306. (codsoc, codpro, sigdep, c01, numlot, nummsk, datmvt, codosk)
  307. VALUES
  308. (2, 1, 1, 1, ' ', 2, '20060101', 'INICPT');
  310. INSERT INTO msk
  311. (codsoc, codpro, sigdep, c01, numlot, nummsk, datmvt, codosk)
  312. VALUES
  313. (2, 1, 1, 1, ' ', 3, '20060101', 'INICPT');
  315. INSERT INTO pro
  316. (codsoc, codpro, sigfou, sigdep, codblocage)
  317. VALUES
  318. (2, 1, 'HERITAGE', 1, 1);
  320. SELECT '1' ord, pro.sigfou, msk.sigdep, pro.codpro, pro.codblocage, 0 entrees, 0 sorties, 0 initinventaire, msk.c01 avantinv
  321. FROM msk, pro
  322. WHERE pro.codsoc = 2
  323. AND pro.sigfou = 'HERITAGE'
  324. AND msk.codsoc = pro.codsoc
  325. AND msk.codpro = pro.codpro
  326. AND msk.sigdep = pro.sigdep
  327. AND msk.numlot = ' '
  328. AND msk.nummsk = 
  329. (SELECT MAX(m1.nummsk)
  330. FROM msk m1
  331. WHERE m1.codsoc = msk.codsoc
  332. AND m1.codpro = msk.codpro
  333. AND m1.sigdep = msk.sigdep
  334. AND m1.numlot = ' '
  335. AND m1.nummsk < (
  336. SELECT MAX(m2.nummsk)
  337. FROM msk m2
  338. WHERE m2.codsoc = m1.codsoc
  339. AND m2.codpro = m1.codpro
  340. AND m2.sigdep = m1.sigdep
  341. AND m2.datmvt BETWEEN '20051231' AND '20060131'
  342. AND m2.codosk = 'INICPT'
  343. ));
  345. EXPLAIN PLAN :
  347. SELECT STATEMENT Hint=RULE                             
  348.   FILTER                             
  349.     TABLE ACCESS BY INDEX ROWID MSK                            
  350.       NESTED LOOPS                             
  351.         TABLE ACCESS BY INDEX ROWID PRO                            
  352.           INDEX RANGE SCAN WI_PRO                            
  353.         INDEX RANGE SCAN MSK_IDX1                            
  354.     SORT AGGREGATE                             
  355.       FILTER                             
  356.         INDEX RANGE SCAN MSK_IDX1                            
  357.         SORT AGGREGATE                             
  358.           TABLE ACCESS BY INDEX ROWID MSK                            
  359.             INDEX RANGE SCAN MSK_WI_IDX2                            
  361. SELECT * FROM (
  362. SELECT '1' ord, pro.sigfou, msk.sigdep, pro.codpro, pro.codblocage, 0 entrees, 0 sorties, 0 initinventaire, msk.c01 avantinv
  363. FROM msk, pro,
  364.      (SELECT codsoc, codpro, sigdep, MAX(m2.nummsk) AS max_nummsk
  365.       FROM msk m2
  366.       WHERE m2.datmvt BETWEEN '20051231' AND '20060131'
  367.       AND m2.codosk = 'INICPT'
  368.       GROUP BY codsoc, codpro, sigdep) sub
  369. WHERE pro.codsoc = 2
  370. AND pro.sigfou = 'HERITAGE'
  371. AND msk.codsoc = pro.codsoc
  372. AND msk.codpro = pro.codpro
  373. AND msk.sigdep = pro.sigdep
  374. AND msk.numlot = ' '
  375. AND sub.codsoc = msk.codsoc
  376. AND sub.codpro = msk.codpro
  377. AND sub.sigdep = msk.sigdep
  378. AND msk.nummsk < sub.max_nummsk
  379. ORDER BY msk.nummsk DESC)
  380. WHERE ROWNUM = 1;
  382. EXPLAIN PLAN :
  384. SELECT STATEMENT Hint=RULE                             
  385.   COUNT STOPKEY                             
  386.     VIEW                             
  387.       FILTER                             
  388.         SORT GROUP BY                             
  389.           TABLE ACCESS BY INDEX ROWID MSK                            
  390.             NESTED LOOPS                             
  391.               NESTED LOOPS                             
  392.                 TABLE ACCESS BY INDEX ROWID PRO                            
  393.                   INDEX RANGE SCAN WI_PRO                            
  394.                 TABLE ACCESS BY INDEX ROWID MSK                            
  395.                   INDEX RANGE SCAN MSK_IDX1                            
  396.               INDEX RANGE SCAN MSK_WI_IDX2


 
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 ...)

n°1297570
casimimir
Posté le 02-02-2006 à 16:35:30  profilanswer
 

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

n°1297571
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 16:35:51  profilanswer
 

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

Code :
  1. select pro.sigfou, pro.sigdep, pro.codpro, pro.codblocage, pro.datmar, pro.nompro, 0 entrees, 0 sorties, 0 initinventaire, msk.c01 avantinv, 0 variationinv
  2. from msk, pro
  3. where pro.codsoc = 2
  4. and pro.sigfou = 'HERITAGE'
  5. and msk.codsoc = pro.codsoc
  6. and msk.codpro = pro.codpro
  7. and msk.sigdep = pro.sigdep
  8. and msk.numlot = ' '
  9. and msk.nummsk =
  10. (
  11.   select max(m1.nummsk)
  12.  from msk m1
  13.  where m1.codsoc = msk.codsoc
  14.  and m1.codpro = msk.codpro
  15.  and m1.sigdep = msk.sigdep
  16.  and m1.numlot = ' '
  17.  and m1.nummsk <
  18.  (
  19.   select max(m2.nummsk)
  20.   from msk m2
  21.   where m2.codsoc = m1.codsoc
  22.   and m2.codpro = m1.codpro
  23.   and m2.sigdep = m1.sigdep
  24.   and m2.datmvt between '20051231' and '20060131'
  25.   and m2.codosk = 'INICPT'
  26.  )
  27. )


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

Code :
  1. select sigfou, sigdep, codpro, codblocage, datmar, nompro, sum(entrees) entress, sum(sorties) sorties, sum(initinventaire) initinventaire, sum(avantinv) avantint, sum(variationinv) variationinv
  2. from
  3. (
  4. select pro.sigfou, pro.sigdep, pro.codpro, pro.codblocage, pro.datmar, pro.nompro, 0 entrees, 0 sorties, 0 initinventaire, msk.c01 avantinv, 0 variationinv
  5. from msk, pro
  6. where pro.codsoc = 2
  7. and pro.sigfou = 'HERITAGE'
  8. and msk.codsoc = pro.codsoc
  9. and msk.codpro = pro.codpro
  10. and msk.sigdep = pro.sigdep
  11. and msk.numlot = ' '
  12. and msk.nummsk =
  13. (
  14.   select max(m1.nummsk)
  15.  from msk m1
  16.  where m1.codsoc = msk.codsoc
  17.  and m1.codpro = msk.codpro
  18.  and m1.sigdep = msk.sigdep
  19.  and m1.numlot = ' '
  20.  and m1.nummsk <
  21.  (
  22.   select max(m2.nummsk)
  23.   from msk m2
  24.   where m2.codsoc = m1.codsoc
  25.   and m2.codpro = m1.codpro
  26.   and m2.sigdep = m1.sigdep
  27.   and m2.datmvt between '20051231' and '20060131'
  28.   and m2.codosk = 'INICPT'
  29.  )
  30. )
  31. union all
  32. select pro.sigfou, pro.sigdep, pro.codpro, pro.codblocage, pro.datmar, pro.nompro, 0 entreesapres, 0 sortiesapres, msk.c01 initinventaire, 0 avantinv, 0 variationinv
  33. from msk, pro
  34. where pro.codsoc = 2
  35. and pro.sigfou = 'HERITAGE'
  36. and msk.codsoc = pro.codsoc
  37. and msk.sigdep = pro.sigdep
  38. and msk.codpro = pro.codpro
  39. and msk.datmvt between '20051231' and '20060131'
  40. and msk.codosk = 'INICPT'
  41. and msk.c01 != 0
  42. union all
  43. select pro.sigfou, pro.sigdep, pro.codpro, pro.codblocage, pro.datmar, pro.nompro, sum(msk.qteope * decode(osk.opec01, '+', 1, 0)) entreesapres, sum(msk.qteope * decode(osk.opec01, '-', 1, 0)) sortiesapres, 0 initinventaire, 0 avantinv, 0 variationinv
  44. from osk, msk, pro
  45. where pro.codsoc = 2
  46. and pro.sigfou = 'HERITAGE'
  47. and msk.codsoc = pro.codsoc
  48. and msk.sigdep = pro.sigdep
  49. and msk.codpro = pro.codpro
  50. and msk.datmvt between '20051231' and '20060131'
  51. and osk.codsoc = msk.codsoc
  52. and osk.codosk = msk.codosk
  53. and osk.opec01 != ' '
  54. and
  55. (
  56.   msk.nummsk > (select max(nummsk) from msk m where m.codsoc = msk.codsoc and m.sigdep = msk.sigdep and m.codpro = msk.codpro and m.datmvt between '20051231' and '20060131' and m.codosk in ('INICPT', 'INVV'))
  57.   or
  58.   not exists (select null from msk m where m.codsoc = msk.codsoc and m.sigdep = msk.sigdep and m.codpro = msk.codpro and m.datmvt between '20051231' and '20060131' and m.codosk in ('INICPT', 'INVV'))
  59. )
  60. group by pro.sigfou, pro.codpro, pro.codblocage, pro.datmar, pro.nompro, pro.sigdep
  61. union all
  62. select pro.sigfou, pro.sigdep, pro.codpro, pro.codblocage, pro.datmar, pro.nompro, 0 entreesapres, 0 sortiesapres, 0 initinventaire, 0 avantinv, sum(msk.qteope) variationinv
  63. from msk, pro
  64. where pro.codsoc = 2
  65. and pro.sigfou = 'HERITAGE'
  66. and msk.codsoc = pro.codsoc
  67. and msk.sigdep = pro.sigdep
  68. and msk.codpro = pro.codpro
  69. and msk.datmvt between '20051231' and '20060131'
  70. and msk.codosk = 'INVV'
  71. and
  72. (
  73.   msk.nummsk > (select max(nummsk) from msk m where m.codsoc = msk.codsoc and m.sigdep = msk.sigdep and m.codpro = msk.codpro and m.datmvt between '20051231' and '20060131' and m.codosk = 'INICPT')
  74.   or
  75.   not exists (select null from msk m where m.codsoc = msk.codsoc and m.sigdep = msk.sigdep and m.codpro = msk.codpro and m.datmvt between '20051231' and '20060131' and m.codosk = 'INICPT')
  76. )
  77. group by pro.sigfou, pro.codpro, pro.codblocage, pro.sigdep, pro.datmar, pro.nompro
  78. )
  79. group by sigfou, sigdep, codpro, codblocage, datmar, nompro


 
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)

n°1297576
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 16:39:38  profilanswer
 

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

n°1297578
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 16:40:39  profilanswer
 

(en fait, ton petit bout de requête tout seul met plus de temps à tourner que ma requête toute entière :D)
 
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)


Message édité par Arjuna le 02-02-2006 à 16:41:36
n°1297586
Beegee
Posté le 02-02-2006 à 16:46:04  profilanswer
 

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

n°1297597
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 16:54:02  profilanswer
 

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


Message édité par Arjuna le 02-02-2006 à 16:54:38
n°1297599
Beegee
Posté le 02-02-2006 à 16:54:36  profilanswer
 

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 :
 

Code :
  1. CREATE INDEX msk_myindex ON msk
  2. (codsoc, codpro, sigdep);
  4. SELECT '1' ord, pro.sigfou, msk.sigdep, pro.codpro, pro.codblocage, 0 entrees, 0 sorties, 0 initinventaire, msk.c01 avantinv
  5. FROM msk, pro
  6. WHERE pro.codsoc = 2
  7. AND pro.sigfou = 'HERITAGE'
  8. AND msk.codsoc = pro.codsoc
  9. AND msk.codpro = pro.codpro
  10. AND msk.sigdep = pro.sigdep
  11. AND msk.numlot = ' '
  12. AND msk.nummsk = 
  13. (SELECT MAX(m1.nummsk)
  14. FROM msk m1
  15. WHERE m1.codsoc = msk.codsoc
  16. AND m1.codpro = msk.codpro
  17. AND m1.sigdep = msk.sigdep
  18. AND m1.numlot = ' '
  19. AND m1.nummsk < (
  20. SELECT MAX(m2.nummsk)
  21. FROM msk m2
  22. WHERE m2.codsoc = m1.codsoc
  23. AND m2.codpro = m1.codpro
  24. AND m2.sigdep = m1.sigdep
  25. AND m2.datmvt BETWEEN '20051231' AND '20060131'
  26. AND m2.codosk = 'INICPT'
  27. ));
  29. --EXPLAIN PLAN :
  31. --SELECT STATEMENT Hint=RULE                             
  32. --  FILTER                             
  33. --    TABLE ACCESS BY INDEX ROWID MSK                            
  34. --      NESTED LOOPS                             
  35. --        TABLE ACCESS BY INDEX ROWID PRO                            
  36. --          INDEX RANGE SCAN WI_PRO                            
  37. --        INDEX RANGE SCAN MSK_MYINDEX                            
  38. --    SORT AGGREGATE                             
  39. --      FILTER                             
  40. --        TABLE ACCESS BY INDEX ROWID MSK                            
  41. --          INDEX RANGE SCAN MSK_MYINDEX                            
  42. --        SORT AGGREGATE                             
  43. --          TABLE ACCESS BY INDEX ROWID MSK                            
  44. --            INDEX RANGE SCAN MSK_WI_IDX2                            
  46. SELECT '1' ord, pro.sigfou, msk.sigdep, pro.codpro, pro.codblocage, 0 entrees, 0 sorties, 0 initinventaire, msk.c01 avantinv
  47. FROM msk, pro,
  48.      (SELECT m1.codsoc, m1.codpro, m1.sigdep, MAX(m1.nummsk) AS prev_nummsk
  49.       FROM msk m1,
  50.           (SELECT codsoc, codpro, sigdep, MAX(m2.nummsk) AS max_nummsk
  51.            FROM msk m2
  52.            WHERE m2.datmvt BETWEEN '20051231' AND '20060131'
  53.            AND m2.codosk = 'INICPT'
  54.            GROUP BY codsoc, codpro, sigdep) sub
  55.       WHERE sub.codsoc = m1.codsoc
  56.       AND sub.codpro = m1.codpro
  57.       AND sub.sigdep = m1.sigdep
  58.       AND m1.numlot = ' '
  59.       AND m1.nummsk < sub.max_nummsk
  60.       GROUP BY m1.codsoc, m1.codpro, m1.sigdep) sub2
  61. WHERE pro.codsoc = 2
  62. AND pro.sigfou = 'HERITAGE'
  63. AND msk.codsoc = pro.codsoc
  64. AND msk.codpro = pro.codpro
  65. AND msk.sigdep = pro.sigdep
  66. AND msk.numlot = ' '
  67. AND msk.codsoc = sub2.codsoc
  68. AND msk.codpro = sub2.codpro
  69. AND msk.sigdep = sub2.sigdep
  70. AND msk.nummsk = sub2.prev_nummsk;
  72. --EXPLAIN PLAN :
  74. --SELECT STATEMENT Hint=RULE                             
  75. --  FILTER                             
  76. --    SORT GROUP BY                             
  77. --      TABLE ACCESS BY INDEX ROWID MSK                            
  78. --        NESTED LOOPS                             
  79. --          NESTED LOOPS                             
  80. --            VIEW                             
  81. --              FILTER                             
  82. --                SORT GROUP BY                             
  83. --                  TABLE ACCESS BY INDEX ROWID MSK                            
  84. --                    NESTED LOOPS                             
  85. --                      INDEX RANGE SCAN MSK_IDX1                            
  86. --                      INDEX RANGE SCAN MSK_WI_IDX2                            
  87. --            TABLE ACCESS BY INDEX ROWID PRO                            
  88. --              INDEX RANGE SCAN WI_PRO                            
  89. --          INDEX RANGE SCAN MSK_MYINDEX

Message cité 1 fois
Message édité par Beegee le 02-02-2006 à 16:55:43
n°1297601
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 16:56:45  profilanswer
 

Beegee a écrit :

Tu as essayé un index sur codsoc, codpro, sigdep ?


 
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... :D

n°1297607
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 17:04:44  profilanswer
 

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 :ange:
(l'espoir fait vivre :D)

n°1297608
Arjuna
Aircraft Ident.: F-MBSD
Posté le 02-02-2006 à 17:06:02  profilanswer
 

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 !

mood
Publicité
Posté le   profilanswer
 

 Page :   1  2
Page Suivante
Page Précédente
Haut de page 

Aller à :
Ajouter une réponse
  FORUM HardWare.fr
  Programmation
  SQL/NoSQL

  [SGBD] Encore une requête à décorner les boeufs...

 

Sujets relatifs
[SGBD] Oracle 8i, TextSearch : besoin d'infosErreur SQL 1064 : Requête imbriquée avec jointure !!!
[Résolu]Requete soustractive MySQL ?Mesurer le temps d'exécution d'une requête XQuery
modifier le contenu d'une requête sélection stockée sous Acessproblème requête client vers servlet
requete+champ+count[Mysql] 1 Grosse requete OU plusieurs petite ?
demande d'aide REQUETE COMPLEXErequête sql : pas d'affichage
Plus de sujets relatifs à : [SGBD] Encore une requête à décorner les boeufs...

Forum MesDiscussions.Net, Version 2010.2
(c) 2000-2011 Doctissimo
Page générée en 0.252 secondes

Copyright © 1997-2022 Hardware.fr SARL (Signaler un contenu illicite / Données personnelles) / Groupe LDLC / Shop HFR