RTPC

Enable Runtime Plan Choice (RTPC) query optimization.

Synopsis

[SQL]    RTPC=n

n is either 1 or 0. The default value is 1.

Description

When RTPC is enabled (n = 1), InterSystems SQL performs extensive optimization of the query based on query input values. For example, RTPC does the following:

Scans for outlier values and optimizes queries based on outlier information.
Efficiently estimates the selectivity of range conditions based on more detailed table statistics.
Efficiently estimates the selectivity of list conditions.
Evaluates explicit and implicit truth value conditions.
Evaluates whether certain resource-expensive functions, such as VECTOR_COSINE, are called on identical expressions in the SELECT clause and the ORDER BY clause of the same query to reduce the number of times the function is executed.

For more information about RTPC query optimization, see Using Runtime Plan Choice.

RTPC and BiasQueriesAsOutlier cannot both be applied to the same comparison condition in a query. If both the RTPC and BiasQueriesAsOutlier settings are set to 1, if RTPC is applied to a comparison condition (see Application of RTPC) then BiasQueriesAsOutlier is ignored for that comparison condition. When RTPC is set, InterSystems SQL determines whether to use outlier optimization on a per-query basis.

If AdaptiveMode is enabled (set to 1) and RTPC is disabled, then Adaptive Mode overrides the RTPC setting and enables Runtime Plan Choice.

In cases where RTPC is not applied to a comparison condition (regardless of the RTPC or AdaptiveMode settings being enabled), the current setting of BiasQueriesAsOutlier (enabled or disabled) can affect the query plan, review the query plan for more information.

Changing This Parameter

To set the desired value for RTPC from the Terminal, use the SetOption(“RTPC”)Opens in a new tab method of the %SYSTEM.SQL.UtilOpens in a new tab class. See the class reference for details.

You can also change RTPC with the Config.SQLOpens in a new tab class (as described in the class reference) or by editing the CPF in a text editor (as described in Editing the Active CPF).