Como usar Hint no Oracle?

Matsuo
abril 7, 2025
Banco De Dados

Super Dicionário de Hints do Oracle

Quando trabalhamos com Oracle, um ponto importante para otimizar o desempenho das consultas SQL é entender como orientar o otimizador a escolher determinados planos de execução. É exatamente para isso que servem os hints.

Ao inserir hints (dicas) diretamente na consulta, podemos influenciar o Cost Based Optimizer (CBO) a tomar decisões mais adequadas em situações específicas.

O que são Hints?

Os hints são instruções especiais escritas em comentários dentro de uma consulta SQL, que afetam o plano de execução escolhido pelo Oracle. O formato geral é:

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

Comentário especial: O Oracle reconhece a estrutura /*+ ... */ após o SELECT.
Case sensitive: Tecnicamente, o texto do hint não faz distinção entre maiúsculas e minúsculas, mas é fundamental que a sintaxe e o nome do hint estejam corretos para que ele seja reconhecido.
Uso do CBO: Qualquer hint (exceto o RULE) instrui o Oracle a usar o Cost Based Optimizer. Se a sintaxe do hint estiver incorreta, ele é simplesmente ignorado.

A

AND_EQUAL

Função: Força o otimizador a fazer um “merge scan” de vários índices de coluna única.
Sintaxe: AND_EQUAL(tabela índice1 índice2 ...)
Status: Depreciado no Oracle 10g e versões posteriores.

APPEND

Função: Em instruções INSERT, faz uma inserção em direct path, ignorando espaço livre no bloco. Pode reduzir o volume de redo gerado quando a tabela está em modo NOLOGGING.
Sintaxe: INSERT /*+ APPEND */ INTO tabela ...
Observação: Exige que a tabela não seja indexada de forma a impedir o uso de bulk inserts, e que não haja triggers ou constraints que impeçam direct path load.
Status: Suportado em várias versões. Continua válido.

APPEND_VALUES

Função: Similar ao APPEND, mas para instruções INSERT ... VALUES, introduzido em versões mais recentes (por volta do Oracle 12c+).
Sintaxe: INSERT /*+ APPEND_VALUES */ INTO tabela VALUES (...)
Observação: Otimiza a inserção de linhas únicas em modo de direct path. Útil quando não se está usando INSERT SELECT.

ALL_ROWS

Função: Otimiza a consulta para melhor throughput (menor consumo total de recursos).
Sintaxe: SELECT /*+ ALL_ROWS */ ...
Status: Válido e muito comum.

B

BITMAP

Função: Força o uso de um índice Bitmap para acessar a tabela.
Sintaxe: BITMAP(tabela índice_bitmap)
Status: Em algumas versões é considerado obsoleto ou pouco suportado, pois a detecção automática de bitmap normalmente funciona bem.

C

CACHE / NOCACHE

Função: Controla como os blocos lidos são colocados no Buffer Cache em caso de full table scan.
Sintaxe: CACHE(tabela) / NOCACHE(tabela)

CHOOSE

Função: Permite ao otimizador escolher entre rule-based e cost-based baseado na presença de estatísticas.
Sintaxe: SELECT /*+ CHOOSE */ ...
Status: Muito antigo, raramente usado hoje. A partir do 10g, o Oracle praticamente só usa CBO.

CLUSTER

Função: Força um cluster scan para a tabela.
Sintaxe: CLUSTER(tabela)

CURSOR_SHARING_EXACT

Função: Desativa a substituição de literais por binds, mesmo que CURSOR_SHARING esteja habilitado no banco.
Sintaxe: SELECT /*+ CURSOR_SHARING_EXACT */ ...

CARDINALITY

Função: Informa ao otimizador uma estimativa do número de linhas esperadas da tabela ou subquery.
Sintaxe: CARDINALITY(tabela ou subquery, linhas)
Observação: Pode ser útil quando as estatísticas não representam corretamente o volume real de dados.

D

DISTRIBUTE / DISTRIBUTE_JOIN

Função: Força a distribuição específica no join paralelo, semelhante ao PQ_DISTRIBUTE, mas usado principalmente em ambientes Exadata ou ambientes paralelos sofisticados.
Sintaxe: DISTRIBUTE_JOIN(tabela, método)
Observação: Os métodos mais comuns são BROADCAST, PARTITION, NONE.

DRIVING_SITE

Função: Em ambientes distribuídos, força a execução remota de parte da query em um site específico.
Sintaxe: SELECT /*+ DRIVING_SITE(tabela_ou_alias) */ ...

DYNAMIC_SAMPLING

Função: Controla a coleta de estatísticas dinâmicas em tempo de compilação para melhorar estimativas de seletividade.
Sintaxe: DYNAMIC_SAMPLING(nível) ou DYNAMIC_SAMPLING(tabela, nível)
Valores: De 0 a 11. Quanto maior, mais esforço (e potencial custo de compilação) para coletar estatísticas. (11 não é documentado , mas ele trabalha como AUTO, legal né? mas trabalha mal.)

E

EXPAND_GSET_TO_UNION

Função: Força a expansão de conjuntos (GROUPING SETS) para instruções UNION ALL.
Status: Depreciado no Oracle 10g.

F

FACT / NO_FACT

Função: Em star transformations, indica se a tabela é ou não considerada fact table.
Sintaxe: FACT(tabela) / NO_FACT(tabela)

FIRST_ROWS(n)

Função: Otimiza para resposta rápida dos primeiros n registros.
Sintaxe: SELECT /*+ FIRST_ROWS(10) */ ...

FULL

Função: Força um full table scan na tabela especificada.
Sintaxe: FULL(tabela)

G

GATHER_PLAN_STATISTICS

Função: Faz o Oracle coletar estatísticas detalhadas de execução para aquela query, que podem ser visualizadas com DBMS_XPLAN.DISPLAY_CURSOR.
Sintaxe: SELECT /*+ GATHER_PLAN_STATISTICS */ ...

H

HASH

Função: Força hash scan (normalmente para tabelas em clusters hash).
Sintaxe: HASH(tabela)

HASH_AJ

Função: Transforma subconsulta NOT IN em hash anti-join.
Sintaxe: HASH_AJ(tabela)
Status: Depreciado no 10g.

HASH_SJ

Função: Força hash semi-join ou hash anti-join.
Sintaxe: HASH_SJ(tabela)
Status: Antigo e pouco utilizado em versões modernas.

I

INDEX

Função: Força o uso de um índice específico ou conjunto de índices.
Sintaxe: INDEX(tabela índice) ou INDEX(tabela índice1 índice2 ...)

INDEX_ASC / INDEX_DESC

Função: Força index range scan em ordem ascendente ou descendente.
Sintaxe: INDEX_ASC(tabela índice) / INDEX_DESC(tabela índice)

INDEX_COMBINE

Função: Força o otimizador a usar uma combinação de índices Bitmap.
Sintaxe: INDEX_COMBINE(tabela índice1 índice2 ...)

INDEX_FFS

Função: Força fast full index scan, em vez de full table scan.
Sintaxe: INDEX_FFS(tabela índice)

INDEX_JOIN

Função: Força o index join de várias combinações de índices para cobrir todas as colunas necessárias.
Sintaxe: INDEX_JOIN(tabela índice1 índice2 ...)

INDEX_SS / INDEX_SS_ASC / INDEX_SS_DESC

Função: Força (ou impede) o index skip scan em ordem ascendente/descendente.
Status: Oracle 10g+.
Sintaxe: INDEX_SS(tabela índice), INDEX_SS_ASC(tabela índice), INDEX_SS_DESC(tabela índice)

NO_INDEX / NO_INDEX_FFS / NO_INDEX_SS

Função: Desabilitam uso de índice em geral, fast full index scan ou skip scan, respectivamente.
Sintaxe: NO_INDEX(tabela índice), NO_INDEX_FFS(tabela), NO_INDEX_SS(tabela índice)

INVISIBLE (NO_USE_INVISIBLE_INDEXES / USE_INVISIBLE_INDEXES)

Função: Controla se o Otimizador pode ou não considerar índices marcados como invisíveis.
Sintaxe: USE_INVISIBLE_INDEXES / NO_USE_INVISIBLE_INDEXES
Status: Disponível a partir do Oracle 11g R2+ (quando índices invisíveis foram introduzidos).

L

LEADING

Função: Define a tabela ou a sequência de tabelas que o Oracle deve usar primeiro em joins.
Sintaxe: LEADING(t1 t2 t3 ...)

M

MERGE

Função: Força a fusão de views no plano de execução.
Sintaxe: MERGE(tabela/view)

MERGE_AJ

Função: Transforma subconsulta NOT IN em merge anti-join.
Status: Depreciado no 10g.

MONITOR / NO_MONITOR

Função: Ativa ou desativa o monitoramento de query em tempo de execução (para V$SQL_MONITOR).
Sintaxe: SELECT /*+ MONITOR */ ... ou SELECT /*+ NO_MONITOR */ ...
Status: Introduzido no Oracle 11g para melhor monitoramento de SQL em tempo real.

N

NL_AJ / NL_SJ

Função: Forçam nested loop anti-join ou nested loop semi-join.
Status: Obsoletos em algumas versões.

NO_EXPAND

Função: Impede a expansão de consultas com OR ou IN-lists em UNION ALL.
Sintaxe: SELECT /*+ NO_EXPAND */ ...

NO_MERGE

Função: Impede a fusão de views.
Sintaxe: NO_MERGE(tabela/view)

NO_PARALLEL / NOPARALLEL

Função: Desativa a execução paralela, mesmo que a tabela tenha atributo PARALLEL.
Sintaxe: NO_PARALLEL(tabela)

NO_PARALLEL_INDEX

Função: Evita parallel index scan.
Sintaxe: NO_PARALLEL_INDEX(tabela índice)

NO_PUSH_PRED / NO_PUSH_SUBQ

Função: Impede que predicados ou subconsultas sejam “empurrados” para etapas anteriores do plano de execução.
Sintaxe: NO_PUSH_PRED, NO_PUSH_SUBQ

NO_QUERY_TRANSFORMATION

Função: Desabilita transformações internas de consulta (por exemplo, unnesting, etc.).
Sintaxe: NO_QUERY_TRANSFORMATION

NO_REWRITE / NOREWRITE

Função: Desabilita o uso de materialized views para reescrever a query.
Sintaxe: NO_REWRITE (renomeado a partir de NOREWRITE no 10g)

NO_STAR_TRANSFORMATION

Função: Impede que o otimizador faça a transformação star.
Sintaxe: NO_STAR_TRANSFORMATION

NO_USE_HASH / NO_USE_MERGE / NO_USE_NL

Função: Forçam o otimizador a não usar hash join, merge join ou nested loops.
Sintaxe: NO_USE_HASH(tabela), NO_USE_MERGE(tabela), NO_USE_NL(tabela)

NOCACHE

Ver CACHE / NOCACHE.

NOAPPEND

Função: Desabilita inserção via direct path, mesmo se APPEND estiver setado como default.
Sintaxe: NOAPPEND

O

OPT_PARAM

Função: Altera parâmetros de otimização apenas para aquela query (como se fosse um session-level hint).
Exemplo: SELECT /*+ OPT_PARAM('_optimizer_cost_based_transformation' 'off') */ ...
Status: Útil para tunar consultas sem alterar parâmetros de todo o banco.

ORDERED

Função: Faz o Oracle fazer joins na ordem em que as tabelas aparecem no FROM.
Sintaxe: ORDERED

ORDERED_PREDICATES

Função: Solicita que o Oracle avalie os predicados na ordem que aparecem na cláusula WHERE.
Status: Depreciado no 10g.

P

PARALLEL

Função: Ativa a execução paralela para a tabela especificada. Pode receber grau de paralelismo.
Sintaxe: PARALLEL(tabela, DOP) ou PARALLEL(tabela, DEFAULT)

PQ_DISTRIBUTE

Função: Controla a distribuição de linhas entre slaves no join paralelo.
Sintaxe: PQ_DISTRIBUTE(tabela, método_produtor, método_consumidor)

PUSH_PRED / PUSH_SUBQ

Função: Forçam o “empurrão” de predicados ou subconsultas para etapas anteriores do plano.
Sintaxe: PUSH_PRED, PUSH_SUBQ

Q

QB_NAME

Função: Nomeia um bloco de consulta (query block) para efeitos de análise e aplicação de outros hints.
Sintaxe: SELECT /*+ QB_NAME(meu_bloco) */ ...

R

REWRITE

Função: Força a reescrita da consulta usando materialized views disponíveis (independente do custo).
Sintaxe: REWRITE

RESULT_CACHE / NO_RESULT_CACHE

Função: Faz caching do resultado da query (ou impede caching) no Query Result Cache.
Sintaxe: SELECT /*+ RESULT_CACHE */ ... ou SELECT /*+ NO_RESULT_CACHE */ ...
Status: Disponível a partir do 11g para frente, se o result cache estiver habilitado.

ROWID

Função: Força acesso via ROWID na tabela especificada.
Sintaxe: ROWID(tabela)
Status: Depreciado no 10g.

RULE

Função: Força o uso do Rule-Based Optimizer. Ignora quaisquer outros hints.
Status: Obsoleto a partir do 10g (Oracle praticamente não usa mais RBO).

S

SPREAD_MIN_ANALYSIS

Função: Reduz algumas otimizações de planilha interna, focando em análises mínimas.
Status: Oracle 10g+.

STAR

Função: Força a tabela maior a ser joinada por último usando nested loops em índices.
Status: Depreciado no 10g.

STAR_TRANSFORMATION

Função: Permite o melhor plano gerado via transformação star.
Sintaxe: STAR_TRANSFORMATION

SWAP_JOIN_INPUTS

Função: Instrui o otimizador a inverter a ordem das tabelas no join, trocando o lado do driver e driven na operação de join
Sintaxe: SWAP_JOIN_INPUTS(tabela)
Observação: Pode melhorar o desempenho quando a ordem natural de join do otimizador não é a ideal.

SWAP_JOIN_INPUTS_AJ

Função: Similar ao SWAP_JOIN_INPUTS, mas aplicado para anti-joins. (subqueries transformadas em NOT EXISTS ou JOIN anti-join).
Sintaxe: SWAP_JOIN_INPUTS_AJ(tabela)

U

UNNEST / NO_UNNEST

Função: Controlam a “descompactação” (unnesting) de subconsultas, transformando-as em joins.
Sintaxe: UNNEST ou NO_UNNEST

USE_SEMI

Função: Força o otimizador a usar semi-joins (subqueries transformadas em EXISTS ou JOIN com filtragem).
Sintaxe: USE_SEMI(tabela)

USE_CONCAT

Função: Converte condições OR em um UNION ALL.
Sintaxe: SELECT /*+ USE_CONCAT */ ...

USE_ANTI

Função: Força o otimizador a usar anti-joins (subqueries transformadas em NOT EXISTS ou JOIN anti-join).
Sintaxe: USE_ANTI(tabela)

USE_HASH / USE_MERGE / USE_NL

Função: Forçam o uso de hash join, merge join ou nested loops para as tabelas especificadas.
Sintaxe: USE_HASH(tabela1 tabela2), USE_MERGE(tabela1 tabela2), USE_NL(tabela1 tabela2)

Dica sobre a Dica..

Versão do Oracle: Alguns hints surgiram ou foram removidos em certas versões. Sempre verifique a documentação da sua versão específica (10g, 11g, 12c, 18c, 19c, 21c, etc.).
Sintaxe Exata: Um simples erro de ortografia (por exemplo, esquecer o “+” em /*+ ... */, escrever INDEXS em vez de INDEX) faz o Oracle ignorar completamente o hint.
Estatísticas Atualizadas: O Cost Based Optimizer (CBO) funciona melhor com estatísticas de tabelas e índices coerentes. Sem elas, mesmo os hints podem produzir resultados inconsistentes.
Ferramentas de Análise:
- Use EXPLAIN PLAN e DBMS_XPLAN.DISPLAY (ou DBMS_XPLAN.DISPLAY_CURSOR) para verificar se o hint foi aplicado e qual foi o plano de execução.
- Verifique também colunas como NOTE que podem indicar se houve “unrecognized hint” ou “hint ignored”.
Relevância:
- Alguns hints (especialmente os marcados como dep. ou deprecated) podem não ter efeito.
- Muitos hints antigos foram substituídos por melhorias automáticas no CBO nas versões recentes.
Testes A/B: Sempre compare o plano de execução com e sem o hint. Às vezes, o otimizador padrão pode ser melhor do que forçar um caminho específico.
Documentação Oficial: Consulte o capítulo “Hints” do Oracle Database SQL Tuning Guide para sua versão.

Boa consulta e bons tunings!