Statement key and compilation key matching

The statement key identifies the application statement to which statement-level optimization guidelines apply. The matching method can be specified using the STMTMATCH element in the optimization profile.

When an SQL statement is compiled, various factors influence how the statement is interpreted semantically by the compiler. The SQL statement and the settings of SQL compiler parameters together form the compilation key. Each part of a statement key corresponds to some part of a compilation key.

A statement key is made of the following parts:
  • Statement text, which is the text of the statement as written in the application
  • Default schema, which is the schema name that is used as the implicit qualifier for unqualified table names. This part is optional but should be provided if there are unqualified table names in the statement.
  • Function path, which is the function path that is used when resolving unqualified function and data-type references. This part is optional but should be provided if there are unqualified user-defined functions or user-defined types in the statement.

When the data server compiles an SQL statement and finds an active optimization profile, it attempts to match each statement key in the optimization profile with the current compilation key. The type of matching depends if exact or inexact matching is specified in the optimization profile. You can specify which type of matching to use by specifying the STMTMATCH element in the optimization profile. By setting the EXACT attribute to TRUE or FALSE, you can enable either exact or inexact matching. If you do not specify the STMTMATCH element, exact matching is automatically enabled.

For exact matching, a statement key and compilation key match if each specified part of the statement key matches the corresponding part of the compilation key. If a part of the statement key is not specified, the omitted part is considered matched by default. Each unspecified part of the statement key is treated as a wild card that matches the corresponding part of any compilation key.

With inexact matching, literals, host variables, and parameter markers are ignored when the statement text from the statement key and compilation key is being matched.

Exact and inexact matching operates as follows:
  • Matching is case insensitive for keywords. For example, select can match SELECT.
  • Matching is case insensitive for nondelimited identifiers. For example, T1 can match t1.
  • Delimited and nondelimited identifiers can match except for one case. For example, T1 and "T1" will match, and so will t1 and "T1". However, t1 will not match with "t1".
There is no match between t1 and "t1" because t1 and "t1" represent different tables. The nondelimited identifier t1 is changed to uppercase and represents the table T1 but in the case of the delimited identifier "t1" is not changed and represents the table t1. In this scenario, the case sensitivity matters because the two identifiers represent different tables.

After the data server finds a statement key that matches the current compilation key, it stops searching. If there are multiple statement profiles whose statement keys match the current compilation key, only the first such statement profile (based on document order) is used.