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.
Oracle 19c provides a set of new SQL functions for bitmap operations. They allow fast computation of COUNT(DISTINCT) expressions. What is the purpose of these functions? The Oracle documentation is very sparse, but at least it tells us that the main focus are Materialized Views on aggregated data. Let’s have a detailed look at this new feature.
Materialized Views are often used in data warehouses to improve query performance on aggregated data. But what if it takes too long to refresh the materialized views? Here are some basic rules to improve refresh performance.
There are many good reasons to use ANSI join syntax instead of the old Oracle join syntax. With one exception: If you want to use your Materialized Views for Query Rewrite, avoid to write them with ANSI joins.
Foreign key constraints are often omitted in Data Warehouses. This blog post explains why this is not a good approach and what is a practical compromise for foreign key constraints in an Oracle Data Warehouse.
Partition Change Tracking (PCT) is a powerful feature in Oracle for fast refresh of materialized views when one of the base tables is partitioned. But under certain conditions, PCT does not work as expected and returns an unpleasant surprise.
Data Warehousing with Oracle 