Discussion :

[romu92]

Bonjour,

je cherche à optimiser des requêtes SQL trop longues à exécuter.
Existent t'ils des règles standards pour optimiser une requête SQL du type :
- l'importance de l'ordre des tables dans les jointures INNER JOIN?
- préférer une clause IN pour filtrer par rapport à une autre table plutôt qu'une clause INNER JOIN?
- préférer les jointures dans le WHERE plutôt que dans l'INNER JOIN?
- l'importance des conditions dans la clause WHERE?

merci pour ces infos,

Romu

[orafrance]

l'important c'est de bien indexer les tables et calculer les stats, après, l'écriture de la requête importe peu.

[mnitu]

l'important c'est de bien indexer les tables et calculer les stats, après, l'écriture de la requête importe peu.

Ca c'est vraiment du Saint Graal.

[orafrance]

C'est à dire ?

Sinon, pour info : http://www.developpez.net/forums/m2548995-2/

[mnitu]

C'est à dire ?

Sinon, pour info : http://www.developpez.net/forums/m2548995-2/

Oui, d'accord l'optimiseur se debrouille généralement bien! Mais je peux te montrer de requêtes assez simple où "l'écriture de la requête importe", pour exemple parce que elle impose le choix du plan (et je ne parle pas de hints).

[orafrance]

Oui, mais ça ne répond à aucune règle, juste trouver l'ordre qui permettra de trouver le bon plan dans le nombre de permutations autorisée. Enfin... si tu veux répondre à la question initiale, fais donc

[skuatamad]

- préférer une clause IN pour filtrer par rapport à une autre table plutôt qu'une clause INNER JOIN?

Ne pas oublier la clause EXISTS qui peut apporter de net gain de performance, mais bien sûr ça reste du cas par cas.
A noter que la jointure n'est pas forcément équivalente en terme de résultat.

[SheikYerbouti]

Voici un petit début d'élément de réponse.

[orafrance]

très bon tuto

[plaineR]

Ah oui, super intéressant, merci Skeik

[SheikYerbouti]

Enfin, il a tout de même un peu vieilli...

[mnitu]

Enfin, il a tout de même un peu vieilli...

Oui, un peu trop. Il était au point en 2000 je pense. Mais certains choses ont évolue depuis.
Petit example

Ne mélangez ni ne comparez des valeurs et des types de données de colonnes car
l’optimiseur ignorerait l’index.
Si le type de colonne est NUMBER, n’utilisez pas de guillemets pour encadrer la valeur.
De même, n’oubliez pas d’encadrer une valeur avec ces guillemets lorsqu’elle est définie
comme de type alphanumérique.

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
 
SQL> set autotrace traceonly explain
SQL> Select *
 from emp t
  where empno = '7369'  2    3  ;
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=3
          7)
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Byte
          s=37)
 
   2    1     INDEX (UNIQUE SCAN) OF 'PK_EMP' (UNIQUE)

Cella ne veut pas dire qu'il faut la faire.

[SheikYerbouti]

Cela dépend du noyau utilisé. Ceux qui travaillent encore sur des bases 8 voir même 9 peuvent encore s'inspirer du document.

[mnitu]

Cela dépend du noyau utilisé. Ceux qui travaillent encore sur des bases 8 voir même 9 peuvent encore s'inspirer du document.

Je teste sur une base Oracle 9

Evitez de coder des sous-requêtes liées dans vos applications. Elles ont un impact négatif sur
les performances de votre système en consommant beaucoup de ressources CPU.
La solution consiste à utiliser des vues en ligne (inline views), c'est-à-dire des sous-requêtes
dans la clause from de l’instruction select, disponible depuis la version 7.3.
Select e.* from EMP e
Where e.salary > ( select avg( salary) from EMP i where i.dept_id =
e.dept_id ) ; -- Mauvais
Select e.* from EMP e,
(select i.dept_id DEP, avg(i.salary) SAL from EMP I group by dept_id
) EMP_VUE
where e.dept_id = EMP_VUE.dept_id
and e.salary > EMP_VUE.SAL ; -- Bon

En fait c'est un peu plus nuancé.

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
 
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
JServer Release 9.2.0.5.0 - Production
 
SQL> set autotrace traceonly explain
SQL> Select e.* from EMP e
Where e.sal > ( select avg( sal) from EMP i where i.deptno = e.deptno )
  2    3  ;
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=4 Card=1 Bytes=3
          7)
 
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=1 Bytes=37)
   3    1     SORT (AGGREGATE)
   4    3       TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=5 Bytes=35)
 
 
 
SQL> Select e.* from EMP e,
(select i.deptno DEPTNO, avg(i.sal) SAL from EMP I group by deptno
) EMP_VUE
where e.deptno = EMP_VUE.deptno
and e.sal > EMP_VUE.SAL
  2    3    4    5    6  ;
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=7 Card=1 Bytes=6
          3)
 
   1    0   HASH JOIN (Cost=7 Card=1 Bytes=63)
   2    1     VIEW (Cost=4 Card=3 Bytes=78)
   3    2       SORT (GROUP BY) (Cost=4 Card=3 Bytes=21)
   4    3         TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=9
          8)
 
   5    1     TABLE ACCESS (FULL) OF 'EMP' (Cost=2 Card=14 Bytes=518)

[mnitu]

Encore une

N’utilisez pas l’opérateur IS NULL dans une colonne indexée, sinon l’optimiseur ignorera
l’index.

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SQL> Select *
  From emp
 Where empno is Null  2    3  ;
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=3
          7)
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=2 Card=1 Byte
          s=37)
 
   2    1     INDEX (RANGE SCAN) OF 'PK_EMP' (UNIQUE)

Je ne dit pas que cella a un sens ni que le comportement c'est le même si la colonne n'est pas déclaré not null

[SheikYerbouti]

Ok mnitu, tu as raison (comme toujours...).
Donc, chers amis, laissez tomber le lien que je vous indiquais, car je ne tiens pas à voir chacun des concepts indiqués remis en question pour savoir s'il est toujours d'actualité sur une base 9, puis 10g et pourquoi pas 11g pendant qu'on y est.

[orafrance]

Disons qu'Oracle s'est efforcé d'améliorer l'optimiseur mais que ça ne fait pas de mal de garder les fondamentaux comme de comparer des types différents

Sinon, Mnitu, on attend toujours que tu répondes à la question ce qui serait bien plus constructif que critiquer les nôtres

[mnitu]

Et cella est eronnée ou je le comprends mal.

Utilisez des index composites. Ceux-ci doivent être classés dans l’ordre décroissant de sélectivité.

[mnitu]

Ok mnitu, tu as raison (comme toujours...).
Donc, chers amis, laissez tomber le lien que je vous indiquais, car je ne tiens pas à voir chacun des concepts indiqués remis en question pour savoir s'il est toujours d'actualité sur une base 9, puis 10g et pourquoi pas 11g pendant qu'on y est.

Non je n'ai pas toujours raison simplement j'esaie d'argumenter mon point de vue.

[mnitu]

Disons qu'Oracle s'est efforcé d'améliorer l'optimiseur mais que ça ne fait pas de mal de garder les fondamentaux comme de comparer des types différents

Sinon, Mnitu, on attend toujours que tu répondes à la question ce qui serait bien plus constructif que critiquer les nôtres

Salut Orafrance,
Mon point de vue est bien exprimé dans le lien que tu a posté. Sinon c'est un sujet assez vaste.

Que faire quand votre requête est trop lente ?

D'abord, il faut vous poser la question pourquoi elle est lente. Quelle est la vraie cause du problème. Si le pourquoi est inconnu, suggérer de réécrire la requête, ou d'utiliser un hint, ou de la paralléliser etc. n'est pas très productif. Parfois vous pouvez avoir de la chance. Mais même dans ce cas là, vous devez comprendre que si le problème semble « résolu » vous ne savez pas pourquoi et rien ne garantit que le problème ne réapparaisse pas le lendemain. En conséquence, la première étape est toujours de rechercher la racine du problème.

Je ne vois pas pour quoi " vous critiquer" ça sera "non constructif". Il me semble que j'ai argumenté mes remarques, n'est pas vrai ?