Refreshing a long-running Materialized View with an Index

[kes]

Version information:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0

We have a materialized view that takes approximately 3 hours to build
without an index. Once we've put an index on a few of the columns it
takes too long to build (we don't complete one build by the time we
need to have it built again).

Are there any good articles on this? One thing I've thought about is
creating an oracle job with something like:

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.

Best,
Alex

[Andreas Piesk]

On 28 Okt., 17:29, kes <abi...@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

[kes]

On Oct 28, 1:29 pm, Andreas Piesk <alphapapa...@googlemail.com> wrote:
<snip/>

> 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.

Thank you. I'll use this and the synonym.