Bonjour,
je souhaiterai optimiser ma requette
 
la table PIZZA fait plusieurs millions de lignes, et la requetes prend au final plus d'une minute pour s'executer, sous Oracle
 
Le but est d'affecter la même date de peremption aux champs de la table PIZZA que les champs de la table TEMP_PIZZA, qui ont le même fabricant.
TEMP_PIZZA n'a que qqes dizaines de lignes
1 ligne de TEMP_PIZZA ne correspond qu'a une ligne de PIZZA
Une ligne de PIZZA peut avoir soit une ligne correspondante dans TEMP_PIZZA, soit aucune.
 

 
 
Avez vous une solution pour l'accelerer et prendre moins d'une minute ?
 
Utiliser un curseur et une boucle loop ?
 
merci

space ton code, tu ne fais pas de join avec ta table temp_pizza ?

surtout, bonjour le produit cartésien entre fabriquant et temp_pizza. du coup non seulement ça doit rammer, mais en plus ça doit faire n'importe quoi.
 
(et à mon avis, pas besoin d'appeler la table fabriquant, l'id doit déjà être dans temp_pizza...)

Franchement je poste mais je ne sais pas si ça va fonctionner... mais je propose

 
En fait, si j'appelle fabricant c'est pour vérifier au passage que l'id du fabricant est bien existant, si l'id est spécifier et n'est pas bon, il sera ignoré. ici j'ai mis id pour simplifier, mais ça peut etre un autre champ qui n'est pas clé primaire, et qui est saisis manuellement.
J'ai simplifié le principe, vous comprenez bien qu'avec plusieurs millions de lignes dans la table je ne manipule pas que des pizza
Et ça ne fait pas n'importe quoi non ça fonctionne Mais c'est clairement pas optimisé c'est sur
 
Merci GeGe585, je teste ça dès que possible

surtout, dans ta requête, t'as pas de lien entre pizza et tmp_pizza. à mon avis il manque un truc. parceque tu mets à jour chaque ligne de pizza avec autant de lignes qu'il y a dans tmp_pizza où vendu = 0 pour chaque ligne qui a un fournisseur qui existe.

 
J'ai l'erreur suivante
 
PL/SQL: ORA-00933: SQL command not properly ended
 
juste avant le 1er INNER JOIN
 
une solution ?
 
MagicBuzz -> oui je sais bien qu'il manque un truc, propose une solution !

Nan mais s'en plus d'infos c'est une peu la roulette que de t'aider

Bon comme le disais fort justement magic buzz je pense qu'il manque une liaison entre pizza et tmp_pizza,  donc admettons qu'il y ait un pizza_id qui fasse la liaison.
J'écrirai donc ta requête ainsi:

UPDATE pizza
SET PERIME = TMP.PERIME
FROM  TEMP_PIZZA
INNER JOIN PIZZA  TMP ON TMP.pizza_id = pizza.id 
INNER JOIN FABRICANT FC ON pizza.FABRICANT_ID = FC.FABRICANT_ID 
WHERE TMP.VENDU = 0

rien a faire, encore la meme erreur juste avant le 1er FROM
(d'ailleurs tas du te tromper avec les alias)
enfin j'ai essayé mais rien a faire.
 
Le principe de la requete que j'ai fait, c'est que pour chaque ligne pizza, il va parcourir les ligne de TEMP_PIZZA pour verifier si l'identifiant est le meme. (disons l'identifiant, en réalité PIZZA a un identifiant FABRICANT, TEMP PIZZA a un identifiant FABRICANT, et la liaison entre tout ça, c'est de garder le même identifiant FABRICANT pour les 3 tables, la table FABRICANT sert juste a verifier que l'identifiant du fabricant existe bel et bien.
En gros on recupere  pizza.fabricant_id = fabricant.id AND pizza_temp.fabricant_id = fabricant.id
 
Et grace au WHERE EXISTS, ça me retourne 1 ligne a chaque fois, et au final la requete agit comme une boucle.
 
Avec le inner join, j'ai peur que ça me retourne a chaque fois plusieurs lignes.

C'est pas une erreur de syntaxte mais un problème d'éxecution

Modèle pourri
Comme fais-tu si 2 pizzas ont le même fabricant? Ou Alors tu as une liaison 1-1 entre fabricant et pizza ( une pizza a un seul fabriquant et un fabriquant "fabrique" une seule pizza)?

fail

Bon en admettons que tu aies bien une liaison 1-1 comme dit plus haut:

UPDATE PIZZA PZ
SET PERIME = TMP.PERIME
FROM  TEMP_PIZZA TMP
INNER JOIN FABRICANT FC ON TMP.FABRICANT_ID = FC.FABRICANT_ID
WHERE TMP.FABRICANT_ID = PZ.FABRICANT_ID AND TMP.VENDU = 0

Bon, d'apres le site d'oracle, on peut pas faire de inner join avec un update
 
ils proposent la requete suivant
 
UPDATE employees a  
    SET department_id =  
        (SELECT department_id  
            FROM departments  
            WHERE location_id = '2100'),  
        (salary, commission_pct) =  
        (SELECT 1.1*AVG(salary), 1.5*AVG(commission_pct)  
          FROM employees b  
          WHERE a.department_id = b.department_id)  
    WHERE department_id IN  
        (SELECT department_id  
          FROM departments
          WHERE location_id = 2900  
              OR location_id = 2700);  
 
The preceding UPDATE statement performs the following operations:
 
Updates only those employees who work in Geneva or Munich (locations 2900 and 2700)
 
Sets department_id for these employees to the department_id corresponding to Bombay (location_id 2100)
 
Sets each employee's salary to 1.1 times the average salary of their department
 
Sets each employee's commission to 1.5 times the average commission of their department
 
 
Donc on peut pas faire plus rapide j'ai l'impression

Bon et si la syntax avec le inner est pas connue d'oracle alors la requête devrait s'écrire ainsi:

UPDATE PIZZA PZ
    SET PERIME = (SELECT TMP.PERIME
    FROM  TEMP_PIZZA TMP
    INNER JOIN FABRICANT FC ON TMP.FABRICANT_ID = FC.FABRICANT_ID
    WHERE TMP.FABRICANT_ID = PZ.FABRICANT_ID AND TMP.VENDU = 0)

Et l'exists est normalement inutile si l'optimizer fait correctement son boulot.

si ce n'est qu'il va mettre a null tous les périmés des fabricants qui ne sont pas dans temp_pizza ^^
 
faut rajouter un nvl pour qu'il remette l'ancienne valeur, ou être certain que le cas ne se présente pas, perso je mettrais le exists tout de même parce que je pense qu'il va tout de même faire un fullscan la, je ne bosse que en 9, ptêtre que l'optimizer dans les versions suivantes est mieux (surement même), mais donc en 9 pour moi il balaye tout

Hello, tu peux tenter cette syntaxe là:
 

UPDATE PZ
SET PZ.PERIME = TMP.PERIME
FROM PIZZA PZ
INNER JOIN TEMP_PIZZA TMP ON TMP.FABRICANT_ID = PZ.FABRICANT_ID
WHERE TMP.VENDU = 0

 
Deux remarques:
- pas besoin de la table fabricant, puisque les ID de fabricant sont déjà présent dans tmp et dans PZ.
- Des criteres de jointures ne doivent etre a la fois dans le ON et dans le WHERE.

> anapajari:
 

UPDATE PIZZA PZ
    SET PERIME = (SELECT TMP.PERIME
    FROM  TEMP_PIZZA TMP
    INNER JOIN FABRICANT FC ON TMP.FABRICANT_ID = FC.FABRICANT_ID
    WHERE TMP.FABRICANT_ID = PZ.FABRICANT_ID AND TMP.VENDU = 0)

 
Surtout PAS !!!
Ce code va réinitialiser TOUS les champs perime de la table pizza avec la derniere valeur trouvée par la sous requete. En effet, sans critere where dans la requete "mere" toute la table va etre mise a jour.

et le "PZ.FABRICANT_ID" dans la sous-requête c'est du poulet ?
 
ceci dit, on retoure à la première requête qu'à tenté Blackhawk8, pour moi il manque clairement un critère de jointure à ce moment.

ha mais j'avais même pas vu que magic avait donné la bonne réponse au troisième post mais que ca continue a y aller de sa soluce

mais y'a pas la bonne soluce vu qu'on a toujours pas le bon problème!!!
La soluce de magic buzz par de l'idée qu'il y a une liaison 1-1 entre temp_pizza et pizza, sauf que cela a été démenti  ensuite :

Donc la liaison doit se faire par fabricant_id entre les trois tables

ben non, là il expliquait juste pourquoi il voulait utiliser fabriquant dans sa requête (et qui est inutile)
 
si y'a pas d'autre lien, alors la réponse est simple : y'a pas de solution tout court. si on peut ne pas identifier les lignes, on peut pas les identifier. on pourra faire tout ce qu'on veut.
 
si findus fait 10 pizza différentes, qu'elles soient dans pizza ou dans tmp_pizza, un lien par fabriquant uniquement fera 10 mises à jour sur chacune des 10 pizza... et surtout on écrira n'importe quoi parcequ'on n'a aucune idée de laquelle des 10 mises à jour sera la dernière
 
bref.
 
si y'a ni pizza.id / tmp_pizza.id ou de pizza.trucquifaitquejesaislaquellecest / tmp_pizza.trucquifaitquejesaislaquellecest le problème devient :
 

Pas comme ça que je le comprends.
AMA, il a "transformé" son problème en utilisant des pizzas sauf que sa transformation fausse le problème.

Comme je comprends son problème, il a 3 tables :
E1 ( pk_E1 ) avec pk_E1 primary key =>C'est sa table "fabricant".
E2 (..., fk_E1) avec fk_E1 foreign key sur pk_E1 => C'est sa table pizzas
E3 (..., fk_E1 ) avec fk_E1 foreign key sur pk_E1 => C'est sa table temp_pizzas

Et donc il a une liaison 1-1 entre E1 et E2 et une autre liaison 1-1 entre E1 et E3.

Dans sa requête il utilisait E1 ( fabricant) uniquement pour s'assurer que la pk pointée par les fk n'avait pas disparue. Et si c'est pas ça j'ai rien compris et comme je le disais plus haut:

ben c'est comme ça que j'ai compris aussi.
 
fabriquant_id est une FK vers une table commune, mais pas une PK. à partir de là, on sait pas matcher les lignes ensemble.
 
et rien n'empêche de lierdirectement E2 et E3 par cette même FK, on n'a pas besoin de passer par la table de référence pour faire des jointures.
 
si par contre "fabriquant_id" est la référence (ID) de la pizza d'un point de vue fabriquant, alors la liaison serait bonne, mais il ne faudrait pas passer par la table fabriquant, donc j'en déduit que c'est pas ça.
 
bref, pour moi ce champ est relativement inutile dans sa requête, et il manque clairement d'autres critères permettant de lier les pizzas de deux tables de façon 1-1.
 
actuellement, avec frabriquant_id, c'est du n-m, on peut absolument rien faire avec.
 
c'est comme si je dis "ben j'ai des chaises, elles sont dans une salle de cours... et j'ai aussi des élèves, ils sont dans la même salle"
ben si y'a pas marqué le nom de l'élève sur la chaise, t'ira trouver toi qui est assi sur quoi... en espérant qu'ils sont pas à 4 sur la même...
 
la requête, actuellement, elle fait s'assoire tous les élèves sur toutes les chaises à la suite. et l'update enregistre le dernier à avoir posé son cul, donc en gros, avec de la chance, on recopie le meme élève sur toutes les chaises... il a un gros cul

En fait, faudrait reprendre le probleme depuis le début.
 
La j'ai essayé les diverses procédures que vous m'avez proposés, et celles-ci durent plus de 15min. Pas facile
 
Mon objectif
- la table PIZZA avec plus de 3 millions de lignes, contient toutes les pizza depuis 1870 on va dire.
- la table TEMP_PIZZA, avec une dizaine de pizza. Elle est remplis que quand on cherche a modifier des pizza.
 
- Pour savoir qu'une pizza modifiée doit remplacer une vieille pizza en particulier dans la grosse table PIZZA, on lui affecte le même CODE_PIZZA (ce code remplace l'ID d'avant) (Je parle d'un code maintenant parce que si je dis ID tout le monde se dit que c'est introduit automatiquement, donc pas besoin de vérifier ; or c'est pas le cas c'est saisis manuellement)
 
(si c'est manuellement c'est qu'il y a une raison, c'est pas parce que le modèle est pourri)
 
- ce code pizza n'est pas un code au hasard, et référence les pizza, donc je ne veux pas tester simplement si PIZZA.CODE = TEMP_PIZZA.CODE, mais je prefere faire
PIZZA.CODE = FABRICANT.CODE_PIZZA AND FABRICANT.CODE_PIZZA = TEMP_PIZZA.CODE
 
Comme ça, si le code est faux, la pizza ne passera pas de la petite table à la grosse.
 
Pour l'instant l'insertion c'est rien, j'en suis a la phase de PEREMPTION, néanmoins je souhaite appliquer la verification du code partout
 
Quand une pizza passe de la table TEMP a la table PIZZA, avant de s'ajouter elle marque la vieille pizza de même code en tant que PERIME. Avec la date de péremption de l'ancienne pizza = "la date d'autorisation d'etre mangé" de la nouvelle pizza
 
Dans mon post de tete, j'avait mis SET PERIME = ( SELECT TMP.PERIME ...)
 
ça serait donc plutot SET PERIME = ( SELECT DROIT_DE_MANGER ...)
 
 
 
Au niveau fonctionnement,
1) il faudrait parcourir CHAQUE ELEMENT de la grosse table PIZZA
2) pour chaque element de la grosse, parcourir la petite table pour vérifier si y a pas une pizza de même code, si c'est le cas, on affecte la date de peremtion.
 
En gros le nombre à parcourir serait de 3 millions de la grosse table x 10 lignes de la petite table (ça peut etre 100 également)
 
 
Et pour le moment je n'ai pas trouvé plus rapide que ma requete du haut

 
bordel !
 
puisque je te dis que àça sert à rien de passer par le fabriquant si c'est pour lire la même info en entrée et en sortie
 
et si le code ne correspond pas à un fabriquant, alors de toute façon la ligne dans pizza n'existe pas, donc tu risques pas de la mettre à jour...
 
par contre, j'aimerais que tu prennes le temps de répondre à cette question :
 
est-ce que FABRIQUANT est le fabriquant de la pizza ?
 
si oui, POURQUOI cette table contient-t-elle l'identifiant de ta pizza ? c'est l'inverse en général... là ça veut dire qu'une même pizza peut être fabriquée par plusieurs fabriquants... et qu'un même fabriquant ne peux fabriquer qu'une seule pizza
 

je comprends pas ta phrase. mais bon, de toute façon on n'en est pas encore là, faudrait déjà savoir comment faire les jointures.
 
moi je mpaintiens, niveau résultat (de toute façon c'est n'importe quoi donc on s'en fout) cette requête (post suivant) est absolument identique à la tienne du début du topic, sauf qu'en plus elle doit être 2 ou 3 fois plus rapide.
(je vois pas trop l'intérêt d'aller lire des millions de fois la table des fabriquants quand il n'est pas question de fabriquant dans ta requête... c'est comme si t'allais pisser chez le plombier qui a posé les sanitaires chaque fois que tu entre dans des chiottes, tu parles d'un gain de temps...
 
 
quand t'as deux boîtes findus sous les yeux. y'a marqué "findus". t'as besoin d'aller dans l'usine pour vérifier qu'on y fabrique bien les deux pizza ? ou si à priori c'est bon, c'est bien une usine findus qui fabrique les deux pizza... bref. vire-moi cette table fabriquant bourdel

+ check les indexs sur les différents ID et sur VENDU

et surtout arrêter de croire que le code du fabriquant est un identifiant unique pour une pizza  

MagicBuzz -> merci pour ton aide
 
Je répond à ta question,
est-ce que FABRIQUANT est le fabriquant de la pizza ?
 
Je dirais que, ON S'EN FOU, je l'ai appelé fabricant, on peut l'appeler recette ou je sais pas. C'est juste que ce fameux code qui est associé manuellement à une nouvelle pizza, doit exister dans une autre table.
On peut dire par exemple que le fabricant n'a le droit de mettre en vente qu'UNE SEULE PIZZA à la fois.
 
Tu as bien compris que je fais une métaphore entre ce que je manipule et des pizza. Je travaille pas chez pizzahut
Si je souhaite vérifier au passage que le code existe, c'est pour une question de sécurité.
En y reflechissant, c'est vrai que c'est pas nécessaire de le vérifier ici, puisqu'il est vérifié à l'insertion.
 
J'ai testé ta requete, je vais essayer d'obtenir des temps corrects, la derniere fois que je l'ai testé ça a duré 16min exactement ; la ou la mienne en haut en prend 1,30, tout en effectuant toutes les vérifications.
 
 
 
EDIT. quant à la phrase que tu ne comprend pas, ce que je veux dire c'est: quand on ajoute une nouvelle pizza de même code, on fait en sorte que la précédente ne soit plus vendable ; c'est pas un flag, c'est une date. DATE_PEREMPTION_ANCIENNE = DATE_CREATION_NOUVELLE.
En disant que la date de création n'est pas égale à la date d'insertion dans la base, donc je ne veux pas d'un simple sysdate dans mon update

pour le problème de temps de réponse, je ne vois pas comment il peut être inférieur avec ta requête, ou alors Oracle est clairement à la rue en ce qui concerne le plan d'exécution.
 
à mon avis, l'étape suivante, c'est de vérifier tes index, ils me semble foireux si le temps change dans ce sens lorsque tu ne passes pas par la troisème table.
 
vérifie bien que tu as les deux index suivantes :
 
PIZZA (fabriquant_id) UNIQUE
TEMP_PIZZA (vendu)
 
(ceci dit, l'index sur TMP_PIZZA est limité inutile si tu n'a qu'une petite centaine de lignes, en dessous du millier tu ne verras pas de diff avec ou sans index)

les index pour les 2 tables sont (CODE (ou fabricant_id), DATE_PEREMPTION)
 
Je reessayerais demain ta requete, j'avais peut etre remplacé les inner join par du simple where, je m'en souviens plus
je reteste ça demain

 
OUPS, je crois que j'ai mis tout à null dans ma table, ce qui peut expliquer le temps que ça a pris
je viens de faire une grosse betise
et ça va etre trop tard pour le rollback  

prends le bon coté, maintenant ta requête devrait être beaucoup plus rapide

c'est bon j'ai rien cassé, je retente tout ça

tant que tu feras des metaphores sur des pizzas ce ne sera pas évident de comprendre ce que tu veux faire, idem pour dire ce qu'il y a exactement dans temp_pizza, parceque une requete c'est avant tout donner un sens a l'information, pas juste faire un truc en se disant que dès que ca met 2 secondes a s'exécuter ce sera bon.
 
et je ne comprend même pas pourquoi metaphoriser

c'est une question que je me posais.
 
autant les données, ça me semble logique de les maquiller (et encore, à la limite dans 95% des cas on s'en bas les couilles total), mais le modèle des données...

ça serait plus compliqué à expliquer je pense.
Sinon je n'ai pas maquillé le modele de données, juste le nom des données c'est tout
 
Pour comprendre le fonctionnement que je voudrais, je l'ai expliqué dans un post précédent, c'est a dire:
 

 
 
Récapitulons,
 
- La requete d'un UPDATE avec un INNER JOIN en requete principale ne marche pas, Oracle refuse
 
- Je n'ai pas cassé toute ma table en mettant tout à null, puisque j'avais pensé à rajouter le code suivant dans la requete principale:

 
En gros on affecte la peremption seulement aux elements dont la date est à null, pas ceux deja perimé.
Donc code à retenir.
 
- Concernant la requete avec un INNER JOIN dans la sous requete SELECT (que vous m'avez proposé precedemment):

 
(j'ai rajouté ma derniere ligne a la fin)
 
Le fait de faire le test avec le fabricant via le INNER JOIN, est ce que ça va me refuser correctement les elements donc l'ID du fabricant n'existe pas ?
(sachant qu'ensuite il y a le WHERE de la sous requete qui teste l'ID_FABRICANT entre TEMP et PIZZA)
PS. Pour la table fabricant on peut compter 25000 lignes.
 
J'ai essayé de mettre le TMP.FABRICANT_ID = PZ.FABRICANT_ID du where dans le ON du INNER JOIN, mais j'ai une erreur de compilation
 
 
Je me suis arreté après 5 min d'execution, je pense qu'elle devrait durer dans les 16 min comme celle que j'avais testé auparavant.
 
=> Donc trop long
 
 
 
- Et faire un LOOP avec un curseur. Pour chaque curseur je fais un select de TEMP pour savoir si les id_fabricant correspondent.
Si ID egaux, je fais l'update sur le curseur courant
ça serait pas plus rapide ?

J'ai reussis !
 
je suis passé par un curseur sur un select et des inner join, de cette façon ça trace
Et une boucle qui déplace le curseur et qui fait ensuite l'update quand il y a besoin
 
Resultat c'est quasiment instantanné
 
Les inner join sont vraiment bien en effet et dans le cas d'un update faut pas hesiter à faire le travail en 2 etapes

Ok c'eest cool, mais ce serait bien que tu postes la solution pour tout ceux qui ont pris la peine de t'aider ^^

On declare le curseur de Select dans la table PIZZA, renvoyant la date a appliquer pour la peremption

 
Ensuite on boucle
 

Ok thanks