MV Refresh On Commit [message #676391] |
Thu, 06 June 2019 07:03 |
|
saipradyumn
Messages: 419 Registered: October 2011 Location: Hyderabad
|
Senior Member |
|
|
Hi
We have one materialized view (ABC) which was configured to be refreshed on DEMAND.This ABC will be refreshed at the end of the day by using one procedure.There is one more count MV (ABC_COUNT) which configured to be refresh ON COMMIT;
When the refresh was happening,first its deleting the total data from MV and re populating the fresh data.
There are some others jobs are executing the at the same time which depends on the same MV few times it leads to missing of some records.
In order to resolve this issue I want to depend on the ABC_COUNT mv to get the exact count.
Here my question was what will be the result of the ABC_COUNT mv when the actual ABC mv refresh was happening?
Is there any change to get the ZERO count just like ZERO results in MV ?
|
|
|
Re: MV Refresh On Commit [message #676511 is a reply to message #676391] |
Thu, 13 June 2019 07:37 |
Bill B
Messages: 1971 Registered: December 2004
|
Senior Member |
|
|
Having a refresh do a truncate or a delete/insert/commit sequence is easy to do.
To refresh your Mview of ABC the following procedure would do a truncate
DBMS_SNAPSHOT.REFRESH (list => 'ABC', method => 'C',atomic_refresh => false);
To do a refresh that will delete/inset/commit so the table is never empty them do
DBMS_SNAPSHOT.REFRESH (list => 'ABC', method => 'C',atomic_refresh => true);
There is NO need for your count mview
[Updated on: Thu, 13 June 2019 07:38] Report message to a moderator
|
|
|
|