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

About these ads

5 thoughts on “A List: Things That Materialized Views Can Be (Philisophically) Similar To

  1. 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.

  2. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s