Oracle 10g : update joins with MERGE !

24.07.2009

Un exercice assez courant : mettre à jour une table A avec les valeurs d'une table B, en faisant la correspondance sur un certain nombre de colonnes.
MySQL permet l'UPDATE de jointure... mais pas Oracle !
J'ai toujours trouvé ça assez naze, car la requête doit exécuter les mêmes accés deux fois pour :
- Rechercher les valeurs dans le SET
- délimiter le périmètre de mise à jour

Mais avec l'évolution de l'instruction MERGE sous Oracle 10g, le problème est enfin résolu !

Gros Erratum :
Sous 9i, il existe déjà une syntaxe pour le faire ! Merci à Mnitu pour l'info : son commentaire donne la syntaxe.
Cela dit, il faut que la table à modifier soit "key preserved" (et surtout le démontrer à Oracle...).
Vous trouverez un petit complément sur le sujet ici.

Allez, un petit exemple pour illustrer la chose.
Création des jeux de tests :

SQL> CREATE TABLE test_merge AS
2 SELECT level AS id, 0 as val
3 FROM DUAL
4 CONNECT BY level <= 1000000
5 /

Table créée.

SQL> CREATE UNIQUE INDEX test_merge_ii ON test_merge(id)
2 /

Index créé.

SQL> CREATE TABLE test_merge_source AS
2 SELECT id, id as val
3 FROM test_merge
4 WHERE mod(id, 1000) = 0
5 /

Table créée.

SQL> CREATE UNIQUE INDEX test_merge_source_ii ON test_merge_source(id)
2 /

Index créé.

Un petit coup de stats :
SQL> exec dbms_stats.gather_table_stats(NULL, 'TEST_MERGE')

Procédure PL/SQL terminée avec succès.

SQL> exec dbms_stats.gather_table_stats(NULL, 'TEST_MERGE_SOURCE')

Procédure PL/SQL terminée avec succès.

On active l'autotrace, et c'est parti !
SQL> set autotrace on

Première méthode, l'UPDATE simple :
SQL> UPDATE test_merge a
2 SET val = (SELECT val
3         FROM test_merge_source b
4         WHERE a.id = b.id)
5 /

1000000 ligne(s) mise(s) à jour.

Plan d'exécution
----------------------------------------------------------
Plan hash value: 1824631898

-----------------------------------------------------------------------------------
| Id | Operation                    | Name                 | Rows | Bytes | Cost
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                      |   996K| 5839K|   501
|   1 | UPDATE                      | TEST_MERGE           |       |
|   2 |   TABLE ACCESS FULL          | TEST_MERGE           |   996K| 5839K|   501
|   3 |   TABLE ACCESS BY INDEX ROWID| TEST_MERGE_SOURCE    |     1 |     7 |     2
|* 4 |    INDEX UNIQUE SCAN         | TEST_MERGE_SOURCE_II |     1 |       |          1
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("B"."ID"=:B1)

Statistiques
----------------------------------------------------------
       2089 recursive calls
    2073432 db block gets
    1192599 consistent gets
          0 physical reads
444612940 redo size
        681 bytes sent via SQL*Net to client
        620 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          6 sorts (memory)
          0 sorts (disk)
    1000000 rows processed

SQL> rollback
2 /

Comme on n'a pas délimité le périmètre, toutes les valeurs sans correspondance sont mises à NULL !

C'est pourquoi on est obligé d'ajouter :
SQL> UPDATE test_merge a
2 SET val = (SELECT val
3         FROM test_merge_source b
4         WHERE a.id = b.id)
5 WHERE EXISTS (SELECT NULL
6            FROM test_merge_source b
7            WHERE a.id = b.id)
8 /

1000 ligne(s) mise(s) à jour.

Plan d'exécution
----------------------------------------------------------
Plan hash value:2830137269

-----------------------------------------------------------------------------------
| Id | Operation                    | Name                 | Rows | Bytes | Cost
-----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                      | 1000 | 10000 |   508
|   1 | UPDATE                      | TEST_MERGE           |       |       |
|   2 |   NESTED LOOPS               |                      | 1000 | 10000 |   508
|   3 |    SORT UNIQUE               |                      | 1000 | 4000 |     2
|   4 |     INDEX FAST FULL SCAN     | TEST_MERGE_SOURCE_II | 1000 | 4000 |     2
|* 5 |    INDEX UNIQUE SCAN         | TEST_MERGE_II        |     1 |     6 |     1
|   6 |   TABLE ACCESS BY INDEX ROWID| TEST_MERGE_SOURCE    |     1 |     7 |     2
|* 7 |    INDEX UNIQUE SCAN         | TEST_MERGE_SOURCE_II |     1 |       |     1
-----------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("A"."ID"="B"."ID")
   7 - access("B"."ID"=:B1)

Statistiques
----------------------------------------------------------
         10 recursive calls
       1032 db block gets
       4013 consistent gets
          0 physical reads
     247328 redo size
        686 bytes sent via SQL*Net to client
        700 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          2 sorts (memory)
          0 sorts (disk)
       1000 rows processed

SQL> rollback
2 /

Annulation (rollback) effectuée.

En regardant le plan d'exécution, on voit bien que la jointure est réalisée 2 fois, ce qui est un véritable drame humain...
Du coup, sous 9i ou moins, il était plus efficace de faire CURSOR UPDATE en parcourant la jointure, un gros aveu d'échec.

Mais depuis 10g, il est possible d'utiliser MERGE (merci SQL 2003 !)pour faire faire un UPSERT... sans le SERT !
(Il n'est plus obligatoire d'implémenter les deux clauses MATCHED et NOT MATCHED.

La petite démo :
SQL> MERGE INTO test_merge a
2 USING test_merge_source b
3 ON (a.id = b.id)
4 WHEN MATCHED THEN UPDATE SET a.val = b.val
5 /
1000 lignes fusionnées.

Plan d'exécution
----------------------------------------------------------
Plan hash value: 2484597143

------------------------------------------------------------------------
| Id | Operation            | Name              | Rows | Bytes | Cost
------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |                   | 1000 | 15000 |   526
|   1 | MERGE               | TEST_MERGE        |       |       |
|   2 |   VIEW               |                   |       |       |
|* 3 |    HASH JOIN         |                   | 1000 | 13000 |   526
|   4 |     TABLE ACCESS FULL| TEST_MERGE_SOURCE | 1000 | 7000 |     3
|   5 |     TABLE ACCESS FULL| TEST_MERGE        |   996K| 5839K|   501
------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("A"."ID"="B"."ID")

Statistiques
----------------------------------------------------------
          1 recursive calls
       1021 db block gets
       1667 consistent gets
          0 physical reads
     248796 redo size
        688 bytes sent via SQL*Net to client
        640 bytes received via SQL*Net from client
          4 SQL*Net roundtrips to/from client
          1 sorts (memory)
          0 sorts (disk)
       1000 rows processed

SQL> COMMIT
2 /

Validation effectuée.

Ils ne sont pas mignons, le plan d'exécution / les stats ?