Oracle Materialized Views

From Minor Miracle Software
Jump to: navigation, search

Oracle Materialized Views

Oracle Documentation[1]

Materialized Views[2][3]
Oracle uses materialized views (known as snapshots in prior releases) to replicate data to non-master sites in a replication environment and to cache expensive queries in a data warehouse environment. Whereas in multimaster replication tables are continuously updated by other master sites, materialized views are updated from one or more masters through individual batch updates, known as a refreshes, from a single master site or master materialized view site.

Materialized Views are supported in: Oracle, Sybase, IBM DB2, MS-SQL as "indexed views". There are work-arounds for PostgreSQL and MySQL.

-- Oracle code for a materialized view that updates once a day
CREATE MATERIALIZED VIEW MV_MY_VIEW
REFRESH FAST START WITH SYSDATE
   NEXT SYSDATE + 1
     AS SELECT * FROM <table_name>;


Internal Links

Parent Article: Microsoft SQL Server (Notes)