Materialisation, estimation nombre de lignes

Salut !

J'utilise une clause WITH pour définir une sélection que je réutilise ensuite plusieurs fois dans la requête, notament pour joindre à de grosses tables.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

WITH t as ( SELECT ... FROM GRILLE, DETAIL_GRILLE, ... ...) SELECT * FROM t WHERE ... UNION ALL SELECT * FROM t LEFT OUTER JOIN GRILLE_ORDRE ON... LEFT OUTER JOIN ORDRE ON ... WHERE...

---

Bien, CBO reconnait que ma requête est très sélective, et me la matérialise : que du bonheur.
(Rows = 23)

Ensuite, lorsqu'avec cette toute petite matérialisation je devrais taper une centaine de fois au total sur des indexes uniques, il veut plus !
HASH JOIN + FTS au lieu de NL JOIN + INDEX UNIQUE SCAN.

Le plan ci-dessous montre que INSERT STATEMENT (matérialisation) => rows = 23
Table access full SYS_TEMP_0FD9D6670_245B0AE9 => 60K rows.

Est-ce que je n'ai rien compris à la signification d'un explain plan, où c'est CBO qui n'est pas consistant sur ces estimations ??

Je suis sous Oracle 9.2.0.8

Merci d'avance ;)

PS : quand je force le plan à coup de hints, le tout s'exécute 15 fois plus vite. Je voudrais surtout qu'on m'éclaire un peu (le summum serait qu'on me dise qu'il y a un patch, ou une option cachée de warior, ...)

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

----------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes |TempSpc| Cost | ------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 254K| 15M| | 29790 | | 2 | TEMP TABLE TRANSFORMATION | | | | | | | 1 | RECURSIVE EXECUTION | SYS_LE_2_0 | | | | | | 0 | INSERT STATEMENT | | 23 | 2139 | | 34 | | 1 | LOAD AS SELECT | | | | | | |* 2 | VIEW | | 23 | 2139 | | 34 | |* 3 | WINDOW SORT PUSHED RANK | | 23 | 1058 | | 34 | |* 4 | HASH JOIN | | 23 | 1058 | | 32 | | 5 | MERGE JOIN CARTESIAN | | 61 | 2379 | | 19 | | 6 | NESTED LOOPS | | 1 | 31 | | 7 | | 7 | TABLE ACCESS BY INDEX ROWID| GRILLE | 1 | 12 | | 1 | |* 8 | INDEX UNIQUE SCAN | PK_GRILLE | 1 | | | | |* 9 | TABLE ACCESS FULL | GRILLE | 1 | 19 | | 6 | | 10 | BUFFER SORT | | 61 | 488 | | 13 | |* 11 | TABLE ACCESS FULL | DETAIL_GRILLE | 61 | 488 | | 12 | | 12 | TABLE ACCESS FULL | DETAIL_GRILLE | 9188 | 64316 | | 12 | | 3 | SORT ORDER BY | | 254K| 15M| 43M| 29790 | | 4 | UNION-ALL | | | | | | |* 5 | VIEW | | 60830 | 3445K| | 113 | | 6 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6670_245B0AE9 | 60830 | 1960K| | 113 | |* 7 | VIEW | | 193K| 12M| | 20656 | |* 8 | WINDOW SORT PUSHED RANK | | 193K| 19M| 43M| 20656 | |* 9 | HASH JOIN OUTER | | 193K| 19M| 19M| 10565 | | 10 | VIEW | | 193K| 17M| | 9935 | |* 11 | HASH JOIN | | 193K| 19M| 17M| 9935 | | 12 | VIEW | | 193K| 15M| | 624 | |* 13 | HASH JOIN OUTER | | 193K| 14M| 4160K| 624 | |* 14 | VIEW | | 60830 | 3445K| | 113 | | 15 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6670_245B0AE9 | 60830 | 1960K| | 113 | | 16 | TABLE ACCESS FULL | GRILLE_ORDRE | 101K| 1789K| | 152 | |* 17 | TABLE ACCESS FULL | ORDRE | 2484K| 47M| | 6324 | | 18 | TABLE ACCESS FULL | PRODUITS | 648 | 7128 | | 12 | -------------------------------------------------------------------------------------------------------------

---

Salut pacman,

Avec seulement ces informations je trouve que c'est compliqué de donner une réponse. Mais, essayez d'utiliser un hint pour préciser la cardinalité de la sous-requête factorisé ainsi que ta demande de la matérialiser (ou utilise rownum dans la sous requête). Essayez aussi d'utiliser un niveau plus élevé du paramètre dynamic sampling au niveau de la session qui exécute la requête. Est-ce que dans ces conditions ton plan s'améliore ?

Salut Mnitu,

Effectivement, j'ai pas trop développé... désolé. Car en fait, pour simplifier, ma question est assez théorique :
Pourquoi la cardinalité calculée n'est pas la même pour un ensemble de lignes strictement équivalent à deux endroits différents ?
En clair, si le CBO détermine que la sous-requête factorisée renvoie 23 lignes, pourquoi par la suite estime-t-il se même truc SYS_TEMP_0FD9D6670_245B0AE9 a 60K lignes ?

Sinon, sur les tests que tu me proposes
- Le dynamic sampling est actuellement à 0.
- Le hint cardinality ne passe pas sur l'alias de la requête, ni lorsque je lui colle le nom de la matérialisation. (Alors que ça marche sur une table normale)
- Remplacer l'alias de la requête factorisée par la même avec rownum < 100 marche : COUNT STOPKEY => 99, ce qui donne presque le bon plan

Enfin comme dit, je voulais surtout savoir pourquoi il ne me sort pas le même nombre aux deux endroits !

(PS : et puis c'est surtout par curiosité, car entre temps j'ai passé le truc en collant un use_nl, ce qui règle laidement et efficacement le problème.)

Il m'a semblé lire je ne sais plus sur quel blog qu'il y avait un comportement singulier avec les CTE et Oracle 9i, comportement corrigé dans les versions ultérieures.
Mais je ne sais pas si celà a à voir avec votre problème.

L’analyse de l’explain plan que tu as fourni semble à indiquer une optimisation de la sous-requête seule suivi d’une optimisation de la requête générale mai s qui se fait avec la table temporaire ; regardez les id des étapes du plan.
Est-ce que la sous requête envoie effectivement seulement 23 lignes ou une valeur de cette ordre ?