Query not using Materialized Views

From: Harvinder Singh <Harvinder.Singh_at_MetraTech.com>
Date: Thu, 15 Nov 2001 11:29:49 -0800
Message-ID: <F001.003C689E.20011115111812@fatcity.com>

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: Harvinder.Singh_at_MetraTech.com

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: ListGuru_at_fatcity.com (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). Received on Thu Nov 15 2001 - 13:29:49 CST