L’article précédent à propos de l’optimisation d’une requête SQL est extrêmement intéressant à de nombreux égards. D’ailleurs vous ne vous y êtes pas trompé au vu du nombre de commentaires générés dès sa sortie.

Note:
Le premier article cité en référence n’est pas mon article, je n’ai pas d’information très précise sue le contexte initial. L’auteur n’hésitera pas à répondre à mes remarques s’il les juge infondées.

L’importance des hypothèses

J’ai tendance à penser qu’un article sur un blog doit être sorti de son contexte. Ça lui donne un caractère plus universel et évite de pointer les erreurs des autres (et les miennes!). Bien sur le contexte peut-être anecdotique ou parfois fondamental.
En revanche, les hypothèses sont toujours fondamentales et dans cet exemple en particulier; la version d’Oracle, les structures de table, une clé primaire font une énorme différence. En outre, je crois qu’un lecteur mérite de pouvoir reproduire un cas d’utilisation simplement afin de comprendre de quoi il s’agit et aller plus loin, s’il le juge nécessaire.
L’article ne décrivant pas les hypothèses, je vous propose donc de choisir les miennes et vous allez voir que ça peut nous emmener en enfer:

• Disons qu’on utilise une base de données 11.2.0.1 sur Linux x86 32 bits mais vous obtiendrez a priori le même résultat en 11.1 ou 10.2
• et…

Une légère différence

Puisqu’il n’y a pas d’hypothèse, définissions les nôtres : je vous propose d’utiliser le script ci-dessous pour construire un exemple:

drop table table1 purge;
drop table table2 purge;
create table table1 (id number primary key,
                col2 number,
                col3 number);
create table table2 (id number primary key,
                col3 number,
                col4 number);
insert into table1 values (1,1,null);
insert into table2 values (2,1,1);
insert into table2 values (1,1,2);
commit;

Maintenant que nous avons créé notre exemple tordu (quoique?), nous allons pouvoir constater que les 2 ordres SQL de l’article précédent n’ont rien à voir; exécutons le premier exemple :

update table1
  set col3 = (select table2.col4
                    from table2
                  where table1.col2 = table2.col3);
                          *
ERROR at line 1:
ORA-01427: single-row subquery returns more than one row

Et maintenant, exécutons notre code PL/SQL :

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.

Et voilà, nous venons de démontrer que les 2 commandes ne font pas la même chose puisque le résultat est différent…

PL/SQL, mon amour

On peut discuter de la manière de coder du PL/SQL, la pertinence d’initialiser une valeur à -13 ou -59, de déclarer des type, d’utiliser FOR IN (SELECT...) LOOP, FORALL, FOR UPDATE, etc. Le fait est qu’on est parfois amené à coder du PL/SQL pour certaines opérations et notamment pour faire des commits par tableau… Pourtant le PL/SQL n’est pas votre meilleur ami et cet exemple l’illustre bien puisque le résultat de notre ordre dépend du placement des données et des plans d’exécution. Je vais vous le démontrer ci-dessous. Comme vous allez vous en apercevoir, le résultat dépend en fait du placement de mes données dans la table table2.
Lorsque vous lisez la table table2 dans l’ordre du stockage et que la ligne contenant la valeur 2 de col4 est stockée après la ligne contenant la valeur 1, vous obtenez ce qui suit :

declare
   type rct is ref cursor ;
   cur rct ;
   l_idp integer := -13 ;
   l_rowid rowid ;
begin
   open cur for
     select /*+ use_hash(table1 table2) full(table2) */
            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.
select col3 from table1;
      COL3
----------
         2

Si par contre, vous utilisez l’index sur ID (organisé dans l’ordre inverse), vous obtenez une autre valeur :

declare
   type rct is ref cursor ;
   cur rct ;
   l_idp integer := -13 ;
   l_rowid rowid ;
begin
   open cur for
     select /*+ use_hash(table1 table2) index(table2) */
            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 ;
/
select col3 from table1;
      COL3
----------
         1

Utilisez Oracle

Mais revenons à nos hypothèses. Imaginons maintenant que nous ayons des hypothèses différentes et sans doute plus proches de la réalité originelle … enfin j’espère. Voici le script que je vous propose d’utiliser pour créer vos structures :

drop table table1 purge;
drop table table2 purge;
create table table1 (id number primary key,
                col2 number,
                col3 number);
create table table2 (col3 number,
                col4 number);
insert into table1 values (1,1,null);
insert into table2 values (1,1);
commit;

Si maintenant, vous ajoutez une contraintes complémentaires, indiquant que les valeurs de la colonne col3 sont uniques, vous pourrez écrire à l’aide d’un ordre update, une logique très similaire à celle du code PL/SQL; En fait, créer un index unique aurait suffit :

alter table table2
   add constraint table2_col3_uniq
         unique (col3);

Et voilà comment vous pouvez écrire votre bloc PL/SQL:

update (select table1.col3, table2.col4
                      from table1
                       join table2 on table1.col2 = table2.col3)
             set col3=col4 ;

Il n’en reste pas moins que les 2 ordres ont des sémantiques différentes puisque l’update initial valide l’unicité du select imbriqué alors que notre second update s’appuie sur une contrainte. J’aurais donc tendance à penser que ce dernier est plus efficace en général. Toutefois, je ne serais pas non plus étonné d’être pris à raconter une ineptie dans certains contextes ou certaines versions …

Conclusion

Voilà, vous en conviendrez, l’article original est fondamentalement intéressant et j’attends avec impatience « Optimisation SQL – 2 »; Supprimez vos 2 tables:

drop table table1 purge;
drop table table2 purge;

Il est intéressant de noter qu’Oracle 11.2.0.1, même si vous essayez de forcer le plan, n’arrive pas à ré-écrire la première version de l’update en seconde version. Preuve, s’il en fallait, qu’Oracle 11.2 ne sera pas la dernière version d’Oracle. Enfin, espérons!