[Oracle 11g] Index non utilisé par oracle

Version imprimable

16/02/2009, 16h54
eryk71

[Oracle 11g] Index non utilisé par oracle

Bonjour,

Afin de tester les performances, je n'arrive pas à faire utiliser mon index dans ma table. Celle-ci possède 190 000 lignes.

L'optimizer_mode est en mode 'CHOOSE'

Voici la structure de ma table :

Citation:

CREATE TABLE DimClient
(
ClientID number(10),
Titre varchar2(8),
Prenom varchar2(50),
Nom varchar2(50),
Email varchar2(50),
Telephone varchar2(20),
Sexe char(1),
AdresseLigne1 varchar2(50),
AdresseLigne2 varchar2(50),
GeoID number(10)
)

Voici mon index crée dans le premier champ :

Citation:

CREATE INDEX idx_dimclient
ON dimclient(clientid)

J'ai vérifié que l'index est valide :

Citation:

select index_name, status from user_indexes where index_name='IDX_DIMCLIENT'

Je fais la requête suivante :

Citation:

select count(*) from dimclient

En regardant le plan d'execution, je suis en "table_access full" avec un "sort".
L'index n'est donc pas utilisé.
J'essaye donc de le forcer :

Citation:

select /*+ index (DIMCLIENT IDX_DIMCLIENT) */ count(*) from dimclient

J'obtiens la même chose dans le plan d'execution. L'index n'est pas utilisé.

J'essaye donc une autre requête :

Citation:

select c.clientid from dimclient c

Même résultat, je suis en "table_access full" sans le "SORT" cette fois ci.
L'index n'est également pas utilisé.

J'essayé de forcer avec le hint :

Citation:

select /*+ index (DIMCLIENT IDX_DIMCLIENT) */ c.clientid from dimclient c

J'obtiens le même résultat l'index n'est toujours pas utilisé dans le plan d'execution

Je n'arrive pas à comprendre pourquoi, pourtant avec 190 000 lignes dans la table, je pense que se serait plus rapide pour Orcle d'utiliser l'index. Mais même en forçant avec le hint, oracle ne l'utilise pas.

Quelqu'un aurait t'il une explication de ce qui se passe?

Je vous remercie par avance.
16/02/2009, 17h03
fatsora

Bonjour,

Quelle est ta version oracle ??

As tu fais une analyse de la table + index ?

quel est le temps d'execution de ton count(*) ?

....

Pour les autres requetes c'est normal que oracle n'utilise pas l'index
car ... tu requetes sans faire de WHERE ...

au delas de environ 20% ,il va faire un full scan

car full scan ne veut pas dire forcement mauvais ...
16/02/2009, 17h14
Waldar
Code:

1 2 select /*+ index (DIMCLIENT IDX_DIMCLIENT) */ c.clientid from dimclient c
Si vous aliasez votre table il vous faut utiliser les alias dans le HINT :
Code:

1 2 select /*+ INDEX(c IDX_DIMCLIENT) */ c.clientid from dimclient c
Par contre dans cette requête il n'y a aucun WHERE.
Pourquoi utiliserait-il l'index ?
16/02/2009, 17h19
eryk71

Bonjour et merci pour ta réponse

Citation:

Quelle est ta version oracle ??

Je suis en 11g comme indiqué dans l'intitulé.

Citation:

As tu fais une analyse de la table + index ?

L'analyse de la table est l'index a été faite entièrement avec un compute statistic :

Citation:

analyze table DIMCLIENT compute statistics
analyze index idx_dimclient compute statistics

Lorsque je vérifie la table en faisant la requête :

Citation:

select table_name, last_analyzed from user_tables where table_name = 'DIMCLIENT'

J'obtiens bien la date de l'analyse par contre lorsque je vérifie l'index en faisant :

Citation:

select index_name, last_analyzed from user_indexes where index_name = 'DIMCLIENT'

Je n'obtiens rien, la table est vide. Pour lorsque je lance l'analyse pour l'index en faisant :

Citation:

analyze index idx_dimclient compute statistics

Je n'ai pas d'erreur, l'instruction est executée en moins d'1 seconde.

Citation:

quel est le temps d'execution de ton count(*) ?

Moins de 3 secondes

Citation:

Pour les autres requetes c'est normal que oracle n'utilise pas l'index
car ... tu requetes sans faire de WHERE ...

Effectivement avec le Where, il utilise l'index, je pensai que juste un select sur l'index lui permettait de l'utiliser.

Citation:

au delas de environ 20% ,il va faire un full scan

Au delas d'environ 20% de quoi? Comment tu calcule les 20%?
16/02/2009, 17h41
mnitu
Ajoutez not null dans la définition de la colonne clientid.
Calculez les statistiques avec
Code:

1 2 begin dbms_stats.gather_table_stats(user, 'DIMCLIENT', cascade => true); end;
Dite à votre dba que la valeur CHOOSE est obsolète dans votre version d’Oracle
16/02/2009, 17h47
PpPool

Citation:

Envoyé par mnitu

Dite à votre dba que la valeur CHOOSE est obsolète dans votre version d’Oracle

ah bon, je n'ai pas encore travaillé sur la 11g mais quelle est la valeur à renseigner désormais ?
16/02/2009, 18h11
Waldar

Citation:

select index_name, last_analyzed from user_indexes where index_name = 'DIMCLIENT'

Je n'obtiens rien, la table est vide.

C'est normal puisque 'DIMCLIENT' est le nom de votre table et pas de votre index.
16/02/2009, 18h24
13thFloor

Citation:

Envoyé par Waldar

Par contre dans cette requête il n'y a aucun WHERE.
Pourquoi utiliserait-il l'index ?

Il devrait parce qu'il est parfois plus judicieux de lire les blocs de l'index, composés des clientid, que tous les blocs de la table => index fast full scan.
Sauf bien sûr si nb de bloc table = nb de bloc index.

ALL_ROWS est la valeur par défaut de optimizer_mode depuis la 10g.

En principe, avec l'alias le hint devrait marcher (en évitant l'espace avant la parenthèse ouvrante).

Il ne peut pas utiliser l'index que si la réponse est correcte

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
 
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
SQL> CREATE TABLE DimClient
  2  as
  3  select level as clientid, rpad(' ',8,'X') titre, dbms_random.string('A',50) Prenom
  4    from dual
  5    connect by level < 10000
  6  /
 
Table crÚÚe.
 
SQL> create index ix_dimclient on dimclient(clientid);
 
Index crÚÚ.
 
SQL> exec dbms_stats.gather_table_stats(user,'DIMCLIENT',cascade=>true);
 
ProcÚdure PL/SQL terminÚe avec succÞs.
 
SQL> set autotrace traceonly explain;
SQL> select count(*) from dimclient;
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 3013303778
 
------------------------------------------------------------------------
| Id  | Operation          | Name      | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |     1 |    20   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |           |     1 |            |          |
|   2 |   TABLE ACCESS FULL| DIMCLIENT |  9999 |    20   (0)| 00:00:01 |
------------------------------------------------------------------------
 
SQL> desc dimclient
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ----------------------------
 CLIENTID                                           NUMBER
 TITRE                                              VARCHAR2(8)
 PRENOM                                             VARCHAR2(4000)
SQL> alter table dimclient modify clientid number not null;
 
Table modifiÚe.
 
SQL> set autotrace off
SQL> exec dbms_stats.gather_table_stats(user,'DIMCLIENT',cascade=>true);
 
ProcÚdure PL/SQL terminÚe avec succÞs.
 
SQL> set autotrace traceonly explain;
SQL> select count(*) from dimclient;
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 4130256396
 
------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |              |     1 |     5   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE       |              |     1 |            |          |
|   2 |   INDEX FAST FULL SCAN| IX_DIMCLIENT |  9999 |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------

Or sans l'information vitale que la colonne est non nulle ...:roll:

16/02/2009, 20h52
pachot

Bonjour,

Citation:

Envoyé par 13thFloor

Il devrait parce qu'il est parfois plus judicieux de lire les blocs de l'index

Comme l'a évoqué mnitu l'index ne peut être utilisé que si la colonne indexée est not null.
Parce que les enregistrements o^il a une valeur null doit être comptée dans le count(*) mais ne sont pas indexées. Donc l'index ne peut pas être utilisé pour faire un count dans ce cas.

Cordialement,
Franck.
16/02/2009, 20h59
mnitu

Citation:

Envoyé par PpPool

ah bon, je n'ai pas encore travaillé sur la 11g mais quelle est la valeur à renseigner désormais ?

Très probablement ALL_ROWS. CHOOSE c'était pour 7 et 8. En 9 c'est déjà mauvais mais il est encore dans la doc. A partir d'Oracle 10 officiellement l'optimiseur RULE n'existe plus, donc choisir quoi précisement :aie:?.

11G et CHOOSE :aie:

bon on teste

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
SQL> alter session set optimizer_mode='all_rows';
 
Session modifiÚe.
 
SQL> select count(*) from big_table;
 
  COUNT(*)
----------
    778880
 
SQL> set autotrace traceonly explain
SQL> select count(*) from big_table;
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 763111886
 
--------------------------------------------------------------------------------
-
 
| Id  | Operation             | Name            | Rows  | Cost (%CPU)| Time
|
 
--------------------------------------------------------------------------------
-
 
|   0 | SELECT STATEMENT      |                 |     1 |   553   (1)| 00:00:07
|
 
|   1 |  SORT AGGREGATE       |                 |     1 |            |
|
 
|   2 |   INDEX FAST FULL SCAN| OBJECT_NAME_IDX |   729K|   553   (1)| 00:00:07
|
 
--------------------------------------------------------------------------------

et si je mets CHOOSE

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SQL> alter session set optimizer_mode='CHOOSE';
 
Session modifiÚe.
 
SQL> set autotrace traceonly explain
SQL> select count(*) from big_table;
 
Plan d'exÚcution
----------------------------------------------------------
Plan hash value: 599409829
 
----------------------------------------
| Id  | Operation          | Name      |
----------------------------------------
|   0 | SELECT STATEMENT   |           |
|   1 |  SORT AGGREGATE    |           |
|   2 |   TABLE ACCESS FULL| BIG_TABLE |
----------------------------------------

allez voir ....

Ceci dit il y a peut etre d'autres parametres d'init qui sont setté pour Oracle 8 voir 7 ???

==> Donc a vérifier aussi

Mais je pense qu'en changeant OPTIMIZER_MODE a ALL_ROWS ...

17/02/2009, 10h29
eryk71

Bonjour,

Je vous remercie tous de vos réponses qui m'ont beaucoup aidé.

En effet, lorsque je met la contrainte de not null dans la PK, l'index est utilisé dans le plan d'éxecution.

J'ai essayé de modifier l'optimiseur mode à ALL_ROWS et à CHOOSE en executant la même requête, je constate que le plan d'execution est pareil.