Materialized View refresh performance issue. [message #663974] |
Thu, 29 June 2017 11:44 |
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 #663977 is a reply to message #663974] |
Fri, 30 June 2017 01:20 |
John Watson
Messages: 8938 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".
|
|
|