Hints are an extension of SQL that provide directives to the SQL optimizer for determining the query execution plan that is 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 needed because due to various conditions with the data, user query, and database, the SQL optimizer is not always able to choose the best possible execution plan. For example, you might want to force a merge join because you know, unlike the optimizer, that your data is already sorted. Also, sometimes specific predicates in queries can cause performance problems that the optimizer cannot eliminate. The optimizer may be using an index that you know is not optimal. In this case, you may want to force the optimizer to use one that produces faster results.
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.
When there is an error in a hint specification, the whole SQL statement fails with an error message.
Enabling and disabling hints
Hints are enabled and disabled using the SQL.EnableHints parameter. By default, hints are enabled (SQL.EnableHints=yes).