Автодобавление партиций в секционированную по списку таблицу для новых ключей

#sql #oracle #oracle12c

Имеется секционированная по списку таблица с ключём из поля code: 

create table pltab (
    id number, code varchar2 (8), name varchar2 (32)
)
partition by list (code) (
    partition part_aa values ('AA', 'aa'),
    partition part_bb values ('BB', 'bb'),
    partition part_def values (default) 
)
;


Вставка уже известных ключей не вызывает проблем. Но всё чаше данные поступают с
новыми ключами, например:

insert into pltab values (3, 'CC', 'item of CC');


и оказываются в партиции по-умолчанию. После этого, приходится вручную отделять записи
с новыми ключами в новые партиции:

alter table pltab split partition part_def values ('CC') into (
    partition part_cc, partition part_def
);


Как реализовать автоматическое добавление новой партиции при вставке с новым  ключом?  
    

Ответы

Ответ 1

Для этого случая есть автоматическое секционирование таблицы по листу
(см. automatic list-partitioned table):

create table ptab (
    id number, code varchar2 (8), name varchar2 (32)
)
partition by list (code) automatic (
    partition partdef values ('undef', 'unknown', 'none', null)
);


С предикатом AUTOMATIC нельзя создать партицию по-умолчанию (с ключом default). Вместо
неё, добавте партицию со списком ключей, для которых заведомо не должна быть создана
новая партиция.

При вставке:

insert into pltab 
   select rownum, column_value, 'item of '||column_value 
   from xmlTable ('"AA", "BB", "CC", "none", "unknown"')
;


будут автоматически созданы новые партиции с генерироавнными именами SYS_Pnnnn: 

select partition_name, high_value, num_rows
from user_tab_partitions
where table_name = upper ('pltab') 
;

PARTITION_NAME   HIGH_VALUE           NUM_ROWS
---------------- ------------------ ----------
PARTDEF          'undef', 'unknown'          2
                 , 'none', null               

SYS_P2088        'AA'                        1
SYS_P2089        'BB'                        1
SYS_P2090        'CC'                        1


PS для уже существующих секционированных таблиц без партиции с ключом default, возможно
их изменение без пересоздания, см. alter_automatic_partitioning.