Home » SQL & PL/SQL » SQL & PL/SQL » MV Refresh On Commit (Oracle Database 11g Enterprise Edition Release - 64bit Production)
MV Refresh On Commit [message #676391] Thu, 06 June 2019 07:03 Go to next message
Messages: 396
Registered: October 2011
Location: Hyderabad
Senior Member

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 Go to previous message
Bill B
Messages: 1868
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

Previous Topic: How to pass input parameter(Inside a procedure) with more than 4000 characters as comma separated va
Next Topic: Using the Sql query to find employees who were recruited on the same day or on neighboring days.
Goto Forum:

Current Time: Mon Jun 24 18:39:49 CDT 2019