Materialized views open up all sorts of possibilities for making reporting more efficient – but at the same time they can introduce some “interesting” side effects when you start seeing refreshes taking place. (Possibly one of the most dramatic surprises appeared in the upgrade that switched many refreshes into “atomic” mode, changing a “truncate / append” cycle into a massively expensive “delete / insert” cycle).
If you want to have some ideas of the type of work that is involved in the materialized view “fast refresh”, you could look at some recent articles by Alberto Dell’Era on (very specifically) outer join materialized views (which a link back to a much older article on inner join materialized view refresh):
Oracle Scratchpad 
Fast refresh, on commit materialized views – good stuff unless you’re in a distributed transaction…
Comment by Dom Brooks — April 30, 2013 @ 9:01 am BST Apr 30,2013 |
Dom,
You were too modest – so I’ve supplied a couple of links to the stuff you’ve written on interesting effects with distributed transactions, which I see (from the second part) is where you lament about MVs comes from. (Personally I’m most unenthusiastic about “refresh on commit” anyway, unless it’s a very rare occurrence – the last time I checked the overhead was several thousand percent.)
Comment by Jonathan Lewis — June 9, 2013 @ 12:18 pm BST Jun 9,2013 |