PARTICIONANDO TABELA ORACLE

“O particionamento de tabelas é uma opção disponível para DBAs (Database Administrators, ou Administradores de Banco de Dados em português) que pode auxiliá-los no desempenho do banco de dados afetando áreas como o balanceamento de I/O, a redução de contenção (disputa entre os processos), o melhor desempenho para instruções SQL e também pode atuar positivamente na disponibilidade dos dados. A versão 11g do Oracle acrescenta mais alguns bons métodos de particionamento, que podem ajudar bastante a atingir alguns desses objetivos.”

Por quê usar particionamento de tabelas?

Existem várias áreas em um banco de dados nas quais os DBAs tendem a concentrar seus esforços na busca por oportunidades de melhoraria do desempenho. Entre elas, destacam-se a utilização de memória, o desempenho das instruções SQL, espaço e necessidade de armazenamento, gargalos de rede e desempenho de I/O.

Entre as muitas funcionalidades de um banco de dados, o particionamento de tabelas pode ajudar em pelo duas das áreas citadas anteriormente: desempenho de I/O e performance das instruções SQL. Tomando como exemplos as grandes tabelas (e seus índices) fazendo uso de particionamento: osimples fato de serem armazenadas em diferentes tablespaces, um DBA já poderá observar significativos ganhos de performance em diversos pontos.

  1. Redução de contenção (menor disputa) por blocos das tabelas ou índices. Isto ocorre em função de termos os blocos distribuídos em diferentes tablespaces, de acordo com as opções de particionamento utilizadas.
  2. Redução de contenção I/O, pois os arquivos de dados das tabelas particionadas podem ser fisicamente colocadas em diferentes dispositivos/unidades de disco.
  3. Melhor performance de instruções SQL. As consultas que façam referência – na cláusula WHERE – às colunas usadas chaves de partição podem retornar mais rapidamente porque o otimizador de consultas é capaz de usar partes da partição (somente leitura de partição específica(s) que correspondam aos dados que estão sendo solicitados).
  4. Outro possível benefício é a disponibilidade dos dados nas tabelas: podem ser feitas tarefas administrativas nas partições ativas e acessíveis, enquanto uma ou outra partição esteja offline por algum motivo.

Com a versão 11g, a Oracle adicionou mais alguns poderosos recursos para a realização do particionamento de tabelas, quem podem ser implementados para obtenção destes benefícios em grandes bases de dados. Porém, antes de discutir os novos métodos de particionamento, lhe convido a fazermos uma rápida revisão dos métodos anteriormente disponibilizados pela Oracle.

Particionamento por Faixa (Range Partitioning)

Apresentado na versão 8.0 do banco de dados Oracle, foi o primeiro método de particionamento disponibilizado pela empresa. Este método possibilita  a particição de uma tabela com base em faixas de dados de determinada coluna desta tabela. É mais comumente utilizado para dividir, por exemplo, uma tabela contendo os dados de vendas em trimestre, mês, ano (ou quaisquer outros critérios de faixa significativa de data).

Particionamento por Hash (Hash Partitioning)

Introduzido na versão 8.1, permite a divisão de uma tabela em várias partições baseado em uma chave que realmente não se presta a uma divisão ampla fácil – como no caso de uma tabela de clientes de grande porte. Podemos criar várias partições com base na identificação do cliente e deixar o Oracle distribuir as linhas entre os espaços da tabela com base nos resultados da passagem da coluna de partição como um parâmentro de um algoritmo de hash e usar esse resultado para determinar onde armazenar a linha.

Particionamento por Faixa de Hash (Range-Hash Partitioning)

Também foi adicionado na versão 8i e permitiu que a primeira partição dos dados fosse baseada em faixas e em seguida, dentro desses limites, a partição fosse efetuada em igual número de partições de hash.

Particionamento por lista
Introduzido no 9i (R1), preencheu uma lacuna que estava faltando ao possibilitar que uma tabela pudesse ser dividida com base em valores distintos, tais como códigos de estado, códigos de país ou de outra chave que com valores diferentes.

Particionamento por Faixa de Lista (List Partitioning)

Método de particionamento composto introduzido no 9i (R2) que permitiu uma tabela ser dividida por faixas e depois sub-dividida por uma lista de valores distintos.

Novos métodos de particionamento introduzidos na versão 11g do Oracle

Particionamento por Intervalos (Interval Partitioning)

Basicamente, o particionamento por intervalo é um aprimoramento do particionamento por intervalo (range). Um desafio com particões antes da versão 11g era não haver um meio de criar de ter novas partições criadas automaticamente, à partir dos dados que foram adicionados ao banco de dados. Por exemplo, se uma tabela dividida por mês o DBA teria que criar manualmente uma participação para os dados de cada mês, prevendo sempre um bom período para frente.

Com o particionamento por intervalo este problema está resolvido, pois o Oracle irá gerar automaticamente novas partições para acomodar novos dados, conforme estes sejam adiocionados às tabelas. Ao definir uma tabela, a nova opção intervalo (interval) foi adicionada ao comando DDL. Além disso, a opção “values less than maxvalue” (valores inferiores ao valor máximo) foi removida. É importante destacar que particionamento por intervalos somente pode ser utilizado em colunas do tipo Date ou Number.

Abaixo está um exemplo da sintaxe de criação da tabela de pedidos (orders) automaticamente particionada por mês, baseado na coluna order_date.

CREATE TABLE orders_tbl

(order_id number(10),

order_date date,

order_mode varchar2(10),

order_total number(15,2)

customer_id number(10))

PARTITION BY RANGE (order_date)

INTERVAL(NUMTOYMINTERVAL(1,’MONTH’))

STORE IN (data01,data02,data03,data04)

(PARTITION JUN10 values less than TO_DATE(’01-07-10′,’dd-mm-yy’)),

PARTITION JUL10 values less than TO_DATE(’01-08-10′,’dd-mm-yy’)),

PARTITION AUG10 values less than TO_DATE(’01-09-10′,’dd-mm-yy’)));

Todos os dados que forem adicionados nos intervalos especificados serão inseridos normalmente, bem como os registros que forem além de agosto ficarão armazenados em uma nova partição gerada automaticamente pelo Oracle. Para saber mais informações sobre as tabelas e suas respectivas partições, você pode consultar a tabela DBA_TAB_PARTITIONS.

Além disso, se você possui uma tabela atualmente particionada por intervalo (range), você pode facilmente convertê-la para uma tabela particionada por intervalo utilizando o seguinte comando: ALTER TABLE table_name SET INTERVAL (interval value);

Particionamento por sistema (System Partitioning)

Se a colocação de dados vai ser especificamente gerenciado por um aplicativo externo e a base de dados atuará na tabela dividindo em partições menores, o particionamento por sistema pode ser uma boa solução.

Com este método, não há nenhuma chave para a partição. Logo, alguns benefícios como quebra de partições e algumas outras tarefas de manutenção das partições deixam de ser suportadas.

CREATE TABLE mypart_tbl
(id number,
desc varchar2(50))
PARTITION BY SYSTEM
(partition part_1 tablespace data01,
partition part_2 tablespace data02,
partition part_3 tablespace data03,
partition part_4 tablespace data04);

Inserir e meclar dados nesta tabela irá requerer a utilização da sintaxe de partição estendida (partition-extended). No entanto Deletes e Updates não exigirão nenhuma sintaxe específica.

INSERT INTO mypart_tab PARTITION part_1 VALUES (1,’Some Text’);

Particionamento por Coluna Virtual (Virtual Column Partitioning)

Colunas virtuais calculadas ou derivadas de outros dados agora podem utilizadas como base para o particionamento de uma tabela. Isso permite que uma tabela possa ser dividida com base em informações de negócios não necessariamente armazenadas em colunas individuais.

Por exemplo, supondo que uma tabela de peças (parts) tenha uma coluna chamada part_id onde o terceiro, quarto e quinto caracteres representem o código do fabricação e o gestor muitas vezes solicita relatórios e informações de fabricantes específicos. Este método pode ser útil nesta situação, onde a partição se baseie no subconjunto de caracteres.

CREATE TABLE parts_tbl

(part_id char(10),

desc varchar2(500),

cost number(10,2)

mfr_code as upper((substr(part_id,3,3)))

PARTITION BY LIST(mfr_code)

(PARTITION ABC_DEF VALUES (‘ABC’,’DEF’),

PARTITION GHI_JKL VALUES (‘GHI’,’JKL’),

PARTITION MNO_PQR VALUES (‘MNO’,’PQR’),

PARTITION STU_VWX VALUES (‘STU’,’VWR’));

Particionamento por Referência (Reference Partitioning)

O últimos dos novos métodos de particionamento introduzidos na versão 11g é o particionamento por referência. Esta é uma opção útil para lidar com duas tabelas em um relacionamento um-para-muitos, tais como pedidos e itens de pedidos.

Se a tabela de pedidos for particionada por faixa (range) ou intervalo baseado na data do pedido, pode fazer algum sentido particionarmos também a tabela itens de pedidos da mesma maneira. No passado, isto exigiria a desnormalização da tabela de itens para adicionada a data do pedido e só então adotar a mesma forma de particionamento. Um dos principais benefícios deste método é não utilizar espaço extra (nova coluna ocupando espaço no disco) e não ter que lidar com as questões de  integridade entre os dados.

Para utilizar o particionamento por referência é necessária a existência/criação de uma chave estrangeira na tabela-filha apontando para a tabela-pai, não sendo possível desativar esta resrição.

CREATE TABLE orders_tbl

(order_id number(10),

order_date date,

order_mode varchar2(10),

order_total number(15,2),

customer_id number(10))

PARTITION BY RANGE (order_date)

INTERVAL(NUMTOYMINTERVAL(1,’MONTH’))

STORE IN (data01,data02,data03,data04)

(PARTITION JUN10 values less than TO_DATE(’01-07-10′,’dd-mm-yy’)),

PARTITION JUL10 values less than TO_DATE(’01-08-10′,’dd-mm-yy’)),

PARTITION AUG10 values less than TO_DATE(’01-09-10′,’dd-mm-yy’)));
CREATE TABLE order_items_tbl

(order_id NUMBER(10),

line_id NUMBER(3),

product_id NUMBER(10),

price NUMBER(10,2),

quantity NUMBER(5),

CONSTRAINT order_items_fk FOREIGN KEY (order_id)

REFERENCES orders_tbl)

PARTITION BY REFERENCE (order_items_fk)

Outras opções de particionamento composto (combinar os métodos de particionamento) também foram adicionados no Oracle 11g. Agora podemos também utilizar as seguintes combinações:

Range-Range
List-List
List-Hash
List-Range
Interval-Range
Interval-Hash
Interval-List

Exemplos de sintaxe para muitas destas combinações podem ser facilmente encontradas  no site da própria Oracle.

Com a adição destes novos métodos de particionamento, os DBAs ganharam algumas opções interessantes e poderosas para considerar ao gerenciar grandes bases de dados.

Anúncios

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em PARTICIONAMENTO e marcado . Guardar link permanente.

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s