Dans un article précédent, je disais que grâce à l'évolution de l'instruction MERGE sous 10g, on pouvait enfin faire des UPDATE de jointure efficaces sous Oracle.
Ce n'est pas tout à fait vrai. Car Oracle permet de mettre à jour des vues... sous certaines conditions seulement !
Nous allons donc dans la suite illustrer les limitations de l'in-line view update, et donc l'avantage certain de l'instruction MERGE.
1) L'exemple basique d'update d'in-line view
Reprenons les jeux de test créés dans l'article précédent et la requête de Mnitu (cf commentaire) :
SQL> set autotrace on
SQL> UPDATE (
2 SELECT a.val oldval, b.val newval
3 FROM test_merge a
4 JOIN test_merge_source b ON a.id = b.id
5 )
6 SET oldval = newval;
1000 rows updated.
------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost |
-----------------------------------------------------------------------|
| 0 | UPDATE STATEMENT | | 1000 | 13000 | 527|
| 1 | UPDATE | TEST_MERGE | | | |
|* 2 | HASH JOIN | | 1000 | 13000 | 527|
| 3 | TABLE ACCESS FULL| TEST_MERGE_SOURCE | 1000 | 7000 | 3|
| 4 | TABLE ACCESS FULL| TEST_MERGE | 1010K| 5918K| 501|
------------------------------------------------------------------------
C'est parfait, exactement ce qu'on souhaite !
Mais ça ne marche pas toujours...
2) Le cas de défaite
Nous étions dans le cas d'une relation 1-1.
Considérons à présent une relation maître-esclave.
SQL> CREATE TABLE acct (acct_num primary key, some_amount, acct_desc) AS
2 SELECT level, 0, lpad('x', mod(level, 10), 'x')
3 FROM DUAL
4 CONNECT BY level <= 100
5 /
Table created.
SQL> CREATE TABLE ecr(acct_num, ecr_num, ecr_amt, some_desc) AS
2 SELECT a.acct_num, b.ecr_num, b.ecr_amt, ' '
3 FROM acct a
4 CROSS JOIN (SELECT level as ecr_num, level * 100 as ecr_amt
5 FROM DUAL
6 CONNECT BY level <= 10) b
7 /
Table created.
SQL> ALTER TABLE ecr ADD CONSTRAINT pk_ecr PRIMARY KEY(acct_num, ecr_num)
2 /
Table altered.
SQL> exec dbms_stats.gather_table_stats('PACMAN', 'ACCT', cascade => true)
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats('PACMAN', 'ECR', cascade => true)
PL/SQL procedure successfully completed.
On passera sur le fait que cet exemple doit forcément piétiner au moins une des formes normales...
Bref, faisons un premier essai, relativement idiot : mettre à jour la colonne some_amount de la table acct avec le montant associé dans ecr(ecr_amt) (ma formulation est volontairement floue, la punition sera immédiate).
SQL> UPDATE
2 (SELECT some_amount, ecr_amt
3 FROM acct a
4 JOIN ecr b ON a.acct_num = b.acct_num)
5 SET some_amount = ecr_amt;
SET some_amount = ecr_amt
*
ERROR at line 5:
ORA-01779: cannot modify a column which maps to a non key-preserved table
Bah ouais, ça plante... voyons pourquoi.
3) Les limitations de l'UPDATE d'in-line views
Lorsqu'on met à jour une vue, qu'elle soit explicitement nommée, ou 'in-line' c'est à dire sous forme de "table dérivée" :
- on ne peut mettre à jour qu'une table à la fois
- la table mise à jour doit être "key-preserved"
Cela signifie que pour une ligne de la table à mettre à jour, il doit y avoir une ligne au plus dans le résultat de la vue.
Le terme "Key-preserved" s'applique bien à une table au sein d'une vue. Essayons par exemple de mettre à jour la table ecr avec les valeurs de acct en utilisant la même jointure :
SQL> ALTER TABLE ecr MODIFY some_desc VARCHAR2(4000) --ptit ajustement à cause du CTAS raté :)
2 /
Table altered.
SQL> UPDATE
2 (SELECT some_desc, acct_desc
3 FROM acct a
4 JOIN ecr b ON a.acct_num = b.acct_num)
5 SET some_desc= acct_desc;
1000 rows updated.
Ca marche, bien sûr, car une seule ligne est associée à chaque ligne de ecr.
La raison pour laquelle il faut que la table mise à jour doit être key-preserved se comprend aisément :
Si on a plusieurs lignes dans la jointure pour une même clef, on va sensément faire plusieurs mises à jour... et c'est la dernière qui gagne !
Mais la notion de dernière mise à jour dépend du plan d'exécution, ce qui est inadmissible.
Rassurez-vous, une telle aberration n'est pas non plus permise avec le MERGE :
SQL> MERGE INTO acct a
2 USING ecr b
3 ON (a.acct_num = b.acct_num)
4 WHEN MATCHED THEN UPDATE SET a.some_amount = b.ecr_amt;
USING ecr b
*
ERROR at line 2:
ORA-30926: unable to get a stable set of rows in the source tables
Jusque là, pas d'avantage concret pour le MERGE.
Sauf que la définition d'une key-preserved table est assez stricte !
Je trouve la doc Oracle un peu floue sur ce point, mais on note quand même :
The key-preserving property of a table does not depend on the actual data in the table. It is, rather, a property of its schema. For example, if in the emp table there was at most one employee in each department, then deptno would be unique in the result of a join of emp and dept, but dept would still not be a key-preserved table.
Par contre, l'Oncle Tom est un peu plus précis sur asktom :
You NEED a primary key/unique constraint on
object_id in T1 to ensure that each row in T joins to AT MOST 1 row in T1.
Et là, on a un problème : avoir besoin d'une contrainte d'unicité, ça limite les possibilités.
Par exemple, il est assez courant de vouloir mettre à jour une table avec un résultat d'aggrégation d'une autre table !
Voyons voir ce que ça donne...
4) After all, MERGE is good for you !
Supposons à présent que l'on veuille affecter pour chaque compte de la table acct, la somme des montants associés dans la table ecr :
SQL> UPDATE
2 (SELECT a.some_amount, b.amt
3 FROM acct a
4 JOIN (SELECT acct_num, sum(ecr_amt) amt
5 FROM ecr
6 GROUP BY acct_num) b ON a.acct_num = b.acct_num
7 )
8 SET some_amount= amt;
SET some_amount= amt
*
ERROR at line 8:
ORA-01779: cannot modify a column which maps to a non key-preserved table
Non key-preserved !
Et pourtant, en faisant la jointure avec un GROUP BY, on est sûr de n'avoir qu'une ligne !
Par contre avec le MERGE :
SQL> MERGE INTO acct a
2 USING (SELECT acct_num, sum(ecr_amt) sumecr
3 FROM ecr
4 GROUP BY acct_num) b
5 ON (a.acct_num = b.acct_num)
6 WHEN MATCHED THEN UPDATE SET a.some_amount = b.sumecr;
100 rows merged.
Ca marche !
Un grand merci quoi qu'il en soit à Mnitu de m'emêcher de dire des conneries impunément...