Introdução ao INDEX_JOIN

O hint INDEX_JOIN é utilizado no Oracle Database para forçar o otimizador a realizar uma junção de índices, evitando acesso à tabela base. Isso pode melhorar o desempenho em consultas que envolvem múltiplas colunas indexadas. Veja exemplos de uso e análise de performance.

Exemplo Básico de Configuração e Uso

Criando tabelas e índices de teste:

CREATE TABLE tbl_origem AS SELECT * FROM dba_objects;
CREATE TABLE tbl_destino AS SELECT * FROM dba_objects WHERE 1=2;
CREATE INDEX idx_obj_id ON tbl_origem(object_id);
CREATE INDEX idx_data_cria ON tbl_origem(created);

Executando uma consulta com o hint INDEX_JOIN:

SET AUTOTRACE TRACEONLY;
INSERT INTO tbl_destino
SELECT /*+ INDEX_JOIN(tbl_origem idx_obj_id idx_data_cria) */ 
       FLOOR((ROWNUM-1)/5) AS grupo, object_id 
FROM tbl_origem 
WHERE created = SYSDATE-1000 AND object_id IS NOT NULL;

Este comando insere dados na tabela destino, usando uma junção de índices para filtrar registros com base em object_id e created, sem acessar a tabela tbl_origem diretamente.

Documentação de Referência

Para mais detalhes sobre hints de otimização, consulte o Oracle Database SQL Tuning Guide.

Cenário Avançado com Monitoramento de Performance

Preparando dados de teste adicionais:

CREATE TABLE tabela_exemplo (
    campo1 VARCHAR2(100),
    campo2 VARCHAR2(100),
    campo3 VARCHAR2(100),
    campo4 VARCHAR2(100),
    campo5 VARCHAR2(100),
    campo6 VARCHAR2(100),
    campo7 VARCHAR2(100),
    data_campo8 DATE
);

INSERT INTO tabela_exemplo 
SELECT 
    TRUNC(DBMS_RANDOM.VALUE(1000,9000)),
    RPAD(TRUNC(DBMS_RANDOM.VALUE(1000,9000)),60,'B2'),
    RPAD(TRUNC(DBMS_RANDOM.VALUE(1000,9000)),60,'B3'),
    RPAD(TRUNC(DBMS_RANDOM.VALUE(1000,9000)),60,'B4'),
    RPAD(TRUNC(DBMS_RANDOM.VALUE(1000,9000)),60,'B5'),
    RPAD(TRUNC(DBMS_RANDOM.VALUE(1000,9000)),60,'B6'),
    RPAD(TRUNC(DBMS_RANDOM.VALUE(1000,9000)),60,'B7'),
    SYSDATE - TRUNC(DBMS_RANDOM.VALUE(1,9000))
FROM DUAL
CONNECT BY LEVEL < 100000;

CREATE INDEX idx_data8 ON tabela_exemplo(data_campo8);
CREATE INDEX idx_campo4 ON tabela_exemplo(campo4);

Criando uma tabela destino para inserção:

CREATE TABLE resultado_insert (
    grupo NUMBER,
    campo4 VARCHAR2(100)
);

Executando a consulta com INDEX_JOIN e monitorando:

INSERT INTO resultado_insert 
SELECT /*+ MONITOR INDEX_JOIN(tabela_exemplo idx_data8 idx_campo4) */ 
       FLOOR((ROWNUM-1)/5) AS grupo, campo4 
FROM tabela_exemplo 
WHERE data_campo8 <= SYSDATE AND campo4 IS NOT NULL;

Análise do Relatório de Monitoramento SQL

Após execução, o relatório SQL Monitor mostra detalhes de performance:

• Tempo decorrido: 2 segundos
• Atividades de IO: 61 MB lidos, 30 MB escritos
• Plano de execução: Utiliza HASH JOIN entre dois índices (idx_data8 e idx_campo4) sem acessar a tabela base.

No plano, a operação VIEW (index$_join$_002) combina dois INDEX FAST FULL SCAN, reduzindo o custo de leitura de dados.

Verificação de Tamanho e Incremento de Dados

Para medir o tamanho dos segmentos:

SELECT bytes/1024/1024, segment_name 
FROM dba_segments 
WHERE segment_name IN ('TABELA_EXEMPLO','IDX_DATA8','IDX_CAMPO4','RESULTADO_INSERT');

Calcule o tamanho dos dados inseridos usando funções de comprimento:

SELECT SUM(LENGTH(campo4))/1024/1024 FROM resultado_insert;
SELECT SUM(LENGTH(campo4))/1024/1024 FROM tabela_exemplo;

Para obter o incremento de dados com base no tempo, use SCN e timestamps. Exemplo para filtrar linhas inseridas em um período específico:

SELECT a.*, 
       TO_CHAR(SCN_TO_TIMESTAMP(DBMS_ROWID.ROWID_BLOCK_NUMBER(a.ROWID)), 'yyyy-mm-dd hh24:mi:ss') AS insercao_tm 
FROM resultado_insert a 
WHERE SCN_TO_TIMESTAMP(DBMS_ROWID.ROWID_BLOCK_NUMBER(a.ROWID)) > TO_DATE('2024-01','YYYY-MM')
  AND ROWNUM < 10;

Este método permite rastrear o volume de dados adicionados por intervalo de tempo, útil para monitoramento de crescimento.