Criação de Índices para Chaves Estrangeiras

No Oracle Database, a ausência de índices em colunas de chaves estrangeiras (foreign keys, ou FKs) pode ser um vilão silencioso para o desempenho e a estabilidade do sistema. Neste artigo, apresento uma query poderosa para identificar tabelas com FKs sem índices, explico como ela funciona e destaco os problemas de locks que podem surgir. Além disso, trago técnicas adicionais de tuning para você aplicar e compartilhar no seu blog.

O Problema: Chaves Estrangeiras sem Índices

Chaves estrangeiras garantem a integridade referencial entre tabelas, mas sem um índice nas colunas da FK na tabela filha, operações na tabela pai (como DELETE ou UPDATE) podem causar sérios impactos:

Full Table Scans: O Oracle precisa verificar todas as linhas da tabela filha para garantir que a integridade referencial não seja violada, resultando em leituras desnecessárias.
Locks Excessivos: Sem índice, o banco pode aplicar locks em nível de tabela (table-level locks) em vez de locks em nível de linha (row-level locks). Isso aumenta a contenção em ambientes com alta concorrência, travando outras sessões que tentam acessar a mesma tabela.
Degradação de Desempenho: Tabelas grandes sem índices amplificam o tempo de resposta, afetando usuários e processos críticos.

Por exemplo, imagine uma tabela DEPARTMENTS (pai) e EMPLOYEES (filha) com uma FK em DEPTNO. Um DELETE FROM DEPARTMENTS WHERE DEPTNO = 10 sem índice em EMPLOYEES.DEPTNO pode travar a tabela EMPLOYEES inteira, bloqueando outras transações.

A Solução: Uma Query para Identificar e Criar Índices

A query a seguir, executada como DBA, analisa todas as tabelas do banco, verifica FKs sem índices e sugere comandos CREATE INDEX para corrigir o problema:

SELECT owner,
       a.table_name,
       x.num_rows,
       'create index '||owner||'.'||a.constraint_name||'_I on '||owner||'.'||a.table_name||' ('||a.columns||');' cre_ind
FROM dba_tables x,
     ( SELECT a.table_name, a.constraint_name,
              MAX(DECODE(position, 1, cname, NULL)) ||
              MAX(DECODE(position, 2, ', '||cname, NULL)) ||
              MAX(DECODE(position, 3, ', '||cname, NULL)) ||
              MAX(DECODE(position, 4, ', '||cname, NULL)) ||
              MAX(DECODE(position, 5, ', '||cname, NULL)) ||
              MAX(DECODE(position, 6, ', '||cname, NULL)) ||
              MAX(DECODE(position, 7, ', '||cname, NULL)) ||
              MAX(DECODE(position, 8, ', '||cname, NULL)) ||
              MAX(DECODE(position, 9, ', '||cname, NULL)) ||
              MAX(DECODE(position, 10, ', '||cname, NULL)) ||
              MAX(DECODE(position, 11, ', '||cname, NULL)) ||
              MAX(DECODE(position, 12, ', '||cname, NULL)) ||
              MAX(DECODE(position, 13, ', '||cname, NULL)) ||
              MAX(DECODE(position, 14, ', '||cname, NULL)) ||
              MAX(DECODE(position, 15, ', '||cname, NULL)) ||
              MAX(DECODE(position, 16, ', '||cname, NULL)) columns
       FROM (SELECT SUBSTR(column_name, 1, 30) cname,
                    SUBSTR(table_name, 1, 30) table_name,
                    SUBSTR(constraint_name, 1, 30) constraint_name,
                    position
             FROM dba_cons_columns) a,
            dba_constraints b
       WHERE a.constraint_name = b.constraint_name
         AND b.constraint_type = 'R'
       GROUP BY a.table_name, a.constraint_name ) a,
     ( SELECT table_name, index_name,
              MAX(DECODE(position, 1, cname, NULL)) ||
              MAX(DECODE(position, 2, ', '||cname, NULL)) ||
              MAX(DECODE(position, 3, ', '||cname, NULL)) ||
              MAX(DECODE(position, 4, ', '||cname, NULL)) ||
              MAX(DECODE(position, 5, ', '||cname, NULL)) ||
              MAX(DECODE(position, 6, ', '||cname, NULL)) ||
              MAX(DECODE(position, 7, ', '||cname, NULL)) ||
              MAX(DECODE(position, 8, ', '||cname, NULL)) ||
              MAX(DECODE(position, 9, cname, NULL)) ||
              MAX(DECODE(position, 10, ', '||cname, NULL)) ||
              MAX(DECODE(position, 11, ', '||cname, NULL)) ||
              MAX(DECODE(position, 12, ', '||cname, NULL)) ||
              MAX(DECODE(position, 13, ', '||cname, NULL)) ||
              MAX(DECODE(position, 14, ', '||cname, NULL)) ||
              MAX(DECODE(position, 15, ', '||cname, NULL)) ||
              MAX(DECODE(position, 16, ', '||cname, NULL)) columns
       FROM (SELECT SUBSTR(column_name, 1, 30) cname,
                    SUBSTR(table_name, 1, 30) table_name,
                    SUBSTR(index_name, 1, 30) index_name,
                    column_position position
             FROM dba_ind_columns) dba_ind_columns
       GROUP BY table_name, index_name ) b
WHERE a.table_name = b.table_name (+)
  AND b.columns (+) LIKE a.columns || '%'
  AND a.table_name = x.table_name
  AND x.num_rows <> 0
ORDER BY DECODE(b.table_name, NULL, '****', 'ok'), x.num_rows DESC NULLS LAST;

Saída Típica

OWNER  TABLE_NAME  NUM_ROWS  CRE_IND
-----  ----------  --------  -------------------------------------------------
SCOTT  EMP         1000      create index SCOTT.FK_EMP_DEPT_I on SCOTT.EMP (DEPTNO);
HR     JOBS        500       [nenhum comando, pois já tem índice]

Locks e a Falta de Índices em FKs

Quando uma tabela pai é modificada, o Oracle bloqueia as linhas correspondentes na tabela filha para verificar a integridade. Sem um índice na FK:

O banco faz um full table scan na tabela filha, aumentando o tempo de execução.
Locks em nível de tabela podem ser aplicados, bloqueando todas as linhas da tabela filha, mesmo as não relacionadas à transação. Isso gera contenção, atrasando outras sessões.
Em cenários de alta concorrência, como sistemas OLTP, isso pode levar a esperas prolongadas ou deadlocks.

Com um índice, o Oracle usa acesso direto às linhas afetadas, aplicando locks apenas onde necessário e reduzindo drasticamente a contenção.

Técnicas Adicionais de Tuning

Além de criar índices em FKs, aqui estão outras estratégias para melhorar o desempenho e evitar problemas como locks:

Atualize Estatísticas Regularmente
- Use DBMS_STATS.GATHER_SCHEMA_STATS para manter as estatísticas atualizadas. Estatísticas desatualizadas em DBA_TABLES.num_rows podem levar a planos de execução ruins.
- Exemplo: EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT', estimate_percent => 20);
Analise Planos de Execução
- Use EXPLAIN PLAN ou DBMS_XPLAN.DISPLAY para verificar se consultas em tabelas com FKs estão usando índices adequadamente.
- Exemplo: EXPLAIN PLAN FOR SELECT * FROM EMP WHERE DEPTNO = 10; SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Considere Índices Bitmap em Tabelas de Baixa Cardinalidade
- Se a coluna da FK tem poucos valores distintos (ex.: STATUS com 'A' ou 'I'), um índice bitmap pode ser mais eficiente que um B-tree.
- Exemplo: CREATE BITMAP INDEX IDX_STATUS ON ORDERS(STATUS);
Reduza Transações Longas
- Transações longas que modificam tabelas pai ou filha mantêm locks por mais tempo, aumentando a chance de contenção. Commit ou rollback mais frequentes ajudam a liberar recursos.
Monitore Locks em Tempo Real
- Use a view GV$LOCK para identificar bloqueios causados por FKs sem índices:
```
  SELECT sid, type, lmode, request, ctime
  FROM GV$LOCK
  WHERE type = 'TM' AND request > 0;
```
- Isso mostra locks em nível de tabela (TM), que podem indicar a ausência de índices.
Particionamento de Tabelas
- Para tabelas grandes com FKs, considere particioná-las por intervalo ou hash nas colunas da FK. Isso reduz o escopo dos locks e melhora o desempenho.

A query apresentada é uma aliada poderosa para DBAs que querem evitar armadilhas de desempenho relacionadas a chaves estrangeiras. A falta de índices em FKs não só degrada a performance, mas também causa locks desnecessários, impactando a experiência do usuário. Combine essa análise com técnicas como estatísticas atualizadas, monitoramento proativo e particionamento para manter seu Oracle Database otimizado e livre de contenções. Experimente essas dicas no seu ambiente e compartilhe os resultados no seu blog!

Identificando e Criando Índices em Chaves Estrangeiras