Devo criar índices nas colunas das FKs?

Por Fabio Prado Oracle ACE
Publicado en Mayo 2017

Revisado por Marcelo Pivovar


Iremos explicar neste artigo uma muito comum que existe entre profissionais que trabalham com Bancos de Dados Oracle: Devo criar índices na FKs?

A resposta é simples: depende! Se índices nas FKs (foreign keys ou chaves estrangeiras) fossem úteis em 100% dos casos, certamente existiria um recurso no Oracle que faria a criação deles automaticamente, na respectiva coluna da FK, durante ou após o seu processo de criação. Índices são criados automaticamente somente na criação das PKs (primary keys ou chaves primárias), pois nestes casos é possível deduzir que serão executadas consultas ou atualizações futuras que usarão a respectiva coluna da PK como filtro, e a probabilidade de ganhos de performance nestas operações é alta com o uso de índices. como caminho de acesso. pelo Otimizador.

Voltando à questão do título deste artigo, quando então devo criar índices nas FKs? A documentação online oficial da Oracle chamada Oracle Database Concepts, capítulo 9 "Data Concurrency and Consistency", diz o seguinte:

Oracle Database maximizes the concurrency control of parent keys in relation to dependent foreign keys. Locking behavior depends on whether foreign key columns are indexed. If foreign keys are not indexed, then the child table will probably be locked more frequently, deadlocks will occur, and concurrency will be decreased. For this reason foreign keys should almost always be indexed. The only exception is when the matching unique or primary key is never updated or deleted.
Locks and Unindexed Foreign Keys

When both of the following conditions are true, the database acquires a full table lock on the child table:

    • No index exists on the foreign key column of the child table.
    • A session modifies a primary key in the parent table (for example, deletes a row or modifies primary key attributes) or merges rows into the parent table.

Note:
Inserts into the parent table do not acquire blocking table locks that prevent DML on the child table. In the case of inserts, the database acquires a lock on the child table that prevents structural changes, but not modifications of existing or newly added rows.

Resumindo o que está escrito acima, a Oracle diz que as tabelas que contém colunas de FKs não indexadas estão mais propensas a ter uma maior quantidade de locks, e até mesmo deadlocks. Por esta razão, as colunas de FKs devem ser quase sempre indexadas, sendo a única exceção, quando a coluna da PK ou UK (unique keys) relacionada na tabela pai nunca é atualizada ou deletada. Um fato curioso que acabei de comentar, é que poucos profissionais sabem que também é possível criar FKs no Oracle, com relacionamento junto a uma coluna que tem apenas uma UK (e não PK) na tabela pai.

É importante ressaltar (isso não está explicíto na documentação citada) que o Otimizador pesquisa internamente as linhas relacionadas nas tabelas filhas quando uma instrução DML de alteração ou deleção é executada na tabela pai. Desse modo, criar índices nas colunas das FKs irá otimizar essas consultas internas, reduzindo consequentemente, o tempo dos locks que ocorrem na tabela filha durante o tempo de execução desses DMLs.

Como acréscimo às informações fornecidas acima pela Oracle, informo também, que ao criar índices nas colunas das FKs, você também poderá otimizar SQLs que fazem join entre uma tabela pai e uma tabela filha. Normalmente o ganho de performance ocorre quando existe um filtro por range de valores na coluna da PK da tabela pai. Veja um exemplo no SQL abaixo, onde eu gero um plano de execução "estimado" antes e outro depois da criação do índice na coluna da FK da tabela filha (coluna prod_id da tabela despatch):

         explain plan for         select  p.prod_id, p.prod_name, d.desp_id        from    products p    
join despatch d on p.prod_id = d.prod_id where p.prod_id between 1 and 100;
select * from table(dbms_xplan.display);

Veja na "Imagem 01" o plano de execução antes da criação do índice na FK:

01

Imagem 01 - Plano de execução antes da criação da FK na coluna PROD_ID da tabela DESPATCH

Veja na imagem 02 o plano de execução depois da criação do índice na FK (create index ix_despatch_prodid on despatch(prod_id)):

02

Imagem 02 - Plano de execução depois da criação da FK na coluna PROD_ID da tabela DESPATCH

Comparando os 2 planos de execução estimados, é possível verificar que o custo do SQL caiu de 102.000 para 26.127, ou seja, após a criação do índice na coluna da FK, a execução do SQL deverá ter um ganho de desempenho de 390,40%. Ótimo, não?

Referências:
http://www.fabioprado.net/2017/03/devo-criar-indices-nas-fks.html


Fabio Prado atua na área de tecnologia desde o ano de 2000, possui experiência e profundos conhecimentos em Análise de Sistemas, programação (Dot Net) e Administração de Bancos de Dados Oracle. É DBA em uma empresa do governo, instrutor na empresa de treinamentos FABIOPRADO.NET, Oracle ACE Member, Organizador do DBA BRASIL e autor do blog fabioprado.net.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.