Optimization-Goal Directives

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

Optimization-Goal Directives

{ ALL_ROWS | FIRST_ROWS }
[ comments ]
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;