Question générale sur utilisation des index

Bonsoir,
si je possède une table définie ainsi :

Code:

---

1 2	MaTable(GroupeId number(3), SousGroupeId number(3),ItemId number(1), LibelleItem Varchar2(100))

---

pour laquelle le champ :
- ItemId est unique par ligne
- SousGroupeId se retrouve n fois par GroupeId et est une FK d'une table de sous-groupements
- GroupeId étant une FK d'une table labellisant "mes groupements" et "regroupant mes sous-groupements"...
Si mes scripts interrogent presque toujours cette table des deux manières suivantes :

Code:

---

1 2	select ItemId,LibelleItem from MaTable where GroupeId =1 and SousGroupeId =1;

---

ou :

Code:

---

select ItemId,LibelleItem from MaTable where SousGroupeId =2;

---

Je pense que je dois créer un index non unique sur le couple (GroupeId, SousGroupeId ) pour la requête 1. Mais pour la requête 2, dois-je aussi créer un second index non unique sur le champ SousGroupeId ? Ou le premier index créé sera quand même utilisé ? Peut-être que le second index sur SousGroupeId n'est-il utile que si je possède un grand nombre de sous-groupes ?

Lek.

Bonjour,

Pourquoi ne pas tester ?

Sur le principe, la deuxième requête pourra utiliser l'index composé en faisant un INDEX SKIP SCAN (si l'optimiseur estime cette méthode plus performante).

Une autre solution serait plutôt de poser un index sur (SousGroupeId, GroupeId).

Après, cela dépend de la sélectivité des deux colonnes GroupeId et SousGroupeId.
Donnez plus de précision sur les volumétries, le nombre de GroupeId et de SousGroupeId distincts, ainsi que la répartition.
Mais le mieux reste encore de faire des tests :)

En règle générale créez l’index sur GroupeId, SousGroupeId. Cella permet à l’index d’être utilisé par les deux requêtes si l’optimiseur estime que cella est optimale. Ca permettra aussi de comprimer l’index ce qui pourrait dans certaines cases rendre l’accès via l’index plus intéressant. Dans votre cas cette ordre permettra peut être aussi accès via un index skip scan pour les requêtes interrogeant qu’avec la colonne SousGroupeId. Mais garder à l’esprit que chaque règle existe pour être ignorée dans les cases où la réalité l’invalide.

Code:

---

1 2	select ItemId,LibelleItem from MaTable where GroupeId =1 and SousGroupeId =1;

---

ou :

Code:

---

1 2	select ItemId,LibelleItem from MaTable where SousGroupeId =2;

---

La première colonne d'un index est très importante. C'est pour cela que dans votre cas un seul index, à savoir ind1(SousGroupeId,GroupeId), pourrait couvrir les deux requêtes que vous avez exposées.

Oubliez l'index skip scan, il ne sera considéré que si la première colonne est très répétitive (peu de valeur distinctes); et lorsqu'il est considéré, son coût sera très élevé par rapport un index range scan. En effet, contrairement à un index range scan qui descend une seule fois l'arborescence de l'index (root-branch-leaf), trouve un block de début (starting block) et un block de fin (stop block), et commence son scan sur le même niveau (leaf), l'index skip scan fonctionne différemment: il fait des allers-retours entre les branches de l'index. Il nécessite aussi un "pinning" (réservation de place (chunk) dans la mémoire) spécial. Chaque block dans le chemin de l'index est réservé (pinned).

Citation:

---

GroupeId étant une FK d'une table labellisant

---

De toute façon, puisque votre groupeId est une FK il doit être indexé (sauf si vous ne faites pas d’opération de delete/update/merge sur la table mère labelisant). Et l’index proposé (SousGroupeId, GroupeId ) pour couvrir votre requête ne couvrira pas la menace de deadlock introduite par des FK non indexées. Alors que le même index inversé (GroupeId, SousGroupeId) couvrira bien la menace de deadlock mais ne couvrira qu’une seule de vos deux requêtes. Dans ce cas un index ind2(GroupeId) deviendra obligatoire.

Ceci sans parler du clustering factor qui peut nous amener à envisager d’autres solutions.

Bonsoir et merci pour vos réponses.
Mes questions portaient sur des cas pour l'instant hypothétiques car je suis en phase de conception mais j'avais déjà effectué le test et effectivement vu que le plan utilisait avec un seul index sous GroupeId,SousGroupId un INDEX SKIP SCAN sans savoir exactement ce que cela impliquait (mais j'ai fait depuis quelques lectures ). Si j'ai préféré néanmoins posé la question c'était davantage pour profiter de votre expérience.

Pour ceux qui comme moi découvre oracle : il y a ici un tuto indiquant ce qu'est la sélectivité dont parle FSiebert .

Merci encore.