Types of Directives That Are Included in SQL Statements

This section contains information on directives that are embedded in queries.

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 IBM Informix: 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 plan
• Join order
• Join plan
• Optimization goal

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.