Gerenciamento de Particionamento de Tabelas no Oracle Database

A funcionalidade de particionamento de tabelas no Oracle Database oferece benefícios significativos para diversas aplicações, melhorando a capacidade de gerenciamento, o desempenho e a disponibilidade dos dados. Frequentemente, o particionamento acelera o desempenho de certas consultas e operações de manutenção. Além disso, simplifica tarefas administrativas rotineiras, tornando-se uma ferramenta crucial para a construção de sistemas com terabytes de dados ou de alta disponibilidade.

O particionamento permite que tabelas, índices ou tabelas organizadas por índice sejam subdivididas em segmentos menores, conhecidos como partições. Cada partição possui um nome distinto e pode ter características de armazenamento próprias. Do ponto de vista do administrador de banco de dados, um objeto particionado consiste em múltiplos segmentos que podem ser gerenciados coletivamente ou individualmente, oferecendo grande flexibilidade. No entanto, para as aplicações, uma tabela particionada se comporta de forma idêntica a uma tabela não particionada, sem a necessidade de modificações nas instruções SQL DML.

Critérios para Utilizar Tabelas Particionadas

Tabelas com tamanho superior a 2GB.
Tabelas que armazenam dados históricos, onde novos registros são adicionados a novas partições.

Vantagens do Particionamento de Tabelas

Melhora no Desempenho de Consultas: Consultas em objetos particionados podem se concentrar apenas nas partições relevantes, aumentando a velocidade de recuperação de dados.
Aumento da Disponibilidade: Se uma partição específica falhar, os dados nas outras partições da tabela continuam acessíveis.
Facilidade de Manutenção: Em caso de falha em uma partição que requer reparo de dados, apenas essa partição precisa ser corrigida.
Balanceamento de I/O: Diferentes partições podem ser mapeadas para discos distintos para equilibrar o I/O, otimizando o desempenho geral do sistema.

Tipos de Particionamento de Tabela

1. Particionamento por Intervalo (RANGE)

O particionamento por intervalo distribui os dados em partições com base em um conjunto de valores ordenados. Uma chave de partição é definida, e cada partição tem um limite superior exclusivo. Este é um dos métodos mais comuns, frequentemente utilizado com chaves de partição baseadas em datas.

Ao usar o particionamento por intervalo, considere as seguintes regras:

Cada partição deve ter uma cláusula VALUES LESS THAN, que especifica um limite superior exclusivo para os valores da chave de partição. Registros com valores iguais ou superiores a este limite serão armazenados na próxima partição superior.
Todas as partições, exceto a primeira, possuem um limite inferior implícito, que é o limite superior da partição anterior.
A partição mais alta geralmente usa MAXVALUE, que representa um valor indefinido, maior do que qualquer outro valor de chave de partição, incluindo valores nulos.

Exemplo 1: Particionamento por Chave Numérica

Considere uma tabela de clientes com um grande volume de registros. Podemos particioná-la pelo ID do cliente, armazenando cada partição em um tablespace distinto para distribuir os arquivos de dados em vários discos físicos.

CREATE TABLE CLIENTES_DB
(
   COD_CLIENTE     NUMBER        NOT NULL PRIMARY KEY,
   PRIMEIRO_NOME   VARCHAR2(50)  NOT NULL,
   SOBRENOME       VARCHAR2(50)  NOT NULL,
   CONTATO_TEL     VARCHAR2(20),
   EMAIL_END       VARCHAR2(100)
)
PARTITION BY RANGE (COD_CLIENTE)
(
   PARTITION P_CLIENTE_1 VALUES LESS THAN (50000)   TABLESPACE TBS_CLIENTES_01,
   PARTITION P_CLIENTE_2 VALUES LESS THAN (100000)  TABLESPACE TBS_CLIENTES_02,
   PARTITION P_CLIENTE_3 VALUES LESS THAN (MAXVALUE) TABLESPACE TBS_CLIENTES_03
);

Exemplo 2: Particionamento por Data

Para uma tabela de registro de atividades de pedidos, podemos particionar por data do pedido.

CREATE TABLE HISTORICO_PEDIDOS
(
   ID_PEDIDO       NUMBER(9)     NOT NULL,
   DATA_PEDIDO     DATE,
   VALOR_TOTAL     NUMBER(12,2),
   ID_CLIENTE      NUMBER(9),
   STATUS_PAGTO    CHAR(1)
)
PARTITION BY RANGE (DATA_PEDIDO)
(
 PARTITION PED_JAN_2023 VALUES LESS THAN (TO_DATE('01-JAN-2023','DD-MON-YYYY')) TABLESPACE TBS_PEDIDOS_2022,
 PARTITION PED_JUL_2023 VALUES LESS THAN (TO_DATE('01-JUL-2023','DD-MON-YYYY')) TABLESPACE TBS_PEDIDOS_H1_2023,
 PARTITION PED_MAX_VAL  VALUES LESS THAN (MAXVALUE) TABLESPACE TBS_PEDIDOS_REST
);

2. Particionamento por Lista (LIST)

O particionamento por lista é adequado quando os valores da coluna de particionamento são discretos e limitados a um conjunto específico. Cada partição é definida por uma lista explícita de valores.

CREATE TABLE VENDAS_REGIONAIS
(
 ID_VENDA    INT PRIMARY KEY,
 PRODUTO     VARCHAR(50),
 REGIÃO      VARCHAR(20)
)
PARTITION BY LIST (REGIÃO)
(
 PARTITION P_NORTE  VALUES ('SAO_PAULO', 'RIO_JANEIRO') TABLESPACE TBS_VENDAS_SP_RJ,
 PARTITION P_SUL    VALUES ('PORTO_ALEGRE', 'CURITIBA') TABLESPACE TBS_VENDAS_POA_CTB,
 PARTITION P_OUTROS VALUES (DEFAULT) TABLESPACE TBS_VENDAS_OUTROS
);

3. Particionamento por HASH

O particionamento por hash utiliza um algoritmo de hashing na coluna especificada para determinar em qual partição uma linha deve ser inserida. É uma boa opção quando não há condições claras para particionamento por intervalo ou lista, pois distribui os dados uniformemente. Apenas o número de partições precisa ser especificado, e o Oracle se encarega da distribuição. Recomenda-se usar um número de partições que seja uma potência de 2 para otimizar a distribuição.

CREATE TABLE TABELA_HIBRIDA
(
 CHAVE_ID     NUMBER(10),
 DESCRICAO    VARCHAR2(200)
)
PARTITION BY HASH (CHAVE_ID)
(
 PARTITION HASH_P1 TABLESPACE TBS_HASH_01,
 PARTITION HASH_P2 TABLESPACE TBS_HASH_02,
 PARTITION HASH_P3 TABLESPACE TBS_HASH_03,
 PARTITION HASH_P4 TABLESPACE TBS_HASH_04
);

-- Ou, especificando diretamente o número de partições
CREATE TABLE USUARIOS
(
   ID_USUARIO    NUMBER(8),
   NOME_COMPLETO VARCHAR2(100),
   DATA_CADASTRO DATE
)
PARTITION BY  HASH (ID_USUARIO) PARTITIONS 8
STORE IN (TBS_USER_01, TBS_USER_02, TBS_USER_03, TBS_USER_04, TBS_USER_05, TBS_USER_06, TBS_USER_07, TBS_USER_08);

4. Particionamento Composto: Intervalo-Lista (RANGE-LIST)

Neste tipo de particionamento, a tabela é primeiramente particionada por intervalo, e então cada partição é subdividida em subpartições por lista. As subpartições são as "partições dentro das partições".

CREATE TABLE REGISTROS_PRODUTOS
(
   ID_PRODUTO   VARCHAR2(10),
   DATA_REGISTRO DATE,
   CUSTO_PROD   NUMBER(12,2),
   ESTADO       VARCHAR2(30)
)
PARTITION BY RANGE(DATA_REGISTRO)
SUBPARTITION BY LIST (ESTADO)
(
  PARTITION ANO_2022 VALUES LESS THAN(TO_DATE('2023-01-01','YYYY-MM-DD')) TABLESPACE TBS_PRODUTOS_2022
         (
             SUBPARTITION P2022_ATIVO VALUES ('ATIVO') TABLESPACE TBS_PRODUTOS_2022,
             SUBPARTITION P2022_INATIVO VALUES ('INATIVO') TABLESPACE TBS_PRODUTOS_2022,
             SUBPARTITION P2022_PENDENTE VALUES ('PENDENTE') TABLESPACE TBS_PRODUTOS_2022
         ),
  PARTITION ANO_2023 VALUES LESS THAN (TO_DATE('2024-01-01','YYYY-MM-DD')) TABLESPACE TBS_PRODUTOS_2023
         (
             SUBPARTITION P2023_ATIVO VALUES ('ATIVO') TABLESPACE TBS_PRODUTOS_2023,
             SUBPARTITION P2023_INATIVO VALUES ('INATIVO') TABLESPACE TBS_PRODUTOS_2023,
             SUBPARTITION P2023_PENDENTE VALUES ('PENDENTE') TABLESPACE TBS_PRODUTOS_2023
         )
);

5. Particionamento Composto: Intervalo-Hash (RANGE-HASH)

Similar ao anterior, a tabela é particionada por intervalo e, em seguida, cada partição é subdividida em subpartições por hash.

CREATE TABLE MOVIMENTACOES_FINANCEIRAS
(
   ID_TRANSACAO    NUMBER PRIMARY KEY,
   ID_ITEM         NUMBER(10) NOT NULL,
   DETALHES_ITEM   VARCHAR2(500),
   DATA_MOV        DATE
)
PARTITION BY RANGE(DATA_MOV)
SUBPARTITION BY HASH(ID_TRANSACAO)
SUBPARTITIONS 5 STORE IN (TBS_MOV_01, TBS_MOV_02, TBS_MOV_03, TBS_MOV_04, TBS_MOV_05)
(
    PARTITION MOV_2021 VALUES LESS THAN(TO_DATE('2022-01-01','YYYY-MM-DD')),
    PARTITION MOV_2022 VALUES LESS THAN(TO_DATE('2023-01-01','YYYY-MM-DD')),
    PARTITION MOV_FUTURO VALUES LESS THAN(MAXVALUE)
);

Operações de Manutenção de Partições

1. Adicionar Partição (ADD PARTITION)

Adiciona uma nova partição a uma tabela particionada. O limite da nova partição deve ser maior que o limite da última partição existente.

ALTER TABLE HISTORICO_PEDIDOS ADD PARTITION PED_JAN_2024 VALUES LESS THAN(TO_DATE('01-JAN-2024','DD-MON-YYYY')) TABLESPACE TBS_PEDIDOS_2024;

Para adicionar uma subpartição (em um esquema composto):

ALTER TABLE REGISTROS_PRODUTOS MODIFY PARTITION ANO_2023 ADD SUBPARTITION P2023_FINALIZADO VALUES('FINALIZADO') TABLESPACE TBS_PRODUTOS_2023_FINAL;

2. Remover Partição (DROP PARTITION)

Remove uma partição específica de uma tabela. Se a partição a ser removida for a única da tabela, a operação não é permitida; a tabela inteira deve ser removida.

ALTER TABLE HISTORICO_PEDIDOS DROP PARTITION PED_JAN_2023;

Para remover uma subpartição:

ALTER TABLE REGISTROS_PRODUTOS DROP SUBPARTITION P2023_FINALIZADO;

3. Mesclar Partições (MERGE PARTITIONS)

Combina duas partições adjacentes em uma única partição. A nova partição resultante adota o limite superior da partição mais alta das duas originais.

ALTER TABLE HISTORICO_PEDIDOS MERGE PARTITIONS PED_JAN_2023, PED_JUL_2023 INTO PARTITION PED_H1_2023_UNIFICADO;

4. Dividir Partição (SPLIT PARTITION)

Divide uma partição existente em duas novas partições. A partição original deixa de existir. Esta operação não é aplicável a partições do tipo HASH.

ALTER TABLE HISTORICO_PEDIDOS SPLIT PARTITION PED_H1_2023_UNIFICADO AT(TO_DATE('01-APR-2023','DD-MON-YYYY')) INTO (PARTITION PED_Q1_2023, PARTITION PED_Q2_2023);

5. Renomear Partição (RENAME PARTITION)

Muda o nome de uma partição existente.

ALTER TABLE HISTORICO_PEDIDOS RENAME PARTITION PED_Q1_2023 TO PEDIDOS_PRIMEIRO_TRIMESTRE_2023;

Operações Relacionadas

Consultar Dados Através de Partições Específicas:

SELECT SUM(VALOR_TOTAL)
FROM
(
   SELECT VALOR_TOTAL FROM HISTORICO_PEDIDOS PARTITION (PED_JAN_2023)
   UNION ALL
   SELECT VALOR_TOTAL FROM HISTORICO_PEDIDOS PARTITION (PED_JUL_2023)
);

Remover Tabela Particionada:

DROP TABLE NomeDaTabela PURGE;

A cláusula PURGE impede que a tabela seja movida para a lixeira do Oracle. Sem ela, a tabela pode ser recuperada da lixeira.

Visualizar Informações de Particionamento:

Detalhes de todas as partições de tabelas do usuário atual:

SELECT * FROM USER_TAB_PARTITIONS;

Informações gerais sobre tabelas particionadas do usuário atual:

SELECT * FROM USER_PART_TABLES;

Conversão de Tabela Não Particionada para Particionada

A conversão de uma tabela não particionada para uma particionada pode ser realizada de várias maneiras, sendo o método de troca de partição (Partition Exchange) uma abordagem comum e eficiente para minimizar o tempo de inatividade.

1. Etapas da Troca de Partição

Crie a nova tabela particionada com a estrutura desejada (ex: com partições P1, P2).
Para cada partição da tabela de destino, crie uma tabela auxiliar não particionada que corresponda à estrutuar da partição e contenha os dados que seriam movidos para essa partição.
Realize a operação de troca de partição entre cada tabela auxiliar e sua respectiva partição na tabela particionada de destino.

2. Exemplo de Fluxo SQL

Tabela Não Particionada Original:

CREATE TABLE TAB_SIMPLES_ORIGEM
(
   ID      NUMBER,
   DATA_MOV DATE
);
-- Inserir dados de exemplo:
INSERT INTO TAB_SIMPLES_ORIGEM VALUES (1, TO_DATE('2022-05-15', 'YYYY-MM-DD'));
INSERT INTO TAB_SIMPLES_ORIGEM VALUES (2, TO_DATE('2023-01-20', 'YYYY-MM-DD'));
INSERT INTO TAB_SIMPLES_ORIGEM VALUES (3, TO_DATE('2023-07-01', 'YYYY-MM-DD'));
COMMIT;

Criação da Tabela Particionada (com a mesma estrutura e índices da original):

CREATE TABLE TAB_PARTICIONADA_DESTINO
(
   ID      NUMBER,
   DATA_MOV DATE
)
PARTITION BY RANGE(DATA_MOV)
(
   PARTITION PART_ANO_2022 VALUES LESS THAN (TO_DATE('2023-01-01', 'YYYY-MM-DD')),
   PARTITION PART_ANO_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
);

Criação de Tabelas Auxiliares (se necessário, para cada partição, com dados filtrados):

Neste cenário simplificado, vamos usar a TAB_SIMPLES_ORIGEM como auxiliar para uma das partições e criar uma nova para a outra.

CREATE TABLE TAB_SIMPLES_AUXILIAR_2023
(
   ID      NUMBER,
   DATA_MOV DATE
);
INSERT INTO TAB_SIMPLES_AUXILIAR_2023 SELECT ID, DATA_MOV FROM TAB_SIMPLES_ORIGEM WHERE DATA_MOV >= TO_DATE('2023-01-01', 'YYYY-MM-DD') AND DATA_MOV < TO_DATE('2024-01-01', 'YYYY-MM-DD');
COMMIT;

-- Os dados para 2022 ficam na TAB_SIMPLES_ORIGEM
-- E usaremos a TAB_SIMPLES_AUXILIAR_2023 para 2023

Troca de Dados das Partições:

ALTER TABLE TAB_PARTICIONADA_DESTINO
   EXCHANGE PARTITION PART_ANO_2022
   WITH TABLE TAB_SIMPLES_ORIGEM
   INCLUDING INDEXES
   WITHOUT VALIDATION;

ALTER TABLE TAB_PARTICIONADA_DESTINO
   EXCHANGE PARTITION PART_ANO_2023
   WITH TABLE TAB_SIMPLES_AUXILIAR_2023
   INCLUDING INDEXES
   WITHOUT VALIDATION;

-- Após a troca, a TAB_SIMPLES_ORIGEM e TAB_SIMPLES_AUXILIAR_2023 contêm os dados antigos (se não houver PURGE na troca) ou ficam vazias dependendo da opção.
-- Geralmente, a tabela auxiliar se torna o "contêiner" dos dados da partição, e a tabela de origem fica vazia.
-- Renomear a tabela particionada para o nome original, e a original para um nome de backup ou descartá-la.
-- RENAME TAB_PARTICIONADA_DESTINO TO TAB_ORIGINAL_AGORA_PARTICIONADA;
-- DROP TABLE TAB_SIMPLES_AUXILIAR_2023 PURGE;

Consulta de Índices da Tabela (Útil antes de qualquer operação):

SELECT INDEX_NAME, TABLE_NAME FROM DBA_INDEXES WHERE TABLE_NAME='TAB_PARTICIONADA_DESTINO';

3. Desfaios Comuns na Troca de Partições

Problema: ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Causa: A chave primária da tabela que está sendo trocada ou da tabela particionada de destino é referenciada por chaves estrangeiras de outras tabelas. Para realizar a troca, as restrições de chave primária e estrangeira envolvidas devem ser desabilitadas.

Solução: Desabilite a chave primária da tabela particionada de destino (e suas chaves estrangeiras dependentes) e da tabela auxiliar antes da troca. A cláusula CASCADE desabilita automaticamente as chaves estrangeiras que referenciam a chave primária.

-- Desabilitar chaves antes da troca
ALTER TABLE TAB_PARTICIONADA_DESTINO DISABLE PRIMARY KEY CASCADE;
ALTER TABLE TAB_SIMPLES_ORIGEM DISABLE PRIMARY KEY CASCADE;

-- Realizar a operação de EXCHANGE PARTITION aqui...

-- Reabilitar chaves após a troca
ALTER TABLE TAB_PARTICIONADA_DESTINO ENABLE PRIMARY KEY;
ALTER TABLE TAB_SIMPLES_ORIGEM ENABLE PRIMARY KEY;

Lembre-se de reabilitar todas as restrições após a conclusão da operação de troca de partição.

Tags: Oracle database SQL Partitioning RangePartitioning

Publicado em 10-29 07:09

Doido Dev