Salut j'ai cette requète
CODE
SELECT /*+
INDEX (TFP_CONTRAT_PIVOT_B)
INDEX (TFP_INDIVIDU_PIVOT_B)
INDEX (T_C_DT_EFFET_CNT_TMP)
INDEX (T_COUVERTURE_MAJ)
*/
TFP_CONTRAT_PIVOT_B.DT_CTR_EFF,
TFP_CONTRAT_PIVOT_B.RF_CTR_REF,
TFP_CONTRAT_PIVOT_B.DT_CTR_EFF_CLO,
TFP_CONTRAT_PIVOT_B.CD_LOP,
TFP_CONTRAT_PIVOT_B.DT_CTR_REF,
TFP_CONTRAT_PIVOT_B.NO_UGT,
TFP_CONTRAT_PIVOT_B.DT_ELP_DEB,
TFP_CONTRAT_PIVOT_B.CD_TY_PRE,
TFP_CONTRAT_PIVOT_B.CD_ELP_FRA,
TFP_CONTRAT_PIVOT_B.DT_ELP_FIN,
TFP_CONTRAT_PIVOT_B.CD_ELP_MOT_FIN,
TFP_CONTRAT_PIVOT_B.NO_ELP_JOUR_PO,
TFP_CONTRAT_PIVOT_B.CD_CTR_COL,
TFP_CONTRAT_PIVOT_B.CD_GP_ASS,
TFP_CONTRAT_PIVOT_B.CRIT_ART_TVL,
TFP_CONTRAT_PIVOT_B.CRIT_RFS_MED,
TFP_CONTRAT_PIVOT_B.CRIT_EXO_TKT,
TFP_CONTRAT_PIVOT_B.CRIT_ADH_TAR,
TFP_CONTRAT_PIVOT_B.NO_CTR_ANETO,
T_COUVERTURE_MAJ.CD_TIN,
TFP_INDIVIDU_PIVOT_B.NO_IND,
TFP_INDIVIDU_PIVOT_B.MATRICULE_SS_INDIVIDU,
TFP_INDIVIDU_PIVOT_B.NO_IND_CLE,
T_C_DT_EFFET_CNT_TMP.NO_CON,
T_C_DT_EFFET_CNT_TMP.NUMSEQ,
T_C_DT_EFFET_CNT_TMP.DT_EFFET,
T_C_DT_EFFET_CNT_TMP.IND_CTR_CJT
FROM TFP_CONTRAT_PIVOT_B,
TFP_INDIVIDU_PIVOT_B,
T_C_DT_EFFET_CNT_TMP,
T_COUVERTURE_MAJ
WHERE TFP_CONTRAT_PIVOT_B.NO_CTR = T_C_DT_EFFET_CNT_TMP.NO_CON
AND T_COUVERTURE_MAJ.DEBUT_DE_DROIT = T_C_DT_EFFET_CNT_TMP .DT_EFFET
AND T_COUVERTURE_MAJ.NO_CTR =TFP_CONTRAT_PIVOT_B.NO_CTR
AND T_COUVERTURE_MAJ.NO_IND =TFP_INDIVIDU_PIVOT_B.NO_IND
AND T_COUVERTURE_MAJ.NO_CTR = T_C_DT_EFFET_CNT_TMP.NO_CON
/CODE
Le problème étant que je n'arrive pas à la faire descendre en dessous de 11064 de coût. Le plan d'exécution est le suivant:
CODE
Operation Object Name Rows Bytes Cost Object Node In/Out PStart PStop
SELECT STATEMENT Optimizer Mode=CHOOSE 1 11064
NESTED LOOPS 1 202 11064
NESTED LOOPS 1 174 11063
HASH JOIN 78 3 K 11051
TABLE ACCESS BY INDEX ROWID TRANSCO.T_C_DT_EFFET_CNT_TMP 212 K 4 M 235
INDEX FULL SCAN TRANSCO.T_C_DT_EFFET_CNT_TMP_IDX1 1 480
INDEX FULL SCAN TRANSCO.SYS_C007061 5 M 140 M 49542
TABLE ACCESS BY INDEX ROWID TRANSCO.TFP_CONTRAT_PIVOT_B 1 124 1
INDEX UNIQUE SCAN TRANSCO.PK_TFP_CONTRAT_B 1
TABLE ACCESS BY INDEX ROWID TRANSCO.TFP_INDIVIDU_PIVOT_B 1 28 1
INDEX UNIQUE SCAN TRANSCO.PK_TFP_INDIVIDU_B 1
/CODE
La version d'Oracle est 9i r 2.0.7.0
PS Désolé pour les balises CODE mais je ne sais pas comment les mettre et je suis également preneur de cette information.
Vincent
Partager