Re: Materialized View fast refresh is not fast

I would not say 420K small for a table. Not terriblly big, but not small either. My guess is that this is something that you will have to live with, as long as you use the "on commit" for the MV. It is nice to have the MV contineously updated, but there is a price to be paid. Entries will be logged in the MV logs when data is changed in the master table, regardless whether or not the data changed is referenced in the MV. Looks to be a limitation on the ORACLE part to me.

But, can you provide these:
1. How long does it take for the MV to do a fast refresh whenever you have an update?
2. How big is the MV?

• Jusung Yang

joyce.cronin_at_usa.xerox.com (joyce) wrote in message news:<746f1321.0210071244.290282b6_at_posting.google.com>...
> I am trying to use Materialized Views to improve report performance in
> an application that does both transaction processing and reporting.
> The MVs do improve report performance considerably. However, they add
> too much overhead to the transactions. I have created MVs that have
> only joins in them and I have selected only certain columns from each
> table to be in the MV. I created the MV logs so that they contain
> only data for columns I am interested in. Here is my create SQL:
>
> create materialized view log on acct
> pctfree 10
> tablespace mat_logs
> storage (initial 1m next 1m) with rowid
> (name)
> including new values
> ;
>
> create materialized view log on acct_hrchy
> pctfree 10
> tablespace mat_logs
> storage (initial 1m next 1m) with rowid
> (acct_id, parent_estblmt_id, end_dt)
> including new values
> ;
>
> create materialized view acct_acct_hrchy
> tablespace mat_views
> storage (initial 5m next 5m )
> nologging
> refresh fast on commit
> using master rollback segment rbsbig01
> enable query rewrite
> as
> select
> acct_hrchy.acct_id ah_acct_id,
> acct_hrchy.parent_estblmt_id ah_parent_estblmt_id,
> acct_hrchy.estblmt_id ah_estblmt_id,
> acct_hrchy.strt_dt ah_strt_dt, acct_hrchy.end_dt ah_end_dt,
> acct.acct_id a_acct_id, acct.name a_name,
> acct.rowid a_rowid, acct_hrchy.rowid acct_hrchy_rowid
> from acct, acct_hrchy
> where acct.acct_id = acct_hrchy.acct_id ;
>
> These are small tables - acct has 195 rows and acct_hrchy has 420K
> rows. There are 2 problems that I can't seem to get around. Changing
> a column that is not referenced in the MV causes a refresh to take
> place and the amount of time for the refresh. Any ideas to improve
> this would be greatly appreciated.
>
> This is running Oracle 8.1.7.3 on Solaris.
Received on Tue Oct 08 2002 - 19:44:10 CDT