Devo criar índices nas colunas das FKs?

Olá pessoal,

     Estou escrevendo este artigo para esclarecer uma dúvida muito comum que recebo em mensagens de e-mail de leitores deste blog, que é dúvida também de grande parte dos alunos das turmas de SQL Tuning, quando abordo em sala de aula o tema relacionado: 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 (a Oracle não explica isso explicitamente 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:

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)):

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%. Bom né?

Espero que você tenha gostado deste artigo e que as informações lhe sejam úteis!

Deixe um comentário se você gostou ou se restar qualquer dúvida sobre o assunto.

Aprenda mais sobre este assunto ou sobre SQL Tuning em geral nos treinamentos de SQL Tuning em videoaulas ou telepresenciais/presenciais FABIOPRADO.NET

[]s