How to Use Query Rewrite with Materialized Views

A materialized view helps only queries that read it, unless query rewrite is enabled. Then the optimizer can answer a query from a materialized view that the query does not even name, whenever the view contains what the query needs. Reports written against the base tables speed up without changing a line.

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.

Requirements

• The materialized view is created or altered with ENABLE QUERY REWRITE.
• The QUERY_REWRITE_ENABLED parameter is TRUE, the default.
• The view is fresh, or QUERY_REWRITE_INTEGRITY allows stale data.
• The query's joins, filters, and groupings can be derived from the view, possibly with further aggregation.

A Query Rewritten to a Summary

The example uses ROUTE_REVENUE_MV, created first with query rewrite enabled:

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

The query below joins TICKETS and FLIGHTS and sums fares by route. It does not mention the materialized view, but the plan reads it.

Example:

explain plan for
select f.route_id, sum(t.fare) as revenue
from   tickets t join flights f on f.flight_id = t.flight_id
group  by f.route_id;

select operation, options, object_name
from   plan_table
where  plan_id = (select max(plan_id) from plan_table)
order  by id;

Output:

Explained.

OPERATION                  OPTIONS     OBJECT_NAME
__________________________ ___________ ___________________
SELECT STATEMENT
HASH                       GROUP BY
MAT_VIEW REWRITE ACCESS    FULL        ROUTE_REVENUE_MV

MAT_VIEW REWRITE ACCESS shows the rewrite. The view is grouped by route and cabin, and the query by route only, so Oracle adds up the cabins of each route from the stored rows instead of joining and aggregating over a thousand tickets.

Turn Rewrite Off for a Query

The NOREWRITE hint makes the optimizer read the base tables, which shows what the rewrite saves.

Example:

-- with the NOREWRITE hint, the same query reads the base tables
explain plan for
select /*+ norewrite */ f.route_id, sum(t.fare) as revenue
from   tickets t join flights f on f.flight_id = t.flight_id
group  by f.route_id;

select operation, options, object_name
from   plan_table
where  plan_id = (select max(plan_id) from plan_table)
order  by id;

select value from v$parameter where name = 'query_rewrite_enabled';

Output:

Explained.

OPERATION               OPTIONS           OBJECT_NAME
_______________________ _________________ ______________
SELECT STATEMENT
HASH                    GROUP BY
HASH JOIN
NESTED LOOPS
NESTED LOOPS
STATISTICS COLLECTOR
TABLE ACCESS            FULL              TICKETS
INDEX                   UNIQUE SCAN       FLIGHTS_PK
TABLE ACCESS            BY INDEX ROWID    FLIGHTS
TABLE ACCESS            FULL              FLIGHTS

10 rows selected.

VALUE
________
TRUE

Without rewrite, the plan scans TICKETS and joins FLIGHTS. The work view of this example was created and dropped by it; QUERY_REWRITE_ENABLED is TRUE.

Things to Know

• DBMS_MVIEW.EXPLAIN_REWRITE explains why a query was or was not rewritten.
• The REWRITE hint encourages rewrite for a query, and NOREWRITE prevents it.
• Stale views are not used for rewrite under the default integrity level, so keep them refreshed.

Related Guides

• How to Create Materialized Views in Oracle
• How to Set Up Fast Refresh on Commit for Materialized Views

Conclusion

Query rewrite lets the optimizer answer queries from materialized views they do not name, even with further aggregation. Enable it on the view, keep the view fresh, and check plans for MAT_VIEW REWRITE ACCESS to confirm it is used.