Discussion :

[jeepnc]

Merci mnitu pour toutes ces remarques, toutes constructives

Bref, il y a certaines choses à corriger, des autres à préciser et malheureusement quelques mythes à combattre.
1) Attention: les valeurs NULL ne sont pas indexées


En fait les choses sont un peu plus nuancés et votre exemple mal choisi. Voici une explication.

=> Complètement d'accord. Personnellement j'utilise souvent l'astuce de tom avec le index(colonne,0). Je vais donc corriger mon article car c'est vrai que tel quel ce que je dis est faux.

C’est quoi « nombre conséquent de lignes » 10 000, 1 000 000, 10 000 000 ?]

Sur du 8 millions de ligne j'ai eu ce genre de choses, ou le exists était bcp plus performant. Pour l'instant je n'ai pas sur moi de quoi reproduire le cas mais je rechercherai ça ASAP. Mais la requête était de mémoire bien complexe, basée sur 6 tables avec des clauses sur des champs ou il n'y a pas d'unicité (dates entre autres), je ne me souviens pas de plus ...

VII-B. La fonction qu'on ne voit pas

=> j'ai été un peu vite celà d'applique aux index bitmaps uniquement.

IV-C. Les indexes composés :quelques règles

Là je me réfèrre au support de cours "oracle sql tuning" officiel d'Oracle. Je n'ai pas eu personnellement à constater ce cas, des collègues m'ont dit l'avoir eu, je n'ai jamais été vérifier.

Enfin je reprécise que ce document est fait avec ce que JE sais, que je n'ai pas 10 ans d'experience dans le domaine et que pour le coup je fais ce que je peux.

[mnitu]

...

=> j'ai été un peu vite celà d'applique aux index bitmaps uniquement.
...

Si, si ! Cella s’applique bien aux index B-tree et c'est maintenant que vous allez un peu trop vite.

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
51
52
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0 
Connected as mni
 
SQL> 
SQL> drop table emp_2
  2  /
 
Table dropped
SQL> create table emp_2 as
  2  select to_char(empno) empno, ename, job, mgr, hiredate, sal, comm, deptno
  3  From emp
  4  /
 
Table created
SQL> alter table emp_2
  2  add constraint pk_emp_2 primary key (empno)
  3  /
 
Table altered
SQL> exec dbms_stats.gather_table_stats(USER, 'EMP_2', cascade => True)
 
PL/SQL procedure successfully completed
 
SQL> set autotrace traceonly explain 
 
SQL> Select *
from emp_2
where empno = '7369'
/
  2    3    4
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=3
          8)
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP_2' (Cost=2 Card=1 By
          tes=38)
 
   2    1     INDEX (UNIQUE SCAN) OF 'PK_EMP_2' (UNIQUE)
 
SQL> Select *
from emp_2
where empno = 7369
/
  2    3    4
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=3
          8)
 
   1    0   TABLE ACCESS (FULL) OF 'EMP_2' (Cost=2 Card=1 Bytes=38)

[mnitu]

5) Les indexes à clefs inversées:

Les indexes à clefs inversées: là c'est très simple : "1234" deviendra "4321" ... La raison de cette inversion provient du fait que les indexes peuvent devenir un goulot d'étranglement quand un grand nombre d'insertions est fait ... L'index étant ordonné, quand vous insérez par exemple beaucoup de valeurs venant d'une séquence, c'est le même bloc de l'index qui est alors systématiquement sollicité. En cassant la continuité, on enlève cet inconvénient ... ce type d'index a toutefois un inconvénient, on ne peut y effectuer des "range scan" (parcours d'une partie de l'index) de part sa nature discontinue. Concrètement cet index pourra être utilisé lors d'une clause where monchamp=maValeur mais pas avec une clause where monChamps > maValeur. Seul l'opérateur égalité peut l'utiliser. Cela peut facilement être gênant.

Désolé mais encore une fois comme pour les Nulls il faut être un peu plus rigoureux : on peut observer des range scan pour ce type d’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
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
 
Connected to Oracle9i Enterprise Edition Release 9.2.0.5.0 
Connected as mni
 
SQL> 
SQL> drop table emp_3
  2  /
 
Table dropped
 
SQL> 
SQL> create table emp_3 as
  2  select 'ORACLE' company, empno, ename, job, mgr, hiredate, sal, comm, deptno
  3  From emp
  4  /
 
Table created
SQL> alter table emp_3
  2  add constraint pk_emp_3 primary key (company, empno)
  3  /
 
Table altered
SQL> alter index pk_emp_3 rebuild reverse
  2  /
 
Index altered
SQL> exec dbms_stats.gather_table_stats(USER, 'EMP_3', cascade => True)
 
PL/SQL procedure successfully completed
 
SQL> set autotrace traceonly explain
SQL> Select *
from emp_3
where company = 'ORACLE'
  2    3    4  /
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=14 Bytes=
          616)
 
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'EMP_3' (Cost=2 Card=14 B
          ytes=616)
 
   2    1     INDEX (RANGE SCAN) OF 'PK_EMP_3' (UNIQUE) (Cost=1 Card=1
          4)

[mnitu]

6) VI. Clefs etrangères et indexes:

Dans de la grande majorité des cas, l'indexation des clefs étrangères peut amener un gain non négligeable ! Vous vous rappelez, je disais que les indexes alentissaient les insertions ... et bien pas toujours.
Imaginons un cas tout simple de clef étrangère: le champ CA de la table A référence le champ CB de la table B. Nous demandons ici à Oracle de vérifier que la valeur que l'on mettra dans CA existe bien dans CB ... et bien comment croyez vous qu'il va la chercher cette valeur pour savoir si elle existe ? En accédant à la table ... Et si le champ est indéxé il y a des chances que ça aille plus vite ...
Une des extrémités (le champ reférencé) est souvent une clef primaire mais pas forcément, une contrainte d'unicité suffit, dans ce cas indexer sera judicieux. L'autre extrémité, le référençant est rarement indexé, pensez y, cela accelera les jointures et recherches qui pourraient se faire via ce champ.

Actuellement je trouve ce paragraphe plutôt confus. La norme concernant l’indexation est d’indexer que ce qui est nécessaire parce que cella coût cher ; comme vous le dit d’ailleurs. Donc dans la majorité des cases il ne faut pas indexer les clefs étrangères. Dans aucun cas je ne pense pas que on peut parler "d’effet d’accélérateur des insertions via les contraintes". L’index sur la table maître existe toujours qu’il s’agit de clé primaire ou unique donc à l’insertion dans ce table il y a toujours plus de boulot à faire en présence d’index. Le fait que la recherche de l’existence de la valeur dans la table maître bénéficie de cette index est toute à fait normale : c’est pour accélérer les recherches qu’on index.
L’indexation de la colonne référencé n’est utile que dans des cas particulières et dans ces cases le gain est toujours dans la recherche des valeurs.

[mnitu]

...
...
Sur du 8 millions de ligne j'ai eu ce genre de choses, ou le exists était bcp plus performant. Pour l'instant je n'ai pas sur moi de quoi reproduire le cas mais je rechercherai ça ASAP. Mais la requête était de mémoire bien complexe, basée sur 6 tables avec des clauses sur des champs ou il n'y a pas d'unicité (dates entre autres), je ne me souviens pas de plus ...
...

Mais, oui un requête bien plus complexe avec six tables et non pas deux et très probablement des critères de recherche peu sélective sur la table principale ainsi que sur les autres tables et pourtant l’intersection des tous ces critères ramène un nombre réduit des enregistrements.
Je ne doute pas du fait que votre solution a changé radicalement le temps de réponse de la requête en question mais je doute fortement de la conclusion que vous avez tiré ; c’est typiquement ce type d’expérience qui enfante les divers mythes. Et c’est exactement ce type d’affirmation : « utilisez plutôt la méthode A à la place de la méthode B» non soutenu par un « test case » ou d’une analyse approfondie qui expliquerais dans quel cas précis cella est vrai, qui me gêne le plus dans un tutorial sur l’optimisation.
Voilà un tkprof de ces mêmes types de requêtes mais arranges un peu différemment. Maintenez-vous toujours votre affirmation ?

Vous constaterez lors de tables avec de nombreux enregistrements, que celà est quand même bien plus rapide ...

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
51
52
53
54
 
SELECT BIGDEPT.*
FROM
 BIGDEPT WHERE EXISTS (SELECT NULL FROM BIGEMP WHERE BIGEMP.DEPTNO =
  BIGDEPT.DEPTNO )
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch   300001      9.90      44.01      11489    1209562          4      300000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   300003      9.90      44.02      11489    1209562          4      300000
 
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 226     (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
 300000  NESTED LOOPS
 300000   SORT UNIQUE
1400000    TABLE ACCESS FULL BIGEMP
 300000   TABLE ACCESS BY INDEX ROWID BIGDEPT
 300000    INDEX UNIQUE SCAN SYS_C0021149 (object id 107421)
 
********************************************************************************
 
SELECT DISTINCT BIGDEPT.*
FROM
 BIGDEPT JOIN BIGEMP ON (BIGDEPT.DEPTNO = BIGEMP.DEPTNO)
 
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.07          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch   300001      9.10      29.18      16338      11168          8      300000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   300003      9.10      29.25      16338      11168          8      300000
 
Misses in library cache during parse: 1
Optimizer goal: FIRST_ROWS
Parsing user id: 226     (recursive depth: 1)
 
Rows     Row Source Operation
-------  ---------------------------------------------------
 300000  SORT UNIQUE
1400000   HASH JOIN
 400000    TABLE ACCESS FULL BIGDEPT
1400000    TABLE ACCESS FULL BIGEMP
 
********************************************************************************

[mnitu]

...

Là je me réfèrre au support de cours "oracle sql tuning" officiel d'Oracle. Je n'ai pas eu personnellement à constater ce cas, des collègues m'ont dit l'avoir eu, je n'ai jamais été vérifier.
...

Voilà un lien qui parle de test proposé par Tom Kyte.

[fdenis]

Si je puis me permettre : quelques fautes d'orthographe et quelques imprécisions :

L'élément à mon avis le plus important est le nombre d'I/O Disque que nécéssite la requête. Lire sur un disque c'est lent. Pire encore, devoir effectuer un tri de données sur le disque s'avère souvent extrêmement coûteux...

Ceci n'est pas exact car cela sous-entend que lire en mémoire est bien plus rapide que sur disque et qu'il faut donc se concentrer sur la réduction des PIO (Physical I/O - lectures sur disque) et non pas sur les LIO (Logical I/O - lectures en mémoire) pour tuner sa DB. Or ceci est faux, c'est même plutôt l'inverse qu'il faut viser. En effet, les zones mémoires d'Oracle sont protégées par des latchs (locks sériazables) ce qui n'est pas le cas des PIO.
Un excellent PDF qui explique tout ça dans le détail sur www.hotsos.fr (LIO instead of PIO)

Les statistiques permettent à l'optimiseur d'estimer le coût d'accès aux données. Normalement, elles sont en général calculées de manière automatique / périodique (Oracle 10G) ou manuelle (10G et avant ...).

En 10, Oracle fourni un job par défaut qui le fait mais tout bon DBA a fait le sien pour les versions antérieures de manière à ce que ce soit automatique

Pour en savoir plus sur le calcul des stats sur vos bases, demandez à votre DBA. Calculer des stats se fait en lançant une requête "ANALYSE TABLE matable COMPUTE STATISTICS;" par exemple ou mieux en utilisant le package dbms_stats comme ici:

=> ANALYZE

Parcours complet de table ou Full Table Scan: Là c'est simple on parcours toute la table. En l'absence d'index approprié à la recherche c'est ce qu'Oracle fait. Ce mode est couteux sur de grosses tables car il génére beaucoup d'accès disque, étant donné que toute la table est lue.

Ca dépend du % de lignes qu'Oracle doit ramener de la table aussi. De mémoire si on ramène 20+% des lignes d'une table, Oracle fait un FTS (Full Table Scan).

Vous pouvez demander à Oracle de conserver certaines tables en mémoire cache. Ceci est particulièrement intéressant pour des petites tables de paramètres que vous accédez souvent, lors de jointures par exemple. Attention toutefois à ne pas en abuser, conserver de grosses tables en mémoire aurait des gros inconvénients au niveau occupation mémoire et performances ...

Précisions ici : Oracle utilise (comme beaucoup) un mécanisme de LRU (Least Recent Used) pour gérer ses caches (les plus anciennement utilisés sont supprimés du cache lorsque des nouveaux ont besoin d'y être mis). Donc dans le cas de table de paramètres, si elles sont effectivement souvent utilisées, elles seront forcément en cache.
L'intérêt de mettre ces tables dans le "KEEP_CACHE" n'est donc pas de "forcer" Oracle à les garder en mémoire mais d'utiliser un autre latch que celui du Buffer Cache pour gérer les accès à ces tables très souvent utilisées.

[jkofr]

Hello,

Voici une petite étude que j'avais faite sur les différents Btree.

Ça peut être intéressant.

A noter bien sur que Oracle n'utilise pas l'algo de suppression

Jko