Re: oracle materialized view - refesh occuring but base tables not being deleted

On Feb 15, 10:36 am, stre..._at_yahoo.co.uk wrote:
> hi,
>
> we have an application which uses materialized views. the refresh
> action (using fast refresh) executes without error but for some reason
> the base materialized views (<schema>.MLOG$_<table>) does not get
> deleted (we do a select count(1) from the base table). as i understand
> it the entries are supposed to be removed. a few additional peices of
> information:
> * the base tables are only used by one materialized view.
> * there have been problems with the refresh over the past few weeks
> failing until we increased the refesh interval (controlled by the
> application)
> * there are over 100,000,000 entries in the base table.
> i'm going to be investigating this in more detail myself over the next
> few days and will add anything i find.
> g

Always specify what version of oracle you are referring to. And read the docs:

For 9i:
http://download.oracle.com/docs/cd/B10501_01/server.920/a96520/mv.htm#32933

For 10g:
http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/basicmv.htm#sthref538

Read the section: "Restrictions on Fast Refresh on Materialized Views with Aggregates"

Note the restriction: "COUNT(*) must be specified." count(coumn_name) alone won't work if there are insert/deletes and updates happening.

HTH
Anurag Received on Fri Feb 15 2008 - 14:08:28 CST