Vendredi 24 juillet 2009
5
24
/07
/Juil
/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 ?
Par Pacman
-
Publié dans : SQL
2
"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
Oh mon Dieu Mnitu, j'ai vécu dans le mensonge !
Merci de m'avoir permis d'en sortir ;)
J'étais pas encore né !
(En fait si, mais j'aime bien répondre ça)
http://www.orafaq.com/wiki/Oracle_7