Types of optimizer directives that are supported in SQL statements

Directives that are in SQL statements are embedded in queries. These directives include access-method directives, join-order directives, join-plan directives, and optimization-goal directives.

Include the directives in the SQL statement as a comment that occurs immediately after the SELECT, UPDATE, or DELETE keyword. The first character in a directive is always a plus (+) sign. In the following query, the ORDERED directive specifies that the tables should be joined in the same order as they are listed in the FROM clause. The AVOID_FULL directive specifies that the optimizer should discard any plans that include a full table scan on the listed table (employee). 
SELECT --+ORDERED, AVOID_FULL(e) * FROM employee e, department d
> 50000;

For a complete syntax description for directives, see the HCL OneDB™ Guide to SQL: Syntax.

To influence the choice of a query plan that the optimizer makes, you can alter the following aspects of a query:
  • Access method
  • Join order
  • Join method
  • Optimization goal
  • Star-join directives

You can also use EXPLAIN directives instead of the SET EXPLAIN statement to show the query plan. The following sections describe these aspects in detail.