Re: Materialized View - Transactional Refreshing

There is a trick you can use to achieve this. Refer to Metalink Note 1029824.6.
Basically you create an MV group and put your MV and a dummy MV in it to be refreshed together. Since all MVs in that group must be refreshed together, 'truncate' can not be used.

Beware though of these potential performance issues. 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

Peter Sylvester <peters_no_spam_please_at_mitre.org> wrote in message news:<chpqjp$dac$1_at_newslocal.mitre.org>...
> I have a materialized view, which due to the complexities, needs to be
> refreshed using the "complete" option (no mview log).
>
> I noticed that the the mview appears to be empty during the refresh
> process (evidently due to a "truncate" being used).
>
> Is there a way to get Oracle to make this transactional (using a delete
> statement), so the mview will always be usable?
>
> I know I can do all this myself, via PL/SQL but would rather use the
> mview technology, if it supports it.
>
> thanks,
> --Peter
Received on Thu Sep 09 2004 - 14:34:34 CDT