Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
1 décembre 2009 2 01 /12 /décembre /2009 13:22

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

Partager cet article

Published by Pacman - dans SQL
commenter cet article

commentaires