Overblog
Suivre ce blog
Editer l'article Administration Créer mon blog
24 juillet 2009 5 24 /07 /juillet /2009 16:29
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 ?



Partager cet article

Published by Pacman - dans SQL
commenter cet article

commentaires

Marius NITU 30/01/2012 21:51

En fait cette syntaxe existe depuis Oracle 7.3 :)

Pacman 31/01/2012 09:59



J'étais pas encore né !


(En fait si, mais j'aime bien répondre ça)


http://www.orafaq.com/wiki/Oracle_7


 



NITU 30/11/2009 16:02


Citation
"MySQL permet l'UPDATE de jointure... mais pas Oracle !"

Je ne sait pas si j'ai bien compris tes propos mais déjà en Oracle 9 il est possible de faire:

1 Update (
2 SELECT a.val oldval, b.val newval
3 FROM test_merge a
4 Join test_merge_source b
5 On (a.id = b.id)
6 )
7* Set oldval = newval

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


Plan d'exécution
----------------------------------------------------------
0 UPDATE STATEMENT Optimizer=ALL_ROWS (Cost=102 Card=1000 Bytes=13000)


1 0 UPDATE OF 'TEST_MERGE'


2 1 NESTED LOOPS (Cost=102 Card=1000 Bytes=13000)


3 2 TABLE ACCESS (FULL) OF 'TEST_MERGE_SOURCE' (Cost=2 Card=1000 Bytes=7000)


4 2 TABLE ACCESS (BY INDEX ROWID) OF 'TEST_MERGE' (Cost=1 Card=1 Bytes=6)


5 4 INDEX (UNIQUE SCAN) OF 'TEST_MERGE_II' (UNIQUE)


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


Pacman 30/11/2009 17:55



Oh mon Dieu Mnitu, j'ai vécu dans le mensonge !
Merci de m'avoir permis d'en sortir ;)