FIRST_ROWS and ALL_ROWS Options

The FIRST_ROWS and ALL_ROWS keyword options identify two different query optimization goals:
  • FIRST_ROWS

    This option directs the optimizer to choose the query plan that returns the first qualifying record as soon as possible, ignoring plans that would sort records or create a hash table.

  • ALL_ROWS

    This option directs the optimizer to choose the query plan that returns all the qualifying records as quickly as possible.

Inline optimizer directives

Rather than choosing either of these SET OPTIMIZATION statement options, you might instead specify an optimization-goal directive for an individual query as a comment immediately following the SELECT keyword that begins the query or subquery. For more information about the syntax and options for inline optimizer directives for queries in DELETE, SELECT, or UPDATE statements, see Optimizer Directives.

External optimizer directives

Besides the query optimizer directives that the SET OPTIMIZATION statement can specify for queries in the current session, or the inline optimizer directives that can follow the SELECT keyword, the database server also supports a third format for influencing the choice of execution path by the query optimizer.

The DBA or user informix can execute the SAVE EXTERNAL DIRECTIVES statement to register external optimizer directives, also called external directives, in the sysdirectives table of the system catalog. If support for external directives has been enabled, the HCL OneDB™ database server applies these directives automatically to queries that match a specified SELECT statement. The ACTIVE, INACTIVE, or TEST ONLY keyword options of SAVE EXTERNAL DIRECTIVES statements respectively enable, disable, or restrict the scope of an external directive.

Setting the EXT_DIRECTIVES configuration parameter in the ONCONFIG file to 1 or 2, and setting the IFX_EXTDIRECTIVES client-side environment variable to 1 enables support for external directives.

Independent of the EXT_DIRECTIVES configuration setting or the IFX_EXTDIRECTIVES client-side settings, the SET ENVIRONMENT EXTDIRECTIVES setting of '1', on, or ON for the session environment enables external optimizer directives in the current user session, if any active external directives are registered in the sysdirectives table.

The SET EXPLAIN output file displays whether external directives are in effect for a query.

For more information about external optimizer directives, see Enabling or disabling external directives for a session.