Materialized Views
This topic explains how to use the Materialized Views feature.
In this topic:
Introduction
Frequently executed complex queries that are resource heavy and run on not so frequently changing tables can be sped up and simplified at query time by defining a materialized view for the query.
Materialized view populates a view with data from a query. It is refreshed from tables references by the query. As the query result is precomputed, this feature removes the need for computing each time query is executed. Configurable refresh options ensure that the result is refreshed upon data changes.
This feature comes with a CPU and storage space cost for maintaining a fresh view data copy after updates of tables.
Use Cases
Here are some of the use cases to leverage this feature:
-
Complex and frequently run queries.
-
Resource heavy queries used for reading data.
-
Data in the source tables changing much less than frequency of execution of queries that make use of materialized views. So it does not fit all queries.
-
Summary reports for calculating a short but compute expensive result from a large data set.
-
Complex aggregation calculations.
-
Complex joins.
Workflow
-
Copy DW query from saved MCPSQuery metadata.
-
Create Materialized view (metadata/materializedviews) with the exact same DW query as copied from saved MCPSQuery.
-
Define refresh options that best matches data change pattern. Materialized View is created and refreshed in ADW through refresh Job run (manually, job workflow triggers or schedule).
-
Verify that the materialized view was refreshed by executing the Materialized View refresh status operation in the admin API.
API Reference
The following APIs are part of the Materialized Views:
-
Materialized View Metadata API: This API enables you to create, retrieve, update and delete Materialized Views.
-
Materialized View Refresh API: This API enables you to refresh Materialized Views.
Faster Execution Time
If a DW query is not frequently changing, then, Materialized View can be created for the query to improve DW query run performance.
The execution time of the query reduces significantly when a Materialized View is used.