Re: MV complete refresh transactions
- From: Ian Cary <ian.cary@xxxxxxxxxxxxxx>
- To: jkstill@xxxxxxxxx
- Date: Thu, 11 Sep 2008 12:38:47 +0100
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@xxxxxxxxx |
| | Sent by: |
| | oracle-l-bounce@fr|
| | eelists.org |
| | |
| | |
| | 10/09/2008 20:08 |
| | Please respond to |
| | jkstill |
| | |
|---------+----------------------------->
>--------------------------------------------------------------------------------------------------------------|
|
|
| To: roman.podshivalov@xxxxxxxxx
|
| cc: oracle-l@xxxxxxxxxxxxx
|
| Subject: Re: MV complete refresh transactions
|
>--------------------------------------------------------------------------------------------------------------|
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
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.
Other related posts:
