Statystyka. Czy możliwe są histogramy wielokolumnowe?

Mam na myśli sytuację, w której mam dwie kolumny o dużej gęstości, ale kolumny te nie są niezależne.

Definicja

Oto definicja tabeli, którą utworzyłem do celów testowych.

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]

Dane

Dane do eksperymentu są następujące

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

Krok 1: Filtrowanie według kol1

SELECT * FROM StatsTest WHERE col1=1

Zgodnie z oczekiwaniami Optymalizator zapytań zgaduje dokładną liczbę wierszy. Rzeczywista liczba rzędów = 10 i szacunkowa liczba rzędów = 10

Krok 2: Filtrowanie według col2

SELECT * FROM StatsTest WHERE col2=1

Ponownie mamy doskonałe oszacowanie.

Rzeczywista liczba rzędów = 5 i szacunkowa liczba rzędów = 5

Krok 3: Filtrowanie według kolumn col1 i col2

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

W tym przypadku oszacowanie jest dalekie od rzeczywistej liczby wierszy. Rzeczywista liczba rzędów = 1 i szacunkowa liczba rzędów = 353553

Problem polega na tym, że implikacja analizatora zapytań zakłada, że col1 i col2 są niezależne, ale nie są.

Krok 4: Filtrowanie według col4

SELECT * FROM StatsTest WHERE col4 = 11

Mogę filtrować według col4 = 11, aby uzyskać takie same wyniki jak zapytanie w kroku 3, ponieważ col4 jest kolumną obliczoną i zgodnie ze sposobem, w jaki zostało zdefiniowane col1 = 1, a col2 = 1 jest równoważne col4 = 11 Tutaj jednak zgodnie z oczekiwaniami oszacowanie jest idealne.

Rzeczywista liczba rzędów = 1 i szacunkowa liczba rzędów = 1

Wniosek / pytanie

¿Czy to sztuczne i nieeleganckie rozwiązanie jest jedyną dostępną opcją umożliwiającą uzyskanie dokładnych oszacowań w przypadku filtrowania według dwóch lub więcej niezależnych kolumn? ¿Czy kolumna obliczana i filtr według kolumny obliczeniowej są niezbędne do uzyskania rzeczywistej precyzji?

Przykład w sqlfiddle

sql-server statistics

— JGA
źródło

Dlaczego nie zbudować niektórych indeksów na col1 / 2?

— LowlyDBA,

W rzeczywistości tak zrobiłem, ale nie znalazłem się tutaj, ponieważ to nie działało. W przypadku histogramu brana jest pod uwagę tylko pierwsza kolumna, a gęstość uwzględnia tylko inną liczbę wartości, a nie ich rozkład

— JGA,

Czy możliwe są histogramy wielokolumnowe?

Nieprawdziwe histogramy wielowymiarowe, nie.

Czy to sztuczne i nieeleganckie rozwiązanie jest jedyną dostępną opcją umożliwiającą uzyskanie dokładnych szacunków w przypadku filtrowania według dwóch lub więcej niezależnych kolumn?

SQL Server obsługuje statystyki „wielokolumnowe” , ale oprócz histogramu na pierwszej nazwie kolumny przechwytują jedynie informacje o średniej gęstości (korelacji). Są one przydatne tylko do porównań równości.

Informacja o średniej gęstości nie przechwytuje żadnych szczegółów, więc uzyskasz taką samą selektywność dla dowolnej pary wartości w dwukolumnowym obiekcie statystycznym. W niektórych przypadkach statystyki wielokolumnowe mogą być wystarczająco dobre i lepsze niż nic. Wielokolumnowe statystyki są automatycznie budowane na podstawie indeksów wielokolumnowych.

W zależności od wersji programu SQL Server możesz również korzystać z filtrowanych indeksów i filtrowanych statystyk :

-- 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;

Lub możesz zbudować indeksowany widok (który może obsługiwać własne indeksy i statystyki). Widoki indeksowane to mechanizm stojący za DATE_CORRELATION_OPTIMIZATIONbazą danych , mało używana funkcja korelacji między tabelami, ale która dotyczy ducha pytania.

Czy kolumna obliczana i filtr według kolumny obliczeniowej są absolutnie niezbędne do uzyskania rzeczywistej precyzji?

To nie jedyna metoda. Oprócz rzeczy już wspomnianych, możesz również określić dokładną definicję tekstową kolumny obliczanej, a optymalizator ogólnie dopasuje ją do statystyk kolumny obliczanej.

Istnieją również flagi śledzenia, które zmieniają założenia dotyczące korelacji wielokolumnowych. Również domyślne założenie korelacji w SQL Server 2014 (z włączonym nowym estymatorem liczności) zostało zmienione z Niezależności na Wykładnicze wycofanie (więcej szczegółów tutaj i tutaj ). Ostatecznie jest to jednak inne założenie. W wielu przypadkach będzie lepiej, aw innych gorzej.

Dokładna ocena estymacji liczności nie zawsze jest potrzebna do uzyskania dobrego planu wykonania. Zawsze istnieje kompromis między generowaniem planu, który może być ponownie użyty dla różnych wartości parametrów, a planem, który jest optymalny dla konkretnego wykonania, ale nie może być ponownie użyty.

— Paul White 9
źródło