Re: Refresh multiple MV's in a single transaction

(First sorry for accidentally replying only to Jared on his questions. Damn gmail)

Update.

After some more testing it turns out that what was tested was slightly invalid. 1 of the 3 MV's used was in fact a Fast refresh MV on which the other 2 were partially built. In that scenario my previous test results stand.

If that 1 Fast refresh MV is taken out and replaced with another MV similar to the other 2 (i.e. Complete refresh and built on the other MV that was taken out) then "atomic" = TRUE seems to work as advertised. I will do more testing but this appears to solve the problem. Incidentally, the read consistent (for the end user) refresh of MV's seems to only be available with the dbms_mview package, which can specify the atomic parameter. dbms_refresh doesn't have that option :

DBMS_REFRESH.MAKE (

   name                  IN    VARCHAR2
   { list                IN    VARCHAR2,
   | tab                 IN    DBMS_UTILITY.UNCL_ARRAY,}
   next_date             IN    DATE,
   interval              IN    VARCHAR2,
   implicit_destroy      IN    BOOLEAN         := false,
   lax                   IN    BOOLEAN         := false,
   job                   IN    BINARY INTEGER  := 0,
   rollback_seg          IN    VARCHAR2        := NULL,
   push_deferred_rpc     IN    BOOLEAN         := true,
   refresh_after_errors  IN    BOOLEAN         := false
   purge_option          IN    BINARY_INTEGER  := NULL,
   parallelism           IN    BINARY_INTEGER  := NULL,
   heap_size             IN    BINARY_INTEGER  := NULL);

If I run into any other issues with this I will update the list. Otherwise, consider it solved.

Special thanks to Jared Still for taking the time to test and much more.

Thanks,
Finn

On Thu, Nov 6, 2008 at 9:01 AM, Finn Jorgensen <finn.oracledba_at_gmail.com>wrote:

> Jared,
>
> >Are these complete refreshes?
>
> Yes. The MV's use the max function call and group by so they have to be.
>
> >How is it you know that the refresh is not taking place in a single
> transaction?
>
> I haven't had the time to do the testing myself so I'm relaying on a
> developer providing me with accurate information. He says he has 3 MV's that
> take about a minute each to do a complete refresh. He has 2 sessions open to
> the DB. He updates a row in an underlying table, commits it and then selects
> the row from the 3 MV's that should be updated. In the other window he then
> kicks off the refresh in the other session. After a minute the updted row
> shows up in the first session. After another minute the 2nd MV shows the row
> and so on. Not very scientific but certainly valid IMHO.
>
> I tried specifying atomic_refresh=>true and I still saw the above behavior
> when using dbms_mview.refresh(list=>LIST). When specifying
> atomic_refresh=>false it initially appears a truncate is used on all MV's in
> the list and then data is inserted in all the MV's in one transaction. I
> will have to dig deeper into this one.
>
> I will try your test case in my db and see what I get.
>
> Keep the ideas and suggestions coming, please.
>
> Thanks,
> Finn
>
>
>

--
http://www.freelists.org/webpage/oracle-l