Discussion :

[jeremzzz]

Bonjour à tous.

Actuellement je travaille sur plusieurs base de données différentes (allant de 6i à 10g) et je me pose la question suivante : Un index est-il vraiment utile sur certaines de mes tables, qui ont des problèmes de performances.

Je vous explique :

Mon entreprise utilise pas mal de tables temporaires pour un peu tout, notamment l'édition des reports.

Ayant parfois plusieurs utilisateurs, nous utilisons un numéro d'identifiant unique par utilisateur qui n'est pas clé primaire sur la table puisque, dans le cas d'une commande par exemple, un utilisateur peut avoir x lignes de commande donc x fois ce numéro.

Ces tables sont donc soumises à de forts changements très fréquents, passant parfois de plusieurs centaines de milliers à 0 ligne en quelques minutes à longueur de journée.

Donc faut-il vraiment qu'il y ait un index sur nos tables temporaires, où cela ralentit-il au contraire les opérations sur les tables ?

Je précise aussi que nous avons parfois plusieurs index, mais ce numéro unique par utilisateur est toujours présent dans chaque index.

Merci d'avance pour votre aide

[ldiaz]

Bonjour,

ton index sera util si la selectivité est bonne sur ta table. On entend par selectivité le fait qu'il y est beaucoup d'ID differents. Exemple.
Si dans une table tu fais un index sur "genre" ici homme ou femme. Ta selectivité risque d'etre naze vu que sur 100 000 lignes tu peux avoir je sais pas: 40 000 hommes et 60 000 femmes.
Donc si tu filtre ta requete par

select blabla, blibli from table
where genre ='homme';

Tu risques d'avoir un full scan et donc une query lente.
Tel que tu explique, cet ID est unique, donc la selectivité sera bonne, donc pas de full scan, sauf si ta table au moment de la requete n'a que tres peu de ligne. Dans ce cas l'optimizeur d'oracle risque de preferer le full scan.

Donc je ferais un index sur la table.

D'autre part pense a executer des stats regulieres d'autant plus si la quantite de ligne change souvent.

Aussi tu peux sortir les snap de toute une journée et regarder le top 5 events avec index et sans index. La probablement tu verras une diference.

J'espere que cela pourra t'aider.

Ciao

[jeremzzz]

Vu le client dont il s'agit, j'ai très rarement peu de lignes, ça tourne souvent à plus de 5000 et ça monte facilement à 100 000 voire même 400 000 parfois (s'il n'y a qu'un seul utilisateur qui utilise le programme)

Merci pour les informations. Je laisse donc mes index.

Pour le moment, j'ai mis en place un script qui recréé la table tous les week end. C'est pas le mieux, mais au moins je n'ai plus mes problèmes de performances.

[ldiaz]

Tu peux aussi faire un simple test

Tu fais une requete avec une conditions where qui utilise un champ indexé (ou pas).
Avant la requete tu fais un set autotrace on
comme ça tu verras les consistent gets et phsysical reades.
Tu test avec et sans index et tu verras les differences.
L'objectif etant d'avoir un minimum de physical reads vu qu'il s'agit des lecture a disque, les plus couteuse, les consistent gets etant les acces aux block de données en memoire (plus rapide).

Bonne chasse

[pachot]

Bonjour,

Ayant parfois plusieurs utilisateurs, nous utilisons un numéro d'identifiant unique par utilisateur qui n'est pas clé primaire sur la table puisque, dans le cas d'une commande par exemple, un utilisateur peut avoir x lignes de commande donc x fois ce numéro.

Il pourrait par contre faire partie de la clé peut-être, même s'il nêst pas identifiant à lui tout seul.

(allant de 6i à 10g)

6i ça n'existe pas.
Est-ce que tu utilise des global Temporary Tables ? Ce serait bien si chaque session ne doit voir que ses données.

Cordialement,
Franck.

[carreau]

bonjour,

De manière similaire et en complément de la discussion,

j'interroge des tables non indexées avec une requete très peu performante
lorsque j'ajoute des index, je divise grosso modo le cout de ma requête par 10

Mais je me pose la question de savoir si l'ajout des index va pénaliser l'insertion

Je ne sais pas comment mesurer la perte de temps et le cout des index à l'insertion
Pourriez vous m'indiquer une méthode ? des outils ?

Merci d'avance
Jerome

[FSiebert]

Bonjour,

Un index pénalise l'insertion d'environ 25%, 33% mais ce n'est pas pour ça qu'un index n'est pas une bonne idée

À lire (par exemple) cet article : http://blog.developpez.com/sqlpro/p7..._d_index_ni_de

[ldiaz]

Salut

et bien oui si la table possede un ou plusieurs index, l'insertion sera plus couteuse. Ce que tu peux faire c'est creer ta table avec l'option NOLOGING, mettre la base en NOARCHIVE, pour le reste tu dois pouvoir decider si l'impacte sur l'insert est plus penalisant que ne pas avoir les index au moment de faire les select. (ufff ma phrase est tordue non?)

[mnitu]

Quels sont vos problèmes de performances et comment se manifestent-ils ? Avez-vous fait une trace SQL étendue ou autre analyse des traitements concernées ? C’est plutôt de cette manière que vous pouvez envisager une bonne solution et moins sur le bout du nez et divers conseils trouvés sur internet.

Le fait d’utiliser des versions différents allant de 8i à 10g c’est embêtant parce qu’il y a des différences énormes en termes d’optimisation entre ces deux versions.

La forte variation en nombre des enregistrements des tables appelle pour l'échantillonnage dynamique (dynamic sampling) mais il faut bien voir de que on parle. Aujourd’hui lire 50 000 enregistrements en balayage complet de la table (full table scan) c’est rien.

Vos problèmes des performances peuvent être tout à fait liés à la manière dans laquelle vous utilisez la base, c’est fous ce que les gens peuvent faire parfois. Du ce point de vue la seule démarche correct est d’analyser les traitements qui posent des problèmes de performance, de comprendre l’origine de ces problèmes ce qui permettra d’identifier et évaluer les solutions à mettre en ouvre !

[StringBuilder]

Je me pose une question à propos du "coût" d'un index en termes d'insertion.

Si j'ai une contrainte d'unicité sur une colonne ou un tuple (typiquement, la clé primaire), lors d'un insert, le SGBD n'aura-t-il pas un avantage à avoir un index sur le tuple, afin d'identifier immédiatement la présence ou non de doublon ?

En effet, dans une table sans index ni contrainte, effectivement, l'insert se contente d'ajouter comme un bourrin les données dans la table sans contrôle, et sera donc plus rapide sans index.

Mais à partir du moment où une contrainte apparaît, je me demande si la présence d'un index ne permettra pas d'accélérer les insertion : entre le temps d'ajouter une entrée dans un index, et le temps à faire un full-scan sur une table entière pour vérifier l'unicité, je pense que l'avantage est à l'utilisation de l'index.

[Waldar]

La création d'une contrainte d'unicité ou de clef primaire implique la création d'un index s'il n'y en a pas déjà un.

[Mohamed.Houri]

Je me pose une question à propos du "coût" d'un index en termes d'insertion.

Dans une application on a tendance souvent à faire plus de selects que d’insert/udpate/delete. C’est pour cette raison qu’un index précis, bien défini peut, malgré la pénalité supplémentaire de maintenance qu’il introduit, s’avérer très utile et même dès fois indispensable.

Mais, il y a certain indexes qui lors de leur création sont propres mais qui explosent au fur et à mesure de leur cycle de vie car ils subissent des updates (delete/insert) et des deletes catastrophiques si bien que leur maintenance (lors d’opérations sur les tables à qui ils appartiennent) devient trop gourmande en temps d’exécution.

Pire encore, il arrive parfois, que les indexes qui ont été crées pour avoir des selects très performants deviennent eux-mêmes indésirables par le CBO à cause de leur clustering factor qui, pour une raison ou pour une autre, augmente et devient plus proche du nombre d’enregistrements de la table que du nombre de blocks de celle-ci. Comme le clustering factor fait partie de la formule qu’utilise le CBO pour évaluer le coût de l’utilisation de l’index:

Code :

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

1
2
3
4
5
 
cost =
blevel +
ceiling(leaf_blocks * effective index selectivity) +
ceiling(clustering_factor * effective table selectivity)

S’il augmente, le coût de son utilisation dans les yeux du CBO augmente aussi devenant donc moins désirable et non utilisé lors des selects. Quel comble ? L’index pénalise l’insert/udpate/delete et n'est pas utilisé lors de select !!!

Il suffit par exemple de faire un shrink d’une table et vous allez augmenter considérablement la possibilité de détériorer la valeur du clustering factor de l’index qui lui est attaché. En effet un shrink d’une table, dans un souci de réorganisation des données dans cette table, transporte des données d’une extrémité de la table vers une autre extrémité (de la fin au début) rompant ainsi tout ordre des données dans la table par rapport à son index.

Mais, après avoir mentionné tout cela, je finirai par dire, qu’il ne faut pas hésiter à créer un index lorsque vos requêtes l’exigent. Pensez surtout à comprendre le cycle de vie d’un index par rapport aux événements que va subir sa table et tenez bien compte de cela.

[mnitu]

Je me pose une question à propos du "coût" d'un index en termes d'insertion.
...

Une table Oracle « normale » sans clé primaire, contrainte d’unicité ou index unique ne fait le moindre contrôle d’unicité et accepte les doublons avec gaieté ignorant les bases théorétiques du modèle relationnel. Donc les inserts dans ce cas sont le plus rapides possible.

[StringBuilder]

Au détail près qu'on peut mettre une contrainte d'unicité dans un trigger par exemple, et dans ce cas, pas d'index créé en automatique.

Par exemple, pour de telles tables, on peut imaginer "si type = cde alors numcde et numlig uniques, sinon, numcde unique".

Et dans ce cas, on va faire des select sur numcde et numlig, et la présence d'un index réduira de façon drastique le temps d'exécution du contrôle.

[mnitu]

Au détail prés que ça ne fonctionne pas et que vous n’avez jamais testé cette solution.

[Waldar]

Je n'ai pas bien compris ce que vous racontez StringBuilder, je veux bien un exemple !

[pachot]

Bonjour,

Je ne sais pas comment mesurer la perte de temps et le cout des index à l'insertion
Pourriez vous m'indiquer une méthode ? des outils ?

Ca c'est une très bonne idée de le mesurer, parce que se baser sur des pourcentages données au pif ne signifie rien.

Sur la théorie:
- l'insert dans la table va modifier en principe un seul block
- pour chaque index il y a au moins un block feuille modifié, parfois plus.
- en plus pour chaque index il a fallu le parcourir, donc lire 2 ou 3 blocs suivant sa hauteur
- chaque block modifié va générer de l'undo (faible pour des inserts) et du redo

Donc le premier index va beaucoup diminuer les perfs par rapport à une table sans index. Mais de toute façon il y en a au moins déjà un pour la PK.
C'est sûr que s'il y a déjà 10 index, le 11ème ne va pas changer grand chose.

Et pour le test:

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
 
SQL> create table test as select rownum a,rownum b,rownum c,rownum d from dual connect by level <1000;
 
Table created.
 
SQL> insert /*+ append */ into test select t1.a,t1.b,t1.c,t1.d from test t1, test t2;
 
998001 rows created.
 
SQL> commit;
 
Commit complete.
 
SQL> select count(*) from test;
 
  COUNT(*)
----------
    999000
 
SQL>

Là on a une table avec presque un million de lignes.

Je vais créer à chaque fois un index de plus, et utiliser autotrace pour voir combien de blocs en plsu l'insert doit toucher.

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115

SQL> set autotrace on stat
SQL> insert into test values (0,0,0,0);

1 row created.


Statistics
----------------------------------------------------------
          0  recursive calls
          1  db block gets
          1  consistent gets
          0  physical reads
          0  redo size
        722  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index test_a on test(a);

Index created.

SQL> insert into test values (0,0,0,0);

1 row created.


Statistics
----------------------------------------------------------
          1  recursive calls
          6  db block gets
          1  consistent gets
          2  physical reads
          0  redo size
        722  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index test_b on test(b);

Index created.

SQL> insert into test values (0,0,0,0);

1 row created.


Statistics
----------------------------------------------------------
          1  recursive calls
          9  db block gets
          1  consistent gets
          2  physical reads
          0  redo size
        722  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index test_c on test(c);

Index created.

SQL> insert into test values (0,0,0,0);

1 row created.


Statistics
----------------------------------------------------------
          1  recursive calls
         12  db block gets
          1  consistent gets
          2  physical reads
          0  redo size
        722  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> create index test_d on test(d);

Index created.

SQL> insert into test values (0,0,0,0);

1 row created.


Statistics
----------------------------------------------------------
          1  recursive calls
         15  db block gets
          1  consistent gets
          2  physical reads
          0  redo size
        722  bytes sent via SQL*Net to client
        487  bytes received via SQL*Net from client
          4  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> set autotrace off

Je regarde surtout les 'db block gets', ceux qui sont nécessaire à la modification de la table ou des index.
pas d'index -> 1 block touché
1 index -> 6 blocks
2 index -> 9 blocks
3 index -> 12 blocks
4 index -> 15 blocks

A chaque index, c'est 3 blocks de plus, donc probablement 2 branches et une feuille. On vérifie la hauteur des index:

Code :

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

1
2
3
4
5
6
7
8
SQL> select index_name,blevel from user_indexes where index_name like 'TEST__';
 
INDEX_NAME                         BLEVEL
------------------------------ ----------
TEST_A                                  2
TEST_B                                  2
TEST_C                                  2
TEST_D                                  2

C'est bien ça...

Donc maintenant vous savez comment tester l'impact sur les performances de l'insert.

Cordialement,
Franck.

[StringBuilder]

Je vois pas en quoi faire un trigger qui effectue un contrôle et provoque une erreur si le contrôle n'est pas vérifié ne pourrait pas marcher ! Vous dites n'importe quoi mnitu !

Ensuite, soit un table "tableA" avec les colonnes "col1", "col2", "col3", "col4" de type int.

Soit les règles suivantes :
- Quand "col1" = 1 alors le tuple (col1, col2, col3) doit être unique
- Quand "col1" = 2 alors aucun contrôle d'unicité est fait
- Quand "col1" prend une autre valeur, alors le tuple (col1, col2) doit être unique

On aura ce genre de situation dans une table de travail pour un rapport statistique par exemple. On recopie dans la table des informations sur des commandes (col1 = 1, col2 = commande.id, col3 = lignecommande.numero). On peut aussi y stocker des appels téléphoniques, à ce moment, col2 = appel.id et il n'y a pas de "lignes" dans un appel). Etc.

Impossible de mettre une simple contrainte check ou de créer un index unique.
Si on veut blinder l'alimentation de la table, il faudra alors faire un trigger qui s'occupera de vérifier les règles, à grand coup de count(*) sur les colonnes col1, col2 et éventuellement col3.
Je vois pas ce qui pourrait poser le moindre problème à Oracle.

En revanche, en l'absence d'index, le trigger va mettre trois plombes à contrôler les règles de gestion.

[Waldar]

C'est votre vocabulaire qui n'était pas précis. J'ai compris et je pense que mnitu me suivra que vous parliez d'une contrainte ALTER TABLE ADD CONSTRAINT... et pas d'une règle de gestion.

Et encore, votre règle de gestion est tout à fait portable dans les règles classique avec une modélisation type héritage,

[skuatamad]

Vous dites n'importe quoi mnitu !

Je ne crois pas...

On recopie dans la table des informations sur des commandes (col1 = 1, col2 = commande.id, col3 = lignecommande.numero). On peut aussi y stocker des appels téléphoniques, à ce moment, col2 = appel.id et il n'y a pas de "lignes" dans un appel). Etc.

Je ne suis pas particulièrement convaincu par le modèle évoqué mais je pense que l'idée était plutôt d'évoquer un contexte avec des contraintes complexes.

Je vois pas ce qui pourrait poser le moindre problème à Oracle.

MUTATING TABLE ! en gros insert into values <=> OK mais insert into select <=> impossible...
Donc tout dépend du contexte, exemple (je n'ai codé que la 1ere contrainte et que en mode insert):

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
SQL> create table t (c1 number, c2 number, c3 number, c4 number)
  2  /
 
Table created.
 
SQL> 
SQL> create or replace trigger trig before insert on t
  2  for each row
  3  begin
  4    if (:new.c1 = 1) then
  5      for c in (select 1
  6                  from t
  7                 where c1 = :new.c1
  8                   and c2 = :new.c2
  9                   and c3 = :new.c3
 10                   and rownum = 1)
 11      loop
 12        raise_application_error(-20001,'tuple c1,c2,c3 already exist');
 13      end loop;
 14    end if;
 15  end;
 16  /
 
Trigger created.
 
SQL> show err
No errors.
SQL> insert into t values (1,1,1,1);
 
1 row created.
 
SQL> insert into t values (1,2,2,2);
 
1 row created.
 
SQL> insert into t values (1,2,2,3);
insert into t values (1,2,2,3)
            *
ERROR at line 1:
ORA-20001: tuple c1,c2,c3 already exist
ORA-06512: at "SKUATAMAD.TRIG", line 10
ORA-04088: error during execution of trigger 'SKUATAMAD.TRIG'

Ca a l'air de marcher mais :

Code :

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

1
2
3
4
5
6
7
SQL> insert into t select 1,2,3,4  from dual;
insert into t select 1,2,3,4  from dual
            *
ERROR at line 1:
ORA-04091: table SKUATAMAD.T is mutating, trigger/function may not see it
ORA-06512: at "SKUATAMAD.TRIG", line 3
ORA-04088: error during execution of trigger 'SKUATAMAD.TRIG'

Impossible de mettre une simple contrainte check ou de créer un index unique.

FBI ! Le MVCC implémenté par Oracle (et bien d'autres) présente de nombreux avantages concernant l'accès aux données (et les locks associés) mais présente des inconvénients sur les contraintes implémentées par trigger.
Par contre Oracle offre également d'autres fonctionnalités :

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
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
SQL> drop table t
  2  /
 
Table dropped.
 
SQL> create table t (c1 number, c2 number, c3 number, c4 number)
  2  /
 
Table created.
 
SQL> 
SQL> create unique index idx1_t on t (
  2    case when c1 = 1 then c1 end,
  3    case when c1 = 1 then c2 end,
  4    case when c1 = 1 then c3 end)
  5  /
 
Index created.
 
SQL> create unique index idx2_t on t (
  2    case when c1 not in (1, 2) then c1 end,
  3    case when c1 not in (1, 2) then c2 end)
  4  /
 
Index created.
 
SQL> insert into t select 1,2,3,4  from dual;
 
1 row created.
 
SQL> insert into t select 1,2,3,4  from dual;
insert into t select 1,2,3,4  from dual
*
ERROR at line 1:
ORA-00001: unique constraint (SKUATAMAD.IDX1_T) violated
 
 
SQL> insert into t select 1,3,3,4  from dual;
 
1 row created.
 
SQL> insert into t select 2,2,2,2  from dual;
 
1 row created.
 
SQL> insert into t select 2,2,2,2  from dual;
 
1 row created.
 
SQL> insert into t select 3,2,2,2  from dual;
 
1 row created.
 
SQL> insert into t select 3,2,2,2  from dual;
insert into t select 3,2,2,2  from dual
*
ERROR at line 1:
ORA-00001: unique constraint (SKUATAMAD.IDX2_T) violated
 
 
SQL> insert into t select 3,3,2,2  from dual;
 
1 row created.
 
SQL> select * from t;
 
        C1         C2         C3         C4
---------- ---------- ---------- ----------
         1          2          3          4
         1          3          3          4
         2          2          2          2
         2          2          2          2
         3          2          2          2
         3          3          2          2
 
6 rows selected.
 
SQL>

Bon inutile de dépenser de l'energie à chercher des cas où les FBI unique ne sont pas suffisant, nous savons tous qu'ils existent, mais en l'occurrence (sauf erreur de ma part sur mes tests plutôt succints) l'exemple choisi n'avait rien d'impossible à être traité en mode déclaratif.