Início / dba / Perguntas / 158651
Accepted
Jason
Asked: 2016-12-20 18:44:53 +0800 CST

Código criando índice columnstore clusterizado enquanto mantém a ordem das linhas

  • 772

Desejo converter uma tabela rowstore em uma tabela columnstore criando um índice columnstore clusterizado. Existem três colunas na tabela: id, hora e valor.

A tabela é ordenada por id e hora antes de criar o índice columnstore; no entanto, depois de criar o índice columnstore, a ordem das linhas fica confusa. Achei que poderia ser devido ao paralelismo e adicionei a maxdop = 1opção, mas isso não resolveu o problema. Alguém pode me ajudar com isso?

Aqui está o código criando tabelas e índices:

-- creating rowstore table
drop table if exists tab1_rstore
select id, time, value
into tab1_rstore
from tab0
order by id_loan, period
option(maxdop 1)

-- creating clustered index on rowstore table
create clustered index idx on tab1_rstore (id,time)

-- creating columnstore table 
select * 
into tab1_cstore
from tab1_rstore
    option(maxdop 1)

-- comparing the first two rows from these two tables
    select top 2 *
from tab1_rstore

    select top 2 *
from tab1_cstore

A captura de tela dos resultados da consulta:

As duas primeiras linhas antes do c-index

-- creating clustered columnstore index
create clustered columnstore index idx on tab1_cstore 
with (maxdop = 1) 

-- comparing the top two rows again
select top 2 *
from tab1_rstore

select top 2 *
from tab1_cstore

A captura de tela dos resultados da consulta com o índice columnstore:

insira a descrição da imagem aqui

Meu entendimento é que a ordem das linhas é determinada pelo algoritmo de compressão e não há nada que possamos fazer a respeito, veja a limitação e restrição no documento aqui com a seguinte citação:

Não é possível incluir as palavras-chave ASC ou DESC para classificar o índice. Os índices Columnstore são ordenados de acordo com os algoritmos de compactação. A classificação eliminaria muitos dos benefícios de desempenho.

Estou usando o SQL Server 2016 Developer Edition no Windows 10 64 bits.

sql-server sql-server-2016

2 respostas

  1. Best Answer

    Um índice columnstore clusterizado é fundamentalmente diferente de um índice rowstore clusterizado. Você deve ter notado que não há nenhuma especificação de coluna chave para um índice columnstore clusterizado. É isso mesmo: um índice columnstore clusterizado é um índice sem chaves - todas as colunas são 'incluídas'.

    A descrição mais intuitiva que ouvi de um índice columnstore clusterizado é pensar nele como uma tabela de heap orientada a colunas (onde o 'RID' é rowgroup_id, row_number).

    Se você precisar de índices para dar suporte à ordenação direta e/ou seleções de intervalo pequeno/ponto, poderá criar índices de árvore b rowstore atualizáveis ​​sobre o columnstore clusterizado no SQL Server 2016.

    Em muitos casos, isso simplesmente não é necessário , pois o acesso columnstore e a classificação do modo de lote são muito rápidos. Muitas das coisas que as pessoas 'sabem' sobre o desempenho do rowstore precisam ser reaprendidas para o columnstore. Scans e hashes são bons :)

    Dito isso, é claro que columnstore tem uma estrutura para seus grupos de linhas (e metadados sobre valores mínimos/máximos em cada segmento), o que pode ser útil em consultas que podem se beneficiar da eliminação de grupos/segmentos de linhas.

    Uma técnica importante nessa área é primeiro criar um índice rowstore clusterizado com a ordem desejada e, em seguida, criar o índice columnstore clusterizado usando a WITH (DROP_EXISTING = ON, MAXDOP = 1)opção . No seu exemplo:

    CREATE [UNIQUE] CLUSTERED INDEX idx 
ON dbo.tab1_cstore (id, time)
WITH (MAXDOP = 1);

CREATE CLUSTERED COLUMNSTORE INDEX idx 
ON dbo.tab1_cstore
WITH (DROP_EXISTING = ON, MAXDOP = 1);

    É necessário cuidado para manter os benefícios da eliminação do grupo/segmento de linhas ao longo do tempo. Além disso, embora columnstore já esteja particionado implicitamente por grupo de linhas, você também pode particioná-lo explicitamente.

    Não tenho 100% de certeza do que você deseja testar, mas é verdade que a 'ordem' dos valores em um segmento é determinada pelo algoritmo de compactação. Meu ponto sobre a criação do índice columnstore DROP_EXISTINGé sobre a ordem dos dados que fluem para o processo de criação do segmento, de modo que os segmentos em geral sejam ordenados de uma maneira específica. Dentro do segmento, todas as apostas estão encerradas.

    • 15

  2. Como um aparte, acredito que a equipe do SQL Server Tiger está ciente da necessidade de uma ORDER BYcláusula ou similar em builds de índice columnstore para maximizar a oportunidade de eliminação de rowgroup.

    Além disso, neste momento é muito importante certificar-se, como em outra resposta aqui, que você usa MAXDOP = 1ao construir o índice columnstore. Caso contrário, você causará o particionamento dos dados em vários encadeamentos, o que reduz essencialmente, talvez drasticamente, a possibilidade da eliminação mais eficiente do grupo de linhas.

    • 2

relate perguntas