Sunday, June 20, 2010

(T) Indexação no Data Warehouse - Parte 2

Consideração sobre o predicado

Digamos que um data mart processasse continuamente consultas usando as colunas PURCHASE_DATE e CUST_NUM como parte do predicado. Essas duas colunas devem ser consideradas para índices. As colunas apresentadas como parte dos resultados da consulta, mas não utilizadas como parte de um predicado, não são boas candidatas para índice. Em outras palavras, a coluna em itálico na listagem a seguir pode não ser conveniente para um índice, enquanto a que parece em negrito pode ser uma candidata ideal:

select sum(aggr_day), region, ...
from day_summary, region
where trans_date between '01-jan-2002' and '31-jan-2002' ...;

Isso nos leva à segunda diretriz de indexação importante.

DICA: As colunas que normalmente fazem parte de critérios de seleção de consulta são candidatas para índices.

Naturalmente, como essa é uma regra (na verdade, uma diretriz), existem exceções. As colunas continuamente mencionadas em um predicado, mas nas quais uma função ou operação é efetuada, não são candidatas para índices. Se uma função deve ser executada em uma coluna, o índice da coluna não é usado. Chamamos isso de supressão de índice. A listagem a seguir mostra dois exemplos de como a coluna TRANS_DATE, da listagem anterior, pode ser usada, mas que não garante um índice:

select sum(aggr_day), region, ...
from day_summary, region
where to_char(trans_date, 'Dy') in ('Mon', 'Tue') ...;

select sum(aggr_day), region, ...
from day_summary, region
where months_between (trans_date, sysdate) > 6 ...;

Existe mais uma diretriz que trata com colunas mencionadas em predicados, com e sem função. Suponha que tenhamos um exemplo de quando a coluna TRANS_DATE de DAY_SUMMARY é usada na instrução SQL:

select ...
from day_summary ...
where to_char (trans_date) ...;

Assim como na instrução SQL:

select ...
from day_summary ...
where trans_date between ...;

DICA: Uma coluna que é usada em um predicado - com e sem função executada nela - ainda pode ser candidata para um índice. Analise o número de instruções SQL que estão usando uma função e implemente um índice, caso ele otimize as instruções sem uma função.

Exclusividade do data warehouse

De certa forma, verificamos aqui que a indexação de um data warehouse ou mesmo um data mart é um ponto importante a ser observado e tratado pelos arquitetos de solução juntamente com os administradores de banco de dados e que não pode ser colocado em segundo plano. Uma ocorrência comum encontrada no projeto de sistemas operacionais/transacionais é que o banco de dados usado durante o desenvolvimento é muito diferente de seu equivalente de produção. Por exemplo, ele poderia conter apenas um pequeno subconjunto dos dados de produção ou as tabelas usadas seriam uma fração de seu tamanho de produção.

Em algumas empresa é possível encontrar tabelas muito grandes, com 30 ou 40 milhões de linhas na produção e um subconjunto de 30 ou 40 mil linhas no desenvolvimento. No ambiente de data warehouse ou data mart, devem existir dados de desenvolvimento suficientes para que decisões de indexação bem pensadas possam ser tomadas em relação a volumes de dados realistas. Sob essas condições, torna-se mais fácil avaliar a eficiência dos índices e realizar testes para ajustar os índices adicionais que poderiam ajudar no desempenho das consultas a serem realizadas. As tabelas podem ser analisadas e cálculos sobre a seletividade podem ser feitos, para decidir quais são as colunas apropriadas para indexação.

DICA: As colunas indexadas nos sistemas operacionais/transacionais não são necessariamente boas candidatas à indexação no data warehouse.

Monday, June 7, 2010

(T) Indexação no Data Warehouse - Parte 1

Um índice é, na maior parte dos casos, uma estrutura separada dos dados da tabela a que ele se refere. Ele armazena a localização de linhas no banco de dados, baseado nos valores de coluna especificados quando o índice é criado. Os índices são como minicópias dos dados da tabela a que se referem. Vamos supor que uma consulta fosse restrita a LAST_NAME e procurasse nomes que começassem com o texto "SM". Sem um índice em LAST_NAME, o banco de dados leria da primeira linha até a última, procurando as linhas com o string de pesquisa desejado. Com um índice, o banco de dados percorreria, obteria um endereço da linha qualificada e, em seguida, apresentaria os dados da linha qualificada, para o processo que fez a consulta. Em resumo, é para isso que servem os índices.

As pequisas de índice são o segredo da otimização do tempo de resposta da maior parte das consultas e são usados sistematicamente em um data warehouse para melhorar seu desempenho de saída.

Um desempenho de saída melhorado contribui para as quatro palavras tão importantes para o sucesso de um projeto de data warehouse - maior satisfação do cliente. No entanto, para se atingir este estágio se faz necessário decidir onde colocar os índices. De muitas maneiras, essa é uma faca de dois gumes: índices demais levam a uma sobrecarga maior do sistema, mas índices de menos podem facilmente diminuir a velocidade de recuperação dos dados no momento da consulta. Então...

Quais colunas indexar?

Existem duas regras ou diretrizes principais com relação a quais colunas indexar no banco de dados: seletividade e critérios de seleção. Seletividade, é uma medida do número de valores distintos na coluna de uma tabela, comparado ao número de linhas da tabela inteira. O predicado de uma instrução SQL é aquela parte onde os critérios de seleção são especificados. Esses critérios de seleção especificam quais linhas de informação devem ser incluídas no conjunto de resultados da consulta. O primeiro critério com a palavra-chave WHERE; todos os critérios subseqüentes começam com a palavra-chave AND. O conjunto de resultados é um conjunto de uma ou mais linhas que se qualificam para inclusão em uma consulta específica.

Consideração sobre a seletividade

Vamos supor que existissem 79.000 linhas em uma tabela, em um data mart financeiro, e que a coluna ACC_TYPE tivesse 8 valores distintos. A seletividade de qualquer linha em uma tabela é calculada de acordo com a seguinte fórmula:

seletividade = (linhas na tabela / valores distintos) * ( 1 / linhas na tabela)

Isso é o mesmo que dizer que a seletividade é o inverso do valor encontrado em NUM_DISTINCT para essa coluna. Em nosso exemplo, a seletividade é igual ao inverso de 8, o que dá 0,125, ou 12,5, expresso como uma porcentagem. Isso leva à primeira das três diretrizes de indexação.

Quando é encontrado um valor de coluna menor do que 5% de todas as linhas em uma tabela, essa coluna é uma boa candidata para um índice.

Espero que esta primeira parte deste importante tópico, tenha sido útil para todos vocês. Na próxima semana falarei da outra diretriz de indexação: consideração sobre o predicado.

Um grande abraço a todos!

Lastest Posts

(T) Using shared variables and key-value pairs