Thursday, January 27, 2011

Drop Materialized View takes a long time

Recently I had to drop a couple of large Materialized View.
And dropping them was taking a long time, as it tries to drop the data in both source and destination DB. In Source DB it tries to purge the mview log and at destination mview itself.
To accelerate the process I tried truncating the mview tables at destination and also the mview log table at source.

At destination (mview site):

truncate table mview_to_drop;

At source (mview log site):

select master,log_table from dba_mview_logs where master='MVIEW_TO_DROP';
LOG_OWNER MASTER LOG_TABLE
------------ ------------------------------ ------------------------------
SCOTT MVIEW_TO_DROP MLOG$_MVIEW_TO_DROP

truncate table SCOTT.MLOG$_MVIEW_TO_DROP;



Now back at destination site:

drop materialized view SCOTT.MVIEW_TO_DROP;

Materialized view dropped.



This is the fastest way I could find, please let me know if anyone else has any ideas.

4 Comments:

Ritzy said...

You may find the following Metalink Link pretty useful as well.

Materialized View Refresh : Log Population and Purge [ID 236233.1]

Apun Hiran said...

Thanks Ritzy,
Its indeed a very useful document, related to the architecture and the way mviews functions.
Regards
Apun

Anonymous said...

I have been solving the same problem. In my case was the solution in droping all indexes on MV before droping the MV itself.

Sorry for no tescase nor idea what might be reason for this behaviour - I don't have time to do it right now. This is just a hint...

Michal

Anonymous said...

U can also try truncate table 'MV'
then do the drop MV.

I tried and it seemed quite handy.