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.

Query Rewrite

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
In of the live demos of my presentation Query Rewrite: The Supreme League of Materialized Views I show the impact of a missing NOT NULL contraint on a Materialized View Delta Join. Without the constraint, Query Rewrite will not take place for this example.

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.

Since Oracle 12.2, it is possible to create Real-Time Materialized Views with the option ENABLE ON QUERY COMPUTATION. The idea of this new type is that Query Rewrite will work on a Materialized View, even if it is not up-to-date. If it is stale, the missing or changed data will be retrieved from the Materialized View Logs and combined with the last state in the Materialized View. A really good idea, which can be very useful for real-time reporting requirements. Unfortunately, there are still some technical issues. I’m still waiting for feedback from Oracle Support about a bug, but one day I will write a blog post about Real-Time Materialized Views.

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.

Read the Oracle Documentation

Last but not least, a general tip about using Oracle Database (or any other software product): RTFM!

The Oracle documentation is very extensive and mostly of very good quality. That was not always the case, but with every release of the Oracle database, it is extended. A lot of useful information about Materialized Views and Query Rewrite you can find in the Data Warehousing Guide, Part II: Optimizing Data Warehouses. The documentation explains all types of Basic Query Rewrite and Advanced Query Rewrite and a lot more details about the tips I wrote about in this overview.