MQT supported function

Although an 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_AES
    • ENCRYPT_RC2
    • ENCRYPT_TDES
    • GENERATE_UNIQUE
    • GETHINT
    • IDENTITY_VAL_LOCAL
    • INSERT
    • MONTHNAME
    • MONTHS_BETWEEN
    • NEXT_DAY
    • RAND
    • RAISE_ERROR
    • REPEAT
    • REPLACE
    • ROUND_TIMESTAMP
    • SOUNDEX
    • TIMESTAMP_FORMAT
    • TIMESTAMPDIFF
    • TRUNC_TIMESTAMP
    • VARCHAR_FORMAT
    • WEEK_ISO

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