Generating materialized query tables from SQL statements

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:

  1. Switch to the Data Project Explorer view in the Data perspective.
  2. Expand a data design project that contains a physical data model and then expand the Data Models folder.
  3. Double-click the physical data model (.dbm) file to open it.
  4. Expand the physical data model, the database, and the SQL Statements folder.
  5. In the SQL Statements folder, right-click a SELECT statement or a FULLSELECT statement, and then click Generate > MQT on the pop-up menu. The MQT is generated from the SQL statement and stored in the physical data model.
  6. 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.

Feedback