Tabelas Particionadas

Tabelas Particionadas

Entendendo o que são:

Tabelas particionadas são recursos disponibilizados pelos SGBD (Sistema Gerenciador de Bancos de Dados) para auxiliar nos serviços de consulta, manutenção, distribuição, atualizamento e remoção de dados. É bastante útil quando bem utilizado, e bastante comprometedor quando mal dimensionado ou utilizado em situações que não se mostram necessárias ou que se poderia utilizar outras funcionalidades.

Normalmente se recomenda o uso de particionamento quando temos tabelas muito grandes, porém, “grande” é um parâmetro relativo, para alguns, 50 GB é mais do que o banco da empresa, para outros, 10 TB “começa” a ficar inoportuno. Portanto, vamos demonstrar o que é, como fazer, e o critério de utilização fica a encargo do bom senso (aquilo que todo mundo diz ter mas nunca lembra de utilizar) de cada AD (Administrador de Dados).

---

Bom Senso. Qualidade de nosso espírito que nos permite distinguir o verdadeiro do falso, o certo do errado. 'O bom senso é a coisa do mundo mais bem distribuída' (Descartes). Às vezes Descartes denomina o bom senso de 'luz natural'. Na maioria dos casos, chama-o simplesmente de razão, instrumento geral do conhecimento que é capaz de 'julgar e distinguir bem o verdadeiro do falso'. Essa faculdade da razão é natural e comum em todos os homens."

---

O particionamento é a possibilidade de desmembramento, vertical ou horizontal, de uma tabela, para que possamos obter um melhor aproveitamento dos dados contidos nela. Se a tabela possui milhões de linhas, é costume fazermos um desmembramento vertical, onde, a partir de uma chave específica (data, por exemplo), se faz as divisões em partições. Outra possibilidade é o particionamento horizontal, quando a tabela possui muitas colunas, replicamos a chave e repartimos a tabela entre as colunas mais e menos acessadas.

Processo de Leitura a Disco:

Os dados são armazenados em blocos de 4, 8 ou 16 KB, o banco de dados trará, em cada leitura, um grupo de blocos, se o registro possui poucas colunas, cada bloco poderá possuir diversos registros, aproveitando melhor cada processo de leitura a disco, que é a operação mais custosa do banco de dados. Uma leitura não irá trazer um único registro e sim um conjunto de registros, se o registro for muito grande, isto é, muitas colunas, é provável que ele esteja armazenado em mais de um bloco, isso poderá acarretar em diversos processos de I/O na busca de determinado dado. Se desejamos apenas conferir o CPF de um cliente, a leitura irá buscar todo o registro e separar o conteúdo que satisfará a nossa pesquisa. Neste caso, quanto menos colunas o registro possuir, mais rápido será o processo e maior a capacidade de armazenamento em memória dos dados recuperados do disco.

Desmembramento Horizontal:

Com base na afirmação acima, cada vez que o SBGD vai ao sistema operacional pedir um bloco para aloca-lo em memória para trabalhar, temos a possibilidade de trazer mais conteúdo nesta modalidade de particionamento.

Imaginemos que possuímos uma tabela com trinta ou quarenta colunas, muito comum em sistemas desenvolvidos a muito tempo, mas também poderá ser encontrado em situações em que são cadastrados detalhes de uma peça, como dimensões, peso, material, etc., além do código e da descrição da peça. Se formos olhar apenas para questões de estoque e faturamento, o código, nome e quantidade são relevantes, as demais informações não são. Se olharmos para uma linha de produção, os demais itens são imprescindíveis, pois temos que colocar a peça certa, caso contrário nosso produto será falho, incompleto ou incorreto.

Assim, nós costumamos repartir a tabela em duas (ou três) sub tabelas, que terão as especificações necessárias para cada parte do sistema sem ter que consultar outras colunas. Para que isso seja factível, temos que replicar o código, ou a chave primária (se for composta), em cada sub tabela, muitas vezes consideramos a utilização de tabelas auxiliares, que possuem ligação com outras tabelas e formam o conjunto, por exemplo, nossa peça ligada com a tabela de matérias primas, com a tabela de medidas, de cores, etc. Cada modelo de aplicação tem sua forma de resolver este processo.

Também podemos pensar em questões de segurança, colocando as colunas com dados confidenciais em uma partição criptografada, que não estará acessível a quem não é de direito.

Desmembramento Vertical:

Este é o tipo de desmembramento mais comum, quando queremos dividir os lançamentos por períodos ou outra forma de agrupamento, então criamos uma cópia da tabela que levará no nome o período solicitado, por exemplo, os registros de ligações controladas por uma empresa de telefonia, mês a mês uma nova partição irá surgir e será alimentada com os dados referentes àquele mês, normalmente a consulta é realizada no período da partição, mas eventualmente é necessário fazer uma consulta mais abrangente, necessitando de uma varredura em toda a tabela. Quando esta tabela é consultada, a varredura acontecerá na partição referenciada, diminuindo assim sensivelmente o tempo de busca de cada registro.

Observações Iniciais:

Existem diversas vantagens e desvantagens para cada opção que resolvermos implementar em nossa aplicação, a princípio, o que temos que levar em conta é:

VANTAGEMS:

Desempenho em leitura.

• Ao particionar uma tabela, o índice parcial irá abranger apenas a massa de dados daquela partição, logo a busca na árvore binária será significativamente menor.
• Com um índice menor, a possibilidade que ele caiba na memória será muito maior, agilizando a pesquisa, pois é na memória que os processos acontecem, se o índice for muito grande, será necessário fazer swap, o que não é bom para o banco.
• Se os particionamentos estiverem distribuídos em discos distintos, uma consulta na partição atual não influenciará uma consulta a períodos anteriores, o que é bastante vantajoso, como veremos mais para frente.
• Em tabelas particionadas horizontalmente, a diminuição do tamanho do registro, número de colunas, possibilitará a alocação de mais conteúdo a cada leitura, diminuindo o consumo de memória e acessos a disco.

Diferentes dispositivos de gravação.

• Com o particionamento, é possível armazenar os dados em diferentes tipos de repositórios, por exemplo, deixar os discos mais lentos para partições pouco utilizadas e discos em rede para registros muito grandes como imagens ou aplicativos.
• IEm particionamento por região, por exemplo, podemos ter gravações locais e consultas remotas, sem que isso cause retenções ou demoras para as ações mais importantes.

Desempenho em gravação.

• Se você for efetuar gravações de dados em áreas distintas da partição, como no caso da partição horizontal, você terá um aumento de performance, pois o processo será dividido em mais de um meio de gravação, podendo acontecer de forma concorrente.
• Da mesma forma, é possível fazer backups parciais, em partições distintas, sem que isso venha a comprometer os demais acessos a disco.
• Em Bancos de Dados clusterizados é possível ler e gravar de forma simultânea, dependendo do desenho da aplicação, diminuindo a concorrência.

Expurgo de dados.

• Quando você deseja eliminar dados antigos, ao invés de utilizar o comando delete, que fará a exclusão linha por linha, você poderá utilizar o comando drop partition, isso eliminará imediatamente a partição em questão, sem maiores custos para o banco de dados e sem resquícios que deverão ser limpos posteriormente com shirink ou vacuum
• Esta opção não é compatível com o particionamento horizontal, pois não faria sentido manter chave e remover os conteúdos.

Rotinas de manutenção.

• Em alguns bancos, como o PostgreSQL e o SQL Server, o comando Shirink ou Vaccum, ou então redefinir os índices, pode ser feito em cada partição de forma individual, evitando assim que os registros fiquem travados por mais tempo que o necessário
• Da mesma forma, os processos de recalculo de estatísticas pode ser feito de forma direcionada.

Pontos de falh

• Com a distribuição dos dados em diversos dispositivos e repositórios, evita-se a perda de dados caso haja falha de acesso em algum dos dispositivos.

Escalabilidade.

• Quanto maior o banco de dados mais demorado o processamento dos dados ali contidos, com o processo de particionamento pode-se aumentar significativamente os limites de armazenamento sem comprometer a estrutura do banco e sua funcionalidade.

DESVANTAGEMS:

Administração.

• O processo de administração de uma tabela muito particionada tende a ser mais complexo conforme aumentam as partições, se a criação de uma nova partição é automática ou manual, o acompanhamento deve ser contínuo, uma parametrização equivocada ou o esquecimento da criação de uma partição pode causar muitos problemas e perda de dados pode ser irreversível.
• Se um range de dados que especifica, por exemplo, um estado ou cidade, for parâmetro de uma partição, a ausência ou a definição incorreta pode tornar certos dados inacessíveis ou uma alocação excessiva de determinada partição.
• Apesar da grande vantagem de excluir-se uma partição de dados, é muito comum encontrarmos nos bancos tablespaces vazias ou então no sistema operacional espaços alocados para uma tablespace que já não é mais utilizada.

Integridade.

• Mudança do valor da chave, dependendo do gerenciador de bancos de dados, pode acarretar em problemas, pois a linha não será transportada de partição e possivelmente o dado deixará de ser encontrado.
• As regras de particionamento devem ser muito claras e mesmo assim, podem ocorrer gravações indevidas caso surjam dados com valores diferentes dos especificados nas regras
• TO relacionamento (chaves estrangeiras) entre tabelas particionadas e não particionadas pode significar um trabalho a mais para cumprir regras e direcionamentos adequados.
• Dependendo do modelo escolhido, pode haver a necessidade de partições e sub partições, não existe um número específico que limite a criação de particionamento, porém quanto mais desmembrarmos uma tabela, mais regras estarão envolvidas e mais trabalho o banco terá para encontrar ou manusear os dados.

Desenvolvimento

• Os comandos de consulta necessitam que as parametrizações do desmembramento façam parte da cláusula de restrição (where), caso contrário toda a tabela será varrida na busca pelo dado.
• Cada nova divisão, além da chave primária, outras colunas serão inseridas, deixando mais complexo o processo de resgate de dados específicos.
• Para tabelas particionadas horizontalmente, a escolha das colunas que farão parte de cada partição é fundamental para o melhor aproveitamento das pesquisas, se eu pegar a chave primária e uma coluna de cada sub partição, então qualquer vantagem será desfeita e o prejuízo começará a se evidenciar.

Escolhendo uma chave:

A chave em uma tabela é fundamental para o MER (Modelo Entidade Relacionamento), todo modelo de dados, por princípio deve possuir chaves primárias e chaves estrangeiras, assim sendo, a escolha da chave deve ser feita de acordo com este modelo, porém, provavelmente esta escolha sofrerá uma mudança estrutural devido à forma de particionamento, possivelmente com a criação de uma chave composta, pelo conteúdo do particionamento e a chave propriamente dita. Se por alguma especificidade sua chave já for composta com mais de três colunas, torna-se necessário repensar esta chave, quanto mais composta ela for, mais complexo será o tratamento das partições e a busca dos dados.

Dependendo da plataforma de banco escolhida, o tratamento de chaves estrangeiras será diferenciado, portanto isto também deverá ser levado em conta quando escolher um tipo de particionamento, já que é provável que muitas redefinições terão que acontecer na adequação a este recurso.

Construindo uma tabela particionada:

A construção de uma tabela particionada pode ser feita em dois momentos distintos, se você está modelando um novo sistema e, antecipadamente, identifica a necessidade de particionamento, ou então quando a tabela está grande e demorada o suficiente que necessite de uma reestruturação para sofrer um particionamento. Existem diversas empresas que acreditam que particionar tabela é ponto fundamental de uma aplicação bem estruturada e não fazem a real avaliação de sua necessidade. Só utilize os recursos quando forem necessários e quando tiver pleno domínio das suas atribuições.

Tabelas oriundas de uma modelagem bem-feita:

Quando a tabela tem sua origem na modelagem, sua criação e manutenção torna-se muito mais simples, porém não isenta de uma série de verificações importantíssimas, como quais serão as colunas chave, quais serão as colunas de particionamento, como os dados serão armazenados e distribuídos, como será o expurgo dos dados, como será o processo de criação de novas partições, etc.

Os dados irão popular a tabela de acordo com as regras de particionamento, então a distribuição será homogênea e a fragmentação será mínima. O crescimento da base pode ser facilmente administrado e uma realocação de partição entre discos não gera grandes dificuldades.

Este tipo de desenvolvimento já vem preparado com uma regra de criação automática de novas partições, a atenção para que dados equivocados não criem partições diferenciadas e acabem causando uma sobrecarga desnecessária nas partições envolvidas.

Tabelas existentes:

Quando trabalhamos com tabelas já populadas, e que necessitam de particionamento, teremos que verificar qual será a forma de transição, o mais comum é a criação de uma tabela com regras de particionamento e depois migrar os dados para ela, se for com particionamento automático a migração deve ser muito bem administrada para que não perca nenhuma chave de particionamento e nem que tenham dados alocados equivocadamente em partição indevida

A migração de dados deve ser gradual e orquestrada harmoniosamente, uma verificação de conteúdos é importante para que não sejam enviados dados incompatíveis e nem inválidos, pois quando particionamos uma tabela, os dados sofrerão mais validações do que antes.

Se for necessária a criação de uma nova coluna para contemplar o particionamento, então o critério de escolha deve ser muito bem elaborado, pois uma mudança posterior em um dado que é chave de partição pode ocasionar uma perda de conteúdo.

Criando tabelas particionadas:

Possibilidades de particionamento:

Quando vamos particionar uma tabela, temos que verificar como serão divididas as áreas de dados, que podem ser por range, lista, hash ou composto.

• Range: por intervalos de valores, normalmente datas, esta forma é a mais comum.
• Lista: as divisões são baseadas em listas de valores, normalmente referenciados por chaves estrangeiras. Como por exemplo por países, estados ou cidades.
• Hash: trabalha com faixas de valores, que não irão se encaixar nem na opção de range nem em listas.
• Composto: Quando utiliza mais de uma forma de particionamento, normalmente ligado a situações que solicitam sub-particionamento.

Modelando uma tabela para particionamento:

Partindo-se do princípio que foi identificada a necessidade de particionamento, temos que definir a chave primária, que irá ser como chave de particionamento também. Se a chave primária for composta, e nenhuma das colunas existentes servem como delimitador de partição, então um novo conjunto de colunas deverá ser especificado, não sendo expressamente necessário que seja uma chave única.

A tabela possui grupos de informações que sofrem ações de consulta, inclusão, manutenção ou exclusão com diferentes ritmos de interferência, algumas colunas são fixas, imutáveis em seu conteúdo e dimensionamento, por exemplo número de documento, outras podem sofrer alterações como redimensionamento, por exemplo na coluna nome, cadastra-se “Márcio”, posteriormente corrige para “Márcio Almeida” e finalmente acerta para “Márcio de Souza Almeida”, estes processos alocam espaços diferentes em cada ação, podendo sofrer fragmentação de acordo om a quantidade de atualizações e tamanho das mudanças executadas. Com base neste conceito, a ordem em que as colunas constituirão a tabela é muito importante, pois uma definição incorreta pode obrigar o banco a ler mais de um bloco de dados para obter o conteúdo de um único registro.

As colunas com tamanho fixo deverão ser as primeiras a compor a tabela, seguidas das com tamanho variável e no final as colunas cujo conteúdo pode não ser preenchido, NULL, isso permitirá uma alocação mais equilibrada e uma busca mais eficiente dos dados armazenados.

Para tabelas com mais de 15 colunas, uma revisão sobre a possibilidade de particionamento horizontal deve ser feita, ou então a utilização de tabelas auxiliares, que serão acessadas por meio de chaves estrangeiras.

Tabelas de LOG são fundamentais para muitos sistemas e, em muitos casos, são muitas as informações colhidas no processo, gerando registros complexos e detalhados, estas são candidatas a particionamento vertical e horizontal.

Tabelas auxiliares:

O uso de tabelas auxiliares, como a de itens de uma nota fiscal, são opções relevantes para evitar particionamento, possibilitando o acesso a dados específicos sem a necessidade de consultas muito complexas.

Outras tabelas como descrição e características de produtos são importantes para evitar a redundância de dados e diminuição de erros de digitação.

Não é recomendável que chaves que façam a ligação com tabelas estrangeiras sejam critério de particionamento de tabela principal, pois a inclusão de novos dados poderá corromper os critérios de particionamento.

Criando índices particionados:

As consultas, em sua quase totalidade, são direcionadas por índices, que nada mais são do que tabelas referenciais com as chaves e o posicionamento físico, ROWID, dos demais dados que se encontram em tabelas de dados. Muitas vezes, encontramos tabelas de dados com mais de dez índices ligados a ela, às vezes até de forma redundante, por este motivo, uma revisão periódica nestes objetos de bancos de dados torna-se necessária.

As consultas, em sua quase totalidade, são direcionadas por índices, que nada mais são do que tabelas referenciais com as chaves e o posicionamento físico, ROWID, dos demais dados que se encontram em tabelas de dados. Muitas vezes, encontramos tabelas de dados com mais de dez índices ligados a ela, às vezes até de forma redundante, por este motivo, uma revisão periódica nestes objetos de bancos de dados torna-se necessária.

Quando fazemos uma consulta na tabela de clientes, passando o CPF e buscando o nome do cliente, o SGBD irá consultar o índice e, após localizar o dado pesquisado, irá na tabela de dados, já com o endereço correto ROWID, agilizando a busca. Porém, se desejamos apenas confirmar que determinado CPF faz parte de nossa base de dados, o processo se encerra na primeira fase, no índice, já que não é necessário trazer o nome, apenas saber se está ou não, isso o índice pode responder, portanto, se conseguimos resolver uma questão somente com o conteúdo do índice, temos uma agilização nos processos de consulta bastante significativos.

Se temos índices particionados, conseguimos agilizar ainda mais o processo, pois o range de busca de um conteúdo torna-se sensivelmente menor, por isso insistimos na elaboração consciente dos índices a ser criados. Dividir para conquistar, este é o princípio dos índices particionados.

Para tabelas particionadas:

Para tabelas particionadas, é bastante evidente, até mesmo intuitivo, que devemos criar índices que acompanhem o critério de particionamento da tabela, estes são os índices locais, pois desta forma podemos seguir o range de dados nos locais onde eles se encontram, por exemplo, se buscamos os dados de uma pessoa do estado do Amazonas, nós vamos fazer essa busca na partição Amazonas, através do índice de partição desse estado.

Podemos também utilizar índices globais que, ao contrário dos índices locais, não estão restritos às respectivas partições, se eu criar um índice global de código de atividade do cliente, isso trará clientes de todas as regiões (partições) a partir de um critério específico, por isso o conceito de global, abrange a tabela independente dos critérios de particionamento. Porém devemos ficar atentos, pois uma ação estrutural na tabela, como um drop partition, poderá tornar o índice global inutilizável.

Também podemos criar índices de bitmap em tabelas particionadas. A única restrição é que os mesmos só podem ser índice loc

Para tabelas não particionadas:

Para tabelas não particionadas, não é possível a criação de índices locais, pois os mesmos estão em ligação restrita à partição, como não há partição, não há como cria-los. Portanto, para tabelas não particionadas, só poderemos utilizar índices particionados do tipo global, que avalia a tabela como um todo.

Lembrando que, quando o retorno de dados de um índice for inferior a uma determinada parcela do índice, o mesmo é desprezado pelo SGBD e a consulta é feita diretamente na tabela. Porém, quando diminuímos o tamanho do índice, teremos uma possibilidade muito grande de o mesmo ser utilizado em uma pesquisa, assim sendo, particionar um índice pode ser uma opção de otimização de consultas, sem que seja necessário o particionamento da tabela.

Conclusão

Este recurso que uma grande quantidade de SGBDs oferecem, é de grande valia para organização, distribuição e recuperação de dados, porém, como todos os demais recursos oferecidos, devem ser avaliados e adequados às necessidades.

---

Marcio Almeida formado em Matemática com Ênfase em Informática, é especialista em Banco de Dados Oracle com sólidos conhecimentos em Tuning de Banco e Aplicação, Sistemas Operacionais Linux e Soluções em Inteligência da Informação; trabalha com Oracle há 21 anos, é Oracle Consulting. Márcio também é membro do Grupo de Usuários Oracle do Brasil (GUOB), entre outros grupos de discução, como GPO, e palestras, como o DBA Brasil.

Este artigo foi revisto pela equipe de produtos Oracle e está em conformidade com as normas e práticas para o uso de produtos Oracle.