One should be able to specify what partition history will go in case when it is possible (INTERVAL partitioning does not allow that).

Example:

create or replace table t1 (x int) with system versioning

partition by system_time partitions 3;

insert t1 values (0);

# rows are deleted from pn and history goes to p0

delete from t1 partition (pn, p0);

insert t1 values (1);

# rows are deleted from pn and history goes to p1

delete from t1 partition (pn, p1);

It should be possible to switch current history partition for subsequent DML commands:

create or replace table t1 (x int) with system versioning

partition by system_time history p0 partitions 3;

insert t1 values (0);

# history goes to p0

delete from t1;

insert t1 values (1);

alter table t1 partition by system_time history p1;

# history goes to p1

delete from t1;

The above should be applied to LIMIT option as well. Currently LIMIT works by the following algorithm:

1. find empty history partition P where P-1 partition is non-empty;
2. if records in P-1 < LIMIT select P-1 otherwise select P.

In other words it can select only the last non-empty partition or the next empty partition. That should be changed and LIMIT should work with any history partition no matter of whether next partitions are filled or not.

Repartitioning should reorder history in chronological order for unconditional, LIMIT or INTERVAL partitioning.

The old LIMIT behaviour retains history partitions in chronological order so it should be possible keep it like this by the FIXED LIMIT option:

create or replace table t1 (x int) with system versioning

partition by system_time fixed limit 1000 partitions 3;

The above does not allow to fill the history partitions in random order.