Hints are an extension of SQL that helps the SQL optimizer determine the query execution plan to use. Hints are specified by using embedded pseudo comments within query statements. The optimizer detects these hints and bases the query execution plan accordingly. Optimizer hints allow applications to be optimized under various conditions according to the data, query type, and the database. Hints not only provide solutions to performance problems that are occasionally encountered with queries, but also move the 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 might be using an index that you know is not optimal. In this case, you might want to force the optimizer to use one that produces faster results.
Hints are available for the following operations:
▪ 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.
Hints syntax
Hints are detected through a pseudo comment syntax as specified in SQL-92.
For information about the hints syntax, see Hints.
Enabling and disabling hints
By default, hints are enabled but they can be disabled by setting the SQL.EnableHints parameter to no, see SQL section. By default, hints are enabled.