Reasons to Use Optimizer Directives

You can use optimizer directives when the optimizer does not choose the best query plan to perform a query, because of the complexity of the query, or because it does not have enough information about the nature of the data. A poor query plan provides poor performance.

Before you decide when to use optimizer directives, you should understand what makes a good query plan.

Although the optimizer creates a query plan based on costs of using different table-access paths, join orders, and join plans, it generally chooses a query plan that follows these guidelines:

• Do not use an index when the database server must read a large portion of the table. For example, the following query might read most of the customer table:

  SELECT * FROM customer WHERE STATE <> "ALASKA";

  Assuming the customers are evenly spread among all 50 states, you might estimate that the database server must read 98 percent of the table. It is more efficient to read the table sequentially than to traverse an index (and subsequently the data pages) when the database server must read most of the rows.

• When you choose between indexes to access a table, use an index that can rule out the most rows. For example, consider the following query:

  SELECT * FROM customer
  WHERE state = "NEW YORK" AND order_date = "01/20/97"

  Assuming that 200,000 customers live in New York and only 1000 customers ordered on any one day, the optimizer most likely chooses an index on order_date rather than an index on state to perform the query.

• Place small tables or tables with restrictive filters early in the query plan. For example, consider the following query:

  SELECT * FROM customer, orders
     WHERE customer.customer_num = orders.customer_num
        AND    
     customer.state = "NEVADA";

  In this example, if you read the customer table first, you can rule out most of the rows by applying the filter that chooses all rows in which state = "NEVADA".

  By ruling out rows in the customer table, the database server does not read as many rows in the orders table (which might be significantly larger than the customer table).

• Choose a hash join when neither column in the join filter has an index.

  In the previous example, if customer.customer_num and orders.customer_num are not indexed, a hash join is probably the best join plan.

• Choose nested-loop joins if:
  • The number of rows retrieved from the outer table after the database server applies any table filters is small, and the inner table has an index that can be used to perform the join.
  • The index on the outermost table can be used to return rows in the order of the ORDER BY clause, eliminating the need for a sort.