Optimization-goal directives

In some queries, you might want to find only the first few rows in the result of a query. Or, you might know that all rows must be accessed and returned from the query. You can use the optimization-goal directives to find the first row that satisfies the query or all rows that satisfy the query.

For example, you might want to find only the first few rows in the result of a query, because the program opens a cursor for the query and performs a FETCH to find only the first row.

Use the optimization-goal directives to optimize the query for either one of these cases:
  • FIRST_ROWS

    Choose a plan that optimizes the process of finding only the first row that satisfies the query.

  • ALL_ROWS

    Choose a plan that optimizes the process of finding all rows (the default behavior) that satisfy the query.

If you use the FIRST_ROWS directive, the optimizer might abandon a query plan that contains activities that are time-consuming up front. For example, a hash join might take too much time to create the hash table. If only a few rows must be returned, the optimizer might choose a nested-loop join instead.

In the following example, assume that the database has an index on employee.dept_no but not on department.dept_no. Without directives, the optimizer chooses a hash join. 
SELECT *
FROM employee, department
WHERE employee.dept_no = department.dept_no
However, with the FIRST_ROWS directive, the optimizer chooses a nested-loop join because of the high initial overhead required to create the hash table. 
SELECT {+first_rows} *
FROM employee, department
WHERE employee.dept_no = department.dept_no