Home » SQL & PL/SQL » SQL & PL/SQL » Materialized View refresh performance issue. (Oracle 11g R2)
Materialized View refresh performance issue. [message #663974] Thu, 29 June 2017 11:44 Go to next message
Manoj.Gupta.91
Messages: 239
Registered: March 2008
Location: Delhi
Senior Member
Hi All,

I've two databases. DB1 and DB2. There is a table in DB1 and I've created a Materialized View Log for the same on DB1.

In DB2 I've created DB Link for DB1.
In DB2 I've created a MV for the base table of DB1 as fast refresh.

Now comes the problem statement.

Whenever I need to alter base table to add new column or to modify column data length MV refresh in DB2 takes more than an hour to refresh. Because there are millions of records in base table.

In this situation I want MV to retain to it's normal behaviour as I've not done any change in data so final result will be same after base table alteration. Although I want MV to refresh all those records which were there in MV Log before base table alteration. i.e. I don't want to miss anything.

Please help me how to can handle this situation.

Thanks & Regards
Manoj
Re: Materialized View refresh performance issue. [message #663975 is a reply to message #663974] Thu, 29 June 2017 13:57 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

DDL should not happen often and only during maintenance period, in this case, drop and recreate the mview and mview log.

[Updated on: Thu, 29 June 2017 13:57]

Report message to a moderator

Re: Materialized View refresh performance issue. [message #663976 is a reply to message #663974] Thu, 29 June 2017 14:10 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It is unwise to try to change a tire on a car while it is still traveling down the road.
If/when you have DDL changes, it should necessitate concurrent application code change;
which should only occur during maintenance downtime.
Re: Materialized View refresh performance issue. [message #663977 is a reply to message #663974] Fri, 30 June 2017 01:20 Go to previous message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Create the materialized view using a pre-created container table with the ON PREBUILT TABLE clause. Then if you need to do a DDL you can drop the mview, apply the DDL to the table, then recreate the mview. No problem.

By the way, I wish you would not say "record" when you mean "row".
Previous Topic: How to generate JSON
Next Topic: What is the error ora-20914 pre insert I am getting this error in the forms?
Goto Forum:
  


Current Time: Thu Mar 28 14:51:58 CDT 2024