Types of Hints

Oracle has kindly provided an alphabetical list of all documented hints. There are also a bunch of undocumented ones, and examples of their use can be found scattered all over the internet and in the multitude of books on Oracle performance tweaking. Undocumented hints are not more dangerous than their documented equivalents; Oracle simply has not gotten round to documenting them yet.

Oracle classifies hints based on their function:

  • Optimization goals and approaches;
  • Access path hints;
  • In-memory column store hints;
  • Join order hints;
  • Join operation hints;
  • Parallel execution hints;
  • Online application upgrade hints;
  • Query tranformation hints;
  • XML hints;
  • Other hints.

In Oracle Database 12c Performance Tuning Recipes, the authors provide two additional types of hints:

  • Data warehousing hints;
  • Optimizer hints.

The data warehousing hints are actually included in Oracle’s query transformation hints.

Access path and query transformation hints are by far the largest two categories, save for the miscellaneous group.

Although interesting in their own way we shall not discuss in-memory column store hints, online application upgrade hints, and XML hints. We shall now go through the remaining categories and discuss the most important hints for each category, so you too can supercharge your SQL statements. There are many more hints than we describe here, and you are invited to check the official documentation for more hints and details.