Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 386 invoked from network); 9 Sep 2008 18:02:07 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by air964.startdedicated.com with SMTP; 9 Sep 2008 18:02:07 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4C10594C743; Tue, 9 Sep 2008 19:02:07 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 21650-03; Tue, 9 Sep 2008 19:02:07 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id BAB9694C329; Tue, 9 Sep 2008 19:02:06 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 09 Sep 2008 19:00:07 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 044CF94C66B for ; Tue, 9 Sep 2008 19:00:07 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 20906-05 for ; Tue, 9 Sep 2008 19:00:06 -0400 (EDT) Received: from hs-out-0708.google.com (hs-out-0708.google.com [64.233.178.246]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id AEF4C94C669 for ; Tue, 9 Sep 2008 19:00:06 -0400 (EDT) Received: by hs-out-0708.google.com with SMTP id k27so657262hsc.2 for ; Tue, 09 Sep 2008 16:00:06 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=BULTzUMLfp6lZgXzZu9ANVpKXkDD9d0VeLjYKKWLJVs=; b=VV2FK2n8RfWBbNcZqH3L6x14PLcy3tGartNeXkEPk9jd+0m8hRewOAgcmumo0Ajkbg RmRLM6mMP9T8FZc+LD6kN0kOt+pIKBjaeNKH0aeqMUsmYUmXKelJUizR+yp9ylTW0r/I FdSuxMl7oCzLa2b/fC4sWwPw7ixpgUZ3YUr/E= 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=JYLV11+wDsAoJ1kpLqYuRHiOz2VWTcWraZTOcfJ4ctBdbR/7lirK38mdiGsBZL4inY DxdZn1kQGsfiXtBoUHSmXS1P4xN4pOJxnJIKwcFkLLz+DXXrQIMfqFIOgAuhheun+zjb DyQX9dYKTqBTbAo98puAM60Kyk5xdvVH22sr8= Received: by 10.151.14.5 with SMTP id r5mr770662ybi.240.1221001206022; Tue, 09 Sep 2008 16:00:06 -0700 (PDT) Received: by 10.151.38.7 with HTTP; Tue, 9 Sep 2008 16:00:05 -0700 (PDT) Message-ID: Date: Tue, 9 Sep 2008 16:00:05 -0700 From: "Jared Still" To: "Roman Podshivalov" Subject: Re: MV complete refresh transactions Cc: "Oracle-L Freelists" In-Reply-To: <55f303590809091547x35f6fb02y2a27108f0799a799@mail.gmail.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_9190_4148030.1221001206033" References: <55f303590809091547x35f6fb02y2a27108f0799a799@mail.gmail.com> X-archive-position: 10707 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: jkstill@gmail.com Precedence: normal Reply-to: jkstill@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_9190_4148030.1221001206033 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline I did consider that. Putting the MV in a refresh group and using dbms_refresh.refresh exhibited the same behavior. Off to a meeting now, but I will try adding a dummy MV to the group and see if the behavior changes. On Tue, Sep 9, 2008 at 3:47 PM, Roman Podshivalov < roman.podshivalov@gmail.com> wrote: > 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 > 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. > > --romas > > > > On Tue, Sep 9, 2008 at 6:16 PM, Jared Still 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 >> >> > -- Jared Still Certifiable Oracle DBA and Part Time Perl Evangelist ------=_Part_9190_4148030.1221001206033 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
I did consider that.

Putting the MV in a refresh group and using dbms_refresh.refresh
exhibited the same behavior.

Off to a meeting now, but I will try adding a dummy MV to the group and see
if the behavior changes.

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

--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





--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

------=_Part_9190_4148030.1221001206033-- -- http://www.freelists.org/webpage/oracle-l