Considere a seguinte tabela de amostra com um índice de várias colunas:
create table BigNumbers (
col1 tinyint not null,
col2 tinyint not null,
col3 tinyint not null,
index IX_BigNumbers clustered (col1, col2, col3)
)
DECLARE @n INT = 100;
DECLARE @x1 INT = 0;
DECLARE @x2 INT = 0;
DECLARE @x3 INT = 0;
SET NOCOUNT ON;
WHILE @x3 <= @n BEGIN
SET @x2 = 0;
WHILE @x2 <= @n BEGIN
SET @x1 = 0;
WHILE @x1 <= @n BEGIN
insert into BigNumbers values (@x1, @x2, @x3);
SET @x1 = @x1 + 1;
END;
SET @x2 = @x2 + 1;
END;
SET @x3 = @x3 + 1;
END;
Meu objetivo agora é obter algumas linhas desse índice, começando com uma determinada chave.
O que parece trivial é um pouco complicado, pois não há uma maneira fácil no SQL de expressar a ordem lexicográfica em que o índice está:
DECLARE @x1 INT = 60;
DECLARE @x2 INT = 40;
DECLARE @x3 INT = 98;
select top 5 *
from BigNumbers
where
col1 > @x1 or
(col1 = @x1 and
(col2 > @x2 or
(col2 = @x2 and col3 >= @x3)))
order by col1, col2, col3
O resultado correto é:
60 40 98
60 40 99
60 40 100
60 41 0
60 41 1
No entanto, o plano de consulta me diz que isso usa uma verificação de índice.
O índice subjacente deve ser capaz de buscar e retornar as primeiras linhas maiores ou iguais (@x1, @x2, @3)na ordem do índice, mas como não há como no SQL expressar essa intenção facilmente, o planejador de consulta parece incapaz de entender a dica e, em vez disso, faz uma varredura .
Dicas de índice não ajudam e FORCESEEKdá um plano horrendo.
Curiosamente, a seguinte versão de duas colunas funciona:
select top 5 *
from BigNumbers
where
col1 = @x1 and
(col2 > @x2 or
(col2 = @x2 and col3 >= @x3))
order by col1, col2, col3
Não sei por que isso acontece, mas o plano não apenas usa uma busca, como também relata corretamente ter tocado apenas 5 linhas:
Eu gostaria de saber se alguém conhece uma maneira de consultar algumas linhas de um índice maior ou igual a uma determinada tupla de valor com uma busca simples de forma confiável.
Parece estranho que o banco de dados obscureça essa capacidade fundamental sob suas abstrações de nível superior.
Se alguém estiver interessado em saber qual é o problema, estou desenvolvendo uma UI genérica para bancos de dados SQL. O lugar mais óbvio onde você precisa disso é para um botão "carregar mais" onde você deseja continuar mostrando o conteúdo do índice para um determinado ponto de partida. Se isso não for possível em geral, a solução alternativa seria primeiro consultar a correção de todas as colunas, exceto a última, e fazer uma segunda consulta e assim por diante. Seria um pouco de vergonha ter que fazer isso embora.
O que você está se referindo é uma comparação de linha e você a está usando para uma consulta de paginação de conjunto de chaves . Nos SGBDs que o suportam , você pode simplesmente fazer
No entanto, o SQL Server não oferece suporte a isso. O que ele suporta é uma busca de índice em vários intervalos. Assim, a busca de índice único torna-se duas ou três, mas a ordenação é compreendida e mantida pelo compilador, de modo que atua efetivamente como uma busca única em um intervalo.
Ele usa isso em vários tipos diferentes de consultas, principalmente
INlistas eORconsultas. Ele também o usa na lógica de comparação de linhas, o que é muito útil ao fazer a Paginação do Conjunto de Chaves.No seu caso, não está reconhecendo esse padrão. Parece que isso ocorre porque você está usando lógica booleana aninhada para expressá-la. Ele reconhecerá com sucesso a seguinte lógica, que é exatamente a mesma semanticamente
db<>violino
Exatamente por que ele reconhece um e não o outro não está claro. Talvez alguém com acesso a um depurador e/ou conhecimento das regras do otimizador possa elaborar.
Em um mundo perfeito, as pessoas seriam capazes de escrever consultas em qualquer forma logicamente equivalente e o otimizador produziria o mesmo plano de execução ideal em todos os casos.
Esta não é uma proposta prática - o otimizador tem um tempo limitado para trabalhar e um conhecimento incompleto das possíveis transformações. Por isso, às vezes temos a necessidade de expressar nossa exigência em uma forma escrita específica para obter o melhor resultado.
O SQL Server faz alguns esforços para padronizar (normalizar) a forma lógica da instrução enviada, mas eles não são exaustivos. Por exemplo, as cláusulas lógicas são transformadas na forma normal de negação (NNF), mas não na forma normal conjuntiva (CNF) ou disjuntiva (DNF). A transformação para NNF é sempre fácil e compacta; nem sempre é assim para CNF ou DNF.
Isso é uma pena no seu caso, porque o formulário DNF é fácil de derivar e funciona bem com a lógica de correspondência de índice, conforme mostrado em outra resposta :
Isso produz um
TRIVIALplano de execução com três operações de busca separadas dentro da busca de índice clusterizado, executadas sequencialmente (com curto-circuito):O ideal, como também já observado, seria o SQL Server implementar construtores de linha, com a lógica expandida conforme necessário para a forma ideal para correspondência de índice. Isso foi solicitado há muito tempo, mas frustrantemente ainda não foi entregue. Por enquanto, precisamos escrever consultas de paginação baseadas em âncoras de uma maneira específica.
Uma solução um pouco menos eficiente, mas ainda decente, é:
Este plano tem três buscas únicas, mas cada uma deve produzir pelo menos uma linha para a comparação de concatenação de mesclagem, para que nenhuma possa ser completamente curto-circuitada.
db<>violino