[9i] hint dans une vue non pris en compte

Bonjour,

Afin de forcer le plan d'exécution d'une de mes vues, j'ai écrit un HINT. Le problème est le suivant :

1) Lorsque j'exécute directement le code de la vue, le HINT semble bien être pris en compte, étant donné le plan d'exécution que j'obtiens
2) Par compte, lorsque je fais un "select * from ma_vue", le HINT n'est plus pris en compte, et le plan d'exécution est pourri, plein de "TABLE ACCESS FULL".

Or, j'aurais eu tendance à dire que ces deux requêtes (exécution directe du code de ma vue, ou "select * from ma_vue") auraient dû avoir le même plan d'exécution?

Ai-je loupé quelque chose?

Merci pour vos réponses.

Code de ma vue :

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 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89

CREATE OR REPLACE VIEW V_PRESTATION_PROVISION_CTP AS SELECT /*+ index (ve1 valelmmgc_uk1) index (ve2 valelmmgc_uk1) index (ve3 valelmmgc_uk1) index (ve4 valelmmgc_uk1) index (ve5 valelmmgc_uk1) index (ve6 valelmmgc_uk1) index (ve7 valelmmgc_uk1) index (ve8 valelmmgc_uk1) index (ve9 valelmmgc_uk1) index (ve10 valelmmgc_uk1) index (ve11 valelmmgc_uk1) index (ve12 valelmmgc_uk1) index (ve13 valelmmgc_uk1) index (ve14 valelmmgc_uk1) index (ve15 valelmmgc_uk1) index (ve16 valelmmgc_uk1) index (ve17 valelmmgc_uk1) index (ve18 valelmmgc_uk1)*/ rc1.idtrsucal ID , sa1.idtsga id_affilie , TO_DATE (NVL (ve1.valrmp, ve1.valelm), 'dd.mm.yyyy') date_calcul , TO_NUMBER (NVL (ve2.valrmp, ve2.valelm)) prime_annuelle , TO_NUMBER (NVL (ve3.valrmp, ve3.valelm)) capital_annuel , TO_NUMBER (NVL (ve4.valrmp, ve4.valelm)) + TO_NUMBER (NVL (ve5.valrmp, ve5.valelm)) capital_libere , TO_NUMBER (NVL (ve4.valrmp, ve4.valelm)) coti_capital_libere , TO_NUMBER (NVL (ve5.valrmp, ve5.valelm)) tft_in_capital_libere , TO_NUMBER (NVL (ve6.valrmp, ve6.valelm)) capital_terme_simule , TO_NUMBER (NVL (ve7.valrmp, ve7.valelm)) + TO_NUMBER (NVL (ve8.valrmp, ve8.valelm)) provision_fin_annee , TO_NUMBER (NVL (ve7.valrmp, ve7.valelm)) coti_provision_fin_annee , TO_NUMBER (NVL (ve8.valrmp, ve8.valelm)) tft_in_provision_fin_annee , TO_NUMBER (NVL (ve9.valrmp, ve9.valelm)) + TO_NUMBER (NVL (ve10.valrmp, ve10.valelm)) pb_capital_annuel , TO_NUMBER (NVL (ve9.valrmp, ve9.valelm)) coti_pb_capital_annuel , TO_NUMBER (NVL (ve10.valrmp, ve10.valelm)) tft_in_pb_capital_annuel , TO_NUMBER (NVL (ve11.valrmp, ve11.valelm)) + TO_NUMBER (NVL (ve12.valrmp, ve12.valelm)) pb_capital_acquis , TO_NUMBER (NVL (ve11.valrmp, ve11.valelm)) coti_pb_capital_acquis , TO_NUMBER (NVL (ve12.valrmp, ve12.valelm)) tft_in_pb_capital_acquis , TO_NUMBER (NVL (ve13.valrmp, ve13.valelm)) + TO_NUMBER (NVL (ve14.valrmp, ve14.valelm)) pb_provision_fin_annee , TO_NUMBER (NVL (ve13.valrmp, ve13.valelm)) coti_pb_provision_fin_annee , TO_NUMBER (NVL (ve14.valrmp, ve14.valelm)) tft_in_pb_provision_fin_annee , TO_NUMBER (NVL (ve15.valrmp, ve15.valelm)) + TO_NUMBER (NVL (ve16.valrmp, ve16.valelm)) pb_an_prec_pm_fin_annee , TO_NUMBER (NVL (ve15.valrmp, ve15.valelm)) coti_pb_an_prec_pm_fin_annee , TO_NUMBER (NVL (ve16.valrmp, ve16.valelm)) tft_in_pb_an_prec_pm_fin_annee , TO_NUMBER (NVL (ve17.valrmp, ve17.valelm)) age_terme , TO_NUMBER (NVL (ve18.valrmp, ve18.valelm)) cotisation_annuelle FROM resultat_calcul rc1 JOIN jeu_donnee jd1 ON (rc1.idtjeudon = jd1.idtjeudon ) JOIN calcul_plan cp1 ON (rc1.idtcalpln = cp1.idtcalpln) JOIN signaletique_affilie_ctp sa1 ON (jd1.idtaflpln = sa1.idtaflpln) LEFT JOIN (valeur_element_mgc ve1 JOIN element_mgc el1 ON ve1.idtelm = el1.idtelm AND UPPER (el1.codcaltqe) = mgckggen.get_par ('k_ctpdtecal')) ON ve1.idtcalpln = cp1.idtcalpln LEFT JOIN (valeur_element_mgc ve2 JOIN element_mgc el2 ON ve2.idtelm = el2.idtelm AND UPPER (el2.codcaltqe) = mgckggen.get_par ('k_ctpprmann')) ON ve2.idtrsucal = rc1.idtrsucal LEFT JOIN (valeur_element_mgc ve3 JOIN element_mgc el3 ON ve3.idtelm = el3.idtelm AND UPPER (el3.codcaltqe) = mgckggen.get_par ('k_ctpcapann')) ON ve3.idtrsucal = rc1.idtrsucal LEFT JOIN (valeur_element_mgc ve4 JOIN element_mgc el4 ON ve4.idtelm = el4.idtelm AND UPPER (el4.codcaltqe) = mgckggen.get_par ('k_ctpcticaplbe')) ON ve4.idtrsucal = rc1.idtrsucal LEFT JOIN (valeur_element_mgc ve5 JOIN element_mgc el5 ON ve5.idtelm = el5.idtelm AND UPPER (el5.codcaltqe) = mgckggen.get_par ('k_ctptincaplbe')) ON ve5.idtrsucal = rc1.idtrsucal   [etc...]   LEFT JOIN (valeur_element_mgc ve18 JOIN element_mgc el18 ON ve18.idtelm = el18.idtelm AND UPPER (el18.codcaltqe) = mgckggen.get_par ('k_ctpctiann')) ON ve18.idtrsucal = rc1.idtrsucal;

---

Plan d'exécution du gros SELECT de la vue (avec hint) :

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 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop   SELECT STATEMENT Optimizer Mode=CHOOSE 24 K 20909 HASH JOIN OUTER 24 K 4 M 20909 HASH JOIN OUTER 24 K 4 M 19735 HASH JOIN OUTER 24 K 4 M 18563 HASH JOIN OUTER 24 K 3 M 17392 HASH JOIN OUTER 24 K 3 M 16223 HASH JOIN OUTER 24 K 3 M 15056 HASH JOIN OUTER 24 K 3 M 13891 HASH JOIN OUTER 24 K 3 M 12727 HASH JOIN OUTER 24 K 2 M 11565 HASH JOIN OUTER 24 K 2 M 10405 HASH JOIN OUTER 24 K 2 M 9247 HASH JOIN OUTER 24 K 2 M 8090 HASH JOIN OUTER 24 K 1 M 6935 HASH JOIN OUTER 24 K 1 M 5782 HASH JOIN OUTER 24 K 1 M 4630 HASH JOIN OUTER 24 K 1 M 3480 HASH JOIN OUTER 24 K 1 M 2332 HASH JOIN OUTER 24 K 949 K 1186 HASH JOIN 24 K 730 K 41 TABLE ACCESS FULL MGC.SIGNALETIQUE_AFFILIE_CTP 5 K 44 K 4 HASH JOIN 29 K 641 K 35 TABLE ACCESS FULL MGC.JEU_DONNEE 30 K 237 K 13 HASH JOIN 29 K 407 K 16 INDEX FAST FULL SCAN MGC.CALPLN_PK 1 K 3 K 2 TABLE ACCESS FULL MGC.RESULTAT_CALCUL 29 K 320 K 13 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 41 K 1136 NESTED LOOPS 4 K 110 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 55 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31 VIEW 4 K 32 K 1136 NESTED LOOPS 4 K 101 K 1136 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS BY INDEX ROWID MGC.VALEUR_ELEMENT_MGC 4 K 46 K 1135 INDEX RANGE SCAN MGC.VALELMMGC_UK1 4 K 31

---

Plan d'exécution de la requête "SELECT * FROM v_prestation_provision_ctp :

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 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111

Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop   SELECT STATEMENT Optimizer Mode=CHOOSE 24 K 13565 HASH JOIN OUTER 24 K 4 M 13565 HASH JOIN OUTER 24 K 4 M 12799 HASH JOIN OUTER 24 K 4 M 12035 HASH JOIN OUTER 24 K 3 M 11272 HASH JOIN OUTER 24 K 3 M 10511 HASH JOIN OUTER 24 K 3 M 9752 HASH JOIN OUTER 24 K 3 M 8995 HASH JOIN OUTER 24 K 3 M 8239 HASH JOIN OUTER 24 K 2 M 7485 HASH JOIN OUTER 24 K 2 M 6733 HASH JOIN OUTER 24 K 2 M 5983 HASH JOIN OUTER 24 K 2 M 5234 HASH JOIN OUTER 24 K 1 M 4487 HASH JOIN OUTER 24 K 1 M 3742 HASH JOIN OUTER 24 K 1 M 2998 HASH JOIN OUTER 24 K 1 M 2256 HASH JOIN OUTER 24 K 1 M 1516 HASH JOIN OUTER 24 K 949 K 778 HASH JOIN 24 K 730 K 41 TABLE ACCESS FULL MGC.SIGNALETIQUE_AFFILIE_CTP 5 K 44 K 4 HASH JOIN 29 K 641 K 35 TABLE ACCESS FULL MGC.JEU_DONNEE 30 K 237 K 13 HASH JOIN 29 K 407 K 16 INDEX FAST FULL SCAN MGC.CALPLN_PK 1 K 3 K 2 TABLE ACCESS FULL MGC.RESULTAT_CALCUL 29 K 320 K 13 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1   [etc...]   VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 41 K 728 HASH JOIN 4 K 110 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 11 M 726 VIEW 4 K 32 K 728 HASH JOIN 4 K 101 K 728 TABLE ACCESS BY INDEX ROWID MGC.ELEMENT_MGC 1 12 1 INDEX UNIQUE SCAN MGC.ELMMGC_UK2 1 TABLE ACCESS FULL MGC.VALEUR_ELEMENT_MGC 993 K 9 M 726

---

si je ne m'abuse, le hint doit être au niveau du select :

Code:

---

1 2

SELECT /*+ index (ve1 valelmmgc_uk1) index (ve2 valelmmgc_uk1) index (ve3 valelmmgc_uk1) index (ve4 valelmmgc_uk1) index (ve5 valelmmgc_uk1) index (ve6 valelmmgc_uk1) index (ve7 valelmmgc_uk1) index (ve8 valelmmgc_uk1) index (ve9 valelmmgc_uk1) index (ve10 valelmmgc_uk1) index (ve11 valelmmgc_uk1) index (ve12 valelmmgc_uk1) index (ve13 valelmmgc_uk1) index (ve14 valelmmgc_uk1) index (ve15 valelmmgc_uk1) index (ve16 valelmmgc_uk1) index (ve17 valelmmgc_uk1) index (ve18 valelmmgc_uk1)*/ * FROM v_prestation_provision_ctp;

---

mais c'est pas bon du tout de mettre autant de hints. Tu as soit :
- un problème de stats
- une table qui met le boxon (MGC.SIGNALETIQUE_AFFILIE_CTP par exemple)
- des critères de restriction manquant
- les UPPER qui empêchent d'utiliser des indexes pertinent. :?

Je suis d'accord sur le principe, mais avec cette vue atroce, j'ai toujours été obligé de forcer le plan. Sinon :

- les stats sont é jour : recomputées ce matin
- les critères de restriction aussi : le résultat de la requête est tout à fait bon
- les UPPER sont volontaires, car il y a une colonne indexée sur UPPER(colonne)

essaye en ayant désactiver le hash au préalable éventuellement :

Code:

---

alter session set hash_join_enabled=false;

---

essaye le hint FIRST_ROWS aussi ;)