Re: Refreshing a long-running Materialized View with an Index
Date: Wed, 28 Oct 2009 10:29:46 -0700 (PDT)
On 28 Okt., 17:29, kes <abi..._at_gmail.com> wrote:
> drop index my_materialize_view_column_idx;
> exec dbms_mview.refresh( 'my_materialized_view', 'C' );
> create bitmap index my_materialize_view_column_idx on
> -- Unfortunately if the index is being used, then I get an error when
> I try to drop it. Is there a way to
> -- wait for a lock on the index before dropping it?
> That way the materialized view is still available (even without the
> columns) while it's refreshing. It would be convenient to rename a
> materialized view, yet that's not possible with my current knowledge.
atomic_refresh takes very long because it uses DML only.
create bitmap index my_materialize_view_column_idx on my_materialized_view(my_column);
exec dbms_mview.refresh( 'my_materialized_view', 'C', atomic_refresh => false );
this way, all indexes on the table are set unusable and the table gets truncated and loaded very fast with insert /*+append*/. afterwards the indizes will be rebuilded automatically.
if you need the matview available all the time you could use two matviews and create a synonym pointing to the matview not being refreshed. after the refresh just switch the synonym to the refreshed matview.
-ap Received on Wed Oct 28 2009 - 12:29:46 CDT