Materialized View fast refresh is not fast

From: joyce <joyce.cronin_at_usa.xerox.com>
Date: 7 Oct 2002 13:44:30 -0700
Message-ID: <746f1321.0210071244.290282b6@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 Mon Oct 07 2002 - 15:44:30 CDT