Miscellaneous

Commonly Used Hints

Introduction

I've found Oracle hints to be incredibly useful for optimizing queries, but I often wished there was more information available beyond the official documentation. That's why I'm starting this series of guides, which will be rolled out over the next several months. Here, you'll find in-depth explanations and practical examples of Oracle hints, often going beyond the official documentation. We'll be covering a wide range of hints, so please check back regularly for updates and new additions. 

Query Blocks

• Using Query Blocks in Hints

Unsupported Hints

• Materialize

Hints for Access Paths

• FULL

• CLUSTER

• HASH

• INDEX and NO_INDEX

• INDEX_ASC and INDEX_DESC

• INDEX_COMBINE

• INDEX_JOIN

• INDEX_FFS and NO_INDEX_FFS

• INDEX_SS and NO_INDEX_SS

• INDEX_SS_ASC

Hints for Join Orders

• LEADING

• ORDERED

Hints for Join Operations

• USE_NL and NO_USE_NL

• USE_NL_WITH_INDEX

• USE_MERGE and NO_USE_MERGE

• USE_HASH and NO_USE_HASH

Hints for Online Application Upgrade

• CHANGE_DUPKEY_ERROR_INDEX

• IGNORE_ROW_ON_DUPKEY_INDEX

• RETRY_ON_ROW_CHANGE

Hints for Parallel Execution

• PARALLEL and NO_PARALLEL

• PARALLEL_INDEX and NO_PARALLEL_INDEX

• PQ_DISTRIBUTE

Hints for Query Transformations

• NO_QUERY_TRANSFORMATION

• USE_CONCAT

• NO_EXPAND

• REWRITE and NO_REWRITE

• MERGE and NO_MERGE

• STAR_TRANSFORMATION and NO_STAR_TRANSFORMATION

• FACT and NO_FACT

• UNNEST and NO_UNNEST

Additional Hints

• APPEND, APPEND_VALUES, and NOAPPEND

• CACHE and NOCACHE

• PUSH_PRED and NO_PUSH_PRED

• PUSH_SUBQ and NO_PUSH_SUBQ

• QB_NAME

• CURSOR_SHARING_EXACT

• DRIVING_SITE

• DYNAMIC_SAMPLING

• MODEL_MIN_ANALYSIS

Useful Initializtion Parameters

Optimizer Adaptive Features

Max Idle Blocker Time 

Useful Features of the Oracle Database

Real-Time Statistics (19c Exadata or Engineered Systems Only)

Useful Information

Sort-Merge-Joins - The what, why and when

Hash-Joins - The what, why and when

Published 

24th May 2022