Il y a quelques jours, j’ai publié un post intitulé « Un Update Pas Si bête… » dans lequel j’illustrai la capacité qu’a Oracle à assurer la consistence des données et écriture. Si vous voulez en savoir plus, reportez-vous aux posts de Tom Kyte intitulés Write Consistency Part I, Part II et Part III.

Dans ce post, j’illustrerai que (1) les 2 sections d’un ordre MERGE ne sont pas exécutées de manière consistentes et (2) que vous pouvez utiliser un ordre MERGE pour supprimer des CHAINED ROWS.

Un schema exemple

Commençons par créer un schéma d’exemple constitué de 2 tables liées par une clé étrangère avec la contrainte ON DELETE CASCADE :

create table master_t(
     id number,
     text varchar2(50),
     constraint master_t_pk
                primary key(id)
     );

insert into master_t
   values (1,'Text 1');

insert into master_t
   values (2,'Text 2');

create table detail_t(
     master_id number,
     constraint detail_master_fk
                foreign key(master_id)
                references master_t(id)
                on delete cascade);

insert into detail_t
   values (1);

commit;

select rowid, id, text
from master_t;

ROWID      ID TEXT
------------------ --- ------
AAATLQAAEAAABY+AAA   1 Text 1
AAATLQAAEAAABY+AAB   2 Text 2

2 MERGE, 2 ORDER BY, 2 résultats

Les 2 exemples suivants illustrent que l’ordre dans lequel vous effectuez un MERGE a son importance et que, en quelque sorte, les différentes sections d’un ordre MERGE ne sont pas exécutées de manière consistente. Le second exemple présente comment vous pouvez remplacer, avec un MERGE une ligne par une nouvelle ligne:

• Exemple 1: Quand MERGE ne fonctionne pas

merge into master_t d
  using (select 'AAATLQAAEAAABY+AAA' rid from dual
         union all
         select 'AAATLQAAEAAABY+AAZ' from dual
         order by rid) s
on (d.rowid=s.rid)
when matched then
 update set d.id=9
when not matched then
 insert (id,text) values (1,'Text 1');

ORA-02292: integrity constraint (SCOTT.DETAIL_MASTER_FK) violated - child

• Exemple 2: MERGE pour changer le rowid d’une ligne;

merge into master_t d
  using (select 'AAATLQAAEAAABY+AAA' rid from dual
         union all
         select 'AAATLQAAEAAABY+AAZ' from dual
         order by rid desc) s
on (d.rowid=s.rid)
when matched then
 update set d.id=9
when not matched then
 insert (id,text) values (1,'Text 1');

2 rows merged.

select rowid, id, text
from master_t;

ROWID      ID TEXT
------------------ --- ------
AAATLQAAEAAABY+AAA   9 Text 1
AAATLQAAEAAABY+AAB   2 Text 2
AAATLQAAEAAABY+AAC   1 Text 1

commit;

delete from master_t where id=9;

commit;

select rowid, id, text
from master_t;

ROWID      ID TEXT
------------------ --- ------
AAATLQAAEAAABY+AAB   2 Text 2
AAATLQAAEAAABY+AAC   1 Text 1

select * from detail_t;

MASTER_ID
---------
       1