Miscellaneous

Commonly Used Hints

Introduction

The following guide shows you how to use hint in you queries.

• 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