Dlaczego wtórny indeks selektywny nie jest używany, gdy klauzula where filtruje wartość `value ()`?

Ustawiać:

create table dbo.T
(
  ID int identity primary key,
  XMLDoc xml not null
);

insert into dbo.T(XMLDoc)
select (
       select N.Number
       for xml path(''), type
       )
from (
     select top(10000) row_number() over(order by (select null)) as Number
     from sys.columns as c1, sys.columns as c2
     ) as N;

Przykładowy kod XML dla każdego wiersza:

<Number>314</Number>

Zadanie dla zapytania polega na zliczeniu liczby wierszy To określonej wartości <Number>.

Można to zrobić na dwa oczywiste sposoby:

select count(*)
from dbo.T as T
where T.XMLDoc.value('/Number[1]', 'int') = 314;

select count(*)
from dbo.T as T
where T.XMLDoc.exist('/Number[. eq 314]') = 1;

Okazuje się, że value()i exists()do działania selektywnego indeksu XML potrzebne są dwie różne definicje ścieżek.

create selective xml index SIX_T on dbo.T(XMLDoc) for
(
  pathSQL = '/Number' as sql int singleton,
  pathXQUERY = '/Number' as xquery 'xs:double' singleton
);

sqlWersja jest value()i xquerywersja jest exist().

Możesz pomyśleć, że taki indeks dałby ci plan z ładnym wyszukiwaniem, ale selektywne indeksy XML są implementowane jako tabela systemowa z kluczem podstawowym Tjako kluczem wiodącym klucza klastrowego tabeli systemowej. Podane ścieżki są rzadkimi kolumnami w tej tabeli. Jeśli potrzebujesz indeksu rzeczywistych wartości zdefiniowanych ścieżek, musisz utworzyć wtórne indeksy selektywne, po jednym dla każdego wyrażenia ścieżki.

create xml index SIX_T_pathSQL on dbo.T(XMLDoc)
  using xml index SIX_T for (pathSQL);

create xml index SIX_T_pathXQUERY on dbo.T(XMLDoc)
  using xml index SIX_T for (pathXQUERY);

Plan zapytania dla exist()wyszukiwania w wtórnym indeksie XML, po którym następuje wyszukiwanie klucza w tabeli systemowej dla selektywnego indeksu XML (nie wiem, dlaczego jest to potrzebne), a na koniec wyszukiwanie w Tcelu upewnienia się, że faktycznie istnieją wiersze tam. Ostatnia część jest konieczna, ponieważ między tabelą systemową a nie ma ograniczenia na klucz obcy T.

Plan value()zapytania nie jest taki miły. Wykonuje skanowanie indeksu klastrowego Tz zagnieżdżonymi pętlami łączącymi się z szukaniem w wewnętrznej tabeli, aby uzyskać wartość z rzadkiej kolumny i na koniec filtrować wartość.

O tym, czy przed optymalizacją należy zastosować wskaźnik selektywny, decyduje się, ale czy należy zastosować wtórny wskaźnik selektywny, czy nie, jest to decyzja optymalizatora oparta na kosztach.

Dlaczego wtórny indeks selektywny nie jest używany, gdy filtruje klauzula where value()?

Aktualizacja:

Zapytania są semantycznie różne. Jeśli dodasz wiersz z wartością

<Number>313</Number>
<Number>314</Number>` 

exist()wersja licz 2 wierszy i values()zapytania licz 1 wiersz. Ale dzięki definicjom indeksów, które są tutaj określone przy użyciu singletondyrektywy SQL Server zapobiegnie dodaniu wiersza z wieloma <Number>elementami.

Nie pozwala to jednak na użycie tej values()funkcji bez określenia, [1]czy kompilator otrzyma tylko jedną wartość. To [1]jest powód, dla którego mamy w planie Top N Sort value().

Wygląda na to, że kończę tutaj odpowiedź ...

Deklaracja singletonwyrażenia w ścieżce indeksu wymusza, że ​​nie można dodawać wielu <Number>elementów, ale kompilator XQuery nie bierze tego pod uwagę podczas interpretacji wyrażenia w value()funkcji. Musisz określić, [1]aby SQL Server był szczęśliwy. Używanie wpisanego XML ze schematem również nie pomaga w tym. Z tego powodu SQL Server tworzy zapytanie, które wykorzystuje coś, co można nazwać wzorcem „zastosuj”.

Najłatwiejszym do wykazania jest użycie zwykłych tabel zamiast XML symulującego zapytanie, które faktycznie wykonujemy, Ti tabelę wewnętrzną.

Oto konfiguracja wewnętrznego stołu jako prawdziwego stołu.

create table dbo.xml_sxi_table
(
  pk1 int not null,
  row_id int,
  path_1_id varbinary(900),
  pathSQL_1_sql_value int,
  pathXQUERY_2_value float
);

go

create clustered index SIX_T on xml_sxi_table(pk1, row_id);
create nonclustered index SIX_pathSQL on xml_sxi_table(pathSQL_1_sql_value) where path_1_id is not null;
create nonclustered index SIX_T_pathXQUERY on xml_sxi_table(pathXQUERY_2_value) where path_1_id is not null;

go

insert into dbo.xml_sxi_table(pk1, row_id, path_1_id, pathSQL_1_sql_value, pathXQUERY_2_value)
select T.ID, 1, T.ID, T.ID, T.ID
from dbo.T;

Mając obie tabele na miejscu, możesz wykonać odpowiednik exist()zapytania.

select count(*)
from dbo.T
where exists (
             select *
             from dbo.xml_sxi_table as S
             where S.pk1 = T.ID and
                   S.pathXQUERY_2_value = 314 and
                   S.path_1_id is not null
             );

Odpowiednik value()zapytania wyglądałby tak.

select count(*)
from dbo.T
where (
      select top(1) S.pathSQL_1_sql_value
      from dbo.xml_sxi_table as S
      where S.pk1 = T.ID and
            S.path_1_id is not null
      order by S.path_1_id
      ) = 314;

top(1)I order by S.path_1_idjest winowajcą i to [1]w wyrażeniu XPath, że jest winny.

Nie sądzę, że Microsoft jest w stanie naprawić to za pomocą bieżącej struktury tabeli wewnętrznej, nawet jeśli pozwolono ci pominąć [1]tę values()funkcję. Prawdopodobnie musieliby utworzyć wiele wewnętrznych tabel dla każdego wyrażenia ścieżki z unikalnymi ograniczeniami, aby zagwarantować optymalizatorowi, że może być tylko jeden <number>element dla każdego wiersza. Nie jestem pewien, czy faktycznie wystarczyłoby to, aby optymalizator „wyłamał się ze stosowanego wzoru”.

Dla tych, którzy uważają to za zabawne i interesujące, a ponieważ nadal je czytasz, prawdopodobnie jesteś.

Kilka zapytań dotyczących struktury wewnętrznej tabeli.

select T.name, 
       T.internal_type_desc, 
       object_name(T.parent_id) as parent_table_name
from sys.internal_tables as T
where T.parent_id = object_id('T');

select C.name as column_name, 
       C.column_id,
       T.name as type_name,
       C.max_length,
       C.is_sparse,
       C.is_nullable
from sys.columns as C
  inner join sys.types as T
    on C.user_type_id = T.user_type_id
where C.object_id in (
                     select T.object_id 
                     from sys.internal_tables as T 
                     where T.parent_id = object_id('T')
                     )
order by C.column_id;

select I.name as index_name,
       I.type_desc,
       I.is_unique,
       I.filter_definition,
       IC.key_ordinal,
       C.name as column_name, 
       C.column_id,
       T.name as type_name,
       C.max_length,
       I.is_unique,
       I.is_unique_constraint
from sys.indexes as I
  inner join sys.index_columns as IC
    on I.object_id = IC.object_id and
       I.index_id = IC.index_id
  inner join sys.columns as C
    on IC.column_id = C.column_id and
       IC.object_id = C.object_id
  inner join sys.types as T
    on C.user_type_id = T.user_type_id
where I.object_id in (
                     select T.object_id 
                     from sys.internal_tables as T 
                     where T.parent_id = object_id('T')
                     );