HINT

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:

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.

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 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:

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;

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:

Everything after the first -- looks like a comment, and therefore your statement looks incomplete. You must do something like the following:

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:

Instead of the SQL standard format, you can use the following solidDB proprietary hint format, which is not nested into comments and is not part of the SQL standard:

For example, instead of the following statement:

SELECT
--(* vendor(solid), product(engine), option(hint)
-- REPLAN *)--
*
FROM TABLE_1, TABLE_2
WHERE TABLE_1.ID = TABLE_2.ID;

SELECT
HINT BEGIN REPLAN HINT END
*
FROM TABLE_1, TABLE_2
WHERE TABLE_1.ID = TABLE_2.ID;

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:

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;

Hints are enabled and disabled using the SQL.EnableHints parameter. By default, hints are enabled (SQL.EnableHints=yes).

SELECT
--(* vendor(SOLID), product(Engine), option(hint)
-- MERGE JOIN *)
--JOIN ORDER FIXED
* FROM TAB1, TAB2
WHERE TAB1.I >= TAB2.I;

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 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