This post was originally published on this site

Although Query Rewrite on Materialized Views is a powerful feature which is available in Oracle databases for a long time, it is rarely used. Many developers and database administrators don’t know about it or do not use it because they think it is too complex. In this blog post, I try to reduce the concerns about complexity with some general design tips for Query Rewrite.

Materialized Views are very useful to increase the performance of frequently executed queries. In many data warehouses and other database applications, Materialized Views are accessed directly using SQL. However, using Query Rewrite is much more elegant and flexible: The query optimizer decides automatically whether a suitable Materialized View can be used to speed up the query.

The goal is to improve the performance of different queries with the smallest possible number of Materialized Views. Query Rewrite offers excellent possibilities for this. But for this you have to consider some preconditions and design guidelines. Here are some design tips about Query Rewrite, how it is configured correctly and what has to be considered when creating Materialized Views to use Query Rewrite.

Enable Query Rewrite

To allow the optimizer to use Query Rewrite, two preconditions must be fulfilled:

• The parameter QUERY_REWRITE_ENABLED must be set to TRUE, either on database level or on session level. Because this is the default since Oracle 10g, this is usually not an issue anymore.
• The Materialized Views must be created with the option ENABLE QUERY REWRITE. The optimizer only evaluates Materialized Views for Query Rewrite when you allow that.

Always use Oracle Join Syntax in Materialized Views

One of the pitfalls why Query Rewrite doesn’t work is the way how you write the joins in the Materialized View definition. I highly recommend to use Oracle join syntax instead of ANSI join syntax when you create a Materialized View for Query Rewrite. Which join syntax you use in your queries does not matter. Query Rewrite will work with ANSI or Oracle join syntax. For further details read my blog post ANSI Join Syntax and Query Rewrite.

Use Constraints on Base Tables

Constraints are not only used for data consistency in the database and for readability of the data model, they are important for the query optimizer, too. For some types of advanced Query Rewrite (e.g. Join Back or Delta Join), existing constraints are important for the optimizer to decide whether Query Rewrite will work or not. So the recommendations are:

• Define referential integrity with primary/foreign key constraints
• Define foreign key columns as NOT NULL whenever possible

Additional Tips for Data Warehouses

Materialized Views are often used in data warehouses, especially for aggregations of facts and rollups on dimensions in a star or snowflake schema. As mentioned before, constraints are important for Query Rewrite. But in a data warehouse, foreign key constraints can be obstructive for ETL dependencies and load performance. A typical compromise is to define them as reliable constraints with RELY DISABLE NOVALIDATE. I explained this in detail in blog post Foreign Key Constraints in an Oracle Data Warehouse.

If you have dimensions with multiple hierarchy levels (which is very common in a star schema), Oracle allows to create “DIMENSION” objects to define the hierarchical relationships within a dimension table. I don’t like the term “dimension” for this kind of object, I usually call them “hierarchy constraints”. They are only declarative, i.e. the data in the dimension tables is not checked. But they are useful for Query Rewrite. For example, the optimizer can decide to to a Query Rewrite on a Materialized View with monthly aggregations, when the query selects aggregations on quarterly or yearly level.

For both features, you have to set the parameter QUERY_REWRITE_INTEGRITY to TRUSTED. With the default setting ENFORCED, reliable constraints or dimensions (“hierarchy constraints”) will not be used for Query Rewrite.

Refresh Materialized Views in a Suitable Way

Normally, Query Rewrite will only work on “fresh” Materialized Views with current data. As soon a some data is changed in one of the base tables, the Materialized View becomes “stale”, and the optimizer will ignore it as a candidate for Query Rewrite.

You could set the parameter QUERY_REWRITE_INTEGRITY to STALE_TOLERATED, but that’s not what we usually want. Query Rewrite would be possible then even for stale Materialized Views, but the result of the query would be outdated. There may be situations where this is requested, but under normal circumstances, this is not useful. I never use the value STALE_TOLERATED for this parameter.

The typical approach is to refresh all Materialized Views after data changes. Depending on the situation, this can be a Complete Refresh or a Fast Refresh. Some general tips and more information you can read in my blog post Materialized View Refresh for Dummies.

Reduce the Number of Materialized Views

The more Materialized Views you have in your database, the more effort it needs to keep them up-to-date. So, it is not a good idea to create a separate Materialized View for each individual query. A much better approach is to create more flexible Materialized Views that can be used for different, but similar queries. Or in other words: As many Materialized Views as required, but as few as possible.

For one special case, queries with COUNT(DISTINCT), there is a new way to reduce the number of Materialized Views. In Oracle 19c, a set of bitmap-based functions was introduced that can be used to create such flexible Materialized Views. In blog post Bitmap-based COUNT(DISTINCT) Functions in Oracle 19c you can read further details about this new possibilities.

Verify Capabilities with DBMS_MVIEW Procedures

There are several restrictions and rules to be considered for Query Rewrite: Additional columns required in the Materialized View definition, parameter settings, constraints that are needed, etc. Sometimes it’s hard to find the exact reason in the documentation why Query Rewrite does not take place. For this purpose, there are two useful procedures available in package dbms_mview.

• The procedure dbms_mview.explain_mview shows the capabilities (e.g. Fast Refresh, Query Rewrite, Partition Change Tracking) for a specific Materialized View and writes them to a table MV_CAPABILITIES_TABLE that has to be created before. In blog post Materialized View Refresh for Dummies you find an example how this procedure can be used.
• The procedure dbms_mview.explain_rewrite works in a similar way, but you can specify a particular SQL query as an input parameter. The result is a detailed explanation whether Query Rewrite will work for this query or – even more important – what are the reasons that is will not work. The results are written to a table REWRITE_TABLE that must be created before with the script $ORACLE_HOME/rdbms/admin/utlxrw.sql.

These two procedures already helped me many times to configure or extend Materialized Views in the correct way to enable Query Rewrite.