MQT

You can set up a task in the task center such that the refresh operation of the MQT will happen at a time of your choice.

You can incrementally refresh a REFRESH DEFERRED MQT if it has a staging table associated with it.

A materialized query table (MQT) is a table whose definition is based upon the result of a query.

The staging table collects changes that need to be applied to synchronize the MQT with its underlying tables.

The existence of many candidate MQTs might prevent the compiler from choosing the best MQT for that query.

It gives hints and tips to determine whether an MQT is being used by the query and, if not, what prevented the use of that MQT.

Other criteria regarding freshness of data in MQT and optimization level used for running the queries are satisfied.

The article starts out with a motivating example to show how MQTs can help query performance in the Understanding MQTs section.

The REFRESH TABLE statement (used for system-maintained MQTs) cannot be issued against user-maintained MQTs.

Please note that while our workload was defined to use only one MQT per query, it is possible to use multiple MQTs to answer a query.

Once the MQT is created, it needs to be populated with data to reflect the results of the query that the MQT is supposed to cache.

An MQT can be defined at table creation time as maintained by the system or maintained by the user.

A replication schedule is automatically set up between the MQT and the nickname on which this MQT is based.

We consider materialized query table (MQT) support an important option to further fine tune the performance of your federated server.

Our experimentation suggests that when relatively large amounts of data need to be loaded into the MQT, LOAD performs faster than INSERT.

Make sure that the predicates applied by the MQT are a subset of the predicates applied by the query.

Another example is the restriction for XML nicknames that a MQT cannot be created for a child XML nickname.

The query optimizer will give priority to an execution plan involving an MQT over a plan that involves access to remote tables.

To test the performance of the MQTs over nicknames we used parts of the " TPC-H" benchmark workload adapted to fit a federated environment.

As the data for the base table(s) changes, the MQT needs to be updated accordingly.

The section walks you through all the steps of creating an MQT, populating it with data, and enabling it for optimization.

The section on troubleshooting helps you determine why an MQT was not used to satisfy a query.

A customer experiences performance issue while refreshing an MQT as part of a batch run.

The MQT is populated with the data once and is reused by multiple queries, providing performance benefit.

Creation of indexes, unique or otherwise, helps read the data from MQTs in an efficient manner, resulting in better performance.

However, the design of an MQT can be affected by which partitioning features are used on the base table.

DW designers may be interested in identifying cases where performance would benefit from converting these logical views to MQTs.

SYSTEM: only the system-maintained MQTs are considered for optimization.

The command below removes the user-maintained MQT from the check pending state and enables it for optimization.

The MQT must reference the whole hierarchy of nicknames from the root nickname to that child nickname.

This option is required if you want MQT recommendations and you are running on a DPF (multiple partition) instance.

This step also needs to be executed whenever the contents of the MQT are to be updated based on the underlying data.

The MQT selects the exact same set or a super set of columns that the user query under consideration selects.

An MQT is a table whose definition is based on the result of a query.

The idea is to use the staging table to incrementally refresh the MQT, rather than regenerate the MQT from scratch.

Next, to populate the MQT with some data, we will import data that had been exported from the EMPLOYEE and SALES tables.

Make sure that the tables referenced in the MQT are a subset of tables referenced in the query.

Leave MQTs for a future iteration, perhaps after validating the initial changes in production.

Computations common to multiple queries can be represented using an MQT.

The article then briefly explains how a decision is made to use an MQT in a query and the benefits of using MQTs in a federated environment.

User-maintained MQTs do allow insert, update, or delete operations to be executed against them.

MQTs are targeted for use in similar situations as the partitioning features - namely data warehouse fact or history tables.

Of particular interest here, are the available MQT refresh policies.

A query against this MQT right after it was created, but before the REFRESH TABLE statement was issued, returns an error.

If the query selects fewer columns than the corresponding matching MQT, the optimizer can eliminate unneeded columns.

Not supported are range clustered tables (RCT), materialized query tables (MQT), and typed tables.

Indexes recommended for MQTs are designed to improve workload performance and not the MQT refresh performance.

Extended statistics are collected on objects referenced in the MQT definition using sampling.

And the best part is that you do not need to modify your applications to take advantage of MQTs.

An MQT can be indexed to provide an even greater boost to query performance.

MQTs cache the data locally in a federated system and avoid expensive trip(s) to back-end data sources to transfer data.

Insert, update, or delete of data into the MQT directly by the user is disallowed.

Loading from a cursor is also an efficient method to populate a materialized query table (MQT) referencing a nickname.

If you have created many MQTs, all of which can be used to satisfy a query, the compilation time of the query can increase.

There are two ways to enable an MQT to be considered for optimization.

The results of the query will be inserted into the MQT in the subsequent LOAD statement.

Please note that these times are provided simply to demonstrate the improvement attainable with MQTs defined over remote tables.

An MQT is also known as a maintained table.

While this example uses Immediate refresh, deferred refresh MQTs are generally more compatible with ETL activity.

Fact data is added nightly, which requires rebuilding the data cache and refreshing the materialized query tables (MQTs).

The database may contain materialized query tables (MQTs), the pre-joined and pre-aggregated tables for DWE OLAP models and cubes.

The section Steps to create and use MQTs discusses how to choose MQTs that are likely to improve the performance of your workload.

Users may insert, update, or delete data in a user-maintained MQT.

Also assume that the MQT aggregate_sales exists as described before.

This option is not available for MQTs whose definition references nicknames.

Step 2: Note that system-maintained MQTs referencing nicknames must be defined as REFRESH DEFERRED.

The option -r instructs the Design Advisor to use extended statistics for the considered MQTs.

The MQT applies the same set or a subset of predicates that the user query under consideration applies.

MQTs provide important statistical information to the optimizer.

The last section in the artcile describes our in-house performance experiments and the performance benefit we obtained with the use of MQTs.

Note that you may need to modify the MQT script to specify proper partition key.

Once you have determined the MQT definitions, the MQTs can be created with the use of a " CREATE TABLE" statement.

The Design Advisor generalizes the recommended MQTs for the given queries by removing the filter (local) predicates from the MQTs.

One needs to carefully determine the clauses to use while creating an MQT.

The next two columns represent the query execution time in seconds when MQTs are disabled and when MQTs are enabled.

Another way of thinking about MQTs is that they are like a view whose result set is stored in a table.

From DWE Design Studio, you can design and deploy materialized query tables (MQTs) for OLAP models and cubes.

Cache tables are similar to MQTs with the restriction that a cache table supports only non-aggregate queries over a single table.

When MQTs are present, they are treated as part of the overall process of adding new data to the warehouse.

This article introduced the following DB2 table design features: table partitioning, MDC, DPF, and MQT.

The data that is contained in an MQT is derived from one or more tables on which the materialized query table definition is based.

Such MQTs can be maintained only by refreshing the whole table.

The use of partitioning features on the base table does not need to take into account whether MQTs will be built upon this table.

The federated query compiler makes a cost-based decision and makes the process of using MQTs transparent to the users.

MQTs can be created based on a table that uses any combination of partitioning features.

Step 2: Populate the MQT with data using a load from cursor statement or insert statements.

You can think of an MQT as a kind of materialized view.

Generally, MQTs are updated to remove the corresponding summary data.

You can query this view to obtain a dependency summary for the MQTs that we have created (Listing 5).

For example, if the considered MQT is a refresh-deferred system-maintained MQT, this special register must be set to ALL or SYSTEM.

In fact, the support of statistical view shares some similar limitations to the support of MQT in DB2.

We customized the recommended MQTs by adding some local predicates from the TPC-H queries that could be routed to those MQTs.

You need to edit the syntax in the recommendations given by Design Advisor to use no system-maintained MQTs.

MQTs are not taken in consideration for static-compiled queries.

They are a special sub-type of user-maintained MQTs, the data in which is populated by the use of Replication.

Make sure the MQT is enabled for optimization.

You can use steps 3 and 4 as outlined above for system-maintained MQTs, to enable routing to a user-maintained MQT.

Finally, MQTs also add considerations to the roll-in process.

l Size limit for the definition of new indexes or MQTs.

You can find more information on the " Restrictions on MQTs over non-relational nicknames" page.

The MQT refresh is triggered when the table tab2 involved in its definition is populated.

REFRESH AGE specifies whether stale data in MQTs is tolerated.

Part 2 covers additional details related to MQTs, MDCs, and DPF partitioning keys, focusing on the data warehouse environment.

The data is loaded into the MQT using the " Load command" .

It shows how to define MQTs suitable for a workload and how to populate them.

Tip: Consider MQTs with deferred refresh.

You can also see a partial list of the recommendations, namely, to create 3 MQTs.

The CURRENT MAINTAINED TABLE TYPES special register controls the kinds of MQTs that are considered for routing.

Replication takes care of keeping the data in the MQT current.

Next in the output are recommendations related to MQTs, as shown below.

Listing 1 shows an example of creating a REFRESH IMMEDIATE system-maintained MQT.

For example, if data from March 2003 is removed from the fact table, then summary data for that month is removed from MQTs.

Listing 2 shows an example of creating a REFRESH DEFERRED user-maintained MQT.

The table below augments Table 3 with a row for MQTs.

These criteria are described in the section 'How to use and create MQTs'.

This step applies to user-maintained MQTs only and it enables population of data in the MQTs.

It also lists the restrictions on the use of MQTs.

Step 2b: This step is specific only to user-maintained MQTs.

Once the MQTs and relevant indexes are created, it is advisable to run " runstats" on MQTs in order to collect statistics.

In this section, we will show a snippet of db2exfmt output to explain how to determine use of MQT.

For example, an MQT can be partitioned using MDC or TP.

You can specify for example what REOPT options to use, or what MQT table to consider or what query optimization to use.

MQTs are important because they can significantly decrease the response time for complex queries.

For example, an MQT can be created on one or more tables that uses MDC and TP.

An MQT involving nicknames makes remote data available locally, thus saving trip(s) over the network to the remote data source.

There is a set of restrictions for MQTs over non-relational nicknames specific to each non-relational data source.

The considerations for designing MQTs in conjunction with partitioning features can be summarized by the following points

A user-maintained MQT can also be populated using a LOAD statement. The steps to accomplish this are listed below

Restrictions on MQTs over nicknames in a partitioned database environment

Step 8: Enable the MQT to be considered for optimization

MQT recommendations (first new ones, then existing ones to keep, and finally unused ones)

If your query is not using an MQT and you believe it should be, check the following

Maintenance of MQT Series Coalmine Pneumatic Roof Bolter

Function and application of MQT series pneumatic roofbolt(cable) drilling

Populating a federated_tool-maintained MQT with data

Populating a system-maintained MQT with data

An MQT can be used to enhance query performance through the pre-computation of aggregate values. For example

Populating a user-maintained MQT with data

Hints and tips on why a query is not using an MQT

Step 1: You could define an MQT as follows

Restrictions on MQTs over nicknames

Here is an example of how to use the Design Advisor to recommend MQTs for a federated workload. In this example

Some things to remember while using MQTs

Step 7: Collect statistics over MQTs using runstats

MQTs can be classified according to the mechanism that is used to populate them with data as follows