Início / dba / Perguntas / 103867
Accepted
Mikael Eriksson
Asked: 2015-06-12 23:34:06 +0800 CST

Por que o índice seletivo secundário não é usado quando a cláusula where filtra em `value ()`?

  • 772

Configurar:

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;

XML de amostra para cada linha:

<Number>314</Number>

A tarefa da consulta é contar o número de linhas Tcom um valor especificado de <Number>.

Existem duas maneiras óbvias de fazer isso:

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;

Acontece que value()requer exists()duas definições de caminho diferentes para o índice XML seletivo funcionar.

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

A sqlversão é para value()e a xqueryversão é para exist().

Você pode pensar que um índice como esse forneceria um plano com uma boa busca, mas os índices XML seletivos são implementados como uma tabela de sistema com a chave primária de Tcomo a chave principal da chave agrupada da tabela de sistema. Os caminhos especificados são colunas esparsas nessa tabela. Se você deseja um índice dos valores reais dos caminhos definidos, precisa criar índices seletivos secundários, um para cada expressão de caminho.

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

O plano de consulta para o exist()faz uma busca no índice XML secundário seguido por uma pesquisa de chave na tabela do sistema para o índice XML seletivo (não sei por que isso é necessário) e, finalmente, faz uma pesquisa Tpara garantir que realmente haja linhas lá dentro. A última parte é necessária porque não há restrição de chave estrangeira entre a tabela do sistema e T.

insira a descrição da imagem aqui

O plano para a value()consulta não é tão legal. Ele faz uma varredura de índice clusterizado Tcom uma junção de loops aninhados contra uma busca na tabela interna para obter o valor da coluna esparsa e, finalmente, filtra o valor.

insira a descrição da imagem aqui

Se um índice seletivo deve ser usado ou não é decidido antes da otimização, mas se um índice seletivo secundário deve ser usado ou não é uma decisão baseada em custo pelo otimizador.

Por que o índice seletivo secundário não é usado quando a cláusula where é filtrada value()?

Atualizar:

As consultas são semanticamente diferentes. Se você adicionar uma linha com o valor

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

a exist()versão contaria 2 linhas e a values()consulta contaria 1 linha. Mas com as definições de índice como elas são especificadas aqui usando a singletondiretiva SQL Server impedirá que você adicione uma linha com vários <Number>elementos.

Isso, no entanto, não nos permite usar a values()função sem especificar [1]para garantir ao compilador que obteremos apenas um único valor. Essa [1]é a razão pela qual temos um Top N Sort no value()plano.

Parece que estou me aproximando de uma resposta aqui ...

sql-server sql-server-2012

1 respostas

  1. Best Answer

    A declaração de singletonna expressão de caminho do índice impõe que você não pode adicionar vários <Number>elementos, mas o compilador XQuery não leva isso em consideração ao interpretar a expressão na value()função. Você precisa especificar [1]para deixar o SQL Server feliz. Usar XML digitado com um esquema também não ajuda nisso. E por causa disso o SQL Server cria uma consulta que usa algo que poderia ser chamado de padrão "aplicar".

    O mais fácil de demonstrar é usar tabelas regulares em vez de XML, simulando a consulta na qual estamos realmente executando Te a tabela interna.

    Aqui está a configuração da mesa interna como uma mesa real.

    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;

    Com ambas as tabelas no lugar, você pode executar o equivalente à exist()consulta.

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

    O equivalente da value()consulta ficaria assim.

    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;

    O top(1)e order by S.path_1_idé o culpado e é [1]na expressão XPath que está a culpa.

    Não acho que seja possível para a Microsoft consertar isso com a estrutura atual da tabela interna, mesmo que você tenha permissão para deixar de fora [1]da values()função. Eles provavelmente teriam que criar várias tabelas internas para cada expressão de caminho com restrições exclusivas para garantir ao otimizador que só pode haver um <number>elemento para cada linha. Não tenho certeza se isso seria realmente suficiente para o otimizador "sair do padrão de aplicação".

    Para você que acha isso divertido e interessante e, como ainda está lendo isso, provavelmente está.

    Algumas consultas para ver a estrutura da tabela interna.

    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')
                     );
    • 11

relate perguntas