Optimization-Goal Directives

In some queries, you might want to find only the first few rows in the result of a query (for example, an ESQL/C program opens a cursor for the query and performs a FETCH to find only the first row). Or you might know that all rows must be accessed and returned from the query. You can 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 the 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