PolarDB:ALTER TABLE...ADD PARTITION

Funcionamento

O comando ALTER TABLE ... ADD PARTITION é uma operação de metadados, mas sua duração e impacto na concorrência dependem quase inteiramente da configuração de índices da tabela.

Ao executar o comando, o banco de dados adquire um bloqueio exclusivo na tabela de destino, impedindo todas as operações simultâneas de SELECT, INSERT, UPDATE e DELETE. O bloqueio permanece ativo até a conclusão do comando.

Comportamento dos índices após ADD PARTITION

Duração da operação

• Tabelas sem índices: execução quase instantânea; apenas os metadados da tabela são atualizados.

• Tabelas com índices: duração proporcional ao tempo necessário para criar índices locais na nova partição vazia.

Pré-requisitos

Antes de começar, verifique se você tem:

• Uma tabela particionada existente (LIST ou RANGE)

• Privilégios de proprietário da tabela ou uma conta privilegiada

Limitações

Regras básicas de definição

1. Consistência do tipo de partição: A nova partição deve corresponder ao tipo de partição existente na tabela (LIST ou RANGE).

2. Consistência da chave de partição: A regra de particionamento deve referenciar as mesmas colunas de chave de partição definidas para a tabela.

3. Nome único da partição: O nome deve ser exclusivo em todas as partições e subpartições da tabela.

Restrições de valores de partição

• Partições MAXVALUE e DEFAULT: Não é possível adicionar uma partição a uma tabela particionada por RANGE que já tenha uma partição MAXVALUE, nem a uma tabela particionada por LIST que já tenha uma partição DEFAULT. Essas partições abrangentes cobrem logicamente todos os valores não especificados, sem deixar espaço para uma nova. Use ALTER TABLE ... SPLIT PARTITION para dividir primeiro a partição MAXVALUE ou DEFAULT e, em seguida, adicione a nova partição.

  A divisão de uma partição MAXVALUE ou DEFAULT pode mover dados, causando sobrecarga significativa de I/O e bloqueios. Execute esta operação fora do horário de pico.

  -- Example: split the MAXVALUE partition to make room for 2024 data
  ALTER TABLE sales SPLIT PARTITION max_partition AT (TO_DATE('2025-01-01', 'YYYY-MM-DD')) INTO (PARTITION p_2024, PARTITION max_partition);

• Ordem das partições RANGE: O valor VALUES LESS THAN da nova partição deve ser maior que o limite superior da partição mais alta atual. Não é possível inserir uma partição entre ou antes das partições existentes.

• Unicidade de valores na partição LIST: Os valores na nova partição não podem se sobrepor aos valores de qualquer partição existente. Valores sobrepostos geram o erro ERROR: partition "xx" would overlap partition "xxx".

Requisitos de privilégio

Você deve ser o proprietário da tabela ou usar uma conta privilegiada.

Sintaxe

ALTER TABLE table_name ADD PARTITION partition_spec;

partition_spec — para uma partição LIST:

PARTITION partition_name VALUES (value_list)
  [TABLESPACE tablespace_name]
  [(subpartition_spec, ...)]

partition_spec — para uma partição RANGE:

PARTITION partition_name VALUES LESS THAN (value_list)
  [TABLESPACE tablespace_name]
  [(subpartition_spec, ...)]

subpartition_spec — para uma subpartição LIST:

SUBPARTITION subpartition_name VALUES (value_list)
  [TABLESPACE tablespace_name]

subpartition_spec — para uma subpartição RANGE:

SUBPARTITION subpartition_name VALUES LESS THAN (value_list)
  [TABLESPACE tablespace_name]

Parâmetros

Adicionar uma partição a uma tabela particionada por LIST

Este exemplo adiciona uma nova região geográfica a uma tabela particionada por país.

1. Crie a tabela de exemplo sales_list, particionada por country.

   CREATE TABLE sales_list (
       dept_no     NUMBER,
       part_no     VARCHAR2(50),
       country     VARCHAR2(20),
       sale_date   DATE,
       amount      NUMBER
   )
   PARTITION BY LIST(country) (
       PARTITION europe   VALUES ('FRANCE', 'ITALY'),
       PARTITION asia     VALUES ('INDIA', 'PAKISTAN'),
       PARTITION americas VALUES ('US', 'CANADA')
   );

2. Adicione uma nova partição east_asia para 'CHINA' e 'KOREA'.

   ALTER TABLE sales_list ADD PARTITION east_asia VALUES ('CHINA', 'KOREA');

3. (Opcional) Verifique se a nova partição foi criada.

   SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS WHERE table_name = 'sales_list';

   O resultado inclui a partição east_asia.

Adicionar uma partição a uma tabela particionada por RANGE

Este exemplo estende uma tabela particionada por data com uma nova partição trimestral.

1. Crie a tabela de exemplo sales_range, particionada por sale_date.

   A função TO_DATE garante a clareza do formato de data. Na prática, o formato da string deve corresponder à configuração NLS_DATE_FORMAT do banco de dados.

   CREATE TABLE sales_range (
       dept_no     NUMBER,
       part_no     VARCHAR2(50),
       country     VARCHAR2(20),
       sale_date   DATE,
       amount      NUMBER
   )
   PARTITION BY RANGE(sale_date) (
       PARTITION q1_2023 VALUES LESS THAN (TO_DATE('2023-04-01', 'YYYY-MM-DD')),
       PARTITION q2_2023 VALUES LESS THAN (TO_DATE('2023-07-01', 'YYYY-MM-DD')),
       PARTITION q3_2023 VALUES LESS THAN (TO_DATE('2023-10-01', 'YYYY-MM-DD')),
       PARTITION q4_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD'))
   );

2. Adicione a partição q1_2024. Seu limite superior deve ser maior que 2024-01-01, o limite mais alto atual.

   ALTER TABLE sales_range ADD PARTITION q1_2024 VALUES LESS THAN (TO_DATE('2024-04-01', 'YYYY-MM-DD'));

3. (Opcional) Confirme se a nova partição aparece no final da lista de partições.

   SELECT partition_name, high_value FROM ALL_TAB_PARTITIONS WHERE table_name = 'sales_range';

Adicionar uma partição a uma tabela com particionamento composto

Para uma tabela com particionamento composto RANGE-LIST, defina subpartições simultaneamente à adição de uma partição de nível superior.

1. Crie a tabela de exemplo composite_sales.

   CREATE TABLE composite_sales (
       sale_id     NUMBER,
       sale_date   DATE,
       region      VARCHAR2(20)
   )
   PARTITION BY RANGE(sale_date)
   SUBPARTITION BY LIST(region) (
       PARTITION p_2023 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')) (
           SUBPARTITION p_2023_north VALUES ('NORTH'),
           SUBPARTITION p_2023_south VALUES ('SOUTH')
       )
   );

2. Adicione a partição p_2024 com duas subpartições.

   ALTER TABLE composite_sales ADD PARTITION p_2024
       VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD')) (
           SUBPARTITION p_2024_north VALUES ('NORTH'),
           SUBPARTITION p_2024_south VALUES ('SOUTH')
       );

3. (Opcional) Verifique se as subpartições foram criadas.

   SELECT partition_name, subpartition_name, high_value
   FROM ALL_TAB_SUBPARTITIONS
   WHERE table_name = 'composite_sales' AND partition_name = 'p_2024';

Melhores práticas

Agende durante uma janela de manutenção

O comando ADD PARTITION mantém um bloqueio exclusivo na tabela durante toda a operação, impedindo qualquer DML. Em tabelas grandes com vários índices locais, o bloqueio pode durar vários minutos. Agende esta operação fora do horário de pico para evitar impactos nos serviços online.

Monitore as esperas de bloqueio

Durante a operação, monitore o banco de dados quanto a esperas de bloqueio. Se o tempo de espera for excessivo, cancele a operação e reagende-a.

Estime a duração antes de executar

Para tabelas com índices locais, estime a duração da operação medindo o tempo necessário para criar os mesmos índices em uma tabela vazia com o mesmo esquema.

Limite a contagem total de partições

Embora não haja um limite rígido para o número de partições por tabela, mantenha o total abaixo de 1.000. Um número excessivo de partições aumenta o custo de análise do otimizador de consultas e pode degradar o desempenho das consultas.

Perguntas frequentes

ALTER TABLE sales_list ADD PARTITION new_region VALUES ('FRANCE', 'SPAIN');

ALTER TABLE sales_list ADD PARTITION new_region VALUES ('SPAIN');

ALTER TABLE sales_range ADD PARTITION q1_2024 VALUES LESS THAN ('2024-04-01');