Auto-add text partitions

Question

Purpose: Automatically add new partitions when adding a new text value.

Situation: There are records, in one field of which there is a repeating text value. A partition has been created on this field in the target table for this data. The value of this field can come in a new value, which is not present in the existing partitions. An error occurs due to the lack of a corresponding section. By the list, these partitions cannot be done in the way that the values that can come are not known in advance.

Question: Is it possible for this case to organize the automatic addition of the necessary sections? Without a trigger, only the means of organizing the table.

Something like this (a made-up example):

create table char_part ( id integer, txt varchar2(200) ) partition by range (txt) interval (1) -- Проблема в автодобавлении нового текст. значения ( partition "abc" values less than ('abc'), partition "max" values less than (maxvalue) -- Для null - значений )

If I'm not mistaken, there is a special type of partition for such a case.

Accepted Answer · 2018-06-22T10:33:07

TL; DR: In version 11g this is not possible. The best solution is to switch to 12c, since in this version automatic partitioning sheet has already been entered.

You can solve the problem through interval partitioning appeared in 11g.
Based on the business requirements, you need to think about how to calculate the column for partitioning. For example, ask Tom .

The proposal for the problem as in the question:

 create table char_part ( id integer, txt varchar2(200), txt# generated always as (coalesce (ora_hash (txt, power (2,20)-1), 0)) virtual ) partition by range (txt#) interval (1) ( partition "empty" values less than (0), partition "undef" values less than (1) ); insert into char_part (id, txt) select 1, 'ABC' from dual union all select 2, 'DEF' from dual union all select 3, 'ZZZ' from dual union all select 9, null from dual ; select table_name, partition_name, high_value from user_tab_partitions where table_name=upper('char_part') ; TABLE_NAME PARTITION_NAME HIGH_VALUE ---------- -------------------- ---------- CHAR_PART SYS_P1141 222410 CHAR_PART SYS_P1142 59915 CHAR_PART SYS_P1143 627928 CHAR_PART empty 0 CHAR_PART undef 1

Answer 2 · 2018-06-08T09:43:57

In version 12.2 this has become possible. For an existing table ALTER TABLE char_part SET PARTITIONING AUTOMATIC; . When creating a table:

 ... PARTITION BY LIST (txt) AUTOMATIC (PARTITION part_abc VALUES ('ABC'), PARTITION part_def VALUES ('DEF'));

Note: not less than , but values , i.e. the table is partitioned by list, not by a range of values; otherwise, the meaning is lost with textual data. The example also states that you can create sections at once, despite the automatic partitioning.

As the following code: create table part_tst (id integer, txt varchar2 (200))
In 11g, it causes an error: ORA-00922: the option is missing or invalid And it is associated just with the AUTOMATIC option

Auto-add text partitions

2 answers 2

More articles: