DomainKey-Signature: a=rsa-sha1; c=nofws;        d=gmail.com; s=gamma;        h=message-id:date:from:to:subject:cc:in-reply-to:mime-version         :content-type:references;        b=beKYQl0jr4gvbvIA4+oyDnp0Q6IYyeN9yU6Y6pD+s8NtWYrogqn5YT9KK/Ka/Cfnnh         DsBI0vToy6cFt7SXhKSf1gWT2XWEepXf1LyMWO6TtQrEfCwG0mNy1tVvF/l7AuKXjboO         rG4lADkCS8N7qaZEMfpxfS7XVks4VP2ilMaS8=
Message-ID: <55f303590809091547x35f6fb02y2a27108f0799a799@mail.gmail.com>
Date: Tue, 9 Sep 2008 18:47:33 -0400
From: "Roman Podshivalov" <roman.podshivalov@gmail.com>
To: jkstill@gmail.com
Subject: Re: MV complete refresh transactions
Cc: "Oracle-L Freelists" <oracle-l@freelists.org>
In-Reply-To: <bf46380809091516v220e52f9q198b31f8664a84f0@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative; boundary="----=_Part_81355_15581163.1221000453867"
References: <bf46380809091516v220e52f9q198b31f8664a84f0@mail.gmail.com>
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: roman.podshivalov@gmail.com
------=_Part_81355_15581163.1221000453867
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

Jared,

You run into very well documented feature. Answer to your question is a last
sentence of the paragraph.

http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/reptrouble.htm#8549

Complete refreshes of a single materialized view internally use the
TRUNCATEfeature 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.

--romas


On Tue, Sep 9, 2008 at 6:16 PM, Jared Still <jkstill@gmail.com> wrote:

> Just ran into something unexpected.
>
> When doing a complete refresh on a materialized view, the deletion of the
> rows
> appears to be a different transaction than the insertion of new rows.
>
> This is not an issue that is affected by ATOMIC_REFRESH => [TRUE|FALSE]
>
> If I do a complete refresh on an MV in one session, a query against the MV
> from
> another session returns 0 rows for a period of time while the data is being
> refreshed.
>
> This is on 9.2.0.6 on Linux.
>
> Until and unless I find a workaround for MV, this is being built as a
> regular table,
> with a procedure using a transaction to delete and insert rows every 15
> minutes.
>
> The manual method works as expected, no interruptions from user queries.
>
> The refresh must be complete BTW, no way around it.
>
> Anyone know a way around the MV issue?
>
> The manual method works, it just is somewhat of a kludge.
>
> --
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
>
>

------=_Part_81355_15581163.1221000453867
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<div dir="ltr">Jared, <br><br>You run into very well documented feature. Answer to your question is a last sentence of the paragraph.<br><br><a href="http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/reptrouble.htm#8549">http://download.oracle.com/docs/cd/B10501_01/server.920/a96567/reptrouble.htm#8549</a><br>
<br>Complete refreshes of a single materialized view internally use the <code>TRUNCATE</code>
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 <code>TRUNCATE</code> feature.<br><br>--romas<br><br><br><div class="gmail_quote">On Tue, Sep 9, 2008 at 6:16 PM, Jared Still <span dir="ltr">&lt;<a href="mailto:jkstill@gmail.com">jkstill@gmail.com</a>&gt;</span> wrote:<br>
<blockquote class="gmail_quote" style="border-left: 1px solid rgb(204, 204, 204); margin: 0pt 0pt 0pt 0.8ex; padding-left: 1ex;"><div dir="ltr">Just ran into something unexpected.&nbsp; <br><br>When doing a complete refresh on a materialized view, the deletion of the rows<br>
appears to be a different transaction than the insertion of new rows.<br><br>This is not an issue that is affected by ATOMIC_REFRESH =&gt; [TRUE|FALSE]<br>
<br>If I do a complete refresh on an MV in one session, a query against the MV from<br>another session returns 0 rows for a period of time while the data is being refreshed.<br><br>This is on <a href="http://9.2.0.6" target="_blank">9.2.0.6</a> on Linux.<br>

<br>Until and unless I find a workaround for MV, this is being built as a regular table,<br>with a procedure using a transaction to delete and insert rows every 15 minutes.<br><br>The manual method works as expected, no interruptions from user queries.<br>

<br>The refresh must be complete BTW, no way around it.<br><br>Anyone know a way around the MV issue?<br><br>The manual method works, it just is somewhat of a kludge.<br clear="all"><br>-- <br>Jared Still<br>Certifiable Oracle DBA and Part Time Perl Evangelist<br>

<br>
</div>
</blockquote></div><br></div>

------=_Part_81355_15581163.1221000453867--
--
http://www.freelists.org/webpage/oracle-l