Re: MV complete refresh transactions

From: Ian Cary <ian.cary_at_ons.gsi.gov.uk>
Date: Thu, 11 Sep 2008 12:38:47 +0100
Message-ID: <OF7BCC24C9.FE0E8B06-ON802574C1.003F3B64-802574C1.003FFA87@ons.gsi.gov.uk>


The refresh behaviour in 10g changed from truncate/ insert to delete/insert even for a single table refresh, largely for the same reasons mentioned in the group refresh.
To preserve 9i behaviour you need to set the parameter atomic_refresh to false e.g.

exec dbms_mview.refresh('MVIEW_NAME',atomic_refresh=>false);

Cheers,

Ian

|---------+----------------------------->

| | jkstill_at_gmail.com |
| | Sent by: |
| | oracle-l-bounce_at_fr|
| | eelists.org |
| | |
| | |
| | 10/09/2008 20:08 |
| | Please respond to |
| | jkstill |
| | |
|---------+-----------------------------> >--------------------------------------------------------------------------------------------------------------| | | | To: roman.podshivalov_at_gmail.com | | cc: oracle-l_at_freelists.org | | Subject: Re: MV complete refresh transactions | >--------------------------------------------------------------------------------------------------------------|

On Tue, Sep 9, 2008 at 3:47 PM, Roman Podshivalov < roman.podshivalov_at_gmail.com> 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


This email was received from the INTERNET and scanned by the Government
Secure Intranet anti-virus service supplied by Cable&Wireless in
partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) In
case of problems, please call your organisation’s IT Helpdesk.
Communications via the GSi may be automatically logged, monitored and/or
recorded for legal purposes.

For the latest data on the economy and society consult National Statistics at http://www.statistics.gov.uk

*********************************************************************************


Please Note:  Incoming and outgoing email messages are routinely monitored for compliance with our policy on the use of electronic communications
*********************************************************************************


Legal Disclaimer  :  Any views expressed by the sender of this message are not necessarily those of the Office for National Statistics
*********************************************************************************


The original of this email was scanned for viruses by the Government Secure Intranet virus scanning service supplied by Cable&Wireless in partnership with MessageLabs. (CCTM Certificate Number 2007/11/0032.) On leaving the GSi this email was certified virus free.
Communications via the GSi may be automatically logged, monitored and/or recorded for legal purposes.
i0zX+n{+i^
Received on Thu Sep 11 2008 - 06:38:47 CDT

Original text of this message