Update d'une table en fonction du résultat d'une autre requête

Version imprimable

07/09/2011, 15h43
shaun_the_sheep

Update d'une table en fonction du résultat d'une autre requête
Bonjour à vous,

Je vais essayer de bien synthétiser à ma question.

J'ai une requete SQL A qui me renvoie plusieurs lignes d'un couple de deux clés , soit cle1 et cle2.

Je veux maintenant mettre à jour une table B par un update qui devrait ressembler à mon exemple ....
Code:

1 2 3 4 5 6 Update MATABLE set MACOLONNE1_FK = (Select COLONNE From tableX, tableY Where ...... and COLONNE_FK=cle 1) Where MACOLONNE2_FK=cle 2
J'avoue me demander comment faire et si cela est bien possible

Merci pour votre aide

Avec du PlSql, je vois quelque chose du genre :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
begin
...
  for cur in (Select COLONNE, cle2 
                From tableX, tableY
                Where ......
                   and COLONNE_FK=cle 1) loop
 
       execute immediate 'Update MATABLE set MACOLONNE1_FK = :v1 
                                 where MACOLONNE2_FK = :v2' 
                                 using cur.COLONNE, cur, cur.cle2;
  end loop;
End
/

Qu'en penses-tu ?
Cdlt,
LBO72.

Citation:

Envoyé par LBO72

Avec du PlSql, je vois quelque chose du genre :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
begin
...
  for cur in (Select COLONNE, cle2 
                From tableX, tableY
                Where ......
                   and COLONNE_FK=cle 1) loop
 
       execute immediate 'Update MATABLE set MACOLONNE1_FK = :v1 
                                 where MACOLONNE2_FK = :v2' 
                                 using cur.COLONNE, cur, cur.cle2;
  end loop;
End
/

Qu'en penses-tu ?
Cdlt,
LBO72.

Pourquoi du dymanique SQL???? Est-il en train de faire du DDL???

On peut bien evidement l'écrire comme ça :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 begin ... FOR cur IN (SELECT COLONNE, cle2 FROM tableX, tableY WHERE ...... AND COLONNE_FK=cle 1) loop Update MATABLE set MACOLONNE1_FK = COLONNE where MACOLONNE2_FK = cle2; end loop; End /
Mais ça sera moins performant(pour des grosses volumétries), puisque l'optimiseur va calculer le plan d'exécution à chaque itération. En utilisant le Sql Dynamique et les bind variables, on évitera cette étape.

Cdlt,
LBO72.

Citation:

Envoyé par LBO72

Mais ça sera moins performant(pour des grosses volumétries), puisque l'optimiseur va calculer le plan d'exécution à chaque itération. En utilisant le Sql Dynamique et les bind variables, on évitera cette étape.

Non le PL/SQL utilise automatiquement des bind varaiables, pas besoin d'execute immediate.
Après pourquoi faire un curseur plutôt qu'une seule requête, avec MERGE on peut écrire quelque chose comme :
Code:

1 2 3 4 5 6 7 8 merge into matable using (SELECT COLONNE, cle2 FROM tableX join tableY ON COLONNE_FK=cle1 WHERE ......) u on (MACOLONNE2_FK = cle2) when matched then SET MACOLONNE1_FK = COLONNE

09/09/2011, 14h59
Mohamed.Houri
Citation:

Envoyé par LBO72

Mais ça sera moins performant(pour des grosses volumétries), puisque l'optimiseur va calculer le plan d'exécution à chaque itération. En utilisant le Sql Dynamique et les bind variables, on évitera cette étape.
Cdlt,
LBO72.

Un petit rappel
Les "bind variables" ont des avantages et des inconvénients. Leur avantage vient du fait qu'elles permettent la réutilisation (ou le partage) des curseurs qui se trouvent dans la SGA (particulièrement dans la "library cache") et, donc, évitent le "hard parse" et ses effets sur la performance au travers du temps que celui-ci nécessite de la part de la CPU (le "hard parse" brule beaucoup de CPU)
Quant à leurs inconvénients, on peut le dire, elles ne s'entendent pas aves les histogrammes. On créé des histogrammes parce que des requêtes identiques génèrent un temps de réponse et un volume de données très différents l'un de l'autre et nous ne voulons pas que ces requêtes identiques utilisent (ou partage) le même explain plan.

Quelle contradiction!!!

L'un (bind variable) est pour le partage et la réutilisation du même curseur (même plan d'exécution) et l'autre est pour que chaque requête ait son propre plan d'exécution
C’est pourquoi en règles général des personnes comme Tom Kyte suggèrent :
1. d’utiliser les bind variables en OLTP
2. de ne pas utiliser des histogrammes en OLTP
3. d’utiliser des histogrammes en DWH
Le petit rappel étant fait, j’aimerai enchainer sur votre réponse pour dire qu’en utilisant du PL/SQL (sql statique tel que procédure stockée) nous avons l’avantage qu’un genre de ‘’binding’’ automatique est fait pour nous. Nous n’avons pas à nous soucier de cela. Nous devrions quand même veiller à ce que les appels de cette procédure soit faits avec des paramètres déclarés en ‘bind variables’’. Sinon, notre libray cache (v$sql) sera remplie d’appel à notre procédure.
Par contre lorsque vous proposez d’utiliser le sql dynamique dans une procédure stockée, là vous devriez faire attention à la bonne utilisation des bind variables. Le concept de ‘’binding’’ automatique disparaît et vous devriez redoubler d'attention quant à l'utilisation de ces variables sans parler des riques (sql injection) que vous pourriez introduire dans votre application lorsque des concaténations sont utilisées. Quant au calcul du plan d’exécution à chaque itération, j’espère que le petit rappel a atteint son but.
09/09/2011, 15h15
mnitu

Citation:

Envoyé par Mohamed.Houri

...On créé des histogrammes parce que des requêtes identiques génèrent un temps de réponse et un volume de données très différents l'un de l'autre et nous ne voulons pas que ces requêtes identiques utilisent (ou partage) le même explain plan.
...

Les histogrammes sont apparus en Oracle 9 pour répondre à une des critiques majeures apportées à l’optimiseur basée sur le coût d'Oracle 8, celui de l’hypothèse d’une distribution uniforme des données (voir Wolfgang Breitling "Fallacies of the cost based optimiser"). Les cases de distribution non-uniforme des données peuvent être décrites avec des histogrammes ce qui permet par la suite à l’optimiseur de trouver les "bons plans" d’exécution adaptées à chaque cas.

Petit remarque : la réutilisation des curseurs date de la version 5/6 d’Oracle et ce mécanisme était à l’époque une réponse adaptée par rapport à l’optimiseur basée sur les règles. Mais l’optimiseur basée sur les règles n’existe plus … bref, il existe toujours mais…