The super dictionary of Oracle hints

When you work with Oracle, an important part of optimizing SQL query performance is understanding how to guide the optimizer toward certain execution plans. That is exactly what hints are for.

By adding hints directly to the query, we can influence the Cost Based Optimizer (CBO) to make better decisions in specific situations.

What are hints?

Hints are special instructions written as comments inside a SQL query that affect the execution plan Oracle chooses. The general format is:

SELECT /*+ HINT_EXEMPLO */ coluna1, coluna2
FROM tabela
WHERE condicao;

• Special comment: Oracle recognizes the /*+ ... */ structure after the SELECT.
• Case sensitive: Technically, the hint text is not case sensitive, but the syntax and the hint name must be correct for it to be recognized.
• CBO usage: Any hint (except RULE) instructs Oracle to use the Cost Based Optimizer. If the hint syntax is incorrect, it is simply ignored.

A

AND_EQUAL

• Function: Forces the optimizer to perform a “merge scan” of several single-column indexes.
• Syntax: AND_EQUAL(tabela índice1 índice2 ...)
• Status: Deprecated in Oracle 10g and later releases.

APPEND

• Function: In INSERT statements, performs a direct path insert, ignoring free space in the block. It can reduce the amount of redo generated when the table is in NOLOGGING mode.
• Syntax: INSERT /*+ APPEND */ INTO tabela ...
• Note: Requires that the table is not indexed in a way that prevents bulk inserts, and that there are no triggers or constraints blocking a direct path load.
• Status: Supported across many releases. Still valid.

APPEND_VALUES

• Function: Similar to APPEND, but for INSERT ... VALUES statements, introduced in more recent releases (around Oracle 12c+).
• Syntax: INSERT /*+ APPEND_VALUES */ INTO tabela VALUES (...)
• Note: Optimizes single-row inserts in direct path mode. Useful when you are not using INSERT SELECT.

ALL_ROWS

• Function: Optimizes the query for the best throughput (lowest total resource consumption).
• Syntax: SELECT /*+ ALL_ROWS */ ...
• Status: Valid and very common.

B

BITMAP

• Function: Forces the use of a Bitmap index to access the table.
• Syntax: BITMAP(tabela índice_bitmap)
• Status: In some releases it is considered obsolete or barely supported, since automatic bitmap detection usually works well.

C

CACHE / NOCACHE

• Function: Controls how the blocks that are read get placed in the Buffer Cache during a full table scan.
• Syntax: CACHE(tabela) / NOCACHE(tabela)

CHOOSE

• Function: Lets the optimizer choose between rule-based and cost-based depending on the presence of statistics.
• Syntax: SELECT /*+ CHOOSE */ ...
• Status: Very old, rarely used today. From 10g on, Oracle essentially uses only the CBO.

CLUSTER

• Function: Forces a cluster scan for the table.
• Syntax: CLUSTER(tabela)

CURSOR_SHARING_EXACT

• Function: Disables the replacement of literals with binds, even if CURSOR_SHARING is enabled in the database.
• Syntax: SELECT /*+ CURSOR_SHARING_EXACT */ ...

CARDINALITY

• Function: Gives the optimizer an estimate of the number of rows expected from the table or subquery.
• Syntax: CARDINALITY(tabela ou subquery, linhas)
• Note: It can be useful when statistics do not correctly reflect the actual data volume.

D

DISTRIBUTE / DISTRIBUTE_JOIN

• Function: Forces a specific distribution in a parallel join, similar to PQ_DISTRIBUTE, but used mostly on Exadata or in sophisticated parallel environments.
• Syntax: DISTRIBUTE_JOIN(tabela, método)
• Note: The most common methods are BROADCAST, PARTITION, NONE.

DRIVING_SITE

• Function: In distributed environments, forces part of the query to run remotely at a specific site.
• Syntax: SELECT /*+ DRIVING_SITE(tabela_ou_alias) */ ...

DYNAMIC_SAMPLING

• Function: Controls dynamic statistics gathering at compile time to improve selectivity estimates.
• Syntax: DYNAMIC_SAMPLING(nível) or DYNAMIC_SAMPLING(tabela, nível)
• Values: From 0 to 11. The higher the level, the more effort (and potential compile-time cost) to gather statistics. (11 is not documented, but it works like AUTO. Cool, right? Except it works poorly.)

E

EXPAND_GSET_TO_UNION

• Function: Forces the expansion of sets (GROUPING SETS) into UNION ALL statements.
• Status: Deprecated in Oracle 10g.

F

FACT / NO_FACT

• Function: In star transformations, indicates whether or not the table is treated as a fact table.
• Syntax: FACT(tabela) / NO_FACT(tabela)

FIRST_ROWS(n)

• Function: Optimizes for a fast response for the first n rows.
• Syntax: SELECT /*+ FIRST_ROWS(10) */ ...

FULL

• Function: Forces a full table scan on the specified table.
• Syntax: FULL(tabela)

G

GATHER_PLAN_STATISTICS

• Function: Makes Oracle gather detailed execution statistics for that query, which can be viewed with DBMS_XPLAN.DISPLAY_CURSOR.
• Syntax: SELECT /*+ GATHER_PLAN_STATISTICS */ ...

H

HASH

• Function: Forces a hash scan (usually for tables in hash clusters).
• Syntax: HASH(tabela)

HASH_AJ

• Function: Turns a NOT IN subquery into a hash anti-join.
• Syntax: HASH_AJ(tabela)
• Status: Deprecated in 10g.

HASH_SJ

• Function: Forces a hash semi-join or hash anti-join.
• Syntax: HASH_SJ(tabela)
• Status: Old and rarely used in modern releases.

I

INDEX

• Function: Forces the use of a specific index or set of indexes.
• Syntax: INDEX(tabela índice) or INDEX(tabela índice1 índice2 ...)

INDEX_ASC / INDEX_DESC

• Function: Forces an index range scan in ascending or descending order.
• Syntax: INDEX_ASC(tabela índice) / INDEX_DESC(tabela índice)

INDEX_COMBINE

• Function: Forces the optimizer to use a combination of Bitmap indexes.
• Syntax: INDEX_COMBINE(tabela índice1 índice2 ...)

INDEX_FFS

• Function: Forces a fast full index scan instead of a full table scan.
• Syntax: INDEX_FFS(tabela índice)

INDEX_JOIN

• Function: Forces an index join across several index combinations to cover all the required columns.
• Syntax: INDEX_JOIN(tabela índice1 índice2 ...)

INDEX_SS / INDEX_SS_ASC / INDEX_SS_DESC

• Function: Forces (or prevents) an index skip scan in ascending/descending order.
• Status: Oracle 10g+.
• Syntax: INDEX_SS(tabela índice), INDEX_SS_ASC(tabela índice), INDEX_SS_DESC(tabela índice)

NO_INDEX / NO_INDEX_FFS / NO_INDEX_SS

• Function: Disable index usage in general, the fast full index scan, or the skip scan, respectively.
• Syntax: NO_INDEX(tabela índice), NO_INDEX_FFS(tabela), NO_INDEX_SS(tabela índice)

INVISIBLE (NO_USE_INVISIBLE_INDEXES / USE_INVISIBLE_INDEXES)

• Function: Controls whether or not the optimizer may consider indexes marked as invisible.
• Syntax: USE_INVISIBLE_INDEXES / NO_USE_INVISIBLE_INDEXES
• Status: Available from Oracle 11g R2+ (when invisible indexes were introduced).

L

LEADING

• Function: Defines the table or the sequence of tables Oracle should use first in joins.
• Syntax: LEADING(t1 t2 t3 ...)

M

MERGE

• Function: Forces the merging of views in the execution plan.
• Syntax: MERGE(tabela/view)

MERGE_AJ

• Function: Turns a NOT IN subquery into a merge anti-join.
• Status: Deprecated in 10g.

MONITOR / NO_MONITOR

• Function: Enables or disables query monitoring at run time (for V$SQL_MONITOR).
• Syntax: SELECT /*+ MONITOR */ ... or SELECT /*+ NO_MONITOR */ ...
• Status: Introduced in Oracle 11g for better real-time SQL monitoring.

N

NL_AJ / NL_SJ

• Function: Force a nested loop anti-join or nested loop semi-join.
• Status: Obsolete in some releases.

NO_EXPAND

• Function: Prevents queries with OR or IN-lists from being expanded into UNION ALL.
• Syntax: SELECT /*+ NO_EXPAND */ ...

NO_MERGE

• Function: Prevents the merging of views.
• Syntax: NO_MERGE(tabela/view)

NO_PARALLEL / NOPARALLEL

• Function: Disables parallel execution, even if the table has the PARALLEL attribute.
• Syntax: NO_PARALLEL(tabela)

NO_PARALLEL_INDEX

• Function: Avoids a parallel index scan.
• Syntax: NO_PARALLEL_INDEX(tabela índice)

NO_PUSH_PRED / NO_PUSH_SUBQ

• Function: Prevents predicates or subqueries from being “pushed” into earlier steps of the execution plan.
• Syntax: NO_PUSH_PRED, NO_PUSH_SUBQ

NO_QUERY_TRANSFORMATION

• Function: Disables internal query transformations (for example, unnesting, etc.).
• Syntax: NO_QUERY_TRANSFORMATION

NO_REWRITE / NOREWRITE

• Function: Disables the use of materialized views to rewrite the query.
• Syntax: NO_REWRITE (renamed from NOREWRITE in 10g)

NO_STAR_TRANSFORMATION

• Function: Prevents the optimizer from performing the star transformation.
• Syntax: NO_STAR_TRANSFORMATION

NO_USE_HASH / NO_USE_MERGE / NO_USE_NL

• Function: Force the optimizer not to use a hash join, merge join or nested loops.
• Syntax: NO_USE_HASH(tabela), NO_USE_MERGE(tabela), NO_USE_NL(tabela)

NOCACHE

• See CACHE / NOCACHE.

NOAPPEND

• Function: Disables direct path inserts, even if APPEND is set as the default.
• Syntax: NOAPPEND

O

OPT_PARAM

• Function: Changes optimizer parameters for that query only (as if it were a session-level hint).
• Example: SELECT /*+ OPT_PARAM('_optimizer_cost_based_transformation' 'off') */ ...
• Status: Useful for tuning queries without changing parameters for the entire database.

ORDERED

• Function: Makes Oracle perform joins in the order the tables appear in the FROM.
• Syntax: ORDERED

ORDERED_PREDICATES

• Function: Asks Oracle to evaluate predicates in the order they appear in the WHERE clause.
• Status: Deprecated in 10g.

P

PARALLEL

• Function: Enables parallel execution for the specified table. It can take a degree of parallelism.
• Syntax: PARALLEL(tabela, DOP) or PARALLEL(tabela, DEFAULT)

PQ_DISTRIBUTE

• Function: Controls how rows are distributed among slaves in a parallel join.
• Syntax: PQ_DISTRIBUTE(tabela, método_produtor, método_consumidor)

PUSH_PRED / PUSH_SUBQ

• Function: Force predicates or subqueries to be “pushed” into earlier steps of the plan.
• Syntax: PUSH_PRED, PUSH_SUBQ

Q

QB_NAME

• Function: Names a query block for analysis purposes and for applying other hints.
• Syntax: SELECT /*+ QB_NAME(meu_bloco) */ ...

R

REWRITE

• Function: Forces the query to be rewritten using available materialized views (regardless of cost).
• Syntax: REWRITE

RESULT_CACHE / NO_RESULT_CACHE

• Function: Caches the query result (or prevents caching) in the Query Result Cache.
• Syntax: SELECT /*+ RESULT_CACHE */ ... or SELECT /*+ NO_RESULT_CACHE */ ...
• Status: Available from 11g onward, if the result cache is enabled.

ROWID

• Function: Forces access via ROWID on the specified table.
• Syntax: ROWID(tabela)
• Status: Deprecated in 10g.

RULE

• Function: Forces the use of the Rule-Based Optimizer. It ignores any other hints.
• Status: Obsolete from 10g on (Oracle essentially no longer uses the RBO).

S

SPREAD_MIN_ANALYSIS

• Function: Reduces some internal spreadsheet optimizations, focusing on minimal analysis.
• Status: Oracle 10g+.

STAR

• Function: Forces the largest table to be joined last using nested loops on indexes.
• Status: Deprecated in 10g.

STAR_TRANSFORMATION

• Function: Allows the best plan generated via the star transformation.
• Syntax: STAR_TRANSFORMATION

SWAP_JOIN_INPUTS

• Function: Tells the optimizer to invert the order of the tables in the join, swapping the driver and driven sides of the join operation.
• Syntax: SWAP_JOIN_INPUTS(tabela)
• Note: It can improve performance when the optimizer’s natural join order is not ideal.

SWAP_JOIN_INPUTS_AJ

• Function: Similar to SWAP_JOIN_INPUTS, but applied to anti-joins (subqueries transformed into NOT EXISTS or an anti-join JOIN).
• Syntax: SWAP_JOIN_INPUTS_AJ(tabela)

U

UNNEST / NO_UNNEST

• Function: Control the “unnesting” of subqueries, turning them into joins.
• Syntax: UNNEST or NO_UNNEST

USE_SEMI

• Function: Forces the optimizer to use semi-joins (subqueries transformed into EXISTS or a JOIN with filtering).
• Syntax: USE_SEMI(tabela)

USE_CONCAT

• Function: Converts OR conditions into a UNION ALL.
• Syntax: SELECT /*+ USE_CONCAT */ ...

USE_ANTI

• Function: Forces the optimizer to use anti-joins (subqueries transformed into NOT EXISTS or an anti-join JOIN).
• Syntax: USE_ANTI(tabela)

USE_HASH / USE_MERGE / USE_NL

• Function: Force the use of a hash join, merge join or nested loops for the specified tables.
• Syntax: USE_HASH(tabela1 tabela2), USE_MERGE(tabela1 tabela2), USE_NL(tabela1 tabela2)

A hint about hints..

1. Oracle version: Some hints appeared or were removed in specific releases. Always check the documentation for your exact version (10g, 11g, 12c, 18c, 19c, 21c, etc.).
2. Exact syntax: A simple spelling mistake (for example, forgetting the “+” in /*+ ... */, or writing INDEXS instead of INDEX) makes Oracle ignore the hint entirely.
3. Up-to-date statistics: The Cost Based Optimizer (CBO) works best with consistent table and index statistics. Without them, even hints can produce inconsistent results.
4. Analysis tools:
   • Use EXPLAIN PLAN and DBMS_XPLAN.DISPLAY (or DBMS_XPLAN.DISPLAY_CURSOR) to check whether the hint was applied and what the execution plan was.
   • Also check columns such as NOTE, which can indicate an “unrecognized hint” or “hint ignored”.
5. Relevance:
   • Some hints (especially those marked dep. or deprecated) may have no effect.
   • Many old hints have been replaced by automatic CBO improvements in recent releases.
6. A/B testing: Always compare the execution plan with and without the hint. Sometimes the default optimizer can do better than forcing a specific path.
7. Official documentation: See the “Hints” chapter of the Oracle Database SQL Tuning Guide for your release.

Happy querying and happy tuning!