Refreshing a long-running Materialized View with an Index
Date: Wed, 28 Oct 2009 09:29:27 -0700 (PDT)
Message-ID: <742cce0a-a501-4a1f-a24b-561a287e5dc2_at_n22g2000yqh.googlegroups.com>
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
Received on Wed Oct 28 2009 - 11:29:27 CDT