How to Create Materialized Views in Oracle

Some queries are expensive and asked again and again: revenue by route and cabin, totals by month, counts by status. A materialized view stores the result of such a query as a table, and remembers the query so the result can be refreshed. Reports read the stored rows instead of recomputing them, and with query rewrite even queries that do not name the materialized view can use it.

Code for This Guide

The main examples are in the examples/views folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create materialized view [if not exists] mview
  [build {immediate | deferred}]
  [refresh {complete | fast | force} [on {demand | commit}]] [never refresh]
  [enable query rewrite]
as subquery

Revenue by Route and Cabin

Example:

create materialized view route_revenue_mv
  build immediate
  refresh complete on demand
  enable query rewrite
as
select f.route_id, t.cabin, count(*) as tickets, sum(t.fare) as revenue
from   tickets t join flights f on f.flight_id = t.flight_id
group  by f.route_id, t.cabin;

select mview_name, refresh_mode, refresh_method, staleness, rewrite_enabled
from   user_mviews;

Output:

Materialized view ROUTE_REVENUE_MV created.

MVIEW_NAME          REFRESH_MODE    REFRESH_METHOD    STALENESS    REWRITE_ENABLED
___________________ _______________ _________________ ____________ __________________
ROUTE_REVENUE_MV    DEMAND          COMPLETE          FRESH        Y

USER_MVIEWS shows the refresh settings and whether the stored result is up to date (STALENESS).

Query the Stored Result

A materialized view is queried like a table. It is stored as one: its segment type is TABLE.

Example:

-- a materialized view is queried like a table, from its stored rows
select route_id, cabin, tickets, revenue
from   route_revenue_mv2
order  by revenue desc
fetch  first 3 rows only;

select segment_type from user_segments where segment_name = 'ROUTE_REVENUE_MV2';

Output:

   ROUTE_ID CABIN          TICKETS     REVENUE
___________ ___________ __________ ___________
         36 ECONOMY             30    41303.09
         35 ECONOMY             28    36581.17
         11 BUSINESS             8    33168.67

SEGMENT_TYPE
_______________
TABLE

The work view of this example was created with the same query and dropped afterward. Drop the example's view with drop materialized view route_revenue_mv.

Things to Know

• Materialized views can be indexed, which speeds up queries on them further.
• A materialized view uses storage and must be refreshed; create them for queries that are expensive and frequent.
• CREATE MATERIALIZED VIEW requires the CREATE MATERIALIZED VIEW privilege.

Related Guides

• How to Use WITH CHECK OPTION on Oracle Views
• How to Use the WITH Clause in Oracle SQL

Conclusion

A materialized view stores a query's result and remembers the query, so expensive results are computed once and read many times. Choose how it is built and refreshed, enable query rewrite to let other queries use it, and refresh it when its base tables change.