A List: Things That Materialized Views Can Be (Philisophically) Similar To

When is a materialized view like a ... ?:

1. Constraint: When you use it to enforce a multirow constraint, such as "SUM(allocation_pct) per Site = 100", which is not supported through regular constraints. Better then triggers because MV's refresh on commit, not on DDLoperations themselves.
   
2. Index: When you use it to speed a full scan of a subset of a table's columns .
3. Partition: When a materialized view selects a subset of a table's rows, thus making the subset accessible through multiblock reads instead of single block index-based access, and without requiring the whole of the original table to be scanned.
4. New Set Of Unbelievably Fast Disks: When a materialized view with query rewrite allows a large data set to be pre-aggregated and subsequently queried in almost no time at all.
5. Trigger: When you stop trying to enforce multirow constraints with triggers and start using materialized views instead.
6. Synonym: When it allows you to give an alternative name to a table.
7. Time Machine: When a stale materialized view shows you the previous state of a table's data. A materliazed view log then gives you an audit trail of sorts for changes since the last refresh*
   

1, 3 and 4 seem to be the most interesting ones there. I don't think I'd use 2, unless it was on a single column with nulls, and a bitmap index was not appropriate, and the query had to consider nulls. 6 seems like a real stretch.


* Thanks Gary