Discussion :

[w3blogfr]

Bonjour à tous,

Je vais essayer d'être assez précis pour que vous puissiez m'aider.

J'ai actuellement une table qui contient des numéros de séries ( 436239 lignes). Le numéro de série est unique sur 18 caractères. J'ai donc mis un index unique dessus.
Habitué à normaliser mes bases, j'utilise une clé primaire numérique.
J'ai 3 tables qui font référence à la clé primaire numérique (dont 1 contenant 373914 lignes)

Sur beaucoup de mes requêtes qui dépendent du numéro de série, j'ai des temps de réponse lent, pouvant attendre 11 secondes, ce qui n'est pas acceptable vu la faible quantité de données (Qu'est qu'un demi-million pour Oracle? ).

Pour info, le client veut impérativement faire ses recherches en mode "contient" et non en mode "commence par". La plupart de mes requêtes ont donc un like "%<NUM_SERIE_LETTER>%'.

J'ai testé différent réglage et essayer sur un base local pour voir si j'avais moyen d'optimiser tout ca. En dupliquant mon champ numéro de série sur les 2 tables référentes, ce qui m'évite une jointure sur la table NumSerie, j'obtiens des temps de réponses de 2 à 3 secondes (en mettant un index), ce qui pourrait paraitre acceptable.

Mais alors dans ce cas, vu que le numéro de série est dupliqué dans 2 de mes tables référentes, ne serait-il pas mieux d'utiliser mon VARCHAR2(18) de numéro de série comme clé primaire plutôt qu'un numérique?

Qu'en pensez-vous?

[mnitu]

Like "%<NUM_SERIE_LETTER>%' fait sauter n'importe quel index.

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
 
SQL> explain plan for
  2  Select *
  from hr.employees t
 where t.email = 'SKING';
 
Explicité.
 
SQL> select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
------
Plan hash value: 563061882
 
--------------------------------------------------------------------------------------------
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |              |     1 |    68 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES    |     1 |    68 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMAIL_UK |     1 |       |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
------
---------------------------------------------------
 
   2 - access("T"."EMAIL"='SKING')
 
SQL> explain plan for
  2  Select *
  from hr.employees t
 where t.email like '%SKING%';
 
Explicité.
 
SQL>  select * from table(dbms_xplan.display);
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
------
Plan hash value: 1342275408
 
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     5 |   340 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     5 |   340 |     3   (0)| 00:00:01 |
-------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------
------
 
   1 - filter("T"."EMAIL" LIKE '%SKING%')

[ojo77]

Comme le précise mnitu, le fait de rechercer en mode "contient" force un parcours complet de la table les statistiques oracle étant insuffisantes pour associer une sélectivité à un contenu.

Si vous êtes absolument certain qu'un passage par index sera plus rapide, vous pouvez indiquer à l'optimiseur d'utliser un parcours par index au moyen d'un "hint" au niveau de la requête.

Code sql :

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

1
2
3
4
 
Select /*+ index(<nom de table> <nom d'index>)*/ ...
from <tables>
Where <conditions>

Mais avant toutes choses :

• Pourquoi le client veut une requête en mode "contient" ?
• Qui est le client (MOE ou MOA) ?
• Que souhaite vraiment la MOA ?
• La MOA peut elle faire évoluer son besoin ? Genre un "commence par" plutôt qu'un "contient" ?

Bref, si le besoin est effectivement une requête en mode "contient" et que chaque requête doit au final remonter un grand nombre de lignes, de mauvaises performances sont à prévoir sans qu'on puisse y faire grand chose.

[w3blogfr]

Justement, je pensais aussi qu'Oracle (10g au passage) ne pouvait pas utiliser d'index avec like '%<FILTRE>%', mais mon EXPLAIN est formel. Il utilise l'index créé par l'unique key.

Code :

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

1
2
 
|*  7 |   INDEX RANGE SCAN  | NUMSERIE_UK1  |    12 |   |  2   (0)| 00:00:01 |

Un autre fait étonnant. Ma table numéro de série contient une forte majorité de numéro de série commencant 'SN' suivi d'une série de chiffre(voir de lettre) que je stocke unique sur 18 caractères, je dirais que les cas 'SN' représente environ 80 % de la table.

L'information remontée par la requête est 5 niveaux plus haut (5 jointures, ça fait beaucoup, je sais).
Un critère like 'SN%' est relativement lent (11 secondes). Le EXPLAIN m'indique un coût de 36. C'est assez normal, mon critère ne filtre finalement pas grand chose, et il retourner 80% de la table.

Mais ce qui est étrange, c'est que pour la même requête, le critère LIKE '%SN%' est plus rapide (2 secondes) alors que le coût du EXPLAIN est de 800. Les donnés stockées font que les 2 requêtes retournent les mêmes résultats.

Les 2 requêtes m'indiquent utiliser l'index unique NUMSERIE_UK1. C'est étrange non? J'arrive pas à comprendre comment LIKE '%SN%' peut être plus performant.


En revanche, Le like '<LETTRE_FILTER>%' devient plus performant à partir de 6 lettres saisies.


Pour la justification du besoin client.
Le numéro de série est constitué de lettres et chiffres qui peuvent correspondre parfois à des dates ou des modèles. C'est pourquoi l'utilisateur peut saisir directement '1001' pour avoir les numéros de séries de Janvier 2010, mais ce n'est pas la majorité des utilisateurs.

Au début persuadé d'un gain de performance en mode "commence par", j'avais testé une modif de l'interface pour laisser le choix à l'utilisateur entre les 2 modes, libre à l'utilisateur de passer en mode "contient" et d'avoir des requêtes plus longues. Ce qui aurait satisfait la fonctionnalité et/ou la performance. Mais les performances ne sont pas si visible comme expliqué ci-dessus.

[w3blogfr]

mnitu, j'obtiens bien le même explain que toi sur ma table avec un select simple sans jointure.

Code :

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

1
2
 
SELECT * FROM NUMSERIE numSerie WHERE numSerie.numSerie LIKE 'BS1001%'

Cette requête utilise l'index.

Code :

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

1
2
 
SELECT * FROM NUMSERIE numSerie WHERE numSerie.numSerie LIKE '%BS1001%'

Cette requête fait un full scan.


Dans tous les cas, vu que numéro de série est unique sur 18 caractères, me conseiller vu de l'utiliser comme primary key au détriment de mon id numérique?

Ca me fait bizarre d'utiliser un varchar en clé primaire, mais c'est surement parce que je suis habitué à MySQL

[mnitu]

La requête de type LIKE 'BS1001%' peut utiliser un index parce que vous avez le début de la chaîne des caractères et vous pouvez la comparer d'une manière efficace avec le contenu de l'index, exemple: BA10000, BA10010, ... BS10000, BS10010, BS100100, BS1001000, ... BT1000, ...
Mais comment voulez vous comparer efficacement le contenu de l'index ci dessus avec la condition LIKE '%10%' ?
Parfois la seule utilisation de l'index dans ces conditions pourrait être de l'utiliser comme une table en full scan, donc de faire un index full scan. Mais cette possibilité est assez restrictive: la colonne doit être not null, le résultat doit pouvoir être obtenu que sur l'index.
L’idée d’utiliser un hint me semble complètement erronée dans ce cas : en présence des bonnes statistiques l’optimiseur fait assez bien son boulot.
Cinq table en jointure c'est rien, le base des données sont là pour faire des jointures.
Mais pour avancer il faut déjà les requêtes ainsi que des autres informations.

[w3blogfr]

Je comprend bien pourquoi un index fonctionne bien avec un like 'BS%'. Cela est dû à la structure B-Tree qui classe les valeurs dans un arbre, et il parcours l'index du noeud parent vers les sous-noeuds.

Toutefois, j'ai compris quelques choses entre temps, Oracle utilise également cet index pour les requête like '%BS%' car ma colonne est unique. Si je désactive la contrainte, il fait un full scan, et ca va plus vite!

A ce propos, lorsqu'on crée un contrainte unique, Oracle crée un index implicite, mais est-ce exactement le même type d'index que si on crée un index unique?

Code :

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

1
2
3
4
5
6
7
 
ALTER TABLE NUMSERIE
ADD CONSTRAINT NUMSERIE_UK1 UNIQUE 
(
  NUMSERIE 
)
ENABLE;

Est-ce exactement la même chose que ca?

Code :

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

1
2
 
CREATE UNIQUE INDEX NUMSERIE_INDEX1 ON GWTNUMSERIE (NUMSERIE);

Du même type que :

Code :

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

1
2
 
/*+ index(<nom de table> <nom d'index>)*/

Existe-t-il de forcer un full scan au lieu d'utiliser un index?


Les données de ma base correspondent à un Dump de la production. Les statistiques n'étant pas bonne au départ, j'avais fait sur les tables:

Code :

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

1
2
 
analyze table NUMSERIE compute statistics;

Est-ce suffisant pour avoir des statistiques corrects?


Merci pour la réponse.

[mnitu]

...Toutefois, j'ai compris quelques choses entre temps, Oracle utilise également cet index pour les requête like '%BS%' car ma colonne est unique.
...

Montrez-le!

...
Si je désactive la contrainte, il fait un full scan, et ca va plus vite!

Mauvais statiqtiques!

...
A ce propos, lorsqu'on crée un contrainte unique, Oracle crée un index implicite, mais est-ce exactement le même type d'index que si on crée un index unique?

Code :

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

1
2
3
4
5
6
7
 
ALTER TABLE NUMSERIE
ADD CONSTRAINT NUMSERIE_UK1 UNIQUE 
(
  NUMSERIE 
)
ENABLE;

Est-ce exactement la même chose que ca?

Code :

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

1
2
 
CREATE UNIQUE INDEX NUMSERIE_INDEX1 ON GWTNUMSERIE (NUMSERIE);

Fait un test par vous même.

Du même type que :

Code :

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

1
2
 
/*+ index(<nom de table> <nom d'index>)*/

Existe-t-il de forcer un full scan au lieu d'utiliser un index?

Oui mais, pour l'instant oubliez l'existence des Hints.

...
Les données de ma base correspondent à un Dump de la production. Les statistiques n'étant pas bonne au départ, j'avais fait sur les tables:

Code :

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

1
2
 
analyze table NUMSERIE compute statistics;

Est-ce suffisant pour avoir des statistiques corrects?


...

Utilisez toujours DBMS_STATS. Quelle est la version d'Oracle ?

[Rams7s]

Justement, je pensais aussi qu'Oracle (10g au passage) ne pouvait pas utiliser d'index avec like '%<FILTRE>%', mais mon EXPLAIN est formel. Il utilise l'index créé par l'unique key.

Code :

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

1
2
 
|*  7 |   INDEX RANGE SCAN  | NUMSERIE_UK1  |    12 |   |  2   (0)| 00:00:01 |

Un autre fait étonnant. Ma table numéro de série contient une forte majorité de numéro de série commencant 'SN' suivi d'une série de chiffre(voir de lettre) que je stocke unique sur 18 caractères, je dirais que les cas 'SN' représente environ 80 % de la table.

L'information remontée par la requête est 5 niveaux plus haut (5 jointures, ça fait beaucoup, je sais).
Un critère like 'SN%' est relativement lent (11 secondes). Le EXPLAIN m'indique un coût de 36. C'est assez normal, mon critère ne filtre finalement pas grand chose, et il retourner 80% de la table.

Mais ce qui est étrange, c'est que pour la même requête, le critère LIKE '%SN%' est plus rapide (2 secondes) alors que le coût du EXPLAIN est de 800. Les donnés stockées font que les 2 requêtes retournent les mêmes résultats.

Bonjour,

Concernant ce point, si j'ai compris, pour le SN% il utilise l'index. Et avec le message de mnitu #6, il apparait que pour %SN% il n'utilise pas l'index.

Maintenant, si c'est bien ca () je vous conseillerai les histogrammes.
http://asktom.oracle.com/pls/asktom/...D:707586567563
Dans le cas ou vous recupererez 80% de la table, c'est plus rapide de faire un full table scan que d'utiliser un index. Maintenant, si le CBO ne sait pas qu'il y a beaucoup beaucoup de SN%, il y a peu de chance qu'il le devine.

http://books.google.ch/books?id=TGSd...page&q&f=false
Page suivante, la 179.

Ca ne resout rien, mais ca peut expliquer un peu.

[w3blogfr]

J'ai dû effectivement me mélanger les pinceaux, je n'arrive plus à voir dans un explain que oracle utilise l'index pour les like '%BS%'.

Pour la différence entre la contrainte unique et l'index, je sais bien qu'il n'y a pas de différence dans un explain, je me posais juste la question de savoir pourquoi les 2 syntaxes était possible?


Je n'ai pas les privilèges suffisant pour DBMS_STATS, j'ai fais une demande pour avoir le privilège, on verra bien.


Voici la requête :

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
 
SELECT COUNT(DISTINCT produit.ProduitId)
FROM Produit produit
JOIN GROUPETEST groupeTest
ON produit.ProduitId=groupeTest.ProduitId
JOIN BANC banc
ON banc.bancId=groupeTest.BancId
AND (banc.bancReference
  ||banc.bancNumSerie LIKE '%BANC%'
OR banc.bancName
  ||banc.bancNumSerie LIKE '%BANC%')
JOIN GROUPETESTNUMSERIE groupeTestNumSerie
ON groupeTestNumSerie.groupeTestId=groupeTest.groupeTestId
AND groupeTestNumSerie.produitId  =produit.produitId
AND groupeTestNumSerie.siteId     =1
JOIN NUMSERIE numSerie
ON numSerie.numSerieId=groupeTestNumSerie.numSerieId
AND numSerie.numSerie LIKE 'BS%'

Avec ou sans index, la recherche est toujours plus longue en mode "Commence par". cela s'inverse qu'à partir de 6 caractères saisies.


La version est la Oracle Database 10g.

[ORA-007]

Bonjour,

Peut on avoir les plans ?

[w3blogfr]

Les plans d'éxecution sont dans les 2 fichiers txt joint précédemment

[mnitu]

Pour l’instant laissé à coté les contraintes et autres modifications de la requête.
Commencez par calculer les statistiques sur toutes les tables et indexes de la requête.
Dans l’analyse de la requête on constate que vous sélectionnez que depuis la table Produit, et encore un peu en trop parce que vous utilisez distinct. Par contre tous vos critères sont ailleurs sur les autres tables. Le temps de réponse de ce type de requête dépende beaucoup de la capacité de filtrage dans les premières étapes du plan d’exécution.
Quand vous ajoutez la contrainte d’unicité il y a création d’un index unique. Cet index n’a pas des statistiques à jour par défaut. Il est nécessaire toujours dans ces conditions de calculer les statistiques pour l’index en question sinon les informations transmises à l’optimiseur sont incomplètes ce qui peut conduire à un mauvais plan.

[Pomalaix]

Quand vous ajoutez la contrainte d’unicité il y a création d’un index unique. Cet index n’a pas des statistiques à jour par défaut. Il est nécessaire toujours dans ces conditions de calculer les statistiques pour l’index en question sinon les informations transmises à l’optimiseur sont incomplètes ce qui peut conduire à un mauvais plan.

Je ne suis pas sûr de bien comprendre ton propos, mais en 10g, tout index, implicite ou non, subit un calcul des statistiques à sa création.

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
SQL> create table t as select * from dba_objects;
TABLE T créé
 
Table créée.
 
SQL> alter table T add constraint TUQ unique(object_id);
INDEX TUQ créé
 
Table modifiée.
 
SQL> select index_name, last_analyzed from user_indexes where index_name='TUQ';
 
INDEX_NAME                     LAST_ANALYZED
------------------------------ -------------------
TUQ                            22/02/2011 17:33:28

[ojo77]

Si la table et l'index ont étés créés avant l'insertion des données il serait bon de recalculer les stats sur les 2 objets.

D'autre part, il semblerait que le calcul simultané de statistiques par dbms_stats et analyze détraquerait l'optimiseur. Il faudrait donc se contenter d'utiliser une méthode (de préfrence dbms_stats).

[w3blogfr]

L'insertion des données a été faite à la création de l'index. On m'a confirmé qu'un refresh dbms_stats avait lieu toutes les nuits. Mes calculs étaient donc peut-être faussé. Je vais donc attendre pour en savoir plus. Je vous tiens au courant.

[Waldar]

Quand vous ajoutez la contrainte d’unicité il y a création d’un index unique.

Pas nécessairement.
Si un index existe déjà à la création de la contrainte, Oracle ne va pas en créer un nouveau.
De plus, dans le cas d'une contrainte d'unicité déferrable, l'index créé ne sera pas unique non plus.

Un index est un objet physique qui est là pour épauler la base de données, une contrainte est un élément fonctionnellement restrictif.
La contrainte d'unicité s'appuie toujours d'un index, mais ce dernier n'est pas forcément unique.

[w3blogfr]

Que d'heure perdu pour des mauvaises statistiques

J'ai mis un schéma (ultra light) pour que ce soit plus claire.

A partir de GroupeTestNumSerie, il existe 2 liens vers produit (Direct et par groupeTest), mais c'est normal. L'un est le lien administratif, et l'autre réel.

Constatant dans tout les cas de meilleurs performances, j'ai préféré dé-normaliser un peu mes données pour éviter une jointure sur 2 tables importante.

J'ai donc mis le numéro de série dans GroupeTestNumSerie avec un index non unique (possibilité de doublon dans cette table).

Voici la nouvelle requête:

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
SELECT COUNT(DISTINCT produit.ProduitId)
FROM Produit produit
JOIN GROUPETEST groupeTest
ON produit.ProduitId=groupeTest.ProduitId
JOIN BANC banc
ON banc.bancId=groupeTest.BancId
AND (banc.bancName LIKE '%BANC%')
JOIN GROUPETESTNUMSERIE groupeTestNumSerie
ON groupeTestNumSerie.groupeTestId=groupeTest.groupeTestId
AND groupeTestNumSerie.produitId  =produit.produitId
AND groupeTestNumSerie.siteId     =1
AND groupeTestNumSerie.numSerie LIKE 'BS%'
WHERE 1=1

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
-----------------------------------------------------------------------------------------------                                                                                                                                                                                                              
| Id  | Operation             | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                              
-----------------------------------------------------------------------------------------------                                                                                                                                                                                                              
|   0 | SELECT STATEMENT      |                       |     1 |    77 |   854   (3)| 00:00:11 |                                                                                                                                                                                                              
|   1 |  SORT GROUP BY        |                       |     1 |    77 |            |          |                                                                                                                                                                                                              
|*  2 |   HASH JOIN           |                       |    17 |  1309 |   854   (3)| 00:00:11 |                                                                                                                                                                                                              
|   3 |    NESTED LOOPS       |                       |   822 | 41922 |    12   (9)| 00:00:01 |                                                                                                                                                                                                              
|*  4 |     HASH JOIN         |                       |   822 | 37812 |    11  (10)| 00:00:01 |                                                                                                                                                                                                              
|*  5 |      TABLE ACCESS FULL| BANC                  |    19 |   665 |     3   (0)| 00:00:01 |                                                                                                                                                                                                              
|   6 |      TABLE ACCESS FULL| GROUPETEST            |  6611 | 72721 |     7   (0)| 00:00:01 |                                                                                                                                                                                                              
|*  7 |     INDEX UNIQUE SCAN | PRODUIT_PK            |     1 |     5 |     1   (0)| 00:00:01 |                                                                                                                                                                                                              
|*  8 |    TABLE ACCESS FULL  | GROUPETESTNUMSERIE    |   319K|  8115K|   838   (3)| 00:00:11 |                                                                                                                                                                                                              
-----------------------------------------------------------------------------------------------

Contrairement a hier, Oracle disposant des bonnes statistiques, il préfère bien le full scan à l'utilisation de l'index qui est plus lent. Et les temps de réponse sont similaire entre LIKE '%BS%' et LIKE 'BS%'.


En revanche, je re-testé l'ancienne requête en allant jusqu'à la table numSerie. Les temps de réponse sont toujours très lent

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
 
SELECT COUNT(DISTINCT produit.ProduitId)
FROM Produit produit
JOIN GROUPETEST groupeTest
ON produit.ProduitId=groupeTest.ProduitId
JOIN BANC banc
ON banc.bancId=groupeTest.BancId
AND banc.bancName LIKE '%BANC%'
JOIN GROUPETESTNUMSERIE groupeTestNumSerie
ON groupeTestNumSerie.groupeTestId=groupeTest.groupeTestId
AND groupeTestNumSerie.produitId  =produit.produitId
AND groupeTestNumSerie.siteId     =1
JOIN NUMSERIE numSerie
ON numSerie.numSerieId=groupeTestNumSerie.numSerieId
AND numSerie.numSerie LIKE '%BS%'
WHERE 1=1;

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
 
-------------------------------------------------------------------------------------------------------                                                                                                                                                                                                      
| Id  | Operation                     | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                      
-------------------------------------------------------------------------------------------------------                                                                                                                                                                                                      
|   0 | SELECT STATEMENT              |                       |     1 |    86 |   868   (3)| 00:00:11 |                                                                                                                                                                                                      
|   1 |  SORT GROUP BY                |                       |     1 |    86 |            |          |                                                                                                                                                                                                      
|   2 |   NESTED LOOPS                |                       |     2 |   172 |   868   (3)| 00:00:11 |                                                                                                                                                                                                      
|*  3 |    HASH JOIN                  |                       |    20 |  1400 |   848   (3)| 00:00:11 |                                                                                                                                                                                                      
|   4 |     NESTED LOOPS              |                       |   822 | 41922 |    12   (9)| 00:00:01 |                                                                                                                                                                                                      
|*  5 |      HASH JOIN                |                       |   822 | 37812 |    11  (10)| 00:00:01 |                                                                                                                                                                                                      
|*  6 |       TABLE ACCESS FULL       | BANC                  |    19 |   665 |     3   (0)| 00:00:01 |                                                                                                                                                                                                      
|   7 |       TABLE ACCESS FULL       | GROUPETEST            |  6611 | 72721 |     7   (0)| 00:00:01 |                                                                                                                                                                                                      
|*  8 |      INDEX UNIQUE SCAN        | PRODUIT_PK            |     1 |     5 |     1   (0)| 00:00:01 |                                                                                                                                                                                                      
|*  9 |     TABLE ACCESS FULL         | GROUPETESTNUMSERIE    |   372K|  6909K|   831   (2)| 00:00:10 |                                                                                                                                                                                                      
|* 10 |    TABLE ACCESS BY INDEX ROWID| NUMSERIE              |     1 |    16 |     1   (0)| 00:00:01 |                                                                                                                                                                                                      
|* 11 |     INDEX UNIQUE SCAN         | NUMSERIE_PK           |     1 |       |     1   (0)| 00:00:01 |                                                                                                                                                                                                      
-------------------------------------------------------------------------------------------------------

Que veut dire exactement TABLE ACCESS BY INDEX ROWID?

Dans l’analyse de la requête on constate que vous sélectionnez que depuis la table Produit, et encore un peu en trop parce que vous utilisez distinct. Par contre tous vos critères sont ailleurs sur les autres tables. Le temps de réponse de ce type de requête dépende beaucoup de la capacité de filtrage dans les premières étapes du plan d’exécution.

Il existe un moyen de filtrer les distincts plus en amont dans ma requête?

[mnitu]

Je ne suis pas sûr de bien comprendre ton propos, mais en 10g, tout index, implicite ou non, subit un calcul des statistiques à sa création.
...

@pomalaix
C'est vrai. je viens de faire un upgrade (ce n'était pas pareil en version 9)
Merci

[mnitu]

Pas nécessairement.
Si un index existe déjà à la création de la contrainte, Oracle ne va pas en créer un nouveau.
De plus, dans le cas d'une contrainte d'unicité déferrable, l'index créé ne sera pas unique non plus.

Un index est un objet physique qui est là pour épauler la base de données, une contrainte est un élément fonctionnellement restrictif.
La contrainte d'unicité s'appuie toujours d'un index, mais ce dernier n'est pas forcément unique.

J'avais supposé le non-existence d'un index pour la colonne en question. Plus précisément, que le plan change due à la création de l'index avec des statistiques incomplètes. Ce qui est faux (voir la remarque du @pomalaix)
Mais, néanmoins ma remarque est incorrecte pour le cas général.