Trabalhar com chaves de classificação

Ao criar uma tabela, você pode definir alternativamente uma ou mais de suas colunas como chaves de classificação. Quando os dados são carregados na tabela vazia, as linhas são armazenadas em disco na ordem de classificação. As informações sobre as colunas de chave de classificação são transmitidas ao planejador de consulta, que usa essas informações para construir planos que exploram a forma como os dados são classificados. Para ter mais informações, consulte CRIAR TABELA. Para obter informações sobre práticas recomendadas ao criar uma chave de classificação, consulte Selecione a melhor chave de classificação.

A classificação permite o manuseio eficiente de predicados de alcance restrito. O Amazon Redshift armazena dados colunares em blocos de disco de 1 MB. Os valores mínimos e máximos para cada bloco são armazenados como parte dos metadados. Se a consulta usar um predicado restrito por intervalo, o processador de consulta poderá usar os valores mínimos e máximos para ignorar rapidamente um grande número de blocos durante varreduras da tabela. Por exemplo, suponha que uma tabela armazena cinco anos de dados classificados por data e uma consulta especifica um intervalo de um mês. Nesse caso, você pode remover até 98% dos blocos de disco da verificação. Se os dados não estiverem classificados, a varredura deverá incluir um número maior de blocos de disco (possivelmente todos).

Você pode especificar uma chave de classificação composta ou intercalada. Uma chave de classificação composta é mais eficiente quando os predicados de consulta usam um prefixo, que é um subconjunto das colunas de chave de classificação ordenadas. Uma chave de classificação intercalada concede igual peso a todas as colunas na chave de classificação, portanto os predicados de consulta podem usar qualquer subconjunto de colunas que componha a chave de classificação, em qualquer ordem.

Para compreender o impacto da chave de classificação escolhida na performance da consulta, use o comando EXPLAIN. Para obter mais informações, consulte Planejamento de consulta e fluxo de trabalho de execução.

Para definir um tipo de classificação, use a palavra-chave INTERLEAVED ou COMPOUND com sua instrução CREATE TABELA ou CREATE TABLE AS. O padrão é COMPOUND. COMPOUND é recomendado quando você atualiza tabelas regularmente com as operações INSERT, UPDATE ou DELETE. Uma chave de classificação INTERLEAVED pode usar um máximo de oito colunas. Dependendo dos dados e do tamanho do cluster, VACUUM REINDEX leva muito mais tempo do que VACUUM FULL porque faz uma passagem adicional para analisar as chaves de classificação intercaladas. As operações de classificação e mesclagem podem levar mais tempo para tabelas intercaladas porque a classificação intercalada pode precisar reorganizar mais linhas do que uma classificação composta.

Para visualizar as chaves de classificação para uma tabela, consulte a exibição de sistema SVV_TABLE_INFO.

Classificação do layout de dados multidimensional (visualização)

Uma chave de classificação do layout de dados multidimensional é um tipo de chave de classificação AUTO que se baseia em predicados repetitivos encontrados em um workload. Se o workload tiver predicados repetitivos, o Amazon Redshift poderá melhorar o desempenho da verificação de tabelas colocando linhas de dados que atendam aos predicados repetitivos. Em vez de armazenar dados de uma tabela em ordem de colunas estrita, uma chave de classificação do layout de dados multidimensional armazena dados analisando predicados repetitivos exibidos em um workload. Mais de um predicado repetitivo pode ser encontrado em um workload. Dependendo do workload, esse tipo de chave de classificação pode melhorar o desempenho de muitos predicados. O Amazon Redshift determina automaticamente se esse método de chave de classificação deve ser usado em tabelas definidas com uma chave de classificação AUTO.

Por exemplo, suponhamos que você tenha uma tabela com dados classificado na ordem de colunas. Muitos blocos de dados talvez precisem ser examinados para determinar se atendem aos predicados no workload. Porém, se os dados forem armazenados no disco em uma ordem de predicados, menos blocos precisarão ser examinados para atender à consulta. O uso de uma chave de classificação de layout de dados multidimensional é benéfico nesse caso.

Para exibir se uma consulta está usando uma chave de layout de dados multidimensional, consulte a coluna step_attribute da exibição SYS_QUERY_DETAIL. Quando o valor é multi-dimensional, o layout de dados multidimensional foi usado na consulta. Para exibir se uma tabela definida com a chave de classificação AUTO está usando um layout de dados multidimensional, consulte a coluna sortkey1 da exibição SVV_TABLE_INFO. Quando o valor é padb_internal_mddl_key_col, o layout de dados multidimensional foi usado na chave de classificação da tabela.

Para evitar que o Amazon Redshift use uma chave de classificação de layout de dados multidimensional, escolha uma opção da chave de classificação de tabela diferente de SORTKEY AUTO. Para obter mais informações sobre opções SORTKEY, consulte CRIAR TABELA.

Chave de classificação composta

Uma chave composta contém todas as colunas listadas na definição de chave de classificação, na ordem em que estão listadas. Uma chave de classificação composta é mais útil quando o filtro de uma consulta aplica condições, tais como filtros e junções, que usam um prefixo das chaves de classificação. Os benefícios de performance da classificação compostos diminuem quando as consultas dependerem somente de colunas de classificação secundárias, sem consultar as colunas principais. COMPOUND é o tipo de classificação padrão.

As chaves de classificação compostas podem acelerar junções, operações GROUP BY e ORDER BY e funções de janela que usam PARTITION BY e ORDER BY. Por exemplo, uma junção de mesclagem, que frequentemente é mais rápida do junções de hash, é possível quando os dados são distribuídos e pré-classificados nas colunas de junção. As chaves de classificação compostas também ajudam a melhorar a compactação.

À medida que você adiciona linhas em uma tabela classificada que já contém dados, a região não classificada aumenta, o que tem um efeito significativo na performance. O efeito é maior quando a tabela usa classificação intercalada, especialmente quando as colunas de classificação contêm dados que crescem monotonicamente, tais como colunas de data ou timestamp. Execute uma operação VACUUM regularmente, especialmente após grandes carregamentos de dados, para reclassificar e reanalisar os dados. Para ter mais informações, consulte Gerenciamento do tamanho da região não classificada. Após a limpeza para reclassificação dos dados, é uma boa prática executar um comando ANALYZE para atualizar os metadados estatísticos para o planejador de consulta. Para obter mais informações, consulte Análise de tabelas.

Chave de classificação intercalada

Uma classificação intercalada concede igual peso a cada coluna ou subconjunto de colunas na chave de classificação. Se várias consultas usam colunas diferentes como filtros, é frequentemente possível melhorar a performance dessas consultas usando um estilo intercalado de classificação. Quando uma consulta usa predicados restritivos em colunas de classificação secundárias, a classificação intercalada melhora significativamente a performance da consulta em relação à classificação composta.

As melhorias de performance que você ganha ao executar uma chave de classificação intercalada devem ser pesadas contra o aumento nos tempos de carregamento e limpeza.

Classificações intercaladas são mais eficazes com consultas altamente seletivas que filtram uma ou mais das colunas de chave de classificação na cláusula WHERE, por exemplo select c_name from customer where c_region = 'ASIA'. Os benefícios da classificação intercalada aumentam com o número de colunas classificadas que são restritas.

Uma classificação intercalada é mais eficaz com tabelas grandes. A classificação é aplicada em cada fatia. Assim, uma classificação intercalada é mais eficaz quando uma tabela é grande o suficiente para exigir vários blocos de 1 MB por fatia. Aqui, o processador de consultas pode ignorar uma proporção significativa dos blocos usando predicados restritivos. Para visualizar o número de blocos que uma tabela usa, consulte a exibição de sistema STV_BLOCKLIST.

Para classificação em uma única coluna, uma classificação intercalada pode apresentar melhor performance do que uma classificação composta se os valores da coluna tiverem um prefixo comum longo. Por exemplo, URLs normalmente começam com “http://www”. As chaves de classificação compostas usam um número limitado de caracteres do prefixo, o que resulta na duplicação de muitas chaves. As classificações intercaladas usam um esquema de compactação interna para valores de mapas de zona que lhes permite melhor distinção entre valores de coluna que possuem um prefixo comum longo.

Ao migrar clusters provisionados do Amazon Redshift para o Amazon Redshift Serverless, o Redshift converte tabelas com chaves de classificação intercaladas e DISTSTYLE KEY em chaves de classificação compostas. O DISTSTYLE não é alterado. Para obter mais informações sobre estilos de distribuição, consulte Trabalhar com estilos de distribuição de dados.

VACUUM REINDEX

À medida que você adiciona linhas a uma tabela classificada que já contém dados, a performance pode deteriorar-se com o tempo. Essa deterioração ocorre para classificações compostas e classificações intercaladas, mas tem um efeito maior em tabelas intercaladas. Um VACUUM restaura a ordem de classificação, mas a operação pode levar mais tempo para tabelas intercaladas, pois a mesclagem de novos dados intercalados pode envolver a modificação de cada bloco de dados.

Quando as tabelas são carregadas inicialmente, o Amazon Redshift analisa a distribuição dos valores nas colunas-chave de classificação e usa essas informações para intercalação ideal das colunas-chave de classificação. À medida que a tabela cresce, a distribuição dos valores nas colunas de chave de classificação pode mudar, ou desviar, especialmente com colunas de data ou timestamp. Se o desvio se tornar muito grande, a performance poderá ser afetada. Para reanalisar as chaves de classificação e restaurar a performance, execute o comando VACUUM com a palavra chave REINDEX. Como ele precisa realizar uma análise adicional dos dados, o comando VACUUM REINDEX pode demorar mais do que um comando VACUUM padrão para tabelas intercaladas. Para visualizar informações sobre desvio de distribuição de chaves e hora da última reindexação, consulte a exibição de sistema SVV_INTERLEAVED_COLUMNS.

Para obter mais informações sobre como determinar a frequência de execução do VACUUM e quando executar o VACUUM REINDEX, consulte Decisão sobre a reindexação.