hint::= [MERGE JOIN | TRIPLE MERGE JOIN | LOOP JOIN | JOIN ORDER FIXED | INTERNAL SORT | EXTERNAL SORT | INDEX [REVERSE] table‑name.index‑name | PRIMARY KEY [REVERSE] table‑name | FULL SCANtable‑name | [NO] SORT BEFORE GROUP BY | UNION FOR OR | OR FOR OR | LOOP FOR OR | REPLAN]
Access requirements
Database user
Usage
Use hints to specify directives to the SQL optimizer for determining the query execution plan that is to be used. Hints are specified by using 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 that are occasionally encountered with queries, but also shift control of response times from the system to the user.
Hints are available for the following query elements:
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.
Hints are enabled by default. You can disable hints by using the SQL.EnableHints parameter, see SQL section.
Note You should test the hints by running the EXPLAIN PLAN output to ensure that the generated plan is optimal for the given query.
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 can be on its own line, or it can be at the end of the last line of the hint. For example, either of the following formats 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 previous examples). 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 new lines, 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 sample:
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 example:
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:
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 separate the hints with spaces or new lines as shown in the following examples:
SELECT --(* vendor(SOLID), product(Engine), option(hint) --MERGE JOIN --JOIN ORDER FIXED *)-- * 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;