2 réflexions à propos de la commande MERGE

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