Materialized View performance problem caused by index
613647Jul 18 2011 — edited Sep 5 2011Hi,
We have a materialized view with index, but when we refesh it with a complete refresh (dbms_mview.Refresh ('<MV>', '?');) we encounter big performance problems.
The problems are caused by the index, because when we drop it, the performance for rebuilding is fine again. So we wrote a small procedure which basically does the following:
1) disable index (alter index ... unusable)
2) refresh MV: dbms_mview.Refresh ('<MV>', '?');
3) rebuild index
BUT... recently we had a few hickups with the MV. This resulted that the index was not rebuilt, causing a lot of other user problems.
My question: are there Materialized view refresh options that handle with the index, so we don't have to disable / enable it? I have searched OTN / google, but didn't find anything yet.
We're using Oracle Database 10g Enterprise Edition Release 10.2.0.3.0
Thank you,
Tom
Edited by: Tom Buma on Jul 18, 2011 12:19 PM