Hints are used to specify directives to the SQL optimizer for determining the query execution plan that is to be used. Hints are specified through embedded pseudo comments within query statements. The optimizer detects these directives or hints and bases its query execution plan accordingly. Optimizer hints allow applications to be optimized under various conditions to the data, query type, and the database. They not only provide solutions to performance problems occasionally encountered with queries, but shift control of response times from the system to the user.
Hints are available for:
▪Selecting merge or nested loop join
▪Using a fixed join order as given in the from list
▪Selecting internal or external sort
▪Selecting a particular index
▪Selecting a table scan over an index scan
▪Selecting sorting before or after grouping
You can place hints in SQL statements as static strings, just after a SELECT, UPDATE, or DELETE keyword. Hints are not allowed after the INSERT keyword.
Pseudo comment identifier
The pseudo comment prefix is followed by identifying information. You must specify the vendor as SOLID, product as Engine, and the option, which is the pseudo comment class name, as a valid hint.
The terminator may be on its own line, or it may be at the end of the last line of the hint. For example, either of the following is acceptable:
Spacing is sensitive. In the pseudo comment prefix --(* and postfix *)-- , there can be no space between the parenthesis and the asterisk. There must be a space prior to the *)-- terminator, that is, prior to the asterisk (see the examples above). No space is required prior to the opening parenthesis in --(*. The terminator *)--cannot be on a line by itself without being after the comment delimiter --.
Table name resolution in optimizer hints
Table name resolution in optimizer hints is the same as in any table name in an SQL statement. This means that if there is a table alias name in the query, you must use the alias, not the table name, in the optimizer hints. For example:
SELECT --(* vendor(SOLID), product(Engine), option(hint) -- FULL SCAN emp_alias *)-- emp_alias.emp_id, employee_name, dependent_name FROM employee_table AS emp_alias LEFT OUTER JOIN dependent_table AS dep_alias ON (dep_alias.emp_id = emp_alias.emp_id) ORDER BY emp_alias.emp_id;
If you specify the table name when you should have specified the alias name, you will get the following error message:
102: Unused optimizer hint.
If you are not using an alias and you are using a table that is in another schema and/or another catalog, then make sure that in the hint you precede the table name with the schema and/or catalog name. For example:
SELECT --(* vendor(SOLID), product(Engine), option(hint) -- FULL SCAN sally_schema.employee_table *)-- emp_id, employee_name FROM sally_schema.employee_table;
Using hints with ODBC or JDBC
If you are using hints and you compose a query as a string and then submit that string using ODBC or JDBC, you must ensure that appropriate newline characters are embedded within that string to mark the end of the comments. Otherwise, you will get a syntax error. If you do not include any newlines, then all of the statement after the start of the first comment will look like a comment. For example, suppose that your code looks like the following:
strcpy(s, "SELECT --(* hint... *)-- col_name FROM table;");
Everything after the first "--" looks like a comment, and therefore your statement looks incomplete. You must do something like the following:
strcpy(s, "SELECT --(* hint... *)-- \n col_name FROM table;");
The embedded newline \n character terminates the comment. A useful technique for debugging is to print out the strings to make sure that they look correct. They should look like:
SELECT --(* hint ... *)--column_name FROM table_name...;
or
SELECT --(* hint ... *)--column_name FROM table_name...;
Subselects and multiple hints
Each subselect requires its own hint; for example, the following are valid uses of hints syntax:
INSERT INTO ... SELECT hint FROM ... UPDATE hint TABLE ... WHERE column = (SELECT hint ... FROM ...) DELETE hint TABLE ... WHERE column = (SELECT hint ... FROM ...)
When specifying multiple hints in one pseudo comment, you must separated the hints with commas as shown in the following examples:
Example 1:
SELECT --(* vendor(SOLID), product(Engine), option(hint) --MERGE JOIN --JOIN ORDER FIXED *)-- * FROM TAB1 A, TAB2 B; WHERE A.INTF = B.INTF;
Example 2:
SELECT --(* vendor(SOLID), product(Engine), option(hint) --INDEX TAB1.INDEX1 --INDEX TAB1.INDEX1 FULL SCAN TAB2 *)-- * FROM TAB1, TAB2 WHERE TAB1.INTF = TAB2.INTF;
Enabling and disabling hints
Hints are enabled and disabled using the SQL.EnableHints parameter. By default, hints are enabled (SQL.EnableHints=yes).
Examples
SELECT -(* vendor(SOLID), product(Engine), option(hint) --MERGE JOIN --JOIN ORDER FIXED -- *)-- col1, col2 FROM TAB1 A, TAB2 B; WHERE A.INTF = B.INTF;
SELECT --(* vendor(SOLID), product(Engine), option(hint) --INDEX TAB1.INDEX1 --INDEX TAB1.INDEX1 FULL SCAN TAB2 -- *)-- * FROM TAB1, TAB2 WHERE TAB1.INTF = TAB2.INTF;
SELECT --(* vendor(SOLID), product(Engine), option(hint) -- INDEX TAB1.IDX1 *)-- * FROM TAB1 WHERE I > 100
SELECT --(* vendor(SOLID), product(Engine), option(hint) -- INDEX MyCatalog.mySchema.TAB1.IDX1 *)-- * FROM TAB1 WHERE I > 100
SELECT --(* vendor(SOLID), product(Engine), option(hint) -- JOIN ORDER FIXED *)-- * FROM TAB1, TAB2 WHERE TAB1.I >= TAB2.I
SELECT --(* vendor(SOLID), product(Engine), option(hint) -- LOOP JOIN *)-- * FROM TAB1, TAB2 WHERE TAB1.I >= TAB2.I
SELECT --(* vendor(SOLID), product(Engine), option(hint) -- INDEX REVERSE MyCatalog.mySchema.TAB1.IDX1 *)-- * FROM TAB1 WHERE I > 100
SELECT --(* vendor(SOLID), product(Engine), option(hint) -- SORT BEFORE GROUP BY *)-- AVG(I) FROM TAB1 WHERE I > 10 GROUP BY I2
SELECT --(* vendor(SOLID), product(Engine), option(hint) -- INTERNAL SORT *)-- * FROM TAB1 WHERE I > 10 ORDER BY I2