Re: Refreshing a long-running Materialized View with an Index
From: Andreas Piesk <alphapapa101_at_googlemail.com>
Date: Wed, 28 Oct 2009 10:29:46 -0700 (PDT)
Message-ID: <ebab0a31-b3ab-4715-ad7b-52ceead89f16_at_s15g2000yqs.googlegroups.com>
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
> my_materialized_view(my_column);
> -- 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.
Date: Wed, 28 Oct 2009 10:29:46 -0700 (PDT)
Message-ID: <ebab0a31-b3ab-4715-ad7b-52ceead89f16_at_s15g2000yqs.googlegroups.com>
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
> my_materialized_view(my_column);
> -- 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.
try this:
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.
regards,
-ap
Received on Wed Oct 28 2009 - 12:29:46 CDT