Este documento explora como o otimizador de consultas do Oracle Database decide utilizar índices compostos (multi-coluna) em diferentes cenários de filtragem. A análise concentra-se em situações em que a cláusula WHERE não inclui a primeira coluna definida no índice.

1. Cenário de Teste e Configuração

O experimento visa identificar se consultas sem a coluna de prefixo resultam em:

• Varredura completa da tabela (TABLE ACCESS FULL)
• Varredura por salto de índice (INDEX SKIP SCAN)
• Diferenças nos planos de execução gerados pelo otimizador

1.1. Ambiente de Execução

Testes realizados em um ambiente Oracle Database 23c. As tabelas de teste foram populadas com um conjunto de dados pequeno para observar o comportamento do otimizador sob condições controladas.

1.2. Estrutura dos Objetos de Banco

Foram criadas duas tabelas relacionadas por uma chave estrangeira lógica. Um índice composto foi definido na tabela principle.

-- Criação das tabelas de teste
CREATE TABLE tabela_principal (
    id_cliente NUMBER(6),
    nome_grupo VARCHAR2(30),
    sub_grupo  VARCHAR2(30),
    detalhe    VARCHAR2(100)
);

CREATE TABLE tabela_secundaria (
    id_cliente NUMBER(6),
    categoria  VARCHAR2(20),
    status     VARCHAR2(10)
);

-- Definição de índice composto (colunas X, Y, Z)
CREATE INDEX idx_principal_xyz ON tabela_principal(id_cliente, nome_grupo, sub_grupo);

-- Carga de dados de exemplo
INSERT ALL
    INTO tabela_principal VALUES (100, 'GrupoA', 'SubA1', 'Detalhe 1')
    INTO tabela_principal VALUES (200, 'GrupoA', 'SubA2', 'Detalhe 2')
    INTO tabela_principal VALUES (300, 'GrupoB', 'SubB1', 'Detalhe 3')
    INTO tabela_principal VALUES (400, 'GrupoB', 'SubB2', 'Detalhe 4')
    INTO tabela_principal VALUES (500, 'GrupoA', 'SubB1', 'Detalhe 5')
    INTO tabela_secundaria VALUES (100, 'Cat1', 'Ativo')
    INTO tabela_secundaria VALUES (200, 'Cat2', 'Ativo')
    INTO tabela_secundaria VALUES (300, 'Cat1', 'Inativo')
    INTO tabela_secundaria VALUES (400, 'Cat2', 'Ativo')
    INTO tabela_secundaria VALUES (500, 'Cat3', 'Ativo')
SELECT 1 FROM DUAL;

-- Atualização das estatísticas do esquema
DBMS_STATS.GATHER_SCHEMA_STATS('USUARIO_TESTE');

2. Análise dos Planos de Execução

2.1. Consulta sem a Coluna de Prefixo do Índice (Retornando Todas as Colunas)

Consulta SQL:

SELECT tp.*, ts.*
FROM tabela_principal tp
LEFT JOIN tabela_secundaria ts ON tp.id_cliente = ts.id_cliente
WHERE tp.nome_grupo = 'GrupoA' AND tp.sub_grupo = 'SubA1';

Plano de Execução Observado:

• Tabela Principal (tp): O otimizador escolheu uma TABLE ACCESS FULL.
• Justificativa: A consulta requer a coluna detalhe, que não faz parte do índice idx_principal_xyz. Como a cláusula WHERE não inclui id_cliente (primeira coluna do índice), o Oracle não conseguiu eficientemente usar o índice composto para localizar as linhas, resultando em uma varredura completa da tabela.

2.2. Consulta com a Coluna de Prefixo do Índice

Consulta SQL:

SELECT tp.*, ts.*
FROM tabela_principal tp
LEFT JOIN tabela_secundaria ts ON tp.id_cliente = ts.id_cliente
WHERE tp.id_cliente = 100 AND tp.nome_grupo = 'GrupoA' AND tp.sub_grupo = 'SubA1';

Plano de Execução Observado:

• Tabela Principal (tp): Continuou com TABLE ACCESS FULL.
• Justificativa: Para conjuntos de dados muito pequenos (neste caso, 5 linhas), o custo de realizar uma varredura completa da tabela é frequentemente inferior ao custo de uma busca por índice seguida de acesso à tabela ("lookup"). O otimizador priorizou a varredura completa por eficiência de I/O nesse contexto específico.

2.3. Consulta Apenas com Colunas Cobertas pelo Índice

Consulta SQL:

SELECT tp.id_cliente, tp.nome_grupo, tp.sub_grupo
FROM tabela_principal tp
LEFT JOIN tabela_secundaria ts ON tp.id_cliente = ts.id_cliente
WHERE tp.nome_grupo = 'GrupoA' AND tp.sub_grupo = 'SubA1';

Plano de Execução Observado:

• Tabela Principal (tp): O otimizador utilizou um INDEX SKIP SCAN no índice idx_principal_xyz.
• Justiifcativa: Como todas as colunas solicitadas (id_cliente, nome_grupo, sub_grupo) estão presentes no índice, não é necessário acessar a tabela base ("table access by index rowid"). O Oracle pode "pular" a primeira coluna (id_cliente) e filtrar diretamente no índice as combinações de nome_grupo e sub_grupo. Isso demonstra que a clusão do índice (index coverage) é um fator decisivo para a viabilidade de um INDEX SKIP SCAN.

3. Conclusões e Melhores Práticas

A partir dos testes, destaca-se o seguinte comportamento do otimizador:

1. Princípio do Prefixo: Para que um índice composto seja utilizado em uma varredura por intervalo (INDEX RANGE SCAN), a cláusula WHERE idealmente deve incluir sua coluna de prefixo.
2. Papel do INDEX SKIP SCAN: Quando a consulta necessita apenas das colunas contidas no índice, o Oracle pode optar por um INDEX SKIP SCAN mesmo sem a coluna de prefixo, evitando um acesso à tabela. Isso é uma otimização crucial para consultas em índices largos.
3. Impacto do Volume de Dados: Em tabelas com poucos registros, o otimizador pode preferir uma varredura completa (TABLE ACCESS FULL) a um acesso por índice, considerando o custo mínimo de varredura sequencial.

Recomendações de Design:

• Projete índices compostos alinhando a ordem das colunas com os padrões de filtro mais frequentes em suas consultas.
• Avalie a possibilidade de criar índices que incluam todas as colunas projetadas por consultas críticas, transformando-as em "covering indexes" e habilitando o INDEX SKIP SCAN ou INDEX FAST FULL SCAN.
• Mantenha as estatísticas do banco atualizadas para que o otimizador tome decisões precisas sobre o custo dos acessos.