Problem Statement
With data spread across multiple partitions, key challenges in optimizing query performance include :
- Repeated resource intensive scans for frequently run queries (especially complex queries)
- Aggregated data over one or more dimensions
- Increased network traffic in a partitioned database environment for frequently joined tables that reside on different partitions
Solution
What is a Materialized Query Table ?
A materialized query table (MQT) is a table whose definition is based upon the result of a query. The data that is contained in an MQT is derived from one or more tables on which the materialized query table definition is based. A MQT provides few benefits over views as described below.
Operation
This tutorial introduces MQTs, the need for them and their advantages. Creation of a replicated MQT is demonstrated.
The performance is not showcased in this case owing to less data being present in the database. However,
with adequate data, there would be a good performance improvement with the use of MQTs.
Benefits of Materialized Query Tables
- Significantly improved query performance by avoiding repetitive table/index scans and calculations
- An MQT actually stores the query results as data, and you can work with the data that is in the MQT instead of the data that is in the underlying tables, highly improving query performance
- Replicated MQTs avoid broadcast of data and increase collocation resulting in improved query performance