Início / dba / Perguntas / 340067
Accepted
Artashes Khachatryan
Asked: 2024-06-07 14:29:23 +0800 CST

Filtrar por número de partição quando a tabela for particionada por coluna computada

  • 772

Criei 2 tabelas idênticas particionadas por uma coluna inteira. Em uma das tabelas a coluna é computada, na outra tabela a coluna não é computada.

Quando eu consulto a tabela com coluna computada filtrando uma única classificação de partição com índice clusterizado (assumindo que os dados de saída já estão classificados, portanto, nenhuma classificação adicional é necessária), ela verifica a tabela inteira.

CREATE PARTITION FUNCTION pf_test(int) AS RANGE RIGHT FOR VALUES(1, 2, 3, 4)
GO
CREATE PARTITION SCHEME ps_test AS PARTITION pf_test ALL TO([PRIMARY])
GO
CREATE TABLE test_computed
(
    ID BIGINT NOT NULL, 
    ID_C AS CAST(ID % 4 AS INT) PERSISTED, 
    PRIMARY KEY CLUSTERED (ID, ID_C) ON ps_test(ID_C)
) ON ps_test(ID_C)
GO
    
CREATE TABLE test_not_computed
(
    ID BIGINT NOT NULL, 
    ID_C INT NOT NULL, 
    PRIMARY KEY CLUSTERED (ID, ID_C) ON ps_test(ID_C)
) ON ps_test(ID_C)
GO
    
INSERT INTO test_computed(ID)
SELECT TOP 1000000 ROW_NUMBER() OVER(ORDER BY GETDATE())
FROM sys.all_columns a
CROSS JOIN sys.all_columns b
GO
    
INSERT INTO test_not_computed(ID, ID_C)
SELECT TOP 1000000 
    ROW_NUMBER() OVER(ORDER BY GETDATE()), 
    ROW_NUMBER() OVER(ORDER BY GETDATE()) % 4
FROM sys.all_columns a
CROSS JOIN sys.all_columns b
GO

Os dados são idênticos, mas os planos de execução para cada consulta são diferentes.

SELECT TOP 100 *
FROM test_computed
WHERE $partition.pf_test(ID_C) = 1
ORDER BY ID DESC
    
SELECT TOP 100 *
FROM test_not_computed
WHERE $partition.pf_test(ID_C) = 1
ORDER BY ID desc

Planos

A tabela real possui bilhões de linhas; estamos usando particionamento para evitar a varredura de toda a tabela.

A versão do SQL Server é

Microsoft SQL Server 2019 (RTM-CU26) (KB5035123) - 15.0.4365.2 (X64)
29 de março de 2024 23:02:47 Copyright (C) 2019 Microsoft Corporation
Developer Edition (64 bits) no Windows Server 2022 Standard 10.0 (Build 20348 :)

sql-server

1 respostas

  1. Best Answer

    A expansão inicial da coluna computada interfere no envio do $partitionpredicado para a varredura.

    A expansão é correspondida de volta à coluna computada persistente posteriormente no processo de compilação, mas já é tarde demais. Você acaba com uma combinação Compute Scalar e Filter em vez de uma busca simples e agradável no ID da partição. O colapso da lógica também leva a um tipo que um ser humano pode ver que não é necessário.

    É um comportamento infeliz sem uma solução totalmente suportada.

    Para ver a diferença que não faz a expansão da coluna computada, você pode tentar sua consulta com o sinalizador de rastreamento 176:

    SELECT TOP (100) 
    TC.*
FROM dbo.test_computed AS TC
WHERE 
    $PARTITION.pf_test(TC.ID_C) = 1
ORDER BY 
    TC.ID DESC
OPTION (QUERYTRACEON 176);

    O plano de execução é então:

    Planeje com TF176

    Escrevi sobre isso em meu artigo, Colunas Computadas Corretamente Persistidas .

    • 9

relate perguntas