Discussion :

[jlinho2]

Bonjour,

j'ai effectué une requête simple qui tourne rapidement sur Oracle 10g chez moi. Le problème est que mon environnement au travail est une Oracle 9i. J'attends vos idées pour la faire autrement (y compris en passant par des tables temporaires ou autre). Voici la requête:

Code :

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

1
2
3
update routes t1 set t1.valid=1
 where not exists (select départ,arrivée from routes t2 where t2.arrivée= t1.départ and t2.valid = 0)
 and t1.valid = 0;

où routes(départ(PK)= NUMBER, arrivée= NUMBER, valid=NUMBER) peut être remplie avec le script (très moche) suivant:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
declare
  rand BINARY_INTEGER;
begin
   execute immediate 'truncate table routes';
  for i in 1..300000 loop
    SELECT  TO_NUMBER(SUBSTR(CURRENT_TIMESTAMP,20,5)) INTO rand FROM dual;
    INSERT INTO routes VALUES (i,rand,0);
  end loop;
 
  commit;
end;
/

Pour donner un peu de concret à la chose, imaginez que chaque nombre dans la table est une ville et que chaque ligne de la table "routes" est une route (la direction est importante) allant d'une ville "départ " à une ville "arrivée".

Une ville-départ est une ville où n'arrivent aucune route, mais d'où part une route (oui que une seule route car "départ" est la PK). Le but de cette requête est de noter "valid=1" toutes les routes qui partent d'une ville-départ.

A noter qu'avant de jouer ma requête il faut faire:

Code :

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

1
2
 
update routes set valid=-1 where départ=arrivée;

Merci d'avance pour votre aide

[orafrance]

T'as essayé de voir si un NOT IN est plus rapide ?

Code :

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

1
2
3
UPDATE routes t1 SET t1.valid=1
 WHERE depart NOT IN (SELECT arrivée FROM routes WHERE t2.valid = 0)
 AND t1.valid = 0;

T'as comparé tes plans d'exécution 9i vs 10g ? T'as quoi comme index ?

[McM]

1/ Il ne faut pas utiliser de colonnes dans un NOT EXISTS, ça oblige oracle à lire les données.

Code :

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

1
2
3
UPDATE routes t1 SET t1.valid=1
 WHERE NOT EXISTS (SELECT 1 FROM routes t2 WHERE t2.arrivée= t1.départ AND t2.valid = 0)
 AND t1.valid = 0;

[orafrance]

1/ Il ne faut pas utiliser de colonnes dans un NOT EXISTS, ça oblige oracle à lire les données.

Plus maintenant me semble-t-il

[jlinho2]

Plan d'execution pour Oracle 9i (avec le 1):

Code :

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

1
2
3
4
5
6
Plan
UPDATE STATEMENT  CHOOSE			
	4 UPDATE STAGIAIRE.TABLE1 		
		3 FILTER  	
			1 TABLE ACCESS FULL STAGIAIRE.TABLE1 
			2 TABLE ACCESS FULL STAGIAIRE.TABLE1

Plan d'exécution pour Oracle 10g Express:

Code :

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

1
2
3
4
5
6
Plan
UPDATE STATEMENT  ALL_ROWSCost: 1,831  Bytes: 19,986,252  Cardinality: 384,351  			
	4 UPDATE STAGIAIRE.TABLE1 		
		3 HASH JOIN ANTI  Cost: 1,831  Bytes: 19,986,252  Cardinality: 384,351  	
			1 TABLE ACCESS FULL TABLE STAGIAIRE.TABLE1 Cost: 208  Bytes: 9,995,726  Cardinality: 384,451  
			2 TABLE ACCESS FULL TABLE STAGIAIRE.TABLE1 Cost: 208  Bytes: 9,995,726  Cardinality: 384,451

La différence de temps est énorme: moins de 20 secondes sur 10g Express, au bout de 10 minutes (traitement interrompu) le traitement 9i était pas terminé ...

Que faire ? Est-il possible de "suivre" - en faisant plusieurs reqûetes quasi-équivalentes - le plan de la 10g ? Peut on utiliser des directives , genre "merge_aj" ?

NB: Le test a été fait avec le "1" dans le NOT EXISTS

[jlinho2]

Y a t'il moyen de forcer le plan d'exécution entier...

[jlinho2]

Bon il me semble que j'ai forcé le même plan d'exécution identique...
Mais c'est toujours aussi lent ...

En tout cas quand je rajoute les hints ainsi:

Code :

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

1
2
3
update /*+ALL_ROWS */routes t1 set t1.valid=1
 where not exists (select /*+HASH_AJ*/  1 from routes t2 where t2.arrivee= t1.depart and t2.valid = 0)
 and t1.valid = 0;

et que je fais un explain plan dessus... j'ai le plan d'exécution voulu sur 9i....

A l'exécution je vois pas de différence...
Est-ce que le plan est bien suivi ?

[orafrance]

Faudrait surtout penser à créer un index pour éviter le FTS

[jlinho2]

Après test, c'est l'update que la 9i ne sait pas bien faire. En changeant le update en select *, et en mettant le hint /*+ HASH_AJ*/ tout se passe bien.

bizarre.

[jlinho2]

Merci

Pour l'index, en quoi m'aiderait-il?

Je doit de toute façon parcourir les tables en entier ou presque...
D'ailleurs l'optimizer n'utilise pas l'index sur "départ".

Bon dans tous les cas je vais tester avec des indexs sur arrivée, départ ou les 2.

Je vous tiens au courant...

[orafrance]

Pour l'index, en quoi m'aiderait-il?

Un FTS ne met pas les blocs en cache, donc tu lis la table 2 fois sur disque.

En tout cas, ça se tente