L'utilisation d'un index dégrade les performances des requêtes si elle entraîne plus IO disque par rapport à l'interrogation de la table avec un index.
Cela peut être démontré à l'aide d'un simple tableau :
create table tq84_ix_test (
a number(15) primary key,
b varchar2(20),
c number(1)
);
Le bloc suivant remplit 1 million d'enregistrements dans cette table. Chaque 250ème enregistrement est rempli avec un rare value dans la colonne b tandis que toutes les autres sont remplies de frequent value :
declare
rows_inserted number := 0;
begin
while rows_inserted < 1000000 loop
if mod(rows_inserted, 250) = 0 then
insert into tq84_ix_test values (
-1 * rows_inserted,
'rare value',
1);
rows_inserted := rows_inserted + 1;
else
begin
insert into tq84_ix_test values (
trunc(dbms_random.value(1, 1e15)),
'frequent value',
trunc(dbms_random.value(0,2))
);
rows_inserted := rows_inserted + 1;
exception when dup_val_on_index then
null;
end;
end if;
end loop;
end;
/
Un index est placé sur la colonne
create index tq84_index on tq84_ix_test (b);
La même requête, mais une fois avec index et une fois sans index, diffère en performance. Vérifiez par vous-même :
set timing on
select /*+ no_index(tq84_ix_test) */
sum(c)
from
tq84_ix_test
where
b = 'frequent value';
select /*+ index(tq84_ix_test tq84_index) */
sum(c)
from
tq84_ix_test
where
b = 'frequent value';
Pourquoi ? Dans le cas sans l'index, tous les blocs de la base de données sont lus, dans un ordre séquentiel. En général, cela est coûteux et donc considéré comme mauvais. En situation normale, avec un index, un tel "balayage complet de la table" peut être réduit à la lecture de 2 à 5 blocs de base de données d'index plus la lecture d'un bloc de base de données qui contient l'enregistrement sur lequel l'index pointe. Dans l'exemple qui nous occupe, c'est tout à fait différent : l'index entier est lu et pour (presque) chaque entrée de l'index, un bloc de base de données est lu également. Ainsi, non seulement la table entière est lue, mais aussi l'index. Notez que ce comportement serait différent si c étaient également dans l'index car dans ce cas, Oracle pourrait choisir de récupérer la valeur de c à partir de l'index au lieu de faire le détour par la table.
Donc, pour généraliser la question : si l'index ne sélectionne pas un grand nombre d'enregistrements, il peut être avantageux de ne pas l'utiliser.
