Optimization-Goal Directives

Use optimization-goal directives to specify the measure that is used to determine the performance of a query result.


Element	Description	Restrictions	Syntax
comments	Text documenting the directive	Must appear between comment symbols	Character string

The two optimization-goal directives are:

FIRST_ROWS
This tells the optimizer to choose a plan that optimizes the process of finding only the first screenful of rows that satisfies the query. Use this option to decrease initial response time for queries that use an interactive mode or that require the return of only a few rows.
ALL_ROWS
This directive tells the optimizer to choose a plan that optimizes the process of finding all rows that satisfy the query.

This form of optimization is the default.

An optimization-goal directive takes precedence over the OPT_GOAL environment variable setting and over the OPT_GOAL configuration parameter.

For information about how to set the optimization goal for an entire session, see the SET OPTIMIZATION statement.

You cannot use an optimization-goal directive in the following contexts:

In a view definition
In a subquery

The following query returns the names of the employees who earned the top fifty bonuses. The optimization-goal directive directs the optimizer to return the first screenful of rows as fast as possible.

SELECT {+FIRST_ROWS 
   Return the first screenful of rows as fast as possible}
   LIMIT 50 fname, lname  FROM employees ORDER BY bonus DESC;