背景

Oracle 中支持很多种分区管理操作。其中 merge partitions 会将多个连续分区合并成一个分区。lightdb 24.1 中支持了该功能。
merge partitions 功能支持 list 和 range 分区，不支持 hash 分区。

用例

range 分区

CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate) (
	PARTITION p0 VALUES LESS THAN (TO_DATE('2020-01-01', 'yyyy-mm-dd')),
	PARTITION p1 VALUES LESS THAN (TO_DATE('2020-02-01', 'yyyy-mm-dd')),
	PARTITION p2 VALUES LESS THAN (TO_DATE('2020-03-01', 'yyyy-mm-dd')),
	PARTITION p3 VALUES LESS THAN (TO_DATE('2020-04-01', 'yyyy-mm-dd')),
	PARTITION p4 VALUES LESS THAN (MAXVALUE)
);
CREATE INDEX idx_measurement_logdate ON measurement (logdate);
insert into measurement values(0, to_date('2019-01-01', 'yyyy-mm-dd'), 0, 0);
insert into measurement values(1, to_date('2020-01-01', 'yyyy-mm-dd'), 1, 1);
insert into measurement values(2, to_date('2020-02-01', 'yyyy-mm-dd'), 2, 2);
insert into measurement values(3, to_date('2020-03-01', 'yyyy-mm-dd'), 3, 3);
insert into measurement values(4, to_date('2020-04-01', 'yyyy-mm-dd'), 4, 4);

在 lightdb 中，可以通过 \dx measurement 查看该表的分区及索引情况。其中，分区表的名字由父表名 + $ + 分区名组成。

--= 1
select count(*) from measurement$p0;
select * from pg_indexes where tablename = 'measurement$p0';

合并后的分区名可以和原来某个分区名同名，

alter table measurement merge partitions p0, p1 into partition p0;
insert into measurement values (6, to_date('2020-01-08', 'yyyy-mm-dd'), 6, 6);
select * from pg_indexes where tablename = 'measurement$p0';

list 分区

CREATE TABLE orders (
    order_id NUMBER,
    customer_id NUMBER,
    total_amount NUMBER(10, 2)
)
PARTITION BY LIST (customer_id)
(
    PARTITION p1 VALUES (1001, 1002, 1003),
    PARTITION p2 VALUES (2001, 2002, 2003),
    PARTITION p3 VALUES (3001, 3002, 3003),
    PARTITION p4 VALUES (4001, 4002, 4003),
    PARTITION p5 VALUES (DEFAULT)
);
\d+ orders
insert into orders values (1, 999, 1);
insert into orders values (2, 1001, 2);
insert into orders values (3, 2002, 3);
insert into orders values (4, 3003, 4);
insert into orders values (5, 4002, 5);

list 分区可以有 default 分区，

alter table orders merge partitions p1, p2, p3 into partition p1;
--= 3
select count(*) from orders$p1;

alter table orders merge partitions p4, p5 into partition p2;
--= 2
select count(*) from orders$p2;