Re: MV complete refresh transactions
- From: "Jared Still" <jkstill@xxxxxxxxx>
- To: "Roman Podshivalov" <roman.podshivalov@xxxxxxxxx>
- Date: Tue, 9 Sep 2008 16:00:05 -0700
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@xxxxxxxxx> 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@xxxxxxxxx> 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
Other related posts:
