Ordem da chave primária
|
Getting your Trinity Audio player ready...
|
A ideia desses 2 scripts é fazer uma simples análise e sugerir uma possível melhoria na ordem da chave primária de uma tabela específica.
Estes scripts não são para serem seguidos a ferro e fogo, existem outros fatores para serem considerados na ordem de uma PK, mas para quem não tem nada, e quer ter pelo menos uma ideia para onde ir montei eles como procedure para o SQL e como função para o Postgresql.
Versão SQL Server:
ALTER PROCEDURE usp_SugerirNovaOrdemChavePrimaria
@SchemaName NVARCHAR(128),
@TableName NVARCHAR(128)
AS
BEGIN
SET ARITHABORT OFF
SET ANSI_WARNINGS OFF
DECLARE @ColumnName NVARCHAR(128);
DECLARE @Sql NVARCHAR(MAX) = '';
DECLARE @OrderSuggestions NVARCHAR(MAX) = '';
DECLARE @DynamicSQL NVARCHAR(MAX);
DECLARE @Densidade FLOAT;
DECLARE @OrderTable TABLE (ColumnName NVARCHAR(128), Densidade FLOAT);
DECLARE @CurrentOrder NVARCHAR(MAX) = '';
-- Cursor para iterar sobre as colunas da chave primária
DECLARE ColumnCursor CURSOR FOR
SELECT COL_NAME(ic.object_id, ic.column_id)
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic ON i.object_id = ic.object_id AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1
AND OBJECT_NAME(ic.object_id) = @TableName;
OPEN ColumnCursor;
FETCH NEXT FROM ColumnCursor INTO @ColumnName;
WHILE @@FETCH_STATUS = 0
BEGIN
-- Constrói e executa a consulta SQL dinâmica para calcular a densidade para cada coluna
SET @DynamicSQL = 'SELECT @DensidadeOUT = (COUNT(DISTINCT ' + QUOTENAME(@ColumnName) + ') * 1.0 / COUNT(*)) FROM ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName);
EXEC sp_executesql @DynamicSQL, N'@DensidadeOUT FLOAT OUTPUT', @Densidade OUTPUT;
-- Adiciona os resultados em uma tabela temporária
INSERT INTO @OrderTable (ColumnName, Densidade) VALUES (@ColumnName, @Densidade);
-- Constrói a ordem atual
SET @CurrentOrder += @ColumnName + ', ';
FETCH NEXT FROM ColumnCursor INTO @ColumnName;
END
CLOSE ColumnCursor;
DEALLOCATE ColumnCursor;
-- Remove a última vírgula e espaço da ordem atual
IF LEN(@CurrentOrder) > 0
BEGIN
SET @CurrentOrder = LEFT(@CurrentOrder, LEN(@CurrentOrder) - 1);
END
-- Constrói a sugestão de ordem com base na densidade
SELECT @OrderSuggestions += ColumnName + ', '
FROM @OrderTable
ORDER BY Densidade ASC, ColumnName;
-- Remove a última vírgula e espaço
IF LEN(@OrderSuggestions) > 0
BEGIN
SET @OrderSuggestions = LEFT(@OrderSuggestions, LEN(@OrderSuggestions) - 1);
END
-- Compara a ordem atual com a sugerida
IF @CurrentOrder = @OrderSuggestions
BEGIN
SELECT @TableName as [Object], 'A ordem atual já é a melhor.' AS SuggestedOrder;
END
ELSE
BEGIN
-- Retorna a sugestão de ordem
SELECT @TableName as [Object], @OrderSuggestions AS SuggestedOrder;
END
END
Versão para Postgresql:
CREATE OR REPLACE FUNCTION mc1_sugerir_nova_ordem_chave_primaria(schema_name text, nome_tabela text)
RETURNS text AS $$
DECLARE
coluna_atual record;
ordem_sugerida text := '';
ordem_atual text := '';
query text;
BEGIN
-- Prepara a query para obter a ordem atual das colunas da chave primária
FOR coluna_atual IN
SELECT kcu.column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
WHERE tc.constraint_type = 'PRIMARY KEY'
AND tc.table_name = nome_tabela
AND tc.table_schema = schema_name
ORDER BY kcu.ordinal_position
LOOP
ordem_atual := ordem_atual || coluna_atual.column_name || ', ';
END LOOP;
-- Remove a última vírgula e espaço da ordem atual
IF LENGTH(ordem_atual) > 0 THEN
ordem_atual := substr(ordem_atual, 1, LENGTH(ordem_atual) - 2);
END IF;
-- Prepara a query para calcular a densidade das colunas da chave primária
query := format($f$
SELECT
kcu.column_name,
(COUNT(DISTINCT %I) * 1.0 / COUNT(*)) AS densidade
FROM
information_schema.table_constraints AS tc
JOIN
information_schema.key_column_usage AS kcu
ON tc.constraint_name = kcu.constraint_name
AND tc.table_schema = kcu.table_schema
JOIN
%I.%I AS t
ON true
WHERE
tc.constraint_type = 'PRIMARY KEY'
AND tc.table_name = %L
AND tc.table_schema = %L
GROUP BY
kcu.column_name
ORDER BY
densidade DESC, kcu.column_name
$f$, 'column_name', schema_name, nome_tabela, nome_tabela, schema_name);
-- Executa a consulta e processa os resultados
FOR coluna_atual IN EXECUTE query
LOOP
ordem_sugerida := ordem_sugerida || coluna_atual.column_name || ', ';
END LOOP;
-- Remove a última vírgula e espaço
IF LENGTH(ordem_sugerida) > 0 THEN
ordem_sugerida := substr(ordem_sugerida, 1, LENGTH(ordem_sugerida) - 2);
END IF;
-- Compara a ordem atual com a sugerida
IF ordem_atual = ordem_sugerida THEN
RETURN 'A ordem atual já é a melhor.';
ELSE
RETURN ordem_sugerida;
END IF;
END;
$$ LANGUAGE plpgsql;