[LightDB兼容增强]支持Oracle PARTITION

 

背景：

　　Oracle支持PARTITION分区以及SUBPARITION分区，且CREATE/ADD/DROP/TRUNCATE均有相应的语法支持，虽然LightDB已经做过一次适配，我们已经能够支持相应的语法操作，为进一步增强兼容性，使得LightDB与Oracle在SQL语法及语意层面的差异性进一步缩小，进一步降低oracle牵移到LightDB存在的障碍，我们在新的版本里面进一步做了如下工作：

　　1，增强CREATE/ADD/DROP/TRUNCATE相应的子句，使得LightDB相应的子句与Oracle一致

　　2，在分区命名，分区查询等方面全面兼容Oracle实现逻辑，对于自动生成的分区，提供DBA_TAB_PARTITIONS/DBA_TAB_SUBPARTITIONS视图用于查询 。

支持版本：自LightDB 23.2

代码实例：

range partition 

create table lt_truncate_t_r( a int)
partition by range(a)
(
	partition p1 values less than(10),
	partition p2 values less than(20)
);
\d+ lt_truncate_t_r;

insert into lt_truncate_t_r(a) values (1);
insert into lt_truncate_t_r(a) values (10);

alter table lt_truncate_t_r truncate partition p1;
alter table lt_truncate_t_r add partition p2 values less than(30);
alter table lt_truncate_t_r truncate partition p2;
alter table lt_truncate_t_r drop partition p2;

SELECT
  table_owner,
  table_name,
  partition_name,
  subpartition_count,
  high_value,
  tablespace_name
FROM
  DBA_TAB_PARTITIONS
WHERE
  table_name = 'LT_TRUNCATE_T_R';

　　

table_owner | table_name | partition_name | subpartition_count | high_value | tablespace_name
-------------+-----------------+----------------+--------------------+------------+-----------------
PUBLIC | LT_TRUNCATE_T_R | P1 | | | DEFAULT
PUBLIC | LT_TRUNCATE_T_R | P2 | | | DEFAULT
(2 rows)

drop table lt_truncate_t_r;

　　

range interval

CREATE TABLE lt_truncate_t_r_interval
(
	a timestamp
)PARTITION BY RANGE (a) INTERVAL (numtodsinterval(3, 'second'))
(
	PARTITION p1 VALUES LESS THAN(to_timestamp('2023-05-01 02:34:09', 'yyyy-mm-dd hh24:mi:ss'))
);
\d+ lt_truncate_t_r_interval

INSERT INTO lt_truncate_t_r_interval(a) VALUES( to_timestamp('2023-05-01 02:34:15', 'yyyy-mm-dd hh24:mi:ss'));
INSERT INTO lt_truncate_t_r_interval(a) VALUES( to_timestamp('2023-05-01 02:34:25', 'yyyy-mm-dd hh24:mi:ss'));

select count(*) from lt_truncate_t_r_interval;
alter table lt_truncate_t_r_interval truncate partition p1;

select count(*) from lt_truncate_t_r_interval;
alter table lt_truncate_t_r_interval truncate partition auto_p1;
select count(*) from lt_truncate_t_r_interval;
drop table lt_truncate_t_r_interval;

 

list partition

create table lt_truncate_l( a int)
partition by list(a)
(
	partition p1 values(1,2,3,4),
	partition p2 values(5,6,7,8)
);
insert into lt_truncate_l(a) values(1);
insert into lt_truncate_l(a) values(2);
insert into lt_truncate_l(a) values(7);
insert into lt_truncate_l(a) values(8);

select count(*) from lt_truncate_l;
alter table lt_truncate_l truncate partition p1;
select count(*) from lt_truncate_l;
alter table lt_truncate_l truncate partition p2;
select count(*) from lt_truncate_l;

drop table lt_truncate_l;

　　

hash partition

create table lt_truncate_h(a int)
partition by hash(a) partitions 4;
insert into lt_truncate_h(a) values(1);
insert into lt_truncate_h(a) values(2);
insert into lt_truncate_h(a) values(3);
insert into lt_truncate_h(a) values(4);

select count(*) from lt_truncate_h;
alter table lt_truncate_h truncate partition p0;
select count(*) from lt_truncate_h;
alter table lt_truncate_h truncate partition p1;
select count(*) from lt_truncate_h;
alter table lt_truncate_h truncate partition p2;
select count(*) from lt_truncate_h;
alter table lt_truncate_h truncate partition p3;
select count(*) from lt_truncate_h;
drop table lt_truncate_h;

CREATE TABLE lt_truncate_h_with_name
(
	a int
) PARTITION BY HASH (a)
(
	PARTITION p1,
    PARTITION p2,
	PARTITION p3
);

insert into lt_truncate_h_with_name(a) values(1);
insert into lt_truncate_h_with_name(a) values(2);
insert into lt_truncate_h_with_name(a) values(3);
insert into lt_truncate_h_with_name(a) values(4);

alter table lt_truncate_h_with_name truncate partition p1;
select count(*) from lt_truncate_h_with_name;
alter table lt_truncate_h_with_name truncate partition p2;
select count(*) from lt_truncate_h_with_name;
alter table lt_truncate_h_with_name truncate partition p3;
select count(*) from lt_truncate_h_with_name;
drop table lt_truncate_h_with_name;

 

range list

create table lt_truncate_rl(a int,b int)
    partition by range(a)
            subpartition by list(b)
            subpartition template
            (
                    subpartition p21 values(0,1,2,3,4),
                    subpartition p22 values(5,6,7,8,9)
            )
    (
            partition p1 values less than(10),
            partition p2 values less than(30)
    )
;

insert into lt_truncate_rl(a,b) values(1,1);
insert into lt_truncate_rl(a,b) values(1,2);
insert into lt_truncate_rl(a,b) values(1,7);
insert into lt_truncate_rl(a,b) values(1,8);

insert into lt_truncate_rl(a,b) values(20,1);
insert into lt_truncate_rl(a,b) values(20,2);
insert into lt_truncate_rl(a,b) values(20,7);
insert into lt_truncate_rl(a,b) values(20,8);

select count(*) from lt_truncate_rl;
alter table lt_truncate_rl truncate partition p1;
select count(*) from lt_truncate_rl;
alter table lt_truncate_rl truncate partition p2;
select count(*) from lt_truncate_rl;
drop table lt_truncate_rl;

　　

range hash

CREATE TABLE lt_truncate_rh
(
	a int,
	b int
) PARTITION BY RANGE(a)
	SUBPARTITION BY HASH (b)
	SUBPARTITIONS 2
(
	PARTITION p1 VALUES LESS THAN(10),
	PARTITION p2 VALUES LESS THAN(30)
);
insert into lt_truncate_rh(a,b) values(1,1);
insert into lt_truncate_rh(a,b) values(1,2);
insert into lt_truncate_rh(a,b) values(1,7);
insert into lt_truncate_rh(a,b) values(1,8);

insert into lt_truncate_rh(a,b) values(20,1);
insert into lt_truncate_rh(a,b) values(20,2);
insert into lt_truncate_rh(a,b) values(20,7);
insert into lt_truncate_rh(a,b) values(20,8);

select count(*) from lt_truncate_rh;
alter table lt_truncate_rh truncate partition p1;
select count(*) from lt_truncate_rh;
alter table lt_truncate_rh truncate partition p2;
select count(*) from lt_truncate_rh;
drop table lt_truncate_rh;

 

普通表转分区表

　　

CREATE TABLE lt_truncate_modify_t(a int);
ALTER TABLE lt_truncate_modify_t MODIFY PARTITION BY RANGE(a)
(
	PARTITION p1 values less than(10),
	PARTITION p2 values less than(20),
	PARTITION p3 values less than(30)
);
insert into lt_truncate_modify_t(a) values(1);
insert into lt_truncate_modify_t(a) values(10);
insert into lt_truncate_modify_t(a) values(20);

select count(*) from lt_truncate_modify_t;
alter table lt_truncate_modify_t truncate partition p1;
select count(*) from lt_truncate_modify_t;
alter table lt_truncate_modify_t truncate partition p2;
select count(*) from lt_truncate_modify_t;
drop table lt_truncate_modify_t;

　　

 

注意：

　　1，添加分区的时候，子分区并不会自动被创建。　　

　　2，自23.4开始：分区表长度限制为31字符，若有二级分区表长度限制为30