OPT_GOAL

onconfig.std value

-1

range of values

0 or -1

takes effect

When the database server is shut down and restarted

refer to

The following manuals:

• ALL_ROWS and FIRST_ROWS directives and on the SET OPTIMIZATION statement, in the IBM Informix Guide to SQL: Reference
• OPT_GOAL environment variable, in the IBM Informix Guide to SQL: Syntax
• Performance issues associated with setting an optimization goal, in the IBM Informix Performance Guide

The OPT_GOAL parameter enables you to specify one of the following optimization goals for queries:

• Optimize for FIRST ROWS
• Optimize for ALL ROWS

A value of 0 sets the optimization goal to FIRST_ROWS. A value of -1 sets the optimization goal to ALL_ROWS, which is the default.

When you set the optimization goal to optimize for FIRST ROWS, you specify that you want the database server to optimize queries for perceived response time. In other words, users of interactive applications perceive response time as the time that it takes to display data on the screen. Setting the optimization goal to FIRST ROWS configures the database server to return the first rows of data that satisfy the query.

When you set the optimization goal to optimize for ALL ROWS, you specify that you want the database server to optimize for the total execution time of the query. Making ALL ROWS the optimization goal instructs the database server to process the total query as quickly as possible, regardless of how long it takes to return the first rows to the application.

You can specify the optimization goal in one of four ways:

• By query (SELECT statement)

  Use the ALL_ROWS and FIRST_ROWS directives.

• By session

  Use the SET OPTIMIZATION statement.

• By environment

  Set the OPT_GOAL environment variable.

• By database server

  Set the OPT_GOAL configuration parameter.

To determine the optimization goal, the database server examines the settings in the order shown. The first setting encountered determines the optimization goal. For example, if a query includes the ALL_ROWS directive but the OPT_GOAL configuration parameter is set to FIRST_ROWS, the database server optimizes for ALL_ROWS, as the query specifies.