Início / dba / Perguntas / 84747
Accepted
JGA
Asked: 2014-12-10 14:21:57 +0800 CST

Estatisticas. Os histogramas de várias colunas são possíveis?

  • 772

Estou pensando em uma situação em que tenho duas colunas com alta densidade, mas essas colunas não são independentes.

Definição

Aqui está a definição da tabela que criei para fins de teste.

CREATE TABLE [dbo].[StatsTest](
    [col1] [int] NOT NULL,  --can take values 1 and 2 only
    [col2] [int] NOT NULL,  --can take integer values from 1 to 4 only
    [col3] [int] NOT NULL,  --integer. it has not relevance just to ensure that each row is different
    [col4]  AS ((10)*[col1]+[col2])  --a computed column ensuring that if two rows have different values in col1 or col2 have different values in col4 
) ON [PRIMARY]

Dados

Os dados para o experimento são os seguintes

col1    col2    col3    col4
1       1       1       11
1       2       2       12
1       2       3       12
1       3       4       13
1       3       5       13
1       3       6       13
1       4       7       14
1       4       8       14
1       4       9       14
1       4       10      14
2       1       11      21
2       1       12      21
2       1       13      21
2       1       14      21
2       2       15      22
2       2       16      22
2       2       17      22
2       3       18      23
2       3       19      23
2       4       20      24

Passo 1: Filtrando por col1

SELECT * FROM StatsTest WHERE col1=1

Como esperado, o Query Optimizer adivinha o número exato de linhas. Número real de linhas = 10 e número estimado de linhas = 10

Passo 2: Filtrando por col2

SELECT * FROM StatsTest WHERE col2=1

Novamente, temos uma estimativa perfeita.

Número real de linhas = 5 e número estimado de linhas = 5

Passo 3: Filtrando por col1 e col2

SELECT * FROM StatsTest WHERE col1=1 AND col2=1

Aqui, a estimativa está longe de estar próxima do número real de linhas. Número real de linhas = 1 e número estimado de linhas = 3,53553

O problema é que o analisador de consulta pressupõe implicitamente que col1 e col2 são independentes, mas não são.

Passo 4: Filtrando por col4

SELECT * FROM StatsTest WHERE col4 = 11

Posso filtrar por col4 = 11 para obter os mesmos resultados da consulta na Etapa 3, porque col4 é uma coluna computada e de acordo com a forma como foi definida col1 = 1 e col2 = 1 é equivalente a col4 = 11 Aqui, porém , como esperado a estimativa é perfeita.

Número real de linhas = 1 e número estimado de linhas = 1

Conclusão/Pergunta

¿Essa solução artificial e deselegante é a única opção disponível para obter estimativas precisas quando se trata de filtragem por duas ou mais colunas não independentes? ¿A coluna calculada e o filtro pela coluna calculada são estritamente necessários para obter a precisão real?

Exemplo em sqlfiddle

sql-server statistics

1 respostas

  1. Best Answer

    Os histogramas de várias colunas são possíveis?

    Não verdadeiros histogramas multidimensionais, não.

    Essa solução artificial e deselegante é a única opção disponível para obter estimativas precisas quando se trata de filtragem por duas ou mais colunas não independentes?

    O SQL Server oferece suporte a estatísticas de "várias colunas" , mas elas capturam apenas informações de densidade média (correlação), além de um histograma na primeira coluna nomeada. Eles são úteis apenas para comparações de igualdade.

    As informações de densidade média não capturam nenhum detalhe, portanto, você obterá a mesma seletividade para qualquer par de valores em um objeto estatístico de duas colunas. Em alguns casos, as estatísticas de várias colunas podem ser boas o suficiente e melhores do que nada. As estatísticas de várias colunas são criadas automaticamente em índices de várias colunas.

    Dependendo da versão do SQL Server, você também pode usar índices filtrados e estatísticas filtradas :

    -- Filtered statistics example
CREATE STATISTICS stats_StatsTest_col2_col1_eq_1
ON dbo.StatsTest (col2)
WHERE col1 = 1;

CREATE STATISTICS stats_StatsTest_col2_col1_eq_2
ON dbo.StatsTest (col2)
WHERE col1 = 2;

    Ou você pode criar uma exibição indexada (que pode oferecer suporte a índices e estatísticas próprias). Visualizações indexadas são o mecanismo por trás da DATE_CORRELATION_OPTIMIZATIONconfiguração do banco de dados , um recurso pouco usado para correlações entre tabelas, mas que se aplica ao espírito da questão.

    A coluna calculada e o filtro pela coluna calculada são estritamente necessários para obter a precisão real?

    Não é o único método. Além das coisas já mencionadas, você também pode especificar a definição textual exata da coluna computada e o otimizador geralmente irá combiná-la com as estatísticas na coluna computada.

    Há também sinalizadores de rastreamento que alteram as suposições feitas sobre correlações de várias colunas. Além disso, a suposição de correlação padrão no SQL Server 2014 (com o novo estimador de cardinalidade habilitado) foi alterada de Independência para Backoff Exponencial (mais detalhes aqui e aqui ). Em última análise, esta é apenas uma suposição diferente. Será melhor em muitos casos e pior em outros.

    A precisão exata na estimativa de cardinalidade nem sempre é necessária para obter um bom plano de execução. Sempre há uma compensação entre gerar um plano que pode ser reutilizado para diferentes valores de parâmetros e um plano ideal para uma execução específica, mas não reutilizado.

    • 16

relate perguntas