Particionamento de Tabelas: uma visão pragmática

05/07/2009 às 12 | Na categoria Geral, Novas Versões | 4 Comentários
Tags: delete, drop partition, partições, particionamento

A 24 de Junho de 1997, quando foi anunciado o Oracle8, todos esperavam saber como seria, mas entre todas as novas funcionalidades, aquela que mais entusiasmo provocou junto da comunidade de utilizadores Oracle da época era o particionamento de tabelas.
Não é que fosse algo de completamente novo, mas pelo menos existia agora um suporte nativo do SGBD para implementar estruturas de dados com comportamento de moving window ou sliding window e para os sistemas que têm capacidades de manter tabelas gigantescas, teriam agora uma forma mais ágil de o fazer. Os sistemas de janela-deslizante não que possuem aquelas tabelas em que entram tantos dados quantos os que saiem. Os exemplos são inúmeros, desde os Call Data Records na indústria de telecomunicações, aos registos de acesso dos ISPs, passado pela tecnologia de tolling nas concessionárias de auto-estradas e vias rápidas.

Porquê usar particionamento?

E ainda há quem se pergunte: quais são as principais vantagens do particionamento?
A pergunta só se coloca dado que a sua implementação comporta um custo extra de licenciamento. Porquê pagar mais?

Em primeiro lugar esta questão nem sequer se deve colocar em sistemas de moving window. Nestes sistemas a não aquisição do sistema de particionamento pode revelar que a necessidade não foi prevista no início, ou que se está a trabalhar com janelas temporais superiores ao previsto. Uma solução de entrada e saída de dados com alguma intensidade, exige um sistema de particionamento. Mas porquê? O problema não está na entrada dos dados, mas sim na saída. Qual dos dois é mais prioritário? Certamente que será a entrada. Mas ao fim de umas horas, dias ou semanas, se os dados que já expiraram não tiverem saído, temos um problema de volume e falta de espaço. Portanto, para que a entrada seja prioritária face à saída é preciso implementar mecanismos de saída que não interfiram na entrada e acima de tudo que não gerem mais carga na BD. Vamos ver que os exemplos deste artigo ilustram que a carga de saída chega a ser mais do dobro da carga de entrada para o mesmo número de registos!
E não pode ser. A saída deve ser feita da forma mais elegante possivel. O comando DELETE pode ter muitas características, mas a “elegãncia” não é certamente uma delas. É um comando que junta o pior dos dois mundos: a destruição e a anulação da destruição. Isto porque como comando DML pode sempre ser anulado no final ou durante a execução, através do mecanismo de ROLLBACK.
Se usamos INSERTs ou SQL*Loader para fazer os dados chegar às tabelas pouco importa, mas a diferença entre fazer esses dados sair uma vez expirados via DELETE ou remoção de partições, é muito grande! Faz toda a diferença do mundo porque o DELETE é proporcional ao volume de dados a remover e o DROP ou TRUNCATE PARTITION não. Com particionamento é igual remover mil ou mil milhões de registos.

Um sistema de moving window que baseie a sua politica em comandos de DELETE tem vários problemas:

* DELETEs são comandos DML e como tal vão gerar mais LOG e mais espaço (cada DELETE gera ainda uma quantidade enorme de UNDO)
* A remoção de linhas de forma explicita via DELETE é perigosa, porque se podem remover linhas erradamente
* Comandos DELETE são muito pesados e devem executar dentro de uma janela de manutenção mas caso essa execução não aconteça durante um determinado periodo, o volume de dados vai aumentando e vários problemas podem acontecer: falta de espaço ou incapacidade em “encaixar” os DELETEs na janela de manutenção existente

Conclusão: implementar a saída de dados em sistemas de sliding window com remoção explicita de linhas (DELETEs) cria uma bomba relógio dentro da base de dados que mais tarde ou mais cedo irá rebentar, e que exige inúmeras horas de atenção humana, gera carga na máquina e custos de manutenção elevados.

Tuning

Em termos de performance as vantagens do particionamento são claras, assim que uma tabela é particionada, o optimizador irá automaticamente detectar quais as partições que irá necessitar percorrer nos seus acessos melhorando assim de forma dramática as consultas sobre as tabelas particionadas.
Em termos de paralelismo, em sistemas massivamente paralelos o facto de a tabela estar já dividida fisicamente em partes, torna quer as entradas de dados quer as consultas exponencialmente mais rápidas, paralelizando qualquer um dos referidos processos.

Particionamento: um exemplo

Ao implementar o mesmo sistema com partições, vamos imaginar que a carga deve ser “limpa” todos os dias. Vamos portanto criar uma partição por cada dia. Não há problema, uma tabela pode ser particionada um milhão de vezes (dá cerca de 2740 anos!).

Aqui fica um exemplo de uma situação em que cada dia irá constituir uma partição, mas em que irão existir apenas 4 tablespaces, um para cada semana do mês. Além disso irá ser criado um tablespace de sobra para os registos com valores nulos na chave de partição ou com valores superiores à ultima definição de particionamento.

Vamos usar como exemplo o particionamento de uma tabela no schema HR de “CHAMADAS” telefónicas. Um exemplo reduzido ao maior grau possivel de simplicidade, com a data de inicio da chamada como chave de particionamento.

Tablespaces criados:

SQL> select * from v$tablespace;

 TS# NAME                           INC BIG FLA ENC
---------- ------------------------------ --- --- --- ---
 0 SYSTEM                         YES NO  YES
 1 SYSAUX                         YES NO  YES
 2 UNDOTBS1                       YES NO  YES
 4 USERS                          YES NO  YES
 3 TEMP                           NO  NO  YES
 6 EXAMPLE                        YES NO  YES
 7 SEMANA1                        YES NO  YES
 8 SEMANA2                        YES NO  YES
 9 SEMANA3                        YES NO  YES
 10 SEMANA4                        YES NO  YES
 11 SEMANA_SOBRAS                  YES NO  YES

11 linhas seleccionadas.

Agora vamos criar a tabela com as partições. Esta parte é trabalhosa, e exige muita atenção. A tabela será criada de forma que todos os dias de Maio terão a sua partição, mas a ocupar os tablespaces semanais:

create table hr.chamadas (codigo number,
descricao varchar2(1000),
data_inicio date,
duracao_segundos number)
partition by range (data_inicio)
(
-- Semana 1
partition CHP1 values less than (to_date('05052009','ddmmyyyy')) tablespace semana1,
partition CHP2 values less than (to_date('06052009','ddmmyyyy')) tablespace semana1,
partition CHP3 values less than (to_date('07052009','ddmmyyyy')) tablespace semana1,
partition CHP4 values less than (to_date('08052009','ddmmyyyy')) tablespace semana1,
partition CHP5 values less than (to_date('09052009','ddmmyyyy')) tablespace semana1,
partition CHP6 values less than (to_date('10052009','ddmmyyyy')) tablespace semana1,
partition CHP7 values less than (to_date('11052009','ddmmyyyy')) tablespace semana1,
-- Semana 2
partition CHP8 values less than (to_date('12052009','ddmmyyyy')) tablespace semana2,
partition CHP9 values less than (to_date('13052009','ddmmyyyy')) tablespace semana2,
partition CHP10 values less than (to_date('14052009','ddmmyyyy')) tablespace semana2,
partition CHP11 values less than (to_date('15052009','ddmmyyyy')) tablespace semana2,
partition CHP12 values less than (to_date('16052009','ddmmyyyy')) tablespace semana2,
partition CHP13 values less than (to_date('17052009','ddmmyyyy')) tablespace semana2,
partition CHP14 values less than (to_date('18052009','ddmmyyyy')) tablespace semana2,
-- Semana 3
partition CHP15 values less than (to_date('19052009','ddmmyyyy')) tablespace semana3,
partition CHP16 values less than (to_date('20052009','ddmmyyyy')) tablespace semana3,
partition CHP17 values less than (to_date('21052009','ddmmyyyy')) tablespace semana3,
partition CHP18 values less than (to_date('22052009','ddmmyyyy')) tablespace semana3,
partition CHP19 values less than (to_date('23052009','ddmmyyyy')) tablespace semana3,
partition CHP20 values less than (to_date('24052009','ddmmyyyy')) tablespace semana3,
partition CHP21 values less than (to_date('25052009','ddmmyyyy')) tablespace semana3,
-- Semana 4
partition CHP22 values less than (to_date('26052009','ddmmyyyy')) tablespace semana4,
partition CHP23 values less than (to_date('27052009','ddmmyyyy')) tablespace semana4,
partition CHP24 values less than (to_date('28052009','ddmmyyyy')) tablespace semana4,
partition CHP25 values less than (to_date('29052009','ddmmyyyy')) tablespace semana4,
partition CHP26 values less than (to_date('30052009','ddmmyyyy')) tablespace semana4,
partition CHP27 values less than (to_date('31052009','ddmmyyyy')) tablespace semana4,
partition CHP28 values less than (to_date('01062009','ddmmyyyy')) tablespace semana4,
-- Sobras e mais tarde objecto de SPLIT
partition CHP99999 values less than (maxvalue) tablespace semana_sobras
)
/

Neste momento de fosse inserida uma linha com data de inicio de chamada nulo, ou com data superior a 1 de Junho de 2009, essa linha iria para a partição “CHP99999″. Antes de chegar o mês de Junho há que dividir a partição CHP99999 em tantas partições quantos os dias de Junho, ou seja 30 operações de SPLIT PARTITION em que a primeira seria algo como:

alter table hr.CHAMADAS
split partition CHP99999
at (to_date('02062009','ddmmyyyy'))
into
(partition CHP29     tablespace semana1,
partition CHP99999     tablespace semana_sobras);

Em alternativa a esta abordagem podemos ir adicionando as partições, mas a desvantagem é que deste modo não podemos ter uma partição MAXVALUE. O comando para adicionar uma nova partição no final seria algo como:

alter table hr.CHAMADAS
add partition CHP30 values less than (to_date('03062009','ddmmyyyy'))
tablespace semana1;

Caso tivessemos a partição MAXVALUE e quisessemos usar o método do ADD PARTITION, antes de adicionar uma nova partição teriamos de apagar a partição MAXVALUE, adicionar as novas partições e no final tornar a adicionar a partição MAXVALUE. Esta jiga-joga deve-se ao simples facto de que não podemos adicionar partições “no meio” de outras, porque se o tentarmos teremos o seguinte erro:

SQL> alter table hr.CHAMADAS
add partition CHP30 values less than (to_date('03062009','ddmmyyyy'))
tablespace semana1;
 2    3  add partition CHP30 values less than (to_date('03062009','ddmmyyyy'))
 *
ERRO na linha 2:
ORA-14074: limite de partição deve ser superior ao da última partição

Para evitar inundar a tabela com split partitions ou add partitions, existem duas hipóteses: subparticionar ou criar logo as partições todas para várias semanas, uma vez que as partições vazias não ocupam espaço. Aqui o processo costuma e deve ser todo automatizado com scripts.

Rodar a Janela

Vamos inserir na primeira partição 500 mil linhas em cinco tranches de 100 mil registos na primeira partição e depois comparar os tempos de remoção de linhas versus remoção de partições.

SQL> ed
Escritos file afiedt.buf

1  begin
2    for i in 1..100000 loop
3       insert into hr.chamadas values(i,'primeiro insert',sysdate-3,10);
4       if i in (1000,5000,7500,10000,20000,35000,60000,80000) then
5        commit write nowait;
6        dbms_output.put_line('Linhas Inseridas: '||to_char(i));
7       end if;
8      end loop;
9      commit;
10* end;
SQL> save load.sql replace
Escritos file load.sql
SQL> set serverout on
SQL> @load
Linhas Inseridas: 1000
Linhas Inseridas: 5000
Linhas Inseridas: 7500
Linhas Inseridas: 10000
Linhas Inseridas: 20000
Linhas Inseridas: 35000
Linhas Inseridas: 60000
Linhas Inseridas: 80000

Procedimento de PL/SQL concluido com exito.

Decorreram: 00:00:11.99
SQL> @load
Linhas Inseridas: 1000
Linhas Inseridas: 5000
Linhas Inseridas: 7500
Linhas Inseridas: 10000
Linhas Inseridas: 20000
Linhas Inseridas: 35000
Linhas Inseridas: 60000
Linhas Inseridas: 80000

Procedimento de PL/SQL concluido com exito.

Decorreram: 00:00:07.30
SQL> @load
Linhas Inseridas: 1000
Linhas Inseridas: 5000
Linhas Inseridas: 7500
Linhas Inseridas: 10000
Linhas Inseridas: 20000
Linhas Inseridas: 35000
Linhas Inseridas: 60000
Linhas Inseridas: 80000

Procedimento de PL/SQL concluido com exito.

Decorreram: 00:00:08.02
SQL> @load
Linhas Inseridas: 1000
Linhas Inseridas: 5000
Linhas Inseridas: 7500
Linhas Inseridas: 10000
Linhas Inseridas: 20000
Linhas Inseridas: 35000
Linhas Inseridas: 60000
Linhas Inseridas: 80000

Procedimento de PL/SQL concluido com exito.

Decorreram: 00:00:10.15
SQL> @load
Linhas Inseridas: 1000
Linhas Inseridas: 5000
Linhas Inseridas: 7500
Linhas Inseridas: 10000
Linhas Inseridas: 20000
Linhas Inseridas: 35000
Linhas Inseridas: 60000
Linhas Inseridas: 80000

Procedimento de PL/SQL concluido com exito.

Decorreram: 00:00:10.45
SQL> select count(*) from hr.chamadas;

COUNT(*)
----------
500000

Repare-se no tempo médio que demorou a inserir cada tranche: 10 segundos. Segundo a lógica do “tudo o que entra sai” seria de esperar um tempo médio de 50 segundos para remover todos estes registos. Vamos ver se é isso que acontece…

Atenção que o DELETE sobre uma tabela particionada iria viciar toda a situação por isso vamos criar uma tabela não-particionada com base na tabela particionada e depois remover os registos:

SQL> r
 1  create table hr.chamadas_np
 2  tablespace semana_sobras
 3  as
 4* select * from hr.chamadas

Tabela criada.

Para tornar isto mais realista vamos criar dois índices sobre a tabela não particionada:

SQL> r
 1  create index CHNP_IDX01 on hr.chamadas_np(codigo)
 2* tablespace users

Indice criado.SQL> r
 1  create index CHNP_IDX02 on hr.chamadas_np(data_inicio,duracao_segundos)
 2* tablespace users

Indice criado.

E agora sim a limpeza, mas só de 499995 regitos, deixando lá 5:

SQL>  delete from hr.chamadas_np
 2  where codigo <> 100000;

499995 linhas apagadas.

Decorreram: 00:01:47.30

Foi mais do dobro do tempo de entrada! Quer dizer que por cada linha que entra no sistema leva o dobro do tempo a sair. Mas as más noticias não se ficam por aqui. Caso eu tenha que parar a BD ou precise de matar um destes processos de remoção linha-a-linha, ou simplesmente me tenha enganado e apagado linhas a mais, o processo de ROLLBACK também não é meigo como podemos ver pelo tempo que demora a anular o DELETE anterior:

SQL> rollback;

Rollback concluido.

Decorreram: 00:00:37.38

Agora vamos  fazer isto com partições! Vejam o tempo que demora a remover a partição onde se encontram meio milhão de registos:

SQL> -- Contagem de Registos Antes
SQL> select count(*) from hr.chamadas;

 COUNT(*)
----------
 500000

Decorreram: 00:00:00.46

SQL> alter table hr.chamadas drop partition chp1;

Tabela alterada.

Decorreram: 00:00:01.93

SQL> select count(*) from hr.chamadas;

 COUNT(*)
----------
 0

Decorreram: 00:00:00.05

Demorou menos de 2 segundos!!

Outra alternativa à remoção da partição, e que é igualmente rápida, seria substituir o comando de DROP por TRUNCATE.
O efeito é o mesmo, a rapidez é a mesma, mas pelo menos deixa a partição vazia na tabela para a eventualidade de mais tarde ser necessário carregar dados antigos na tabela.

Manutenção de Índices

Em termos de índices quer o TRUNCATE, quer o DROP ou até mesmo o SPLIT irão deixar no estado “UNUSABLE” todos os índices que existam sobre a tabela mas apenas se as partições afectadas por estes comandos tiverem dados.
Para que as aplicações não “tropecem” nestas operações de manutenção existe um parâmetro que antigamente só podia ser usado ao nível da sessão mas que no Oracle 10g passou a ter o âmbito do sistema e que já vem com o valor por defeito a TRUE: SKIP_UNUSABLE_INDEXES. Assim é possivel que a tabela continue a ser alvo de DML, consultas, etc., mas com os índices inutilizados. Nada que um REBUILD não resolva. Mas atenção que no caso dos índices particionados, o comando de REBUILD deve ser feito ao nível da partições senão será levantado o seguinte erro:

SQL> alter index hr.chix3 rebuild;
alter index hr.chix3 rebuild
 *
ERRO na linha 1:
ORA-14086: um indice particionado não pode ser recriado como um todo

Para reconstruir os índices particionadas basta que se inclua a claúsula PARTITION e o nome da partição (ver em DBA_IND_PARTITIONS):

SQL> alter index hr.chix3 rebuild partition chp2;

Indice alterado.

A outra operação que tem este efeito de inutilização sobre os índices é o MOVE PARTITION e que é um comando parecido com o seu congénere ao nível da tabela e que permite mover a partições de tablespace.

Particionamento: a história das versões

Para terminar este artigo falta só referir de que esta é uma das tecnologias que mais tem evoluido ao longo das versões Oracle senão vejamos as novidades ao longo dos tempos:

Oracle8 (1997): Criação das Partições (apenas disponivel o tipo RANGE)

Oracle8i (1998): Particionamento do tipo HASH; subparticionamento RANGE-HASH; Poder efectuar MERGE de partições

Oracle9i (2001): Particionamento do tipo LIST;
Oracle9i release 2: Subparticionamento Range-List; o algoritmo de SPLIT de partições foi melhorado

Oracle10g (2003): Índices hash globais; manutenção de índices locais
Oracle10g relase 2: Aumentou o máximo de partições por tabela para um milhão; particionar IOTs pelo método LIST

Oracle11g (2007): Novos métodos de particionamentos: SYSTEM, INTERVAL, REF e por coluna virtual; Novo Partition Advisor; novos métodos de subparticionamento

Ao contrário de outras funcionalidades existente no SGBD, tem existido aqui uma preocupação constante da Oracle em fazer evoluir esta tecnologia, da qual se esperam mais novidades.