Home » Server Options » Replication » Improve Materialized View Refresh
Improve Materialized View Refresh [message #109488] Thu, 24 February 2005 19:19 Go to next message
T. Sri Sai Krishna
Messages: 17
Registered: May 2002
Junior Member

We have some Materialized views created which are getting created from several tables. The refresh for these Mviews has been set like this

create materialized view MY_MV
refresh force on demand
start with to_date('25-02-2005 13:33:54', 'dd-mm-yyyy hh24:mi:ss') next sysdate + 1
enable query rewrite

This refresh first of all takes a long time and sometimes doesn't happen.

For a particular MV we are using almost 13 tables with a UNION also involved (these tables are live transactions tables). Could anyone suggest an alternative to refresh these MVs more quickly. Or is there a better approach than this. Any help from you guys will be of immense help.

Thanks & regards
Sri Sai
Re: Improve Materialized View Refresh [message #109619 is a reply to message #109488] Sun, 27 February 2005 05:43 Go to previous messageGo to next message
Frank Naude
Messages: 4569
Registered: April 1998
Senior Member
You can try to create separate materialized views for each of these tables (using fast refresh). After that, create a UNION ALL-view to combine them again.

Best regards.

Re: Improve Materialized View Refresh [message #153101 is a reply to message #109488] Thu, 29 December 2005 01:20 Go to previous message
Messages: 4
Registered: December 2005
Location: Down Under
Junior Member
hey man

im having the same issue but im dealing with 27 live tables ...

from what i have found out researching is that by doing a fast refresh it only increments the materialized view with the latest changes but you need to create a materialized view log on each of the base tables if you want to do a refresh fast ...

another way that i thought of was to create a trigger but im not too sure about this ... can you disable the refresh option and when a base table changes/commits it should trigger the trigger and update the materialized view ...

if you find a better solution, plz let me know ...

Previous Topic: Materialized View Refresh Problem
Next Topic: reduce the downtime in replicated environment
Goto Forum:

Current Time: Thu Jul 29 16:34:52 CDT 2021