When is a materialized view like a … ?:
- 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.
- Index: When you use it to speed a full scan of a subset of a table's columns .
- 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.
- 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.
- Trigger: When you stop trying to enforce multirow constraints with triggers and start using materialized views instead.
- Synonym: When it allows you to give an alternative name to a table.
- 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
A time-machine.
An unrefreshed materialized view can show what the data used to look like.
And if you are using materialized view logs, you get a [sort of] audit trail of changes thrown in for free.
Ooooo, that’s nice.
I’ll add that to the list, if that’s ok.
How about a load mechanism? MV is defined as SELECT * FROM external_table. DBMS_MVIEW.REFRESH(mv). Job done.
About
2 Index: When you use it to speed a full scan of a subset of a table’s columns .
With MVs we can simulate indexes on data from multiple base tables without denormalisation.
An MV may have multiple tables in the query. So an index on the MV (theoritically) gives you the ability to create an index on multiple base tables.
Forgot to mention. I was wondering how to create an index that had columns from multiple tables, without denormalization. And this came out of a suggestion from Tom Kyte.
http://asktom.oracle.com/pls/ask/f?p=4950:61:5511895981090480194::::P61_ID:53840322636873
I apologize if I might have interpreted him incorrectly.