In Oracle, CREATE MATERIALIZED VIEW statement creates a view that stores the query result similar to a table that stores its rows.
You can specify when to refresh the data in a materialized view: when changes to the underlying objects are committed, on demand, at specified time intervals, or never refresh.
Materialized views can be used to
Oracle:
-- The query of the view joins 2 tables and the result will be stored CREATE MATERIALIZED VIEW cities_v AS SELECT c.name AS city, s.name AS state FROM cities c, states s WHERE c.state = s.id;
Summary information:
Data | Data is stored and storage space is consumed | |
Default Refresh | ON DEMAND | Use DBMS_MVIEW procedures to refresh |
Query Rewrite | Allows to increase performance without changing the queries in application |
Assume there are states and cities tables:
Oracle:
-- Table definitions CREATE TABLE states ( id CHAR(2), name VARCHAR(90) ); CREATE TABLE cities ( name VARCHAR(90), state CHAR(2) ); -- Data INSERT INTO states VALUES ('CA', 'California'); INSERT INTO cities VALUES ('San Francisco', 'CA'); INSERT INTO cities VALUES ('San Diego', 'CA');
Now let's create a materialized view that retrieves cities and the full name of the state:
Oracle:
CREATE MATERIALIZED VIEW cities_v AS SELECT c.name AS city, s.name AS state FROM cities c, states s WHERE c.state = s.id;
When you select rows from cities_v Oracle does not need to perform a join:
-- Enable trace in SQL*Plus to see statistics SET AUTOTRACE ON SELECT * FROM cities_v; # San Francisco California # San Diego California -- Fragment of statistics returned by SQL*Plus after executing the query # Execution Plan # 0 SELECT STATEMENT # 1 MAT_VIEW ACCESS FULL | CITIES_V
Often you do not have access to the source code of the application, so you cannot change a query in it to use a materialized view.
The application still executes a join, and the materialized view is not used:
Oracle:
-- Application still contains a SQL SELECT with join SELECT c.name AS city, s.name AS state FROM cities c, states s WHERE c.state = s.id; # San Francisco California # San Diego California -- Fragment of statistics returned by SQL*Plus after executing the query # Execution Plan # 0 SELECT STATEMENT # 1 HASH JOIN # 2 TABLE ACCESS FULL | STATES # 3 TABLE ACCESS FULL | CITIES
But if you specify ENABLE QUERY REWRITE option when you create a materialized view, Oracle can use it even if the application still executes the original statement containing joins, aggregates:
-- Define a materialized view with QUERY REWRITE option CREATE MATERIALIZED VIEW cities_v_query_rewrite ENABLE QUERY REWRITE AS SELECT c.name AS city, s.name AS state FROM cities c, states s WHERE c.state = s.id;
Now when you execute the original query, the Oracle optimizer automatically recognizes that the materialized view can be used, and no joins are performed:
-- Application still contains a SQL SELECT with join SELECT c.name AS city, s.name AS state FROM cities c, states s WHERE c.state = s.id; # San Francisco California # San Diego California -- Fragment of statistics returned by SQL*Plus after executing the query # Execution Plan # 0 SELECT STATEMENT # 1 MAT_VIEW REWRITE ACCESS FULL | CITIES_V_QUERY_REWRITE
Oracle 11g R2 SQL Language Reference
Oracle 11g R2 Database Concepts
Oracle 11g R2 Advanced Replication
Oracle 11g R2 Data Warehousing Guide
SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.