Quand Créer un index

Version imprimable

15/10/2009, 14h12
LBO72

Quand Créer un index

Bonjour,

Je suis en Oralce 10gR2, et je me pose la question suivant :
J'ai une table qui fait plus de 1.700.00 records avec plusieurs colonnes. Cette table possède une colonne C1 avec seulement 3 valeurs distinctes :
je lance la requête ci-dessous :
Select * from MaTable where C1 = 1 ==> temps de réponse 15 secondes
J'ai ensuite je crée un index sur cette colonne C1 ==> temps de réponse : moins de 2 secondes ???

Je croyais que les indexes ne sont à créer que sur les colonnes les plus discriminantes...
je ne comprends plus rien..

Merci de vos lumières :-)
.
LBO72
15/10/2009, 14h35
farenheiit

3 valeurs distinctes ok mais combien de lignes pour chacun des valeurs de C1?

quelles sont les valeurs des paramètres suivantes:
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_COST_ADJ

Citation:

Envoyé par LBO72

Bonjour,

Je croyais que les indexes ne sont à créer que sur les colonnes les plus discriminantes...
LBO72

D'ou tu a eu cette information ?

voici la doc officielle

Code:

1
2
3
4
5
6
7
8
9
10
11
12
 
 
Index the Correct Tables and Columns
 
Use the following guidelines for determining when to create an index:
 
    *
 
      Create an index if you frequently want to retrieve less than 15% of the rows in a large table. The percentage varies greatly according to the relative speed of a table scan and how the distribution of the row data in relation to the index key. The faster the table scan, the lower the percentage; the more clustered the row data, the higher the percentage.
    *
 
      To improve performance on joins of multiple tables, index columns used for joins.

Sinon la doc plus longue

http://download.oracle.com/docs/cd/B...s.htm#i1006278

15/10/2009, 15h24
Marc Musette

ce serait intéressant que tu nous fournisses les explain plan de chacune des exécutions
15/10/2009, 15h28
mongolic

plan d'exécution

Bonjour,

Est ce que tu as les plans d'exécutions associés à ces deux requetes ?
15/10/2009, 17h36
LBO72

1 pièce(s) jointe(s)

Bonjour,

Le plan d'exécution en PJ, il porte le nom "Plan.jpg'.

Je rappelle que ma table contient plus de 16.500.000 records et que les valeurs distinctes pour BC_DERN_VERS c'est une seule valeur

En vous remerciant,

LBO72
15/10/2009, 18h43
mongolic

plans

La différence de temps s'explique par la différence de plan : dans un cas tu un full table scan et dans l'autre tu as un fast full index scan.
Il est plus rapide d'effectuer le fast full index scan car il y a ,normalement,moins de blocs à lire.

Le fast full index scan est utilisé lorsqu'oracle peut récupérer toutes les données depuis l'index sans accéder à la table.

Ce que je ne comprends pas, c'est si tu as construit un index sur la colonne C1 et que tu fais un select *, il ne peut donc pas récupérer toutes les données depuis l'index car celui-ci ne contient que la colonne C1 à moins que ta table ne contienne que la colonne C1 mais dans ce cas la volumétrie de la table et de l'indexe serait équivalente, et les temps d'exécution également.

A moins que ta requête soit select C1 from ....et non pas select * from ....
15/10/2009, 20h37
pachot
Citation:

Envoyé par mongolic

A moins que ta requête soit select C1 from ....et non pas select * from ....

Voire même un select count(*) car on voir un SORT AGGREGATE...

Si tu remplace la clause select par un count(*) pour ne pas tout afficher, alors celà change le plan d'execution.
Une solution serait de faire:
Code:

1 2 3 select count(*) from ( Select * from MaTable where C1 = 1 )
Celà évite d'afficher (et de fetcher) les enregistrements tout en ayant un plan d'execution représentatif de la requête interieure.

Cordialement,
Franck.
17/10/2009, 20h20
Pomalaix

Citation:

Envoyé par LBO72

Je croyais que les indexes ne sont à créer que sur les colonnes les plus discriminantes...

Ce qui est tout à fait exact dans le principe !
Mais certaines circonstances favorables peuvent rendre efficace un index sur lequel on n'aurait pas misé un kopeck.
Votre colonne n'a que 3 valeurs distinctes, mais sont-elles représentées de manière égale ? Si les données sont très déséquilibrées, ce fait peut être pris en compte par les histogrammes de fréquence générés lors du calcul des statistiques, et selon la valeur exacte recherchée, l'index peut être ou non utilisé.
Et même pour des valeurs équitablement réparties, si le hasard fait que les lignes de données correspondant à la valeur recherchée ne sont pas dispersées dans tous les blocs de la table, mais sont au contraire regroupées, l'usage de l'index restera bénéficiaire.

En tout cas, la sélectivité vue en nombre de lignes n'est pas un critère suffisant, il faut raisonner en nombre de blocs à lire par rapport au nombre total de blocs dans la table pour juger de la pertinence d'un index vis à vis d'une requête donnée.
27/10/2009, 16h33
sbra001

Citation:

Envoyé par LBO72

Bonjour,

Je suis en Oralce 10gR2, et je me pose la question suivant :
J'ai une table qui fait plus de 1.700.00 records avec plusieurs colonnes. Cette table possède une colonne C1 avec seulement 3 valeurs distinctes :
je lance la requête ci-dessous :
Select * from MaTable where C1 = 1 ==> temps de réponse 15 secondes
J'ai ensuite je crée un index sur cette colonne C1 ==> temps de réponse : moins de 2 secondes ???

Je croyais que les indexes ne sont à créer que sur les colonnes les plus discriminantes...
je ne comprends plus rien..

Merci de vos lumières :-)
.
LBO72

Bonjour,

Si tu executes pour la première fois la requête les blocs de données sont mis dans le data buffer cache, donc plus de temps pour les I/O. maintenant si tu crées un index et tu recommences juste après la même requête, je pense que la réponse va être plus rapide car les données sont déjà dans le data buffer cache.

Merci.