Re: MV complete refresh transactions
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: "Roman Podshivalov" <roman.podshivalov@xxxxxxxxx>
- Date: Wed, 10 Sep 2008 12:08:27 -0700
On Tue, Sep 9, 2008 at 3:47 PM, Roman Podshivalov <
roman.podshivalov@xxxxxxxxx> wrote:
>
> Complete refreshes of a single materialized view internally use the
> TRUNCATE feature to increase speed and reduce rollback segment
> requirements. However, until the materialized view refresh is complete,
> users may temporarily see no data in the materialized view. Refreshes of
> multiple materialized views (for example, refresh groups) do not use the
> TRUNCATE feature.
>
>
I did some testing on this.
dbms_mview.refresh will remove the data and insert in two different
transactions.
eg. users will see no data while the MV is being refreshed.
dbms_refresh.refresh exhibits the same behavior with a refresh group, if
there
is only one MV in the group.
If the number of MV's in the group >1, then the delete and refresh are done
as part
of one transactions. eg. the user always sees data, normal consistent reads
work.
So, the trick is to use a refresh group, and add a small single row, ( or
maybe no row)
MV to the group, and the refresh will work as expected.
This again is on 9.2.0.6. Haven't tested the behavior on 10g, but it is
supposed to
provide data consistency by default on 10g.
--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
Other related posts:
