Mise à jour de données entre deux tables sous Oracle

Une problématique assez fréquente consiste à mettre à jour le contenu d'une table à partir de données contenues dans une autre table.

Nous allons illustrer cette problématique avec une base assez simple. Cette base contient une table avec des employés (BIGEMP) et une table avec des augmentations (AUGMENTATION)

La table BIGEMP est une duplication de la table exemple Oracle EMP dupliquée 100 000  fois. Il y a 1,4 million d'enregistrements dans cette table. Ci-dessous un extrait de cette table.

La table AUGMENTATION contient le montant des augmentations des employés. Il y a 1,4 million d'enregistrements dans cette table aussi. Ci-dessous un extrait de cette table.

La modification des données que nous souhaitons faire est d'ajouter la valeur de la colonne Montant de la table AUGMENTATION à la colonne SAL de la table BIGEMP.

Dans notre base, tout employé a un enregistrement dans la table des augmentations, cependant nous allons considérer dans cet article que nous n'avons pas la certitude que tous les employés sont présents dans cette table et qu'il ne faut donc augmenter que les employés qui y sont présents et laisser le salaire inchangé pour les autres.

Le résultat désiré est équivalent à la requête suivante :

select E.EMPNO,e.ename,e.sal,a.montant,e.sal+a.montant NouveauSalaire
from bigemp E join augmentation A on e.empno=a.empno

Si vous souhaitez faire quelques tests vous-même, cette base est disponible à l'adresse suivante : http://www.altidev.com/livres.php .

Sous le nom "Base Employés (Grosse)".

Vous pouvez aussi utiliser les scripts présents à la fin de l'article.

La méthode classique pour réaliser cette modification est d'utiliser un update et une sous-requête corrélée.

update bigemp e 
  set e.sal = e.sal + (select a.Montant from augmentation a 
                       where a.empno=e.empno)
  where e.empno in (select a.empno from augmentation a)

ou la variante suivante.

UPDATE bigemp e 
   SET e.sal = e.sal + (SELECT a.montant FROM augmentation a 
                        WHERE a.empno = e.empno)
 WHERE EXISTS (SELECT NULL FROM augmentation a WHERE a.empno = e.empno)

Cette solution a un temps d'exécution de 34 secondes et a la trace d'exécution suivante :

Nous ne commentons pas ces données pour l'instant, le but est de s'en servir comme référence pour les autres solutions.

II-A. Et s'il n'y avait pas d'index ?▲

Nous remarquons que la requête précédente utilise l'index de clé primaire de la table augmentation (PK_AUGMENTATION). Il arrive parfois dans une application que la table qui sert de source aux modifications soit une table temporaire créée sans grande attention et du coup, il est possible qu'elle n'ait pas de clé primaire et encore moins d'index. L'objet de ce paragraphe est d'étudier l'impact de cet oubli.

Nous créons une copie des données sans aucune clé avec la requête suivante :

 

Sélectionnez

create table augmentation_sansPK as  
select * from augmentation

Nous collectons les statistiques,

 

Sélectionnez

exec dbms_stats.gather_table_stats(user,'augmentation_sansPK');

Et exécutons la même requête dessus

 

Sélectionnez

update bigemp e 
set sal=sal+(select Montant from augmentation_sansPK where empno=e.empno)
where empno in (select empno from augmentation_sansPK )

Nous constatons une très nette détérioration puisque le temps d'exécution est à présent de plusieurs heures (je ne sais pas combien car j'ai arrêté avant la fin).

Pourtant le plan d'exécution ci-dessous ne nous laissait pas entrevoir une telle dégradation puisque le coût annoncé était le même.

Il faut donc retenir que le champ qui sert à faire le lien dans la sous-requête corrélée doit impérativement être indexé sur ce genre de requête si la table a une taille significative (pour simplifier, considérez que cela est nécessaire à partir de quelques milliers d'enregistrements).

II-B. Et si on utilisait une IOT ?▲

Une table IOT (Index Organized table) est une table qui est organisée en index

(voir https://oracle.developpez.com/guide/architecture/tables/?page=Chap1#L1.4 )

On peut se demander si cette organisation aura un impact sur notre requête.

Nous créons la table avec l'instruction suivante :

 

Sélectionnez

create table AUGMENTATION_IOT (
  EMPNO   NUMBER not null,
  MONTANT NUMBER(7,2),
  constraint PK_AUGMENTATION_IOT primary key (EMPNO)
) organization index

Nous la remplissons

 

Sélectionnez

insert into AUGMENTATION_IOT 
select * from AUGMENTATION

Puis l'analysons

 

Sélectionnez

exec dbms_stats.gather_table_stats(user,'augmentation_IOT');

Nous exécutons notre requête

 

Sélectionnez

update bigemp e 
set sal=sal+(select Montant from augmentation_IOT where empno=e.empno)
where empno in (select empno from augmentation_IOT )

Et constatons un temps d'exécution de 25 secondes avec la trace d'exécution suivante :

Le résultat est donc que, malgré un coût annoncé plus élevé, le résultat est meilleur avec une table IOT. Cela provient d'une forte baisse des opérations Consistent Gets (-50 %), gagné sur l'opération Table Access By Index RowID sur la table augmentation qui n'est plus nécessaire grâce à la structure IOT (voir colonne LAST_CR_BUFFER_GETS) .

L'instruction Merge introduite en version 9i d'Oracle est conçue pour fusionner deux tables (voir https://oracle.developpez.com/faq/?page=3-1#merge) cependant elle peut être légèrement détournée de son usage initial pour faire ce genre de requête de mise à jour en ignorant la clause WHEN NOT MATCHED

MERGE into bigemp E using augmentation A on (a.empno=e.empno)
WHEN MATCHED THEN UPDATE set sal=sal+Montant
WHEN NOT MATCHED THEN insert (empno) values (null)

Cette notation a l'avantage de ne pas contenir de sous-requête corrélée, ce qui ne déplaira pas aux développeurs peu à l'aise avec ces dernières.

La clause WHEN NOT MATCHED ne nous intéresse pas vraiment, c'est juste qu'elle est obligatoire pour les versions <10g, à partir de la version 10g vous pouvez vous contenter de

MERGE into bigemp E using augmentation A on (a.empno=e.empno)
WHEN MATCHED THEN UPDATE set sal=sal+Montant

Si votre table Augmentation contient des employés non présents dans la table Bigemp, le MERGE dans sa version avec clause WHEN NOT MATCHED plantera, car c'est dans le cas présent ce qui me parait le plus adapté.

Côté performances, nous constatons un temps d'exécution de 35 secondes avec la trace d'exécution suivante :

Les performances sont donc moins bonnes que la solution avec un update conventionnel mais il y a des situations où cette solution s'avère 10 à 40 %  plus performante.

Par exemple si la table bigemp a une structure IOT l'update conventionnel passe à un temps d'exécution de 44 s alors que la solution MERGE descend à 24 secondes.

Création de la table

create table BIGEMP_IOT
(
  EMPNO    NUMBER not null,
  ENAME    VARCHAR2(10) not null,
  JOB      VARCHAR2(9),
  MGR      NUMBER,
  HIREDATE DATE,
  SAL      NUMBER(7,2),
  COMM     NUMBER(7,2),
  DEPTNO   NUMBER not null,
  constraint PK_BIGEMP_IOT primary key (EMPNO)
)
organization index;
insert into BIGEMP_IOT 
select * from BIGEMP;
exec dbms_stats.gather_table_stats(user,'bigemp_IOT');

Requête merge sur table BIGEMP en IOT

MERGE into bigemp_iot E using augmentation A on (a.empno=e.empno)
WHEN MATCHED THEN UPDATE set sal=sal+Montant;

III-A. Et s'il n'y avait pas d'index ?▲

III-B. Informations complémentaires sur l'instruction MERGE▲

MERGE into bigemp E 
  using (select * from augmentation where empno<10000) A 
  on (a.empno=e.empno)
  WHEN MATCHED THEN UPDATE set sal=sal+Montant

Une autre façon de faire cette mise à jour est d'utiliser l'instruction UPDATE dans une forme plutôt méconnue qui consiste à faire un UPDATE sur une sous-requête.

update (select sal,Montant from augmentation a
	  join  bigemp e 
        on a.empno=e.empno) 
set sal=sal+Montant

Cette notation, comme le MERGE, a l'avantage de ne pas contenir de sous-requête corrélée.

Cette solution à un temps d'exécution de 23 secondes et la trace d'exécution suivante

Le temps d'exécution s'est amélioré, et on constate une chute drastique des Consistent Gets. Cette solution est significativement plus performante que l'update conventionnel avec une sous-requête corrélée.

Il y a cependant des restrictions sur les cas où il est possible d'utiliser cette notation. Je n'ai pas trouvé dans la doc la liste de ces cas, mais ils sont plus nombreux que les cas de restriction du MERGE.

IV-A. Et s'il n'y avait pas d'index ?▲

Voilà un résumé des temps d'exécution, même si ce n'est pas forcement le seul indicateur à prendre en compte, ça donne une idée qui convient dans la plupart des situations.

La solution utilisant MERGE a régulièrement de meilleures performances mais rarement de moins bonnes que l'update classique et a l'avantage d'être assez pratique à écrire et de permettre d'écrire simplement des mises à jour compliquées.

La dernière solution, utilisant un UPDATE  avec une sous-requête, est légèrement plus performante sur ce cas-là et sur ma machine (sur la machine de Waldar c'est identique au Merge) et est en plus assez élégante à écrire, elle a cependant quelques contraintes d'utilisation qui ne permettent donc pas de l'utiliser dans tous les cas.

Cependant, comme je le dis toujours, en matière de performance, il faut être pragmatique, chaque situation a ses spécificités, il faut donc toujours tester et mesurer dans des conditions équivalentes aux conditions réelles avant de prendre une décision.

Cette phrase peut paraître bateau, mais par exemple sur l'environnement de Waldar le MERGE est systématiquement plus performant que l'update basique alors que sur mon environnement ce n'est pas toujours le cas.

Merci à Antoun, Waldar, hmira et Claude pour leur relecture, et Djug de m'avoir guidé.

Article traitant de l'optimisation

https://jpg.developpez.com/oracle/tuning/

 Livre optimisation des bases de données (Mise en oeuvre sous Oracle)

http://www.altidev.com/livres.php