Optimization-Goal Directives (IDS)

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-'
 

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}