Optimisation de rêquetes

Version imprimable

Bonjour à tous,

J'ai testé deux requêtes qui donnent le même résultat et j'ai été pas mal surpris du résultat obtenu !

Voici les requêtes :

Code:

1
2
3
4
5
6
7
8
 
SELECT SCHEMA.CLIENT.LIEU AS LIEU
     , SCHEMA.CLIENT.IDENT AS ID
     , (SELECT SCHEMA.INFO.LABEL 
        FROM SCHEMA.INFO
        WHERE SCHEMA.INFO.IDENT = SCHEMA.CLIENT.IDENT
       ) AS LABEL
FROM SCHEMA.CLIENT

Code:

1
2
3
4
5
6
 
SELECT SCHEMA.CLIENT.LIEU AS LIEU
     , SCHEMA.CLIENT.IDENT AS ID
     , SCHEMA.INFO.LABEL AS LABEL
FROM SCHEMA.INFO
     LEFT JOIN SCHEMA.INFO ON SCHEMA.INFO.IDENT = SCHEMA.CLIENT.IDENT

Et voici le résultat :

Code:

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
 
Premier Query
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------
 
Plan hash value: 4037814144
 
-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |   496K|  6307K|   337   (3)| 00:00:05 |
|   1 |  TABLE ACCESS BY INDEX ROWID| INFO |     1 |    10 |     3   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | SYS_C0041052    |     1 |       |     2   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL          | CLIENT   |   496K|  6307K|   337   (3)| 00:00:05 |
-----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("INFO"."IDENT"=:B1)
 
 
Second Query
 
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------
 
Plan hash value: 1523841922
 
-------------------------------------------------------------------------------------------------
| Id  | Operation             | Name            | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                 |   496K|    10M|       |  1376   (2)| 00:00:17 |
|*  1 |  HASH JOIN RIGHT OUTER|                 |   496K|    10M|  6152K|  1376   (2)| 00:00:17 |
|   2 |   TABLE ACCESS FULL   | INFO |   286K|  2795K|       |   142   (3)| 00:00:02 |
|   3 |   TABLE ACCESS FULL   | CLIENT   |   496K|  6307K|       |   337   (3)| 00:00:05 |
-------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("INFO"."IDENT"(+)="CLIENT"."IDENT")

Je suis assez étonné car la première requête effectue une requête supplémentaire par enregistrement... La deuxième traite je suppose plus de données étant donné la jointure, mais de là à avoir un résultat 3x plus lent...
La deuxième requête me semblait plus propre..

Qu'en pensez-vous ?

Merci d'avance

Bonjour,

tes requêtes ne sont pas ISO.

Si elles ramènent le même nombre de lignes c'est qu'il doit y avoir une relation 1..1 entre tes 2 tables .
Code:

1 2 3 4 5 6 7 8 SELECT SCHEMA.CLIENT.LIEU AS LIEU , SCHEMA.CLIENT.IDENT AS ID , (SELECT SCHEMA.INFO.LABEL FROM SCHEMA.INFO WHERE SCHEMA.INFO.IDENT = SCHEMA.CLIENT.IDENT ) AS LABEL FROM SCHEMA.CLIENT
Ici ton sous select ne doit ramener qu'une seule ligne sinon KO.

L’équivalent serait plutôt
Code:

1 2 3 4 5 6 SELECT SCHEMA.CLIENT.LIEU AS LIEU , SCHEMA.CLIENT.IDENT AS ID , SCHEMA.INFO.LABEL AS LABEL FROM SCHEMA.INFO INNER JOIN SCHEMA.CLIENT ON (SCHEMA.INFO.IDENT = SCHEMA.CLIENT.IDENT)
Sinon pourquoi est ce plus long, et bien cela est du au type de la jointure .
Dans le 1e cela doit etre une Nested loop en passant par des indexs.

La deuxieme du fait que c'est une jointure ouverte, oracle doit pouvoir gerer le fait qu'il n'y a pas de match entre les 2 tables, pour ce faire il commence par fetcher les 2 tables en memoire puis il travail en hash join ( regarde le coup CPU qui explose ) ce type de jointure est assez efficace lorsque tu croises 2 grosses volumetries.

Cordialement.

28/01/2011, 11h53
mnitu
Votre deuxième requête c'est un peu n'importe quoi donc il ne faut pas s'étonner que ça prends du temps
Code:

1 2 3 4 ... FROM SCHEMA.INFO LEFT JOIN SCHEMA.INFO ON SCHEMA.INFO.IDENT = SCHEMA.CLIENT.IDENT
@ORA-007
Une sous-requête scalaire se remplace par un outer join et non pas par un inner join.

Citation:
Envoyé par mnitu
Votre deuxième requête c'est un peu n'importe quoi donc il ne faut pas s'étonner que ça prends du temps
Code:

1 2 3 4 ... FROM SCHEMA.INFO LEFT JOIN SCHEMA.INFO ON SCHEMA.INFO.IDENT = SCHEMA.CLIENT.IDENT
@ORA-007
Une sous-requête scalaire se remplace par un outer join et non pas par un inner join.
Navré, il est évident que j'ai simplement fait une erreur en recopiant.. :oops:
Je répète les deux requêtes :
Code:

1 2 3 4 5 6 7 8 SELECT SCHEMA.CLIENT.LIEU AS LIEU , SCHEMA.CLIENT.IDENT AS ID , (SELECT SCHEMA.INFO.LABEL FROM SCHEMA.INFO WHERE SCHEMA.INFO.IDENT = SCHEMA.CLIENT.IDENT ) AS LABEL FROM SCHEMA.CLIENT
2è :
Code:

1 2 3 4 5 6 SELECT SCHEMA.CLIENT.LIEU AS LIEU , SCHEMA.CLIENT.IDENT AS ID , SCHEMA.INFO.LABEL AS LABEL FROM SCHEMA.CLIENT LEFT JOIN SCHEMA.INFO ON SCHEMA.INFO.IDENT = SCHEMA.CLIENT.IDENT
@ORA-007 : Oui le sous-select ne renvoit qu'un enregistrement..
Les deux requêtes fonctionnent et donnent le bon résultat.
Je m'étonne juste de la différence de performence.

28/01/2011, 13h26
orawiss

Bonjour,

Peut être j'ai pas compris la question ... mais je ne vois aucun problème. La différence est bien évidente car l'optimizer utilise l'index dans la première req et utilise un full scan dans la seconde, d'ou la diff de performance.

Merci,
Wissem
www.oracle-class.com (Vidéos, Articles, Livres, Forum, Webinar ...tous sur Oracle)
www.oracle-tns.com
OCA & OCP Oracle
28/01/2011, 13h40
skuatamad

Tout dépend de la volumétrie des 2 tables, si INFO a beaucoup plus de ligne que client alors il paraît normal que la requête scalaire soit plus performante. Quels sont les temps d'exécution des 2 requêtes ?

[EDIT] En fait j'ai toutes les infos dans le plan, étrange INFO semble quasiment 2x plus petite que CLIENT, les stats sont elles à jour ?
28/01/2011, 13h55
mnitu

Citation:

Envoyé par libuma

Navré, il est évident que j'ai simplement fait une erreur en recopiant.. :oops:

C'est vrai, sinon la deuxième requête n'aurais pas pu tourner :aie:
Commencez par vérifier/recalculer les statistiques.
Quelle est la version d'Oracle ?
28/01/2011, 14h22
libuma

@orawiss : Donc lorsqu'il s'agit d'outer join, il est préférable d'utiliser la première méthode ? je suppos que dans le cas d'un inner join, c'est quand même préférable la deuxième méthode. Plutôt que d'avoir un select from (x table) where, on se retrouve alors avec des select (x select imbriqués) from where.. ça me paraît brouillon.

@skuatamad : C'est une table relation 1-1 avec une info liée à un client, cette info peut être inexistante.

@mnitu : Oracle 10g
28/01/2011, 16h14
mnitu

Oracle 10g est plutôt bien au niveau d’optimiseur. Avez-vous vérifié les statistiques ?
Dpv optimisation, la façon d’écrire la requête n’est pas toujours neutre, surtout quand on sort du relationnel (via la sous-requête scalaire). Quand vous utilisez des sous-requêtes scalaires vous envoyé un signale fort à l’optimiseur en ce qui concerne le plan d’exécution. L’optimiseur a le choix de transformer votre sous-requête en utilisant un outer join mais comme ce type de transformation n’est pas toujours possible il est très probable qu’il va « suivre » vos indications.
Mais, avec les statistiques à jour je pense qu’il pourrait opter pour un nested loop à la place de hash join.
Exécutez le deuxième requête avec le hint gather_plan_statistics pour afficher les différences entre les cardinalités réelles et celles estimées.
28/01/2011, 16h43
Waldar

Vous n'avez pas aussi fait une erreur en recopiant la première requête ?
Car il ne fait qu'un et un seul unique appel à l'index, comme si on lui avait passé une valeur.

Comme dit précédemment, les deux requêtes sont équivalentes dans une relation (0, 1).

Citation:

Envoyé par Waldar

Vous n'avez pas aussi fait une erreur en recopiant la première requête ?
Car il ne fait qu'un et un seul unique appel à l'index, comme si on lui avait passé une valeur.
...

Je ne pense pas que c'est un souci

Code:

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
 
SQL> Select e.first_name,
  2         e.last_name,
  3         (Select d.department_name
  4            From hr.departments d
  5           Where  e.department_id = d.department_id
  6         )
  7    From hr.employees e
  8  /
 
108 ligne(s) sÚlectionnÚe(s).
 
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 1821517685
 
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |   108 |  1944 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DEPARTMENTS |     1 |    16 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DEPT_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   3 |  TABLE ACCESS FULL          | EMPLOYEES   |   108 |  1944 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("D"."DEPARTMENT_ID"=:B1)

31/01/2011, 09h10
libuma

Merci pour ces infos :)