Leonardo Pedroso Costa
MongoDB | SQL Server | Oracle

Listar FK’s sem índice no Oracle

Um erro muito comum em empresas que possuem equipes distintas de DBA, AD e Desenvolvimento é criar as estruturas de banco sem o devido cuidado em indexar as FK’s. Sistemas e melhorias são feitos diariamente e isso pode incluir novos dados, colunas, constraints e demais objetos no banco dedados.

Um script que costumo rodar semanalmente é o que verifica se as FK’s estão indexadas, pois dependendo do uso da tabela, podemso ter um aumento do wait TM CONTENTION. NO meu caso, todos os schemas começam com DB, portanto eu ajustei em 2 lugares para trazer somente os dados referente á esses schemas:

[code language=”sql”]
AND a.owner LIKE ‘DB%’
WHERE c.index_owner LIKE ‘DB%’
[/code]

Basta adaptar o script ás suas necessidades, ou se preferir não filtrar nada, retira esse trecho do código e execute-o.

O script é simples mas de grande utilidade, conforme pode ser visualizado abaixo:

[code language=”sql”]
SELECT CASE
WHEN b.table_name IS NULL THEN ‘unindexed’
ELSE ‘indexed’
END AS status,
a.owner,
a.table_name AS table_name,
a.constraint_name AS fk_name,
a.fk_columns AS fk_columns,
b.index_name AS index_name,
b.index_columns AS index_columns
FROM (SELECT a.owner,
a.table_name,
a.constraint_name,
Listagg(a.column_name, ‘,’)
within GROUP (ORDER BY a.position) fk_columns
FROM dba_cons_columns a,
dba_constraints b
WHERE a.constraint_name = b.constraint_name
AND b.constraint_type = ‘R’
AND a.owner LIKE ‘DB%’
AND a.owner = b.owner
GROUP BY a.owner,
a.table_name,
a.constraint_name) a,
(SELECT table_name,
index_name,
Listagg(c.column_name, ‘,’)
within GROUP (ORDER BY c.column_position) index_columns
FROM dba_ind_columns c
WHERE c.index_owner LIKE ‘DB%’
GROUP BY table_name,
index_name) b
WHERE a.table_name = b.table_name(+)
AND b.index_columns(+) LIKE a.fk_columns
|| ‘%’
ORDER BY 1 DESC,
2;
[/code]

Leonardo Pedroso

Leonardo Pedroso

Profissional de TI experiência em administração de banco de dados: SQL Server 2000 a 2019, Oracle 11g a 19c, PostgreSQL, MySQL, MongoDB, Cassandra. Atua realizando análise de desempenho, DR e HA, implantação, administração de banco de dados em cluster e implementação de projetos de banco de dados NoSQL (MongoDB, Cassandra), bem como automação de processos utilizando Shell script, Powershell (dbatools), e players de Cloud: Azure e Aws.