index non utilisé

Je sais que c'est une question ultra courante, tapez pas de suite.... :oops:

J'ai trois tables:
A (col_id number not null, col1 number not null, col2 varchar2 null)
==> 100 000 lignes
B (col_id number not null, col2 varchar2 not null)
==> 500 lignes
C (col_id number not null, col2 varchar2 not null)
==> pas de lignes

Voilà la requete:

select distinct B.col_id, A.col_id
from tableA A, tableB B
where A.col1 = 0 and NVL(A.col2, ' ')=B.col2
union
select distinct C.col_id, A.col_id
from tableA A, tableC C
where A.col1 = 0 and NVL(A.col2, ' ')=C.col2;

J'ai mis deux index dans la table A, le premier sur col1 et le deuxième est un index de fonction sur NVL (col2).
Col1 ne peut prendre que 3 valeurs: 0,1 et 2 , la valeur utilisée dans la requête (donc 0) ne concerne qu'un quart de la table, donc est très discriminante.

Aucun des ces deux indexs n'est utilisés, même en tentant d'en forcer l'utilisation avec un hint...

Les stats sont à jour (j'ai tenté en mode estimate et en mode compute) et les indexs rebuildés....

Deux ou trois fois, lors de mes tests, Oracle a utilisé les indexs et le temps a été divisé par 3.
A vrai dire, je ne vois pas comment un table full scan pourrait être plus efficace qu'un index sur 1/4 de la table?

Ah oui, je précise que l'index est un b-tree car ma boite ne veut utiliser que la version standard d'oracle.

Encore une précision, le nombre de lignes dans mes tables n'est pas opérationnel, les ordres de grandeur sont plutot:
A ==> 1 millions
B et C ==> 2-3 millions

J'espère que ce n'est pas ca qui fausse mes tests?

De même, je me demandais si Oracle recalculais vraiment le plan d'execution à chaque fois que je teste ma requête, ou s'il ne réutilisait pas ce qu'il avait déjà dans son cache? Auquel cas, ce n'est pas la peine de rajouter ou d'enlever des indexs, il ne le "verra" pas.

Qq aurait-il une idée?

Merci!

Essayez un seul index sur le couple (col1, nvl(col2, ' ')), ce sera plus efficace.

En index B-tree, si je ne dis pas de bétise, Oracle ne peut en utiliser qu'un seul par table.

Après il faut vérifier toutes les requêtes qui interrogent votre table, car pour cette requête-ci cet index sera le plus efficace mais ce ne sera pas nécessairement vrai pour une autre.

J'ai lu un peu trop vite et ai raté ceci :

Citation:

---

Encore une précision, le nombre de lignes dans mes tables n'est pas opérationnel, les ordres de grandeur sont plutot:
A ==> 1 millions
B et C ==> 2-3 millions

---

Idéalement, votre point d'entrée est la table A et vous chercher à trouver les occurences des tables B et C.

Sur de telles volumétries, il se peut que même pour 25% Oracle ne trouve pas le critère suffisament discriminant et préfère faire un HASH JOIN plutôt qu'un INDEX RANGE SCAN.
Et en général sur ces volumétrie, il a raison !

L'index est vraiment plus utile pour trouver quelques valeurs.
Essayez l'index proposé ci-dessus, indexez vos col2 des tables B et C, mais si Oracle ne les utilise pas ne vous offusquez pas !

Vous pouvez aussi forcer l'utilisation des index avec le hint /*+ INDEX(A indA) */. Pensez à utiliser l'alias de la table dans le hint également.

Tiens, oui, c'est vrai qu'un index couplé sera meilleur, s'il est utilisé.

Si je comprends bien ta deuxième phrase, tu dis qu'Oracle ne peut utiliser qu'un seul index B-tree par table dans une requête? Argh, je ne savais pas...?!?

Pas de panique pour les besoins des "autres", cette table compte 32 indexs (no comment) et on m'a clairement dit que ce n'était pas mon boulot d'essayer de secouer tout ca pour voir ceux qui sont réellement utilisés et efficaces (sachant que j'ai des indexs sur des colonnes nullables...). Enfin bref, donc je rajoute mon index à moi (ou mes indexs à moi) sans m'occuper du reste. :?

On s'est croisé..
Votre réponse me fait penser que j'ai oublié plusieurs points:
les colonnes des tables B et C sont également indexées, et là il les utilise correctement.

J'ai tenté le hint, mais je n'ai pas pensé à préfixer du nom de la table. J'ai lu la doc en diagonal il semblerait...

arf, j'ai hâte de tenter tout ca demain (là je vais quand même rentrer chez moi....)

Au fait, les volumétries opérationnelles dont je parle, je n'en ai pas pour mes tests (j'attends d'ailleurs un dump de ces bases pour pouvoir faire mes tests sur qqchse de réaliste).
Pour mes test actuels, je n'ai que les volumétries indiquées au début de mon post.

Et effectivement, Oracle me fait bien un hash join.
Je suis novice en sql (et pl/sql), et c'est la première fois que je me "bats" avec un plan d'execution d'Oracle, alors pour l'instant, tout ce que je vois surtout est que l'index n'est pas utilisé...

Citation:

---

Envoyé par puck78

Je suis novice en sql (et pl/sql), et c'est la première fois que je me "bats" avec un plan d'execution d'Oracle, alors pour l'instant, tout ce que je vois surtout est que l'index n'est pas utilisé...

---

Faites vos tests, sachez que ce n'est pas forcément une mauvaise chose.

Alors, ca y est j'arrive bien à utiliser l'index avec le hint (il me manquait effectivement le nom de la table devant le nom de l'index dans le hint).

En terme de coût, Oracle calcule 3974 avec l'index et 711 sans. Les temps d'execution, eux, sont les mêmes.

Comprends pas... :koi:

Comment savoir si je dois créer l'index pour qu'Oracle l'ait à dispo ou si, du coup, ce n'est pas la peine d'alourdir le système avec mon index?

Si tu n'as pas de gain, tu devrais enlever le hint. C'est un peu l'optimisation de la dernière chance... Et si jamais la volumétrie de ta table évolue il peut ne plus être d'actualité

Citation:

---

Envoyé par puck78

Comment savoir si je dois créer l'index pour qu'Oracle l'ait à dispo ou si, du coup, ce n'est pas la peine d'alourdir le système avec mon index?

---

Bonjour,

C'est tout l'art du tuning SQL :) si en transactionnel on évite de créer des index dans tous les sens à cause des impacts sur les insertions/suppressions/MAJ, oN a moins cette contrainte en décisionnel.
Ceci étant dit, il n'est pas toujours judicieux de passer par un index et l'optimiseur s'en arrange bien le plus souvent. Cependant, dans certains cas, la valeur du littéral va être discriminente pour le plan ; lorsque la distribution des valeurs n'est pas uniforme les statistiques doivent le refléter pour renseigner l'optimiseur (qui pour simplifier ne considère que le nombre de valeurs distinctes pour la sélectivité de l'index), on peut alors calculer des histogrammes sur les colonnes concernées. Attention, le calcul d'histogrammes dans les statistiques peut être consommateur de ressources.
Pour résumer :
1- utiliser un index n'est pas toujours le meilleur choix
2- pour la même requête il peut être bénéfique ou non de passer par un index en fonction de la valeur du littéral.

Bonjour,

Attention avec les cost et les temps de réponse:
un cost inférieur ne signifie nullement quel la requete sera plus rapide.

Ensuite pour les temps de réponses : si les données sont en cache cela peut etre trompeur.

Pour comparer les deux requetes (SET AUTOTRACE ON ou TRACEONLY) ou tkprof

La comparaison sera alors efficace (nb de lecture physique, mémoire,...)

Laurent