Working with Materialized Query Tables
A Materialized Query Table (MQT) is a table derived from a query result set. MQT optimizes SQL performance against data warehouses.
If you enable Query
Optimization for a MQT then the SQL is dynamically modified in the
database. This happens if another query result set content is completely
or partially similar to the MQT. The database compares the SQL
with the FULLSELECT statement that was used to define the MQT. If it determines
that there is either a partial or full overlap between the submitted
SQL and theFULLSELECT statement of the MQT, then the database automatically rewrites the
submitted query to access the MQT instead of updating the base tables
that were originally specified in the query. For non-overlapping
parts of the query, the database accesses the base tables specified
in the query.
Materialized Query Tables are enabled from the view definition in the Application Designer.
Image: Materialized Query Table Check Box
The following example illustrates a view definition Record Type tab where the Materialized Query Table check box exists:
You can enable or disable the Materialized Query Table for any record type of SQL View or Query View.
You can access the Materialized View Maintenance page if you are granted the Materialized View Administrator role. Use a run control ID to populate the MQTs on the page. As an administrator, you can refresh the MQTs and enable Query Optimization on them.
Access the Materialized View Maintenance page ().
Image: Materialized View Maintenance page
The following example illustrates the Materialized View Maintenance page. The description of the fields and controls follows that:
|
Field or Control |
Definition |
|---|---|
| Owner |
Search and select an owner of the tables to limit the search result of MQTs. |
| Refresh? |
Select to schedule a refresh on the record. |
| Materialized Query Table |
Displays the name of the record. |
| Materialized |
Displays if the MQT is materialized in the database which makes them readily available in the database and the query does not have to be run again. |
| Query Optimization? |
Select to allow the database to rewrite the SQL if another query result set has similar content in a MQT. |
| Query Optimization |
Displays Enabled, if Query Optimization is enabled for the view. It is a display-only field. |
| Refreshed |
Indicates if the record is refreshed (Yes) or not (No). |
| Last Refresh Date |
List the date and time the record was last refreshed. |
| Staleness |
Indicates if the record is stale or fresh based on the database catalog. |
On the page, select the MQTs to refresh. Click the Save button. On saving the page, the Run button gets enabled.
Click the Run button. The Process Scheduler Request page with PTMATREFVW process is displayed. Click the OK button to initiate the process. The Application Engine program PTMATREFVW executes and refreshes the MQTs.
For more information on Process Scheduler Request page see, Maintaining Materialized Views documented under the Oracle platform section.
Access the Change Properties page ().
The page displays a list of the views delivered from the applications. You can select specific views to enable or disable materialized view feature. This example illustrates the Change Properties page. The columns on the page are explained below:
Image: Change Properties page
This example illustrates the Change Properties page. The columns on the page are described below:
This example illustrates the Enable Materialized Query Tables page. The columns on the page are described below:
|
Field or Control |
Definition |
|---|---|
| Owner |
Search and select an owner of the tables to limit the search result. |
| Enable? |
Select the record to render it as a materialized view. |
| Disable? |
Select to convert the MQT to a normal SQL view. |
| Materialized Query Table |
Enter the name of the MQT. You can use the prompt for selecting the name. |
| Status |
Displays Enabled or Disabled depending on the current status of the view. |
| Tablespace Name |
Displays the tablespace name to which the record is assigned when it is materialized. |
Save the page after you select the views to enable or disable MQTs. Open the ENABLEMV project in the Application Designer and build the project to render the selected views as materialized query tables. Similarly, open the DISABLEMV project in the Application Designer and build it to convert the materialized query tables to normal SQL views.
You can run the following DDDAudit queries for materialized query tables to resolve any inconsistency in the database.
MQT–1
MQT–2
MQT–3
MQT–4
See the Materialized Query Table Queries [DB2 ZOS] section for detailed audit queries.