MQT supported function

 

Although a MQT can contain almost any query, the optimizer only supports a limited set of query functions when matching MQTs to user specified queries. The user specified query and the MQT query must both be supported by the SQE optimizer.

The supported function in the MQT query by the MQT matching algorithm includes:

• Single table and join queries

• WHERE clause

• GROUP BY and optional HAVING clauses

• ORDER BY

• FETCH FIRST n ROWS

• Views, common table expressions, and nested table expressions

• UNIONs

• Partitioned tables

There is limited support in the MQT matching algorithm for the following:

• Scalar subselects

• User Defined Functions (UDFs) and user defined table functions

• Recursive Common Table Expressions (RCTE)

• The following scalar functions:

  • ATAN2

  • DAYNAME

  • DBPARTITIONNAME

  • DECRYPT_BIT

  • DECRYPT_BINARY

  • DECRYPT_CHAR

  • DECRYPT_DB

  • DIFFERENCE

  • DLVALUE

  • DLURLPATH

  • DLURLPATHONLY

  • DLURLSEVER

  • DLURLSCHEME

  • DLURLCOMPLETE

  • ENCRYPT_RC2

  • GENERATE_UNIQUE

  • GETHINT

  • INSERT

  • MONTHNAME

  • NEXT_DAY

  • RADIANS

  • REPEAT

  • REPLACE

  • SOUNDEX

  • VARCHAR_FORMAT

IBM recommends that the MQT only contain references to columns, and column functions. In many environments, queries that contain constants will have the constants converted to parameter markers. This allows a much higher degree of ODP reuse. The MQT matching algorithm attempts to match constants in the MQT with parameter marks or host variable values in the query. However, in some complex cases this support is limited and may result in the MQT not matching the query.

 

Parent topic:

Materialized query table optimization

 

Related concepts

Query Dispatcher

 

Related reference

Details on the MQT matching algorithm