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.
If you work with Data Vault for a data warehouse running in an Oracle database, I strongly recommend to use Oracle 12.2 or higher. Why that? Since Oracle 12c Release 2, join elimination works for more than one join column. This is essential for queries on a Data Vault schema.
NLS (National Language Support) parameters are very useful for multilingual applications in an Oracle database. But it is important to understand their impact on query performance. I was aware that NLS is important for indexes, but never thought about its impact on partitioning.
An almost unknown simplification in the usage of dbms_xplan is possible since Oracle 12.2. Sometimes, there are small details that makes our job (a little bit) easier.
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.
The data type of primary key and foreign key columns has an impact on the query performance. This should be considered in the design decision of keys in a Data Vault schema.
