Index oracle fonctionnement
Tme
-
tme15 -
tme15 -
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).
J'ai essayé ces requetes:
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:
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 :)
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 :)
A voir également:
- Index oracle fonctionnement
- Fonctionnement processeur - Guide
- Index téléphonique - Guide
- Fonctionnement du protocole http - Guide
- Telecharger index - Télécharger - Gestion de fichiers
- Notice de fonctionnement - Guide
2 réponses
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 :
=> 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é.
=> 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
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.
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 :
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".
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".
