Re: complete refresh of large materialized view, online or any alternatives?

dachselt_at_firstgate.de (Martin Dachselt) wrote in message news:<bdafd998.0307301001.66d1332d_at_posting.google.com>...
> JusungYang_at_yahoo.com (Jusung Yang) wrote in message
> > Nope. You can not even use online redefinition on the MV base table.
>
> Of course you can. You have to bind two or more snapshots (for example
> one dummy snapshot and your desired snapshot) in one refresh group.
> Then oracle uses delete instead of trunc.
>
> Example from oracle:
> create snapshot snap_test
> refresh complete
> as select * from test_at_db_link;
>
> create snapshot snap_dual
> refresh complete
> as select * from dual;
>
> execute dbms_refresh.make(
> name => 'group1',
> list => 'snap_test,snap_dual',
> next_date => sysdate,
> interval =>'sysdate+1/24',
> implicit_destroy =>true);
>
> Martin

Ah, neat trick. This example is provided in Metalink Note 1029824.6. All MVs in the refresh group need to be refreshed together. If one fails, all others need to be rolled back. So ORACLE needs to use 'delete' rather than 'truncate'.

Beware though, when you refresh a whole group and ORACLE uses 'delete' to refresh the MVs, there are two issues that could hurt your refresh performance.
1. A normal complete refresh uses direct path insert with the /*+ append */

   hint, so you can create the MV with NOLOGGING to save redo generation.

   With group refresh, direct insert can not be used - since each direct insert

   needs to be followed by a commit or rollback. 2. You are looking at potentially huge rollback consumption with 'delete', as

   complete refresh often involves large amount of data.

• Jusung Yang