Hi, I have just created a materialized view on 3 base tables involving only join condition. But oracle is not using this MV when resolving this query. Schema is analyzed with compute. query_rewrite_enable=true. What can be the possible cause.
Thanks -Harvinder create MATERIALIZED view t_vw_allrateschedules_po1 tablespace mv pctfree 0 storage(initial 1M next 1M pctincrease 0) build immediate refresh force on commit enable query rewrite as select tmInner.rowid as map_rowid,rschedInner.rowid as hed_rowid, teInner.rowid as effdate_rowid, tmInner.id_po as id_po, tmInner.id_paramtable as id_paramtable, tmInner.id_pi_instance as id_pi_instance, tmInner.id_pi_template as id_pi_template, tmInner.id_sub as id_sub, rschedInner.id_sched as id_sched, rschedInner.dt_mod as dt_mod, teInner.n_begintype as rs_begintype, teInner.n_beginoffset as rs_beginoffset, teInner.dt_start as rs_beginbase, teInner.n_endtype as rs_endtype, teInner.n_endoffset as rs_endoffset, teInner.dt_end as rs_endbase, rschedInner.id_pricelist as id_pricelist from t_pl_map tmInner,t_rsched rschedInner,t_effectivedate teInner where rschedInner.id_pricelist = tmInner.id_pricelist AND rschedInner.id_pt =tmInner.id_paramtable AND rschedInner.id_pi_template = tmInner.id_pi_template and teInner.id_eff_date = rschedInner.id_eff_date; create index idx_t_vw_allrateschedules_po1 on t_vw_allrateschedules_po1(map_rowid) tablespace mv; -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Harvinder Singh INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).