You can use SELECT statements and FULLSELECT statements in a physical
data model of a data design project to generate materialized query tables
(MQTs) in the physical data model.
An MQT is a table whose definition is based on the result of a
running a query. The MQT typically contains precomputed results that are based
on the data existing in the table or tables that its definition is based on.
Prerequisite: The
SELECT statement or FULLSELECT statement must exist in the SQL
Statements folder of a physical data model in a data design project.
To generate an MQT in a physical data model:
- Switch to the Data Project Explorer view in the Data perspective.
- Expand a data design project that contains a physical data model
and then expand the Data Models folder.
- Double-click the physical data model (.dbm) file to open it.
- Expand the physical data model, the database, and the SQL
Statements folder.
- In the SQL Statements folder, right-click
a SELECT statement or a FULLSELECT statement, and then click on
the pop-up menu. The MQT is generated from the SQL statement
and stored in the physical data model.
- Save the physical data model. When you save the physical
data model, you save all the data objects in the physical data model that
have outstanding changes.
The MQT is selected in the Data Project Explorer view so that you
can locate it easily. You can copy or move the MQT to another database schema
by dragging the MQT or by using commands on the pop-up for the MQT. The generated
MQT is assigned the name "MQTn," where n is a sequential number
that starts at 1, and increases by 1 for each MQT that you generate. You can
use the Properties view to change the name and other properties of the MQT.