Discussion :

[Tay94]

Bonjour,

Je souhaiterais savoir si pour Oracle il y a une différence de performance entre les 2 requêtes suivantes, sachant que dans la table TAB1 ATT2='Sarkozy' :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
update TAB1 T
set T.ATT2='Sarkozy'
where T.ATT1='123456'

Et

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
update TAB1 T
set T.ATT2='Sarkozy'
where T.ATT1='123456'
and T.ATT2<>'Sarkozy'

Autrement dit, lorsqu'une valeur à mettre à jour est identique à sa nouvelle valeur, Oracle réécrit il tout de même cette valeur sur le disque dur ?

Laquelle est la plus performante (la seconde selon moi) ?

[sgora]

Avec Sarkozy, il y a de toute façon beaucoup de problèmes de performances

Sinon, pour tes deux requêtes, je dirais ça dépend...

[mnitu]

Dans un update il y a une partie mise à jour des données (SET) et une partie recherche de données analogue au Select * from table Where...
Du ce point de vue, vous comparez deux requête qui sont différentes

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
...
WHERE T.ATT1='123456'

avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
...
WHERE T.ATT1='123456'
AND T.ATT2<>'Sarkozy'

et donc pour lequel il n’y a pas vraiment de comparaison possible en terme de performance, parce qu'elles ne font pas la même chose.

[Tay94]

Les 2 requêtes étaient effectivement différentes, mais ma question était d'ordre général.

Ainsi les 2 requêtes ci-dessous donneront au final le même résultat sans faire la même chose, et je me demande bien laquelle utiliseriez vous :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
UPDATE TAB1 T1
SET T1.ATT2=
	(select T2.ATT2
	 from TAB2 T2
	 where T1.ATT1=T2.ATT1
	 and T1.ATT2<>T2.ATT2)
WHERE EXISTS
	(select 1
	 from TAB2 T2
	 where T1.ATT1=T2.ATT1
	 and T1.ATT2<>T2.ATT2)

ou

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
UPDATE TAB1 T1
SET T1.ATT2=
	(select T2.ATT2
	 from TAB2 T2
	 where T1.ATT1=T2.ATT1)
WHERE EXISTS
	(select 1
	 from TAB2 T2
	 where T1.ATT1=T2.ATT1)

La 1ère doit se révéler plus performante dans bien des cas selon moi, car elle minimise les écritures sur le disque, à moins qu'Oracle vérifie que la valeur nouvelle est égale à l'ancienne avant d'effectuer la mise à jour...

[sgora]

Tu sais, quand Oracle fait ce genre d'opération, tout se déroule en mémoire, la différence (si différence il y a ) est minime. Oracle écrit ensuite les données modifées sur disque de manière asynchrone, au fil de l'eau.

C'est à ça que sert le buffer cache !

[Waldar]

Oui mais quand même, rien ne sert de remettre à jour la même information.

Imaginez ce cas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
create table toto1
(
   tid    number(1),
   tname  varchar2(4)
)
 
insert /*+ APPEND */ into toto1
select 1, 'NOM' from dual
connect by level <= 1e6;
 
commit;
 
insert into toto1 values (2, 'NOM2')
 
commit;
 
create table toto2
(
   tid    number(1),
   tname  varchar2(4)
)
 
insert into toto2 values (1, 'NOM');
insert into toto2 values (2, 'NOM3');
 
commit;
 
update toto1 t1
   set t1.tname = (select t2.tname
                     from toto2 t2
                    where t2.tid = t1.tid)
 where exists (select null
                 from toto2 t2
                where t2.tid = t1.tid);
 
-- 1000001 lignes maj, 8 sec
 
rollback;
 
update toto1 t1
   set t1.tname = (select t2.tname
                     from toto2 t2
                    where t2.tid = t1.tid
                      and t2.tname <> t1.tname)
 where exists (select null
                 from toto2 t2
                where t2.tid = t1.tid
                  and t2.tname <> t1.tname);
 
-- 1 ligne maj, 250 ms

Les volumétries en jeu font que la requête qui précise la différence sur les noms est nettement plus rapide.

[mnitu]

Au départ vous avez une demande fonctionnelle : mettre à jour telle table si telle condition. Si les règles de gestion imposent de ne pas mettre à jour les lignes qui contient déjà la valeur vous ne devez pas le faire. Et le choix est vite fait.

Une fois que les règles de gestion ont été comprises vous pouvez analyser les diverses solutions par leur impact en termes de performance; en pratique c’est un peu plus compliqué que la règle que vous voulez valider ici. Il se peut bien que fonctionnellement la situation que vous décriviez n’arrive jamais.

Dans votre cas précis il se pourrait bien que ça va aller plus vite si vous pouvez parcourir la table TAB2 une seule fois au lieu de 2 fois comme actuellement: une fois dans le select et une fois dans le EXISTS. En fonction de la version de votre base Oracle peut être que vous allez pouvoir utiliser l’instruction MERGE à la place d’un UPDATE. Et probablement que cette solution dépasse de loin en termes de performance votre proposition.

[Waldar]

En fonction de la version de votre base Oracle peut être que vous allez pouvoir utiliser l’instruction MERGE à la place d’un UPDATE. Et probablement que cette solution dépasse de loin en termes de performance votre proposition.

C'est ce que je me disais mais en fait peut-être pas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
MERGE INTO toto1 t1
USING toto2 t2
   ON (t2.tid = t1.tid)
 WHEN MATCHED THEN UPDATE
  SET t1.tname = t2.tname;
 
-- 1000001 lignes maj, 8 sec
 
rollback;
 
MERGE INTO toto1 t1
USING toto2 t2
   ON (t2.tid = t1.tid
  AND t2.tname <> t1.tname)
 WHEN MATCHED THEN UPDATE
  SET t1.tname = t2.tname;
 
-- ORA-38104: Les colonnes référencées dans la clause ON ne peuvent pas être mises à jour : "T1"."TNAME"

Il y a peut-être un moyen de contourner.

[pachot]

Bonjour,

Oracle réécrit il tout de même cette valeur sur le disque dur

Oui, et avec son undo et son redo, pour la table et pour les éventuels indexes.
Donc la deuxième requête sera plus rapide.

Mais ce n'est pas tout. Il peut y avoir d'autres conséquences.

Imaginez qu'il y ait un trigger qui fassent certaines action lorsque la colonne est updatée. Du genre mettre à jour une date de dernière modification. Est-ce fonctionellement vous considerez que c'est une mise à jour si la nouvelle valeur est la même que la précédante ? Quelle doit être la date de dernière modification ?

Et les verrous: l'update va tout de même vérouiller l'enregistrement. C'est peut être souhaitable (pour la même raison qu'on fait des select for update) ou bien un problème de performance (risque de blocage inutile).

Cordialement,
Franck.

[sgora]

Effectivement, on peut imaginer beaucoup de choses, mais quand on voit d'où on est parti...

On peut raisonnablement penser qu'avec une clause where avec un prédicat d'égalité, la requête va nous ramener entre 0 et 10 lignes, donc le fait de mettre à jour ou pas une ligne qui a déjà la bonne valeur, c'est discuter du sexe des anges, vous trouvez pas ?

Comme aurait le grand Bill, Much ado about nothing...

[Tay94]

c'est discuter du sexe des anges, vous trouvez pas ?

Je ne suis pas d'accord, car dans le cas ou l'on a pas de trigger sur la mise à jour des lignes, qu'on a un grand nombre de lignes à mettre à jour dans une table A à partir d'une table B et que beaucoup de valeurs sont identiques (exemple donné précédemment), et bien au lieu de mettre à jour 1 million de lignes on n'en mettra à jour que 50 000 par exemple.

Ce qui peut apporter un gain de temps non négligeable.

[sgora]

Ah ben avec un trigger, ça peut être encore pire au niveau performance. Si t'as un grand nombre de lignes à mettre à jour dans ta base, c'est une mauvaise solution côté performance à mon avis. Et je maintiens que vu tes requêtes de départ, la différence de performance doit être minime.

Maintenant, si tu peux nous fournir des stats sur la table concernée et les requêtes que t'utilises, ce serait mieux que de parler dans le vide, tu trouves pas ?

Sinon, il y a d'autres méthodes créatives pour réaliser des updates massifs sur des tables, voir :

http://asktom.oracle.com/pls/apex/f?...:6407993912330

[pachot]

Et je maintiens que vu tes requêtes de départ, la différence de performance doit être minime.

Ah ? J'ai vu la requête, mais ni le nombre de lignes de la table, ni le nombre de fois où cette requête est exécutée, ni ...
Et lire 2 fois plus de blocs (undo) ou générer 2 fois plus de redo peut avoir de grosses conséquences si c'est exécuté plusieurs fois sur un système chargé. Même s'il n'y a que 3 lignes dans la table.

[sgora]

Donc la conclusion est on sait pas, jusqu'à ce qu'on ait plus de précisions de l'auteur, qui nous a baladés sur des requêtes, qu'il a ensuite changées en cours de route, si bien qu'on ne comprend pas bien ce qu'il cherche.

Bien sûr qu'il y a une différence si la requête est exécutée une fois ou mille, mais comme on a aucun élément qui pourrait montrer que la requête est récurrente, et comme ça ressemble beaucoup plus à un "one shot'' je penche pour cette solution.

Maintenant, si tu préfères faire des suppositions à n'en plus finir, libre à toi, mais je pense que ça va pas t'amener très loin...

[Tay94]

En faisant quelques tests, je me suis rendu compte que les explain plan des 2 types de requêtes ne sont pas forcément identiques en utilisant les mêmes tables.

En effet avec la requête stipulant T1.ATT2<>T2.ATT2 j'avais un FULL TABLE SCAN contrairement à l'autre où j'utilisais l'index. Ainsi pour la 1ère le temps était d'une minute et le ROLLBACK de moins d'une seconde, alors que pour la deuxième le temps de traitement était de 5 secondes et le ROLLBACK de 30 secondes.

Ce qui confirme bien les dire de pachot concernant les REDO et UNDO générés en plus par la deuxième.

Désolé de ne pas pouvoir montrer de jeu de test : car je n'ai pas le droit de créer des tables (je ne suis pas DBA), ainsi je dois utiliser les tables déjà existantes.

qui nous a baladés sur des requêtes

Je me posais une question générale sur le fonctionnement d'Oracle (pas sur une requête particulière précisément), et je n'ai obligé personne à y répondre.

[sgora]

Le problème avec les questions générales c'est qu'on y répond souvent au doigt mouillé, si tu nous avais dit dès le départ : "voilà le problème, j'ai une table de tant de lignes avec telle structure, j'ai des index sur telles colonnes, j'essaie de faire ça, quelle est la meilleure solution pour le faire ?", on gagnerait du temps et on aurait moins l'impression de pédaler dans le vide.
Toi, tu as commencé à nous parler de requêtes Sarkozy sur une table, puis d'autres requêtes sur deux tables, crois-tu vraiment avoir été clair et être allé à l'essentiel ? Hmm ?
Je te fais remarquer qu'on a toujours aucune idée sur la volumétrie des tes tables ainsi que sur la distribution des données.

Donc le meilleur moyen de faire c'est pas la première requête, pas la deuxième, ça dépend du contexte dans lequel tu veux le faire, que tu connais mais qu'on ne connaît pas. On peut donc te dire des choses très générales sur Oracle qui ne seront pas applicables dans ton cas, parce qu'il est, comme chaque cas, particulier. Ca me rappelle les débats entre not in et not exists, y en a pas un qui est meilleur que l'autre, ça dépend...

Bon, allez je te laisse avec les experts en généralités et en suppositions, je préfère personnellement traiter les cas intéressants