Résumé des épisodes précédents
Lors de précédents messages, mes collègues yooo13 et arkzoid se sont penchés sur la mise à jour une table en fonction d’une autre en utilisant la logique suivante :
update table1
set col3 = (select table2.col4
from table2
where table1.col2 = table2.col3);
Puis sur la réécriture de cette mise à jour dans du PL/SQL en comparant les performances des deux méthodes.
declare
type rct is ref cursor ;
cur rct ;
l_idp integer := -13 ;
l_rowid rowid ;
begin
open cur for select table2.col4, table1.rowid
from table1
join table2 on table1.col2 = table2.col3 ;
loop
fetch cur into l_idp, l_rowid ;
exit when cur%notfound ;
update table1 set col3=l_idp where rowid=l_rowid ;
end loop ;
close cur ;
end ;
Le premier proposait le modèle d’optimisation et le deuxième le démontait en montrant que les SQL et PL/SQL donnés n’étaient pas équivalent, en conséquence de quoi l’optimisation proposée n’en était pas vraiment une.
Ce chapitre se placera donc dans les conditions où l’ordre de mise à jour et le PL/SQL associés sont équivalents c’est à dire s’il y a une surjection de table1.col2 vers table2.col3 au travers d’une relation d’égalité (tout le monde se rappelle ce qu’est une surjection ?), qu’a chaque valeur de table1.col2 correspond une et une seule valeur dans table1.col3 et que table1.col3 n’est pas une clé primaire.
Créons donc notre jeu de test qui va bien.
- Pour commencer la table table1qui contient trois colonnes (col1, col2 et col3) de type numérique.
- La première est unique et contient un entier compris entre 1 et 100 000. Aucune valeur n’est nulle.
- La deuxième contient des valeurs comprises entre 1 et 10 000. Aucune valeur n’est nulle.
- La troisième ne contient rien, toutes les valeurs sont nulles.
create table table1 ( col1 number, col2 number, col3 number ); insert /*+ APPEND */ into table1 select ROWNUM col1 , 1+ABS(MOD(dbms_random.random,10000)) col2 , null col3 from sys.dual connect by rownum <=1E5 ; commit; - Ensuite la table table2qui contient deux colonnes (col3 et col4) de type numérique.
- La première colonne est unique et contient un nombre compris entre 1 et 10 000. Aucune valeur n’est nulle.
- La seconde colonne contient un nombre aléatoire compris entre 1 et 100. Aucune valeur n’est nulle.
create table table2 as select ROWNUM col3 , 1+ABS(MOD(dbms_random.random,100)) col4 from sys.dual connect by rownum <=10000 ; - Enfin, dans le but de faire plusieurs tests on sauvegarde la table table1 dans la table s_t1et on calcule les statistiques sur le schéma :
create table s_t1 as select * from table1 ; exec dbms_stats.gather_schema_stats(user);
Avec tout ça, il est possible de reproduire les tests dans n’importe quel environnement Oracle. Ici on se place sur une base Oracle 10.2.0.4.0 sur une machine munie d’une douzaine de processeurs le tout soutenu par un système AIX 6.1 tout à fait honorable.
Tir de référence
Après avoir positionnées l’environnement pour obtenir le temps passé, le plan d’exécution et la consommation de ressources sous SQL*PLUS il est temps de lancer la requête de mise à jour.
set timi on autot on
update table1
set col3 = (select table2.col4
from table2
where table1.col2 = table2.col3) ;
100000 rows updated.
Elapsed: 00:02:43.31
Execution Plan
----------------------------------------------------------
Plan hash value: 2195448793
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | UPDATE STATEMENT | | 100K| 781K| 47 (3)| 00:00:01 |
| 1 | UPDATE | TABLE1 | | | | |
| 2 | TABLE ACCESS FULL| TABLE1 | 100K| 781K| 47 (3)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TABLE2 | 1 | 6 | 6 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Plus de deux minutes pour mettre à jour à peine cent-milles lignes c’est un peu long. Ceci dit force est de constater d’ores et déjà que l’optimiseur Oracle se plante joyeusement en estimant qu’il lui suffira de lire 6 octets de table2 pour obtenir la ligne qui correspond à celle qu’il traite dans table1.On restaure notre environnement et on se tente le PL/SQL proposé par yooo13.
drop table table1 ;
create table table1 as select * from s_t1;
exec dbms_stats.gather_schema_stats(user);
set timi on
declare
type rct is ref cursor ;
cur rct ;
l_idp integer := -13 ;
l_rowid rowid ;
begin
open cur for select table2.col4, table1.rowid
from table1
join table2 on table1.col2 = table2.col3 ;
loop
fetch cur into l_idp, l_rowid ;
exit when cur%notfound ;
update table1 set col3=l_idp where rowid=l_rowid ;
end loop ;
close cur ;
end ;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:07.09
Sept secondes et neuf centièmes, c’est déjà mieux. Le code PL/SQL proposé par yooo13 est donc bien, dans les conditions du test, une optimisation de la mise à jour. Le plan d’exécution lié au curseur est le suivant :
Execution Plan ---------------------------------------------------------- Plan hash value: 1317353746 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100K| 1367K| 54 (4)| 00:00:01 | |* 1 | HASH JOIN | | 100K| 1367K| 54 (4)| 00:00:01 | | 2 | TABLE ACCESS FULL| TABLE2 | 10000 | 60000 | 6 (0)| 00:00:01 | | 3 | TABLE ACCESS FULL| TABLE1 | 100K| 781K| 47 (3)| 00:00:01 | -----------------------------------------------------------------------------
La jointure entre table1 et table2 se fait par hashage et non plus ligne à ligne ce qui gagne un temps considérable. Je ne suis cependant toujours pas satisfait du résultat. Est-il possible de faire mieux que ça ?
Comment améliorer encore ce temps de réponse ?
Chacun des tests qui suivront sera effectué sur un environnement identique : la table table1 aura été restaurée et les statistiques sur le schéma recalculées selon la procédure décrite plus haut.
Utilisation de la commande merge comme proposé en commentaire de l’article d’origine.
merge into table1 dest using (select col3, col4 from table2) source on (dest.col2 = source.col3) when matched then update set dest.col3 = source.col4; 100000 rows merged. Elapsed: 00:00:02.75 Execution Plan ---------------------------------------------------------- Plan hash value: 186136457 ------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------- | 0 | MERGE STATEMENT | | 100K| 2050K| 54 (4)| 00:00:01 | | 1 | MERGE | TABLE1 | | | | | | 2 | VIEW | | | | | | |* 3 | HASH JOIN | | 100K| 1367K| 54 (4)| 00:00:01 | | 4 | TABLE ACCESS FULL| TABLE2 | 10000 | 60000 | 6 (0)| 00:00:01 | | 5 | TABLE ACCESS FULL| TABLE1 | 100K| 781K| 47 (3)| 00:00:01 | -------------------------------------------------------------------------------
La commande merge, qui existe depuis oracle 9i effectue la mise à jour en moins de trois secondes, on s’améliore. Cette amélioration est essentiellement due, comme dans la procédure PL/SQL proposée par yooo13, à l’utilisation d’une jointure par hashage en remplacement du produit cartésien effectué par la commande update à la modification à la volée des lignes dans le bloc en cours de traitement qui est plus rapide que le ligne à ligne du code PL/SQL.
Trois secondes sur une mise à jour de cent-mille lignes c’est certes intéressant en regard des près de trois minutes initiales mais on peut faire mieux.
Utilisation de la commande create table as select
Cette fois ci on va créer une table de résultat et la renommer par la suite. Bien sûr on fait du chargement direct en utilisant la commande create table as select afin d’éviter les lourdeurs liées à la journalisation.
set timi on
create table t1_tmp as
select t1.col1
, t1.col2
, case when t1.col2=t2.col3 then t2.col4
else t1.col3
end col3
from table1 t1
left join table2 t2 on ( t1.col2=t2.col3 ) ;
Table created.
Elapsed: 00:00:00.16
drop table table1;
Table dropped.
Elapsed: 00:00:00.01
alter table t1_tmp rename to table1 ;
Table altered.
Elapsed: 00:00:00.03
On obtient un temps total de traitement de seize plus un plus trois centièmes de secondes, soit vingt centièmes de secondes. Le plan d’exécution n’effectue que l’instruction select en utilisant une jointure par hashage, il n’y a aucun bloc à modifier ils sont directement insérés dans une nouvelle table d’où l’économie de temps… Cette fois ci je suis satisfait du temps de traitement.
Il est à noter que si on ne charge pas la table en mode direct le temps lié au chargement est de vingt-quatre centièmes soit cinquante pour cent de temps en plus rien que pour l’écriture dans les journaux.
A suivre …
Le modèle présenté ici est très simple, deux tables, pas d’index, et surtout une surjection de table1.col2 vers table2.col1 au travers d’une relation d’égalité, par la suite on compliquera le modèle en faisant en sorte que table2.col3 ne corresponde plus à toutes les valeurs de table1.col2 et en regardant l’impact des index sur une mise à jour.
