Re: MView fast refresh taking long time

From: Randolf Geist <>
Date: Thu, 3 Dec 2009 14:11:24 -0800 (PST)
Message-ID: <>


a couple of points:

  1. Even if the query was running well: I think your query refers to the delete part of the fast refresh and the fast refresh performs single row operations with the obtained ROWIDs - so potentially 3 million single row operations by ROWID (for the delete and update part), so this part of the fast refresh might require millions of consistent gets (and potentially a lot of physical gets, too). I'm not sure if this is reasonable performance-wise, so with that change volume a full refresh might actually be faster - or as suggested perform the fast refresh more often if possible.
  2. The index on the MLOG (MLOG$_BILLSUMMARY_AK1) looks like a custom index - I don't believe this is created by Oracle by default when creating a Materialized View log.
  3. The subquery cannot be unnested because the M_ROW$$ column is defined as NULLable by default when creating the MLOG I think - if you define it as mandatory more transformations like unnesting might be possible - you can force them (if valid) by adding a "/*+ unnest */" hint to the subquery for testing purposes.
  4. Even with unnesting possible the range of transformations is quite restricted due to the correlated NOT IN clause used by Oracle (the unnested query might still use a NESTED LOOP, which might not be much different from the FILTER performance-wise, potentially even worse due to the subquery FILTER caching performed by the Oracle runtime engine).
  5. As Mark has already pointed out - what execution plan do you get for the statement - are the estimates in the right ballpark?


Oracle related stuff blog:

Co-author of the forthcoming "OakTable Expert Oracle Practices" book: Received on Thu Dec 03 2009 - 16:11:24 CST

Original text of this message