Oracle SQL Hints --- Abstraction

SQL HINT description and demonstration

SQL Hint is one of most important approaches to change the activity of optimizer and SQL execution, it’s also pretty important for SQL tuning. For instance, HINT is a part of the SQL Profiler advised by SQL Tuning Advisor. In each Oracle version, corresponding to the SQL features changes, new hints will be introduced, and old hints may be obsolete. Oracle introduced a new dynamic view, V$SQL_HINT, to show in which version the hint was involved in, and in which version it began work as outline data. The hints are associated with special SQL features. It will work only if the related features are enabled. Take HASH_AJ for example, it’s a CBO (QKSFM_CBO) feature hint, and it will not work if the SQL optimizer mode is set to RBO.

Some hints are only effect in the internal recursive SQLs, cannot be used in user SQL directly.

The embedded hints in SQL are a piece of comment, with the format /*+ <hint 1> [<hint 2> ...]*/. One comment may involve multiple hints, and one SQL may also involve multiple hint comments. And the hint will work only if they exist in the comment following the key words, SELECT, UPDATE, INSERT, MERGE and DELETE. If the SQL is a complex query involved in sub-query, the hint could be written as global or local format.The local format hints exist in the sub-query, and it can only affect the sub-query. While the global format hits exist in the main part of the query, it can be specified to affect any object in whole query by adding <object>@<block>. The alias could be used to replace the object name.

Tip: Since the embedded SQL hint is a piece of comment, its format could also be --+<hint>. For example,

HELLODBA.COM>select --+full(u)

2 * from t_users u where user_id =1;

We will descript all of hints, and also give demonstration of their usage.

SQL Features

Below hierarchy diagram shows all SQL features and their dependencies. Be aware, some features may be based on multiple features.