Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle Materialized Views and MySQL Summary Tables or Views

Oracle Usage

Oracle materialized views are table segments where the contents are periodically refreshed based on the results of a stored query. Oracle materialized views are defined with specific queries and can be manually or automatically refreshed based on specific configurations. A materialized view runs its associated query and stores the results as a table segment.

Oracle materialized views are especially useful for:

Such as ordinary views, you can create materialized views with a SELECT query. The FROM clause of a materialized view query can reference tables, views, and other materialized views. The source objects that a materialized view uses as data sources are also called master tables (replication terminology) or detail tables (data warehouse terminology).

Immediate or Deferred Refresh

When you create materialized views, use the BUILD IMMEDIATE option can to instruct Oracle to immediately update the contents of the materialized view by running the underlying query. This is different from a deferred update where the materialized view is populated only on the first requested refresh.

Fast and Complete Refresh

You can use one of the two following options to refresh data in your materialized view.

Materialized View Logs

When you create materialized views, use a materialized view log to instruct Oracle to store any changes performed by DML commands on the master tables that are used to refresh the materialized view, which provides faster materialized view refreshes.

Without materialized view logs, Oracle must re-run the query associated with the materialized view each time. This process is also known as a complete refresh. This process is slower compared to using materialized view logs.

Materialized View Refresh Strategy

You can use one of the two following strategies to refresh data in your materialized view.

Examples

The following example creates a simple Materialized View named mv1 that runs a simple SELECT statement on the employees table.

CREATE MATERIALIZED VIEW mv1 AS SELECT * FROM hr.employees;

The following example creates a more complex materialized view using a database link (remote) to obtain data from a table located in a remote database. This materialized view also contains a subquery. The FOR UPDATE clause allows the materialized view to be updated.

CREATE MATERIALIZED VIEW foreign_customers FOR
UPDATE AS SELECT * FROM sh.customers@remote cu WHERE EXISTS
(SELECT * FROM sh.countries@remote co WHERE co.country_id = cu.country_id);

The following example creates a materialized view on two source tables: times and products. This approach enables FAST refresh of the materialized view instead of the slower COMPLETE refresh. Also, create a new materialized view named sales_mv which is refreshed incrementally REFRESH FAST each time changes in data are detected (ON COMMIT) on one or more of the tables associated with the materialized view query.

CREATE MATERIALIZED VIEW LOG ON times
WITH ROWID, SEQUENCE (time_id, calendar_year)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW LOG ON products
WITH ROWID, SEQUENCE (prod_id)
INCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW sales_mv
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT t.calendar_year, p.prod_id,
SUM(s.amount_sold) AS sum_sales
FROM times t, products p, sales s
WHERE t.time_id = s.time_id AND p.prod_id = s.prod_id
GROUP BY t.calendar_year, p.prod_id;

For more information, see Basic Materialized Views in the Oracle documentation.

MySQL Usage

Oracle materialized views have no equivalent feature in MySQL, but other features can be used separately or combined to achieve similar functionality.

Make sure that you evaluate each case on its own merits, but options include:

For more information, see CREATE TABLE Statement, Trigger Syntax and Examples, and CREATE VIEW Statement in the MySQL documentation.