Estou particionando uma tabela com base em uma coluna que não é uma chave primária? Eu li algumas informações conflitantes hoje sobre se a coluna de partição deve fazer parte da chave primária. Meu instinto diz que não, mas não tenho 100% de certeza. Então perguntas...
Este é um problema com o qual me deparo periodicamente e ainda não encontrei uma boa solução.
Supondo a seguinte estrutura de tabela
CREATE TABLE T
(
A INT PRIMARY KEY,
B CHAR(1000) NULL,
C CHAR(1000) NULL
)
e o requisito é determinar se uma das colunas anuláveis Bou Crealmente contém algum NULLvalor (e, em caso afirmativo, qual(is)).
Suponha também que a tabela contém milhões de linhas (e que não há estatísticas de coluna disponíveis que possam ser espiadas, pois estou interessado em uma solução mais genérica para essa classe de consultas).
Posso pensar em algumas maneiras de abordar isso, mas todas têm pontos fracos.
Duas EXISTSdeclarações separadas. Isso teria a vantagem de permitir que as consultas parem de varrer antecipadamente assim que um NULLfor encontrado. Mas se ambas as colunas de fato não contiverem nenhum NULLs, resultarão em duas varreduras completas.
Consulta agregada única
SELECT
MAX(CASE WHEN B IS NULL THEN 1 ELSE 0 END) AS B,
MAX(CASE WHEN C IS NULL THEN 1 ELSE 0 END) AS C
FROM T
Isso pode processar as duas colunas ao mesmo tempo, portanto, o pior caso é uma varredura completa. A desvantagem é que, mesmo que encontre um NULLem ambas as colunas muito cedo, a consulta ainda acabará varrendo todo o restante da tabela.
Variáveis do usuário
Eu posso pensar em uma terceira maneira de fazer isso
BEGIN TRY
DECLARE @B INT, @C INT, @D INT
SELECT
@B = CASE WHEN B IS NULL THEN 1 ELSE @B END,
@C = CASE WHEN C IS NULL THEN 1 ELSE @C END,
/*Divide by zero error if both @B and @C are 1.
Might happen next row as no guarantee of order of
assignments*/
@D = 1 / (2 - (@B + @C))
FROM T
OPTION (MAXDOP 1)
END TRY
BEGIN CATCH
IF ERROR_NUMBER() = 8134 /*Divide by zero*/
BEGIN
SELECT 'B,C both contain NULLs'
RETURN;
END
ELSE
RETURN;
END CATCH
SELECT ISNULL(@B,0),
ISNULL(@C,0)
mas isso não é adequado para código de produção, pois o comportamento correto para uma consulta de concatenação agregada é indefinido. e encerrar a varredura lançando um erro é uma solução horrível de qualquer maneira.
Existe outra opção que combine os pontos fortes das abordagens acima?
Editar
Apenas para atualizar isso com os resultados que recebo em termos de leituras das respostas enviadas até agora (usando os dados de teste do @ypercube)
+----------+------------+------+---------+----------+----------------------+----------+------------------+
| | 2 * EXISTS | CASE | Kejser | Kejser | Kejser | ypercube | 8kb |
+----------+------------+------+---------+----------+----------------------+----------+------------------+
| | | | | MAXDOP 1 | HASH GROUP, MAXDOP 1 | | |
| No Nulls | 15208 | 7604 | 8343 | 7604 | 7604 | 15208 | 8346 (8343+3) |
| One Null | 7613 | 7604 | 8343 | 7604 | 7604 | 7620 | 7630 (25+7602+3) |
| Two Null | 23 | 7604 | 8343 | 7604 | 7604 | 30 | 30 (18+12) |
+----------+------------+------+---------+----------+----------------------+----------+------------------+
Para a resposta de @Thomas, mudei TOP 3para TOP 2potencialmente permitir que ela saísse mais cedo. Eu tenho um plano paralelo por padrão para essa resposta, então também tentei com uma MAXDOP 1dica para tornar o número de leituras mais comparável aos outros planos. Fiquei um pouco surpreso com os resultados, pois no meu teste anterior eu havia visto esse curto-circuito na consulta sem ler a tabela inteira.
O plano para meus dados de teste que curtos-circuitos está abaixo
O plano para os dados do ypercube é
Portanto, ele adiciona um operador de classificação de bloqueio ao plano. Eu também tentei com a HASH GROUPdica, mas isso ainda acaba lendo todas as linhas
Portanto, a chave parece ser obter um hash match (flow distinct)operador para permitir que esse plano entre em curto-circuito, pois as outras alternativas bloquearão e consumirão todas as linhas de qualquer maneira. Não acho que haja uma dica para forçar isso especificamente, mas aparentemente "em geral, o otimizador escolhe um Flow Distinct onde determina que são necessárias menos linhas de saída do que valores distintos no conjunto de entrada". .
Os dados do @ypercube têm apenas 1 linha em cada coluna com NULLvalores (cardinalidade da tabela = 30300) e as linhas estimadas que entram e saem do operador são ambas 1. Ao tornar o predicado um pouco mais opaco para o otimizador, gerou um plano com o operador Flow Distinct.
SELECT TOP 2 *
FROM (SELECT DISTINCT
CASE WHEN b IS NULL THEN NULL ELSE 'foo' END AS b
, CASE WHEN c IS NULL THEN NULL ELSE 'bar' END AS c
FROM test T
WHERE LEFT(b,1) + LEFT(c,1) IS NULL
) AS DT
Editar 2
Um último ajuste que me ocorreu é que a consulta acima ainda pode acabar processando mais linhas do que o necessário caso a primeira linha que encontrar com a NULLtenha NULLs na coluna Be no C. Ele continuará digitalizando em vez de sair imediatamente. Uma maneira de evitar isso seria desarticular as linhas à medida que são digitalizadas. Então, minha correção final para a resposta de Thomas Kejser está abaixo
SELECT DISTINCT TOP 2 NullExists
FROM test T
CROSS APPLY (VALUES(CASE WHEN b IS NULL THEN 'b' END),
(CASE WHEN c IS NULL THEN 'c' END)) V(NullExists)
WHERE NullExists IS NOT NULL
Provavelmente seria melhor que o predicado fosse, WHERE (b IS NULL OR c IS NULL) AND NullExists IS NOT NULLmas contra os dados de teste anteriores que não me dê um plano com um Flow Distinct, enquanto o NullExists IS NOT NULLoutro faz (plano abaixo).
Eu herdei um grande banco de dados (SQLServer) com centenas de colunas que representam quantidades de uma coisa ou outra. As unidades para esses valores (por exemplo, "galões", "polegadas", etc) são armazenadas no campo MS_Description de Extended Properties. Eu estou querendo saber se existe uma maneira melhor de armazenar essas informações. Suponho que seja bom para fins de documentação, mas seria difícil fazer cálculos robustos de conversão de unidades com base nesses dados. Neste ponto, não estou preparado para fazer uma mudança invasiva, mas se tiver a chance de fazê-lo, qual é a melhor prática recomendada a esse respeito? As opções, de cara, podem incluir:
ATUALIZAÇÃO: Depois de ler a resposta de @Todd Everett, uma possível solução me ocorreu, então vou em frente e respondo à minha própria pergunta. (Veja abaixo)
O IntelliSense no SSMS não está atualizando, a menos que eu o desligue e reinicie. Tentei desconectar do servidor e reconectar enquanto a sessão do SSMS está ativa, mas não está funcionando.
Passos para recriar:
Como posso forçar o IntelliSense a atualizar sem reiniciar o SSMS?
Eu realmente preciso de algum conhecimento sobre mysql. Eu sou um novato no mysql e estou vendo alguma falha no servidor do meu banco de dados na última semana 1.
Estou usando o mysql 5.1.36 no Ubuntu. Este é um servidor mysql dedicado com dual core e 4GB de memória e 40GB SSD.
Os erros de registro são:
120413 23:57:15 [Note] Plugin 'FEDERATED' is disabled.
120413 23:57:15 [Warning] option 'innodb-autoextend-increment': unsigned value 2000 adjusted to 1000
120413 23:57:15 InnoDB: Initializing buffer pool, size = 2.9G
120413 23:57:15 InnoDB: Completed initialization of buffer pool
120413 23:57:16 InnoDB: Started; log sequence number 0 44234
120413 23:57:16 [Note] Event Scheduler: Loaded 0 events
120413 23:57:16 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.58-1ubuntu1-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
120414 0:00:25 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=e2-relay-bin' to avoid this problem.
120414 0:00:25 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='', master_log_pos='4'. New state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87039427'.
120414 0:58:37 [Note] 'CHANGE MASTER TO executed'. Previous state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87039427'. New state master_host='', master_port='3306', master_log_file='mysql-bin.000043', master_log_pos='87846901'.
120414 2:20:34 InnoDB: ERROR: the age of the last checkpoint is 241588252,
InnoDB: which exceeds the log group capacity 241588224.
InnoDB: If you are using big BLOB or TEXT rows, you must set the
InnoDB: combined size of log files at least 10 times bigger than the
InnoDB: largest such row.
My.cnf é como abaixo.
default-storage-engine=innodb
default-table-type=innodb
key_buffer = 384M
max_allowed_packet = 64M
thread_stack = 256K
thread_cache_size = 16
max_heap_table_size = 64M
myisam_sort_buffer_size = 64M
join_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 3M
tmp_table_size = 64M
# Innodb changes
innodb_additional_mem_pool_size = 16M
innodb_autoextend_increment = 2000
innodb_buffer_pool_size = 3000M #As current Db is around 1.2G.
innodb_file_per_table
innodb_data_file_path = ibdata1:512M;ibdata2:512M:autoextend
innodb_flush_log_at_trx_commit = 2 #For more reliablity use 1
innodb_flush_method = O_DIRECT
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M #Transaction Log up to 1/4 Buffer Pool
innodb_thread_concurrency = 16
#innodb_force_recovery = 2
#innodb_read_io_threads = 8
#innodb_write_io_threads = 8
innodb_lock_wait_timeout = 50
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 400
table_cache = 1024M
thread_concurrency = 16
Alguma sugestão o que posso mudar.
Digamos que você tenha o seguinte código (ignore que é horrível):
BEGIN TRAN;
DECLARE @id int
SELECT @id = id + 1 FROM TableA;
UPDATE TableA SET id = @id; --TableA must have only one row, apparently!
COMMIT TRAN;
-- @id is returned to the client or used somewhere else
A meu ver, isso NÃO está gerenciando a simultaneidade corretamente. Só porque você tem uma transação não significa que outra pessoa não lerá o mesmo valor que você leu antes de chegar ao seu extrato de atualização.
Agora, deixando o código como está (percebo que isso é melhor tratado como uma única instrução ou ainda melhor usando uma coluna de autoincremento/identidade), quais são as maneiras seguras de fazê-lo lidar com a simultaneidade adequadamente e evitar condições de corrida que permitem que dois clientes obtenham o mesmo valor id?
Tenho certeza de que adicionar um WITH (UPDLOCK, HOLDLOCK)ao SELECT resolverá o problema. O nível de isolamento da transação SERIALIZABLE também parece funcionar, pois nega que qualquer outra pessoa leia o que você fez até que a transferência termine ( ATUALIZAÇÃO : isso é falso. Veja a resposta de Martin). Isso é verdade? Ambos funcionarão igualmente bem? Um é preferido em detrimento do outro?
Imagine fazer algo mais legítimo do que uma atualização de ID - algum cálculo baseado em uma leitura que você precisa atualizar. Pode haver muitas tabelas envolvidas, algumas das quais você escreverá e outras não. Qual é a melhor prática aqui?
Tendo escrito esta pergunta, acho que as dicas de bloqueio são melhores porque você está apenas bloqueando as tabelas de que precisa, mas gostaria da opinião de qualquer pessoa.
PS E não, não sei a melhor resposta e realmente quero entender melhor! :)
Durante o teste, desmarquei os direitos de administrador de sistema para o meu login e agora não posso adicioná-lo novamente (porque não tenho direitos de administrador de sistema).
Não há outras contas sysadmin para a instância, exceto a [sa]conta.
Eu estava configurado apenas para autenticação do Windows, então invadi LoginModeo registro para 2poder fazer login sausando a autenticação SQL. Isso realmente define o modo de login como Mixed, no entanto, o sausuário está desabilitado por padrão e não posso reativá-lo porque não tenho direitos de administrador do sistema.
Como habilito o salogin para que eu possa entrar e reatribuir o administrador do sistema à minha conta normal? Existe uma configuração de registro para isso também ou ela está armazenada no banco de dados mestre?
Estou fazendo alguns ajustes de desempenho em um grande banco de dados SQL Server 2008 e o grupo de TI não está disposto a conceder permissão SHOWPLAN. No passado, "Mostrar Plano de Execução" era a maneira mais eficaz de entender o desempenho de consultas e procedimentos individuais.
Quais são os riscos inerentes à concessão dessa permissão? Existe uma justificativa legítima para essa restrição em uma cópia de desenvolvimento do banco de dados?
Observação: esse grupo de TI do SQL tem mais de 200 bancos de dados em uma única instância do SQL Server. Obrigado.
Resposta : Estou considerando a falta de resposta como significando que não há riscos de segurança significativos, além do indicado abaixo. Basicamente, restringir isso em um banco de dados de desenvolvimento é contraproducente.
Vou atualizar isso se alguém vier com uma resposta melhor. Obrigado por seus comentários!
Lembro-me de aprender a fazer isso em um curso de DBMS para alunos do Master of Information Services. Para economizar um pouco de digitação, você pode digitar:
SELECT t1.id, t2.stuff
FROM
someTable t1
INNER JOIN otherTable t2
ON t1.id=t2.id
;
Mas... Por que isso é aceitável em stored procedures e tal? Parece que tudo o que faz é prejudicar a legibilidade da declaração, economizando uma quantidade extremamente pequena de tempo. Existe alguma razão funcional ou lógica para fazer isso? Parece adicionar ambiguidade em vez de removê-la; a única razão aceitável que posso ver para usar esse formato é se você estiver adicionando um alias semanticamente significativo - por exemplo, FROM someTable idsTable- quando o nome da tabela não for descritivo o suficiente.
O aliasing de tabela é uma prática ruim ou é apenas um uso indevido de um sistema útil?
Eu quero mesclar dados de um banco de dados para outro. Então eu crio dump mysqldumpe importo para outro banco de dados (com a mesma estrutura de tabelas). Não tenho nenhum problema (como entradas duplicadas ou qualquer outra coisa) neste caso.
Mas eu faço algumas mesclagens para fins de teste e farei a mesclagem final mais tarde. Então, eu quero executar a mesclagem (os dados podem ser alterados) algumas vezes. Observe que minhas linhas em minhas tabelas nunca são excluídas, apenas podem ser inseridas ou atualizadas.
Posso criar mysqldump com a opção ON DUPLICATE? Ou pode ser que eu possa mesclar o despejo que insere novos dados e atualiza os dados modificados?
Claro, posso inserir ON DUPLICATEno dump manualmente, mas quero automatizar o processo de mesclagem.