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..

1. 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.).
2. 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.
3. 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.
4. 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”.
5. 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.
6. 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.
7. Documentação Oficial: Consulte o capítulo “Hints” do Oracle Database SQL Tuning Guide para sua versão.

Boa consulta e bons tunings!