Índices no DB2 — Clustering, Non-clustering e Quando Usar

-- Sem clustering por data: uma query de range lê páginas espalhadas
SELECT * FROM TRANSACOES
 WHERE DT_TRANSACAO BETWEEN '2026-06-01' AND '2026-06-30';
-- → pode precisar de milhares de I/Os aleatórios

-- Com clustering por DT_TRANSACAO: todas as linhas do mês estão
--   juntas fisicamente → I/Os sequenciais, prefetch ativo → muito mais rápido

-- Índice único (chave primária é criada automaticamente com unique index)
CREATE UNIQUE INDEX SCHEMA1.XCONTA01
    ON SCHEMA1.CONTAS (COD_CONTA);

-- Clustering index por data de transação
CREATE INDEX SCHEMA1.XTRANS01
    ON SCHEMA1.TRANSACOES (DT_TRANSACAO)
    CLUSTER;

-- Índice composto — ordem importa (ver seção 5)
CREATE INDEX SCHEMA1.XTRANS02
    ON SCHEMA1.TRANSACOES (COD_AGENCIA, DT_TRANSACAO);

-- Índice com coluna descendente
CREATE INDEX SCHEMA1.XTRANS03
    ON SCHEMA1.TRANSACOES (DT_TRANSACAO DESC);

-- Remover índice
DROP INDEX SCHEMA1.XTRANS03;

-- 3 matching columns — usa o índice inteiro
WHERE COD_AGENCIA = '001'
  AND COD_PRODUTO = 'CC'
  AND DT_TRANSACAO = '2026-06-15'

-- 2 matching columns — usa as duas primeiras colunas do índice
WHERE COD_AGENCIA = '001'
  AND COD_PRODUTO = 'CC'

-- 1 matching column — usa só a primeira coluna
WHERE COD_AGENCIA = '001'

-- 0 matching columns — NÃO usa o índice (pula a primeira coluna)
WHERE COD_PRODUTO = 'CC'
  AND DT_TRANSACAO = '2026-06-15'

-- Função aplicada na coluna indexada → índice não é usado
WHERE YEAR(DT_TRANSACAO) = 2026
-- Solução: usar range explícito
WHERE DT_TRANSACAO BETWEEN '2026-01-01' AND '2026-12-31'

-- LIKE com % no início → índice não é usado
WHERE NOME LIKE '%SILVA'
-- Funciona: % só no final
WHERE NOME LIKE 'SILVA%'

-- NOT ou != geralmente impedido
WHERE STATUS != 'C'
-- Solução: reformular com OR ou IN se possível
WHERE STATUS IN ('A', 'P', 'S')

-- Coluna com baixa seletividade → tablespace scan pode ser mais barato
WHERE STATUS = 'A'  -- se 90% das linhas são 'A', scan é mais eficiente

//RUNSTATS EXEC PGM=DSNUTILB,PARM='DB2P,RUNSTATS'
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  RUNSTATS TABLESPACE SCHEMA1.TSCONTAS
    TABLE(SCHEMA1.CONTAS)
    INDEX(ALL)
    SHRLEVEL REFERENCE
/*

//REORG EXEC PGM=DSNUTILB,PARM='DB2P,REORG'
//SYSPRINT DD SYSOUT=*
//SYSIN    DD *
  REORG TABLESPACE SCHEMA1.TSCONTAS
    UNLOAD CONTINUE
    SHRLEVEL NONE
/*

      * Índice em COD_CONTA (chave primária) — 1 matching column
           EXEC SQL
               SELECT SALDO, STATUS
                 INTO :WS-SALDO, :WS-STATUS
                 FROM CONTAS
                WHERE COD_CONTA = :WS-COD-CONTA
           END-EXEC

      * EVITE: YEAR() na coluna indexada DT_ABERTURA
           EXEC SQL
               SELECT COUNT(*)
                 INTO :WS-QTD
                 FROM CONTAS
                WHERE YEAR(DT_ABERTURA) = :WS-ANO
           END-EXEC

      * PREFIRA: range explícito — usa o índice
           EXEC SQL
               SELECT COUNT(*)
                 INTO :WS-QTD
                 FROM CONTAS
                WHERE DT_ABERTURA BETWEEN :WS-DT-INI
                                      AND :WS-DT-FIM
           END-EXEC

      * Clustering index em DT_TRANSACAO — ORDER BY aproveita a ordem física
      * e elimina operação de sort interna
           EXEC SQL
               DECLARE CURSOR-TRANS CURSOR FOR
               SELECT COD_CONTA, VL_TRANSACAO, DT_TRANSACAO
                 FROM TRANSACOES
                WHERE COD_AGENCIA = :WS-AGENCIA
                  AND DT_TRANSACAO BETWEEN :WS-DT-INI
                                       AND :WS-DT-FIM
                ORDER BY DT_TRANSACAO
           END-EXEC