CHAVE ESTRANGEIRA SEM INDICE

Tempo de leitura: menos de 1 minuto

o Oracle não cria índices em colunas de chave estrangeira. Isso pode levar a um desempenho fraco e deadlocks. Isso ocorre porque quando a coluna da tabela principal onde a chave estrangeira aponta é atualizada, a Oracle precisa verificar as restrições do FK da tabela filho. Na ausência de um índice, isso significa uma varredura de tabela completa e um bloqueio de mesa. Normalmente, chaves estrangeiras apontam para chaves primárias na tabela pai e você raramente precisa alterar uma chave primária, então você pensaria que isso não deveria acontecer com freqüência. Mas a exclusão de linhas da tabela pai também tem o potencial de violar as restrições do FK. Portanto, operações que excluem linhas na tabela pai podem resultar em deadlocks em alguns casos.
De qualquer forma, aqui está o script Oracle para identificar chaves estrangeiras sem índices:

 

select table_name, constraint_name,
cname1 || nvl2(cname2,’,’||cname2,null) ||
nvl2(cname3,’,’||cname3,null) || nvl2(cname4,’,’||cname4,null) ||
nvl2(cname5,’,’||cname5,null) || nvl2(cname6,’,’||cname6,null) ||
nvl2(cname7,’,’||cname7,null) || nvl2(cname8,’,’||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from sys.user_cons_columns ) a,
sys.user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = ‘R’
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from sys.user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)

Deixe uma resposta

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *