The query Specification

The query specification that follows the FOR keyword in SAVE EXTERNAL DIRECTIVES must specify the syntax of a valid SELECT statement, as described in SELECT statement. If the query text also includes any inline optimizer directives, the inline directives are ignored when external directives are applied to the query.

When external directives are enabled and the sysdirectives system catalog table is not empty, the database server compares every query with the query text of every ACTIVE external directive, and for queries executed by the DBA or user informix, with every TEST ONLY external directive. If an external directive has been applied to a query, output from the SET EXPLAIN statement indicates “EXTERNAL DIRECTIVES IN EFFECT” for that query.

The purpose of external directives is to improve the performance of queries that match the query string, but the use of such directives can potentially slow other queries, if the query optimizer must compare the query strings of a large number of active external directives with the text of every SELECT statement. For this reason, HCL recommends that the DBA not allow the sysdirectives table to accumulate more than a few ACTIVE rows. (Another way to avoid unintended performance impact on other queries is to disable this feature.)

If more than one SET EXTERNAL DIRECTIVES statements associate active external directives with the same query, the effect is unpredictable, because the optimizer uses the first sysdirectives row whose query string matches the query.

Related Statements

For information about optimizer directives and their syntax, see the segment “Optimizer Directives” in Optimizer Directives.

For information about the sysdirectives table and the IFX_EXTDIRECTIVES environment variable, see the HCL OneDB™ Guide to SQL: Reference.