Impacts des index (0, 1, 2 et 5) sur le temps des INSERTs - Impacts of indexes (0, 1, 2 and 5) on INSERT time

30 janvier 2022

Impacts des index (0, 1, 2 et 5) sur le temps des INSERTs - Impacts of indexes (0, 1, 2 and 5) on INSERT time

Introduction
Tout DBA sait que plus on ajoute des index à une table, plus cela ralentit les INSERTs. Mais est-ce que ce ralentissement croit proportionnellement avec le nombre d'index ou non? En clair, si un index ajoute +30% de temps de traitement sur un INSERT, est-ce que 5 index vont ajouter +150% (5*30%) ou plus ou moins? C'est l'objet de cet article.

Points d'attention
N/A.

Base de tests
Une base Oracle 19c multi-tenants.

Exemples
============================================================================================
Base de test
============================================================================================
Créer une table sans index (ni PK, ni colonne UNIQUE) en se basant sur DBA_OBJECTS. Je fais un CTAS (Create Tables As Select) avec la condition WHERE 9=55 pour créer la table mais sans les données; je pourrais mettre WHERE 1=2 comme on le voit sur le Net mais j'avais envie de changer :-)
     SQL> CREATE TABLE zztest AS SELECT * FROM dba_objects WHERE 9=55;
     Table created.
     SQL> select count(*) from zztest;
     COUNT(*)
     ----------
        0

Ajout d'une colonne basée sur une séquence.
     SQL> ALTER TABLE zztest ADD id NUMBER GENERATED ALWAYS AS IDENTITY;
     Table altered.

DBA_OBJECTS fait 80 000 lignes.
     SQL> select min(object_id), max(object_id) from dba_objects;
     MIN(OBJECT_ID) MAX(OBJECT_ID)
     -------------- --------------
              2       80326

Le test sera d'insérer deux millions de lignes dans la table de test : pour cela, on fait un produit cartésien (ou produit en croix) avec deux instances de dba_objects, en ne gardant qu'une partie des vues de SYS.
     SQL> SELECT count(*) FROM dba_objects d1, dba_objects d2 WHERE d1.owner = 'SYS' and d2.owner = 'SYS' AND d1.object_type = 'VIEW' AND d2.object_type = 'VIEW' and d1.OBJECT_ID< 4600 and d2.OBJECT_ID< 4600;
     COUNT(*)
     ----------
        1990921

============================================================================================
INSERTs avec 0 index
============================================================================================
Tracer le temps des requêtes.
     SQL> set timing on

Pour les INSERTs avec 0 et 5 index, je doublonne ceux-ci deux jours de suite, soit dix tests en tout pour avoir plus de données.

INSERT 01
     SQL> INSERT INTO zztest (OWNER, OBJECT_NAME, SUBOBJECT_NAME, OBJECT_ID, DATA_OBJECT_ID, OBJECT_TYPE, CREATED, LAST_DDL_TIME, TIMESTAMP, STATUS, TEMPORARY, GENERATED, SECONDARY, NAMESPACE, EDITION_NAME, SHARING, EDITIONABLE, ORACLE_MAINTAINED, APPLICATION, DEFAULT_COLLATION, DUPLICATED, SHARDED, CREATED_APPID, CREATED_VSNID, MODIFIED_APPID, MODIFIED_VSNID) SELECT d1.* FROM dba_objects d1, dba_objects d2 WHERE d1.owner = 'SYS' and d2.owner = 'SYS' AND d1.object_type = 'VIEW' AND d2.object_type = 'VIEW' and d1.OBJECT_ID< 4600 and d2.OBJECT_ID< 4600;
     1990921 rows created.
     Jour 1 Elapsed: 00:00:31.09
     Jour 2 Elapsed: 00:00:21.05
     SQL> commit;
     Commit complete.

INSERT 02
Dans la suite de cet article, pour des soucis de lisibilité, je remplace le texte de l'INSERT ci-dessus par "INSERT INTO ***".
     SQL> INSERT INTO ***
     1990921 rows created.
     Jour 1 Elapsed: 00:00:24.58
     Jour 2 Elapsed: 00:00:29.07
     SQL> commit;
     Commit complete.

INSERT 03
     SQL> INSERT INTO ***
     1990921 rows created.
     Jour 1 Elapsed: 00:00:27.96
     Jour 2 Elapsed: 00:00:28.50
     SQL> commit;
     Commit complete.

INSERT 04
     SQL> INSERT INTO ***
     1990921 rows created.
     Jour 1 Elapsed: 00:00:43.56
     Jour 2 Elapsed: 00:00:40.78
     SQL> commit;
     Commit complete.

INSERT 05
     SQL> INSERT INTO ***
     1990921 rows created.
     Jour 1 Elapsed: 00:00:32.66
     Jour 2 Elapsed: 00:00:32.75
     SQL> commit;
     Commit complete.

============================================================================================
INSERTs avec 1 index
============================================================================================
Je recrée la table pour être au plus près du premier test.
     SQL> drop table zztest;
     Table dropped.

Recréer la table mais cette fois avec une contrainte d'intégrité PRIMARY KEY.
     SQL> CREATE TABLE zztest AS SELECT * FROM dba_objects WHERE 9=55;
     Table created.

     SQL> ALTER TABLE zztest ADD id NUMBER GENERATED ALWAYS AS IDENTITY;
     Table altered.
     SQL> ALTER TABLE zztest ADD CONSTRAINT zztest_pk_id primary key (id);
     Table altered.

Vérification qu'un index a été créé.
     SQL> SELECT index_name FROM dba_indexes WHERE owner = 'HR' AND table_name = 'ZZTEST' order by 1;
     INDEX_NAME
     --------------------
     ZZTEST_PK_ID

Insertion du même nombre d'enregistrement que lors des tests précédents.
INSERT 01
SQL> INSERT INTO ***
     1990921 rows created.
     Elapsed: 00:00:35.10
     SQL> commit;
     Commit complete.

INSERT 02
SQL> INSERT INTO ***
     1990921 rows created.
     Elapsed: 00:00:33.52
     SQL> commit;
     Commit complete.

INSERT 03
SQL> INSERT INTO ***
     1990921 rows created.
     Elapsed: 00:00:35.77
     SQL> commit;
     Commit complete.

INSERT 04
SQL> INSERT INTO ***
     1990921 rows created.
     Elapsed: 00:00:39.33
     SQL> commit;
     Commit complete.

INSERT 05
SQL> INSERT INTO ***
     1990921 rows created.
     Elapsed: 00:00:45.62
     SQL> commit;
     Commit complete.

============================================================================================
INSERTs avec 2 index
============================================================================================
Cette fois la table a deux index : un index non unique sur la colonne owner en plus d'une PRIMARY KEY.
     SQL> drop table zztest;
     Table dropped.

     SQL> CREATE TABLE zztest AS SELECT * FROM dba_objects WHERE 9=55;
     Table created.

     SQL> ALTER TABLE zztest ADD id NUMBER GENERATED ALWAYS AS IDENTITY;
     Table altered.
     SQL> ALTER TABLE zztest ADD CONSTRAINT zztest_pk_id primary key (id);
     Table altered.

     SQL> CREATE INDEX idx_zztest_owner ON zztest(owner);
     Index created.

Vérification que les deux index ont été créés.
     SQL> SELECT index_name FROM dba_indexes WHERE owner = 'HR' AND table_name = 'ZZTEST' order by 1;
     INDEX_NAME
     ---------------------
     IDX_ZZTEST_OWNER
     ZZTEST_PK_ID

Insertion du même nombre d'enregistrement.
INSERT 01
     SQL> INSERT INTO ***
     1990921 rows created.
     Elapsed: 00:00:58.97
     SQL> commit;
     Commit complete.

INSERT 02
     SQL> INSERT INTO ***
     1990921 rows created.
     Elapsed: 00:01:15.45
     SQL> commit;
     Commit complete.

INSERT 03
     SQL> INSERT INTO ***
     1990921 rows created.
     Elapsed: 00:01:12.42
     SQL> commit;
     Commit complete.

INSERT 04
     SQL> INSERT INTO ***
     1990921 rows created.
     Elapsed: 00:01:21.25
     SQL> commit;
     Commit complete.

INSERT 05
     SQL> INSERT INTO ***
     1990921 rows created.
     Elapsed: 00:01:25.87
     SQL> commit;
     Commit complete.

============================================================================================
INSERTs avec 5 index
============================================================================================
La table a maintenant 5 index : quatre index en plus de la PRIMARY KEY.
     SQL> drop table zztest;
     table dropped.

     SQL> CREATE TABLE zztest AS SELECT * FROM dba_objects WHERE 9=55;
     Table created.

     SQL> ALTER TABLE zztest ADD id NUMBER GENERATED ALWAYS AS IDENTITY;
     Table altered.

     SQL> ALTER TABLE zztest ADD CONSTRAINT zztest_pk_id primary key (id);
     Table altered.
     SQL> CREATE INDEX idx_zztest_owner ON zztest(owner);
     Index created.
     SQL> CREATE INDEX idx_zztest_object_name ON zztest(object_name);
     Index created.
     SQL> CREATE INDEX idx_zztest_object_type ON zztest(object_type);
     Index created.
     SQL> CREATE INDEX idx_zztest_object_id ON zztest(object_id);
     Index created.

Vérification que les cinq index ont été créés.
     SQL> SELECT index_name FROM dba_indexes WHERE owner = 'HR' AND table_name = 'ZZTEST' order by 1;
     INDEX_NAME
     --------------------------------------------------------------------------------
     IDX_ZZTEST_OBJECT_ID
     IDX_ZZTEST_OBJECT_NAME
     IDX_ZZTEST_OBJECT_TYPE
     IDX_ZZTEST_OWNER
     ZZTEST_PK_ID

Comme pour le test avec la table ayant zéro index, je fais mes cinq INSERTs deux fois sur deux jours, soit dix INSERTs.
INSERT 01
     SQL> INSERT INTO ***
     1990921 rows created.
     Jour 1 Elapsed: 00:02:22.04
     Jour 2 Elapsed: 00:03:28.80
     SQL> commit;
     Commit complete.

INSERT 02
     SQL> INSERT INTO ***
     1990921 rows created.
     Jour 1 Elapsed: 00:03:41.35
     Jour 2 Elapsed: 00:04:02.07
     SQL> commit;
     Commit complete.

INSERT 03
     SQL> INSERT INTO ***
     1990921 rows created.
     Jour 1 Elapsed: 00:03:58.48
     Jour 2 Elapsed: 00:04:18.63
     SQL> commit;
     Commit complete.

INSERT 04
     SQL> INSERT INTO ***
     1990921 rows created.
     Jour 1 Elapsed: 00:04:55.55
     Jour 2 Elapsed: 00:04:15.88
     SQL> commit;
     Commit complete.

INSERT 05
     SQL> INSERT INTO ***
     1990921 rows created.
     Jour 1 Elapsed: 00:05:52.87
     Jour 2 Elapsed: 00:05:25.73
     SQL> commit;
     Commit complete.

Calcul des stats du schéma HR.
     SQL> EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS(ownname => 'HR');
     PL/SQL procedure successfully completed.
     Elapsed: 00:04:46.00

Dans DBA_SEGMENTS se trouvent les infos sur la table et ses index. On voit que la taille des index et le nombre d'extents pour ceux-ci est différent, certainement lié à la taille de la colonne indexée.
     SQL> select SEGMENT_NAME, SEGMENT_TYPE, sum(bytes)/(1024*1024) AS "TAILLE Mo", sum(blocks), sum (extents) from dba_segments where owner = 'HR' and segment_name like '%ZZTEST%' group by segment_name, segment_type order by SEGMENT_TYPE, SEGMENT_NAME;
     SEGMENT_NAME               SEGMENT_TYPE TAILLE Mo SUM(BLOCKS) SUM(EXTENTS)
     ------------------------------ ------------------ ---------- -----------
     IDX_ZZTEST_OBJECT_ID       INDEX           226       28928      107
     IDX_ZZTEST_OBJECT_NAME   INDEX           455       58240      140
     IDX_ZZTEST_OBJECT_TYPE   INDEX           284       36352      114
     IDX_ZZTEST_OWNER           INDEX           256       32768      109
     ZZTEST_PK_ID               INDEX           162       20736       93
     ZZTEST                     TABLE           1579      202112      217
     6 rows selected.

============================================================================================
Tableau synthétique des temps de réponse
============================================================================================
0 INDEX
Jour 1
00:00:31.09
00:00:24.58
00:00:27.96
00:00:43.56
00:00:32.66
Moyenne: 32 secondes.

Jour 2
00:00:21.05
00:00:29.07
00:00:28.50
00:00:40.78
00:00:32.75
Moyenne: 30 secondes
Temps moyen total sur deux jours : 31 secondes.

1 INDEX
00:00:35.10
00:00:33.52
00:00:35.77
00:00:39.33
00:00:45.62
Moyenne: 38 secondes
Ecart avec 0 index: 7 secondes soit 22,5% de plus de temps par index.

2 INDEX
00:00:58.97
00:01:15.45
00:01:12.42
00:01:21.25
00:01:25.87
Moyenne: 75 secondes
Ecart avec 0 index: 44 secondes soit 141% de plus de temps pour deux index donc 70,5% par index.

5 INDEX
Jour 1
00:02:22.04
00:03:41.35
00:03:58.48
00:04:55.55
00:05:52.87
Moyenne: 4 minutes 10 secondes soit 250 secondes

Jour 2
00:03:28.80
00:04:02.07
00:04:18.63
00:04:15.88
00:05:25.73
Moyenne: 4 minutes 18 secondes soit 258 secondes
Temps moyen total sur deux jours : 254 secondes
Ecart avec 0 index: 223 secondes soit 719% de plus de temps pour cinq index soit 144% par index.

Conclusion
Nous avons vu lors de ce test que plus on augmente le nombre d'index dans une table, plus les INSERTs sont lents. Mais, contrairement à ce que j'attendais, la progression n'est pas proportionnelle mais croit rapidement avec le nombre d'index, du moins pour mon test.

Dans DBA_SEGMENTS, le nombre d'extents varie de 93 à 140 selon la colonne indexée, ce qui pose problème pour faire une comparaison car deux paramètres varient : le nombre d'index et la taille des colonnes indexées. Ceci pourrait expliquer ce délai plus important qu'attendu : pour un seul index, j'ai indexé la colonne ID qui occupe 93 extents et, pour les cinq index, j'ai ajouté notamment la colonne object_name qui occupe 140 extents, ce qui représente 50% d'extents en plus. Autre point, l'index sur la pk occupe 162Mo, celui sur object_name 455Mo; forcément, cela prend plus de temps à créer lors des INSERTs.

On pourra m'objecter que pour que le test soit pertinent, il aurait fallu avoir 5 index sur des colonnes de même type et de même taille que le premier index, celui sur la colonne id. J'objecterai de mon côté que, dans la vraie vie, quand on indexe une table, il est très rare que toutes les colonnes indexées soient identiques. Par exemple, pour la table des clients, on peut indexer l'id (number 6 par exemple), l'adresse mail (varchar2 50), le nom (varchar2 30), son statut vip (char 1)... donc mon test est plus pertinent que certains ne le pensent :-)

La conclusion de tout ceci est qu'il n'est pas possible de prévoir le temps d'un INSERT si on augmente le nombre d'index de la table. On sait que ce temps de traitement va croitre mais celui-ci dépend plus de la taille des colonnes indexées que de leur nombre.