A warning: Don, we tried materialized views that refresh on commit on a project. It all worked fine and dandy in the lab, but it was a complete disaster when it came to the real system with concurrent accesses and considerably more data. Contention was so bad that Oracle came to a halt every 15 minutes or so.
Jonathan Lewis in "Practical Oracle 8i" on it:
"First, the ovehead that hits the system when you commit is large. In a busy OLTP system, several users could hit that moment together aund cause some fairly severe contention for resources. Second, if we execute a query against the base data when we have updated ut, but not yet committed our change, the query rewrite may not take place, and we may actually visit the base table rather than the materialied view." (p 530)
"Materialized views with fresh on commit have a signifanct overhead on commit." (Page 532)
Tom Kyte kinda writes the same.
