Discussion :

[eryk71]

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 :

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 :

CREATE INDEX idx_dimclient
ON dimclient(clientid)

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

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

Je fais la requête suivante :

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 :

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 :

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 :

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.

[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 ...

[Waldar]

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ?

[eryk71]

Bonjour et merci pour ta réponse

Quelle est ta version oracle ??

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

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 :

analyze table DIMCLIENT compute statistics
analyze index idx_dimclient compute statistics

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

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 :

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 :

analyze index idx_dimclient compute statistics

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

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

Moins de 3 secondes

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.

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

Au delas d'environ 20% de quoi? Comment tu calcule les 20%?

[mnitu]

Ajoutez not null dans la définition de la colonne clientid.
Calculez les statistiques avec

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[PpPool]

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 ?

[Waldar]

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.

[13thFloor]

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).

[mnitu]

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ...

[pachot]

Bonjour,

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.

[mnitu]

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 ?.

[fatsora]

11G et CHOOSE

bon on teste

Code sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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 sql :

Sélectionner tout - Visualiser dans une fenêtre à part

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 ...

[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.