I have a view based
on the query below which is executed in a reasonable way on the master site but
this same view takes hours on the snapshot site. Any idea
?
CREATE OR
REPLACE VIEW V_ENTITE_EMETTEUR2 AS
select /*+ ORDERED USE_NL(E A SI SY O LF MO AM MR ST) INDEX(E I_REF_EMETTEUR)*/
E.COMP_EMETTEUR, E.NOM_STATION, E.REF_EMETTEUR, E.PIRE, E.PIRE_CALCULEE,
E.HAUT_SOL_ANT, E.AZIMUT, E.ELEVATION, E.OWNER, E.CODE_INTERNE_TS,
E.CODE_INTERNE_TC, E.DEP_CODE_GESTION, SY.COMP_SYSTEME, SY.NOM_SYSTEME,
O.COMP_OPERATEUR, O.CODE_OPERATEUR, SI.COMP_SITE, SI.REF_SITE, SI.NOM_SITE,
A.COMP_ANTENNE, A.NOM_ANT, MO.REF_MODU, AM.REF_AMPLI, MR.REF_MAT_RECEPT, LF.CODE_ETAT, ST.CODE
from t_station E, t_antena A, t_site SI, t_system SY, t_operator O, t_lf_states LF,
v_station_modu MO, v_station_ampli AM, v_station_recept MR, t_status ST
where E.COMP_SYSTEME = SY.COMP_SYSTEME and E.COMP_OPERATEUR = O.COMP_OPERATEUR
and E.COMP_SYSTEME = O.COMP_SYSTEME and E.COMP_SITE = SI.COMP_SITE
and E.COMP_ANTENNE = A.COMP_ANTENNE and E.COMP_EMETTEUR = MO.COMP_EMETTEUR(+)
and E.COMP_EMETTEUR = AM.COMP_EMETTEUR(+) and E.COMP_EMETTEUR = MR.COMP_EMETTEUR(+)
and not (E.SUPPR_A_ANALYSER_FAE is not null and E.SUPPR_A_ANALYSER_FAE = 'O')
and E.TYPE_STATION = LF.COMP_ETATS and E.COMP_STATUS = ST.COMP_STATUS(+)
and exists (select 1 from v_mygroup_users V
where E.owner=user_name and rownum<=1);
select /*+ ORDERED USE_NL(E A SI SY O LF MO AM MR ST) INDEX(E I_REF_EMETTEUR)*/
E.COMP_EMETTEUR, E.NOM_STATION, E.REF_EMETTEUR, E.PIRE, E.PIRE_CALCULEE,
E.HAUT_SOL_ANT, E.AZIMUT, E.ELEVATION, E.OWNER, E.CODE_INTERNE_TS,
E.CODE_INTERNE_TC, E.DEP_CODE_GESTION, SY.COMP_SYSTEME, SY.NOM_SYSTEME,
O.COMP_OPERATEUR, O.CODE_OPERATEUR, SI.COMP_SITE, SI.REF_SITE, SI.NOM_SITE,
A.COMP_ANTENNE, A.NOM_ANT, MO.REF_MODU, AM.REF_AMPLI, MR.REF_MAT_RECEPT, LF.CODE_ETAT, ST.CODE
from t_station E, t_antena A, t_site SI, t_system SY, t_operator O, t_lf_states LF,
v_station_modu MO, v_station_ampli AM, v_station_recept MR, t_status ST
where E.COMP_SYSTEME = SY.COMP_SYSTEME and E.COMP_OPERATEUR = O.COMP_OPERATEUR
and E.COMP_SYSTEME = O.COMP_SYSTEME and E.COMP_SITE = SI.COMP_SITE
and E.COMP_ANTENNE = A.COMP_ANTENNE and E.COMP_EMETTEUR = MO.COMP_EMETTEUR(+)
and E.COMP_EMETTEUR = AM.COMP_EMETTEUR(+) and E.COMP_EMETTEUR = MR.COMP_EMETTEUR(+)
and not (E.SUPPR_A_ANALYSER_FAE is not null and E.SUPPR_A_ANALYSER_FAE = 'O')
and E.TYPE_STATION = LF.COMP_ETATS and E.COMP_STATUS = ST.COMP_STATUS(+)
and exists (select 1 from v_mygroup_users V
where E.owner=user_name and rownum<=1);
-----------------------------------------------------------------------------------------
Abiy Alemu
Phone: (+33) 1-58 17 04 56
Fax: (+33) 1-58 17 04 54
Phone: (+33) 1-58 17 04 56
Fax: (+33) 1-58 17 04 54
