Optimizer Directives as Comments

Optimizer directives require valid comment indicators as delimiters.

The closing delimiter you use depends on the opening delimiter:
  • If { is the opening delimiter, you must use } as the closing delimiter.
  • If /* are the opening delimiters, you must use */ as the closing delimiters.
  • If -- are the opening delimiters, then no closing delimiter is needed.

An optimizer directive or a list 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 an optimizer directive is always a plus ( + ) sign. No blank space or other white-space character is allowed between the comment indicator and the plus sign.

You can use any of the following comment indicators:
  • A double hyphen ( -- ) delimiter

    The double hyphen needs no closing symbol because it specifies only the remainder of the current line as comment. When you use this style, include the optimizer directive on only the current line.

  • Braces ( { . . . } ) delimiters

    The comment extends from the left brace ( { ) until the next right ( } ) brace; this can be in the same line or in some subsequent line.

  • C-language style slash and asterisk ( /* . . . */ ) delimiters

    The comment extends from the initial slash-asterisk ( /* ) pair until the next asterisk-slash ( */ ) characters in the same line or in some subsequent line.

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

For additional information, see How to Enter SQL Comments.

If you specify multiple directives in the same query, you must separate them with a blank space, a comma, or by any character that you choose. It is recommended that you separate successive directives with a comma.

If the query declares an alias for a table, use the alias (rather than the actual table name) in the optimizer directive specification. Because system-generated index names begin with a blank character, use quotation marks to delimit such names.

Syntax errors in an optimizer directive do not cause a valid query to fail. You can use the SET EXPLAIN statement to obtain information related to such errors.

In distributed queries, optimizer directives can reference objects in other databases of the same server instance by using the database:table or database:owner.table notation to qualify the name of a table in another database of the local database server.