index oracle fonctionnementFermé

Question

Bonjour, 

J'ai effectué divers Tests pour comprendre le fonctionnement des index en oracle,mais certaines choses m'échappent...

J'ai une Table JOUEURS(ID,NOM,PRENOM).

CREATE INDEX idx_nom ON JOUEURS(NOM);

J'ai essayé ces requetes:

SELECT * FROM JOUEURS WHERE NOM='MESSI' ->Pas D'index Utilisé
SELECT PRENOM FROM JOUEURS WHERE NOM='MESSI' ->Pas d'index Utilisé
SELECT NOM FROM JOUEURS WHERE NOM='MESSI' ->Index Utilisé

Je ne comprend pas bien Pourquoi Oracle n'utilise pas idx_nom pour les deux premieres.
J'ai remarqué que si je fais CREATE UNIQUE INDEX idx_nom ON JOUEURS(NOM) A la place,l'index sera bel et bien utilisé,savez vous pourquoi?

Ma 2eme question concerne les index multi colonnes:

CREATE UNIQUE INDEX multi ON JOUEURS(NOM,PRENOM);

select * from joueurs where Nom='LIONEL' and Prenom='MESSI' -> Index multi utilisé
select * from joueurs where Prenom='LIONEL' and Nom='MESSI' ->Index multi utilisé

Pourquoi dans le deuxième cas l'index multi est utilisé?Je croyais que l'on pouvais seulement utiliser la partie gauche d'un index multi-colonne?donc Nom ,ou Nom et Prenom?
Bref j'ai du mal à saisir...

Merci d'avance pour vos réponses :)

productif · Accepted Answer

Il faut savoir que même en présence d'un index, l'usage de ce dernier n'est pas systématique ; en effet, pour un nombre de lignes à retourner important, l'usage d'un index est plus coûteux qu'un parcours complet de la table. C'est donc très courant que les index ne soient pas utilisés dès que le moteur SQL estime que le nombre de lignes à récupérer dépasse une certaine valeur (pas très facile à estimer a priori).

Pour revenir aux cas cités :

SELECT * FROM JOUEURS WHERE NOM='MESSI' ->Pas D'index Utilisé
SELECT PRENOM FROM JOUEURS WHERE NOM='MESSI' ->Pas d'index Utilisé

=> si les statistiques ne sont pas calculées, si le nombre de d'occurrences de 'MESSI' est important ou si la taille de la table est très petite, il est plus de coûteux de lire l'index puis une partie de la table que de lire la table en intégralité.


SELECT NOM FROM JOUEURS WHERE NOM='MESSI' ->Index Utilisé


=> la clause select contient seulement le champ indexé ; toutes les données nécessaires se trouvent dans l'index et il est inutile de lire la table


CREATE UNIQUE INDEX idx_nom ON JOUEURS(NOM) -> A la place,l'index sera bel et bien utilisé

L'index unique garantit qu'il y aura au plus une seule ligne de données à récupérer ; dès qu'il y a plus d'une poignée de lignes dans la table, l'usage de l'index devient intéressant.


select * from joueurs where Nom='LIONEL' and Prenom='MESSI' -> Index multi utilisé
select * from joueurs where Prenom='LIONEL' and Nom='MESSI' ->Index multi utilisé

Ces requêtes sont identiques d'un point de vue sémantique, aucun risque qu'elles aient un plan différent (sauf éventuellement en cas d'utilisation de "hints").
C'est lorsque la partie gauche de l'index ne subit pas de condition qu'un index multi-colonnes est ignoré, par exemple :


select * from joueurs where Prenom='MESSI' ->Index multi non-utilisé


En fait, dans certains cas particuliers, Oracle saura quand même utiliser l'index multi-colonnes dans une telle requête ; ils appellent ça l' "index skip scan".

tme15 · Answer

Je te remercie de ta réponse trés clair(un peu tardivement)

A bientôt

Index oracle fonctionnement

2 réponses

Discussions similaires

Newsletters