Informix Guide to SQL: Syntax Segments

The Optimizer Directives segment specifies keywords that you can use to partially or fully specify the query plan of the optimizer. Use this segment whenever you see a reference to Optimizer Directives in a syntax diagram.

Use one or more optimizer directives to partially or fully specify the query plan of the optimizer.

When you use an optimizer directive, the scope of the optimizer directive is for the current query only.

By default, optimizer directives are enabled. To obtain information about how specified directives are processed, view the output of the SET EXPLAIN statement. To disable optimizer directives, you must set either the IFX_DIRECTIVES environment variable to 0 or OFF or the DIRECTIVES parameter in the onconfig file to 0.

An optimizer directive or a string of optimizer directives immediately follows the DELETE, SELECT, or UPDATE keyword in the form of a comment.

After the comment symbol, the first character in a directive is always a plus (+) sign. No space is allowed between the comment symbol and the plus sign.

You can use any of the following comment styles:

• A double dash (--)

The double dash needs no closing symbol because it sets off only one comment line of text. When you use this style, include only the optimizer directive information on the comment line.

• Braces ({})
• C-language style comments, slash and asterisk (/* */).

In ESQL/C, the -keep command option to the esql compiler must be specified when you use C-style comments.

For more information on SQL comment symbols, see How to Enter SQL Comments.

If you use multiple directives in one query, you must separate them. You can separate directives with a space, a comma, or any character that you choose. However, Informix recommends that you separate directives with a comma.

Syntax errors that appear in an optimizer directive do not cause a working query to break. The output of the SET EXPLAIN statement contains information related to such errors.

In general, you can specify optimizer directives for any query block in a DELETE, SELECT, or UPDATE statement. However, you cannot use optimizer directives when your statement includes one of the following items:

• Distributed queries, that is, queries that access one or more remote tables
• In ESQL/C, statements that contain the WHERE CURRENT OF cursor clause

Use the ORDERED join-order directive to force the optimizer to join tables in the order in which they appear in the FROM clause.

For example, the following query forces the database server to join the dept and job tables, and then join the result with the emp table.

SELECT --+ ORDERED
	name, title, salary, dname
FROM dept, job, emp
Where title = 'clerk'
AND loc = 'Palo Alto'
AND emp.dno = dept.dno
AND emp.job= job.job

Because no predicates occur between the dept table and the job table, this query forces a Cartesian product.

When your query involves a view, the placement of the ORDERED join-order directive determines whether you are specifying a partial- or total-join order.

• Specifying partial-join order when you create a view

If you use the ORDERED join-order directive when you create a view, the base tables are joined contiguously in the order specified in the view definition.

For all subsequent queries on the view, the database server joins the base tables contiguously in the order specified in the view definition. When used in a view, the ORDERED directive does not affect the join order of other tables named in the FROM clause in a query.

• Specifying total-join order when you query a view

When you specify the ORDERED join-order directive in a query that uses a view, all tables are joined in the order specified, even those tables that form views. If a view is included in the query, the base tables are joined contiguously in the order specified in the view definition.

For examples that use the ORDERED join-order directive with views, refer to your Performance Guide.

Use the access-method directive to specify the manner in which the optimizer should search the tables.

.

Element	Purpose	Restrictions	Syntax
alias	Temporary alternative name assigned to the table or view in the FROM clause	When an alias is declared in the FROM clause, the alias also must be used in the optimizer directive.	Identifier, p. 4-205
comments	Any text that explains the purpose of the directive or other significant information	Text must appear outside the parenthesis, but inside the comment symbols.	Character string
index	Name of the index for which you want to specify a query plan directive	The index must be defined on the specified table. With the AVOID _INDEX directive, at least one index must be specified.	Database Object Name, p. 4-50
synonym	Name of the synonym for which you want to specify a query plan directive	The synonym and the table to which the synonym points must exist.	Database Object Name, p. 4-50
table	Name of the table for which you want to specify a query plan directive	The table must exist.	Database Object Name, p. 4-50

You can separate the elements that appear within the parentheses with either one or more spaces or by commas.

The following table lists the purpose of each of the access-method directives and how it affects the query plan of the optimizer.

Both the AVOID_FULL and INDEX keywords specify that the optimizer should avoid a full scan of a table. However, Informix recommends that you use the AVOID_FULL keyword to specify the intent to avoid a full scan on the table. In addition to specifying that the optimizer not use a full-table scan, the negative directive allows the optimizer to use indexes that are created after the access-method directive is specified.

In general, you can specify only one access-method directive per table. However, you can specify both AVOID_FULL and AVOID_INDEX for the same table. When you specify both of these access-method directives, the optimizer avoids performing a full scan of the table and it avoids using the specified index or indexes.

This combination of negative directives allows the optimizer to use indexes that are created after the access-method directives are specified.

Suppose that you have a table named emp, that contains the following indexes: loc_no, dept_no, and job_no. When you perform a SELECT that uses the table in the FROM clause you might direct the optimizer to access the table in one of the following ways:

SELECT {+INDEX(emp dept_no)}

In this example the access-method directive forces the optimizer to scan the index on the dept_no column.

SELECT {+AVOID_INDEX(emp loc_no, job_no), AVOID_FULL(emp)}

This example includes multiple access-method directives. These access-method directives also force the optimizer to scan the index on the dept_no column. However, if a new index, emp_no is created for table emp, the optimizer can consider it.

Use join-method directives to influence how the database server joins tables in a query.

You can separate the elements that appear within the parentheses with either one or more spaces or by commas.

The following table lists the purpose of each of the join-method directives

A join-method directive takes precedence over the join method forced by the OPTCOMPIND configuration parameter.

When you specify that you want to avoid or use a hash join, you can also specify the role of each table:

• BUILD

When used with USE_HASH, this keyword indicates that the specified table be used to construct a hash table. When used with AVOID_HASH, this keyword indicates that the specified table not be used to construct a hash table.

• PROBE

When used with USE_HASH, this keyword indicates that the specified table be used to probe the hash table. When used with AVOID_HASH, this keyword indicates that the specified table not be used to probe the hash table. You can specify multiple probe tables as long as there is at least one table for which you do not specify PROBE.

If neither the BUILD nor PROBE keyword is specified, the optimizer uses cost to determine the role of the table.

In the following example, the USE_HASH join-method directive forces the optimizer to construct a hash table on the dept table and consider only the hash table to join the dept table with the other tables. Because no other directives are specified, the optimizer can choose the least expensive join methods for the other joins in the query.

SELECT /*+ USE_HASH (dept /BUILD) 
	Force the optimizer to use the dept table to 
	construct a hash table */
name, title, salary, dname
FROM emp, dept, job
WHERE loc = 'Phoenix'
AND emp.dno = dept.dno
AND emp.job = job.job

Use optimization-goal directives to specify the measure that is used to determine the performance of a query result.

The two optimization-goal directives are:

• First_Rows

This directive tells the optimizer to choose a plan that optimizes the process of finding only the first screenful of rows that satisfies the query.

Use this option to decrease initial response time for queries that use an interactive mode or that require the return of only a few rows.

• All_Rows

This directive tells the optimizer to choose a plan that optimizes the process of finding all rows that satisfy the query.

This form of optimization is the default.

An optimization-goal directive takes precedence over the OPT_GOAL environment variable and the OPT_GOAL configuration parameter.

You cannot use an optimization-goal directive in the following instances:

• In a view definition
• In a subquery

The following query returns the names of the employees who earned the top fifty bonuses. The optimization-goal directive directs the optimizer to return the first screenful of rows as fast as possible.

SELECT {+FIRST_ROWS 

	Return the first screenful of rows as fast as possible}
FIRST 50 lname, fname, bonus
FROM emp
ORDER BY bonus DESC

For information about how to set the optimization goal for an entire session, see the SET OPTIMIZATION statement.

Use the EXPLAIN directive-mode directive to turn SET EXPLAIN ON for a particular query. You can use this directive to test and debug query plans. Information about the query plan is printed to the sqexplain.out file. This directive is redundant when SET EXPLAIN ON is already specified.

You cannot use the EXPLAIN directive-mode directive in two situations:

• In a view definition
• In a subquery

For information about the sqexplain.out file, see SET EXPLAIN.

For information about how to set optimization settings for an entire session, see SET OPTIMIZATION.

For a discussion about optimizer directives and performance, see your Performance Guide.

For information on the IFX_DIRECTIVES environment variable, see the Informix Guide to SQL: Reference.

For information on the DIRECTIVES parameter in the onconfig file, see your Administrator's Reference.