Otimização de Índices

Tipos de Índices Oracle

CREATE INDEX idx_employees_email ON employees(email);
CREATE UNIQUE INDEX idx_emp_cpf ON employees(cpf);

-- Bom para: sexo (M/F), status (A/I), departamento (10 valores)
CREATE BITMAP INDEX idx_emp_gender ON employees(gender);
CREATE BITMAP INDEX idx_prod_category ON products(category_id);

-- Para queries com UPPER(), LOWER(), TO_DATE(), etc
CREATE INDEX idx_emp_upper_name ON employees(UPPER(name));
CREATE INDEX idx_ord_year ON orders(EXTRACT(YEAR FROM order_date));

-- Para: WHERE dept_id = 10 AND status = 'A'
CREATE INDEX idx_emp_dept_status ON employees(department_id, status);

-- Ordem importa! Este índice serve para:
-- WHERE department_id = X (usa índice)
-- WHERE department_id = X AND status = Y (usa índice completo)
-- WHERE status = Y (NÃO usa o índice - status não é a primeira coluna)

Monitorando Índices

-- Ver todos os índices de uma tabela
SELECT
    index_name,
    index_type,
    uniqueness,
    status,
    num_rows,
    distinct_keys,
    blevel,                    -- Profundidade da árvore (ideal: 2-3)
    leaf_blocks,               -- Blocos folha
    clustering_factor          -- Quão organizado está (quanto menor, melhor)
FROM dba_indexes
WHERE table_name = 'EMPLOYEES';

-- Ver colunas de cada índice
SELECT
    index_name,
    column_name,
    column_position,
    descend
FROM dba_ind_columns
WHERE table_name = 'EMPLOYEES'
ORDER BY index_name, column_position;

-- Monitorar uso de índices (12c+)
ALTER INDEX idx_employees_email MONITORING USAGE;

-- Após algumas horas/dias, verificar
SELECT * FROM v$object_usage WHERE index_name = 'IDX_EMPLOYEES_EMAIL';

-- Identificar índices não usados (candidatos a DROP)
SELECT
    index_name,
    table_name,
    used,
    start_monitoring,
    end_monitoring
FROM v$object_usage
WHERE used = 'NO' AND monitoring = 'YES';

Manutenção de Índices

-- Verificar fragmentação
SELECT
    index_name,
    blevel,                               -- > 4 indica problema
    leaf_blocks,
    (del_lf_rows / NULLIF(lf_rows, 0)) * 100 AS pct_deleted,  -- > 20% = fragmentado
    height,
    pct_used
FROM dba_ind_statistics
WHERE table_name = 'EMPLOYEES';

-- Rebuild índice (recria estrutura, otimiza espaço)
ALTER INDEX idx_employees_email REBUILD ONLINE;  -- ONLINE mantém acesso durante rebuild

-- Rebuild em paralelo (mais rápido)
ALTER INDEX idx_employees_dept REBUILD ONLINE PARALLEL 4;
ALTER INDEX idx_employees_dept NOPARALLEL;  -- Volta ao padrão após rebuild

-- Coalesce (reorganiza sem recriar - mais rápido que rebuild)
ALTER INDEX idx_employees_email COALESCE;

-- Rebuild de todos os índices de uma tablespace
SELECT 'ALTER INDEX ' || index_name || ' REBUILD ONLINE;'
FROM dba_indexes
WHERE tablespace_name = 'USERS'
  AND status = 'VALID';

-- Atualizar estatísticas de índices
EXEC DBMS_STATS.GATHER_INDEX_STATS('SCHEMA_NAME', 'IDX_EMPLOYEES_EMAIL');

-- Validar estrutura de índice
ANALYZE INDEX idx_employees_email VALIDATE STRUCTURE;