【LightDB 22.3版本新特性】Oracle分区语法兼容
陈实
【LightDB 22.3版本新特性】Oracle分区语法兼容
1. LIST分区语法兼容
1. 创建LIST分区
Oracle语法如下:
CREATE TABLE oracle_list1
(
a int,
b float,
c date,
d timestamp,
e varchar(20)
) PARTITION BY LIST(e)
(
PARTITION p1 VALUES ('0001', '0002', '0003', '0004', '0005'),
PARTITION p2 VALUES ('0006', '0007', '0008', '0009'),
PARTITION p3 VALUES ('0010', '0011')
);
LightDB-22.3之前版本要想要实现，语句如下：
CREATE TABLE lt_list1
(
a int,
b float,
c date,
d timestamp,
e varchar(20)
) PARTITION BY LIST(e);
CREATE TABLE p1 PARTITION OF lt_list1 FOR VALUES IN ('0001', '0002', '0003', '0004', '0005');
CREATE TABLE p2 PARTITION OF lt_list1 FOR VALUES IN ('0006', '0007', '0008', '0009');
CREATE TABLE p3 PARTITION OF lt_list1 FOR VALUES IN ('0010', '0011');
会发现创建子分区的语句是分开的。
LightDB-22.3版本做了兼容：
1）创建分区支持创建list子分区语句。
2）取值关键字支持VALUES（原FOR VALUES IN）。
实现效果如下：
lightdb@test=# CREATE TABLE lt_oracle_list1
lightdb@test-# (
lightdb@test(# a int,
lightdb@test(# b float,
lightdb@test(# c date,
lightdb@test(# d timestamp,
lightdb@test(# e varchar(20)
lightdb@test(# ) PARTITION BY LIST(e)
lightdb@test-# (
lightdb@test(# PARTITION p1 VALUES ('0001', '0002', '0003', '0004', '0005'),
lightdb@test(# PARTITION p2 VALUES ('0006', '0007', '0008', '0009'),
lightdb@test(# PARTITION p3 VALUES ('0010', '0011')
lightdb@test(# );
CREATE TABLE
lightdb@test=# \d+ lt_oracle_list1
Partitioned table "public.lt_oracle_list1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | date | | | | plain | |
d | timestamp without time zone | | | | plain | |
e | character varying(20) | | | | extended | |
Partition key: LIST (e)
Partitions: lt_oracle_list1_1_prt_p1 FOR VALUES IN ('0001', '0002', '0003', '0004', '0005'),
lt_oracle_list1_1_prt_p2 FOR VALUES IN ('0006', '0007', '0008', '0009'),
lt_oracle_list1_1_prt_p3 FOR VALUES IN ('0010', '0011')
lightdb@test=#
这里要注意的是分区表名称自动加了前缀， 来防止子分区表名称冲突。
2. 添加LIST分区
Oracle语法如下：
ALTER TABLE oracle_list1 ADD PARTITION p4 VALUES ('0012');
LightDB-22.3之前版本要想要实现，语句如下：
CREATE TABLE p4 PARTITION OF lt_list1 FOR VALUES IN ('0012');
会发现LightDB是建表语句，而Oracle是修改表语句。
LightDB-22.3版本做了兼容：
即：支持在修改表语句中新增list分区。
实现效果如下：
lightdb@test=# ALTER TABLE lt_oracle_list1 ADD PARTITION lt_oracle_list1_p4 VALUES ('0012');
ALTER TABLE
lightdb@test=# \d+ lt_oracle_list1
Partitioned table "public.lt_oracle_list1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | date | | | | plain | |
d | timestamp without time zone | | | | plain | |
e | character varying(20) | | | | extended | |
Partition key: LIST (e)
Partitions: lt_oracle_list1_1_prt_p1 FOR VALUES IN ('0001', '0002', '0003', '0004', '0005'),
lt_oracle_list1_1_prt_p2 FOR VALUES IN ('0006', '0007', '0008', '0009'),
lt_oracle_list1_1_prt_p3 FOR VALUES IN ('0010', '0011'),
lt_oracle_list1_p4 FOR VALUES IN ('0012')
lightdb@test=#
这里需要注意的是在修改表新增分区时，子分区名称并没有自动添加前缀。
3. 删除LIST分区
Oracle语法如下：
ALTER TABLE oracle_list1 DROP PARTITION p4;
LightDB-22.3之前版本要想要实现，语句如下：
DROP TABLE p4;
会发现LightDB是直接删表语句，而Oracle是修改表语句。
LightDB-22.3版本做了兼容：
即：支持在修改表语句中删除list分区。
实现效果如下：
lightdb@test=# ALTER TABLE lt_oracle_list1 DROP PARTITION lt_oracle_list1_p4;
ALTER TABLE
lightdb@test=# \d+ lt_oracle_list1
Partitioned table "public.lt_oracle_list1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | date | | | | plain | |
d | timestamp without time zone | | | | plain | |
e | character varying(20) | | | | extended | |
Partition key: LIST (e)
Partitions: lt_oracle_list1_1_prt_p1 FOR VALUES IN ('0001', '0002', '0003', '0004', '0005'),
lt_oracle_list1_1_prt_p2 FOR VALUES IN ('0006', '0007', '0008', '0009'),
lt_oracle_list1_1_prt_p3 FOR VALUES IN ('0010', '0011')
lightdb@test=#
2.HASH分区兼容
1. 创建HASH分区
Oracle语法如下：
语法1：
CREATE TABLE oracle_hash1
(
a int,
b float,
c VARCHAR(20),
d DATE,
e timestamp
) PARTITION BY HASH (a)
(
PARTITION p1,
PARTITION p2,
PARTITION p3
);
语法2：
CREATE TABLE oracle_hash2
(
a int,
b float,
c VARCHAR(20),
d DATE,
e timestamp
) PARTITION BY HASH (a) PARTITIONS 3;
其中语法1和语法2的区别在于语法1指定了子分区名称，而语法2子分区名称则由系统内部生成。
LightDB22.3之前版本要想要实现，语句如下：
CREATE TABLE lt_hash1
(
a int,
b float,
c VARCHAR(20),
d DATE,
e timestamp
) PARTITION BY HASH (a);
CREATE TABLE p1 PARTITION OF lt_hash1 FOR VALUES WITH (MODULUS 3, REMAINDER 0);
CREATE TABLE p2 PARTITION OF lt_hash1 FOR VALUES WITH (MODULUS 3, REMAINDER 1);
CREATE TABLE p3 PARTITION OF lt_hash1 FOR VALUES WITH (MODULUS 3, REMAINDER 2);
会发现创建子分区的语句是分开的，多了hash范围，即除数(MODULUS)和余数(REMAINDER)。
LightDB-22.3版本做对语法1和语法2都做了兼容：
1）创建分区支持创建hash子分区语句。
2）自动生成除数(MODULUS)和余数(REMAINDER)。
3）支持关键字PARTITIONS。
实现效果如下：
语法1：
lightdb@test=# CREATE TABLE lt_oracle_hash1
lightdb@test-# (
lightdb@test(# a int,
lightdb@test(# b float,
lightdb@test(# c VARCHAR(20),
lightdb@test(# d DATE,
lightdb@test(# e timestamp
lightdb@test(# ) PARTITION BY HASH (a)
lightdb@test-# (
lightdb@test(# PARTITION p1,
lightdb@test(# PARTITION p2,
lightdb@test(# PARTITION p3
lightdb@test(# );
CREATE TABLE
lightdb@test=# \d+ lt_oracle_hash1
Partitioned table "public.lt_oracle_hash1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | character varying(20) | | | | extended | |
d | date | | | | plain | |
e | timestamp without time zone | | | | plain | |
Partition key: HASH (a)
Partitions: lt_oracle_hash1_1_prt_p1 FOR VALUES WITH (modulus 3, remainder 0),
lt_oracle_hash1_1_prt_p2 FOR VALUES WITH (modulus 3, remainder 1),
lt_oracle_hash1_1_prt_p3 FOR VALUES WITH (modulus 3, remainder 2)
lightdb@test=#
会发现根据建表的子分区个数来确定(MODULUS)和余数(REMAINDER)。
这里要注意的是分区表名称自动加了前缀， 来防止子分区表名称冲突。
语法2：
lightdb@test=# CREATE TABLE lt_oracle_hash2
lightdb@test-# (
lightdb@test(# a int,
lightdb@test(# b float,
lightdb@test(# c VARCHAR(20),
lightdb@test(# d DATE,
lightdb@test(# e timestamp
lightdb@test(# ) PARTITION BY HASH (a) PARTITIONS 3;
CREATE TABLE
lightdb@test=# \d+ lt_oracle_hash2
Partitioned table "public.lt_oracle_hash2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | character varying(20) | | | | extended | |
d | date | | | | plain | |
e | timestamp without time zone | | | | plain | |
Partition key: HASH (a)
Partitions: lt_oracle_hash2_1_prt_p0 FOR VALUES WITH (modulus 3, remainder 0),
lt_oracle_hash2_1_prt_p1 FOR VALUES WITH (modulus 3, remainder 1),
lt_oracle_hash2_1_prt_p2 FOR VALUES WITH (modulus 3, remainder 2)
lightdb@test=#
会发现根据关键字PARTITIONS指定的子分区个数来确定(MODULUS)和余数(REMAINDER)。
2. HASH分区的添加和删除特别说明
LightDB-22.3版本暂不支持修改表语句对HASH分区的添加和删除。
原因如下：
1）Oracle目前也不支持对HASH子分区进行删除。
2）Oracle支持对HASH分区添加子分区，这里面涉及到数据的备份与迁移。
LightDB的HASH分区，在创建表之后，除数与余数固定，如果要添加一个新的HASH子分区，需要先备份数据，将原来的分区删掉，重新建立HASH分区，再将备份的数据重新导入，很是麻烦。并且实际的使用场景不多，所以LightDB-22.3版本暂不做兼容，后续如有需求再做兼容。
3. RANGE分区兼容
1. 创建RANGE分区
Oracle语法如下：
CREATE TABLE oracle_range1
(
a int,
b float,
c date,
d timestamp
)PARTITION BY RANGE (a)
(
PARTITION p1 VALUES LESS THAN(10),
PARTITION p2 VALUES LESS THAN(20),
PARTITION p3 VALUES LESS THAN(30),
PARTITION p4 VALUES LESS THAN(MAXVALUE)
);
LightDB22.3之前版本要想要实现，语句如下：
CREATE TABLE lt_range1
(
a int,
b float,
c date,
d timestamp
)PARTITION BY RANGE (a);
CREATE TABLE p1 PARTITION OF lt_range1 FOR VALUES FROM (MINVALUE) TO (10);
CREATE TABLE p2 PARTITION OF lt_range1 FOR VALUES FROM (10) TO (20);
CREATE TABLE p3 PARTITION OF lt_range1 FOR VALUES FROM (20) TO (30);
CREATE TABLE p4 PARTITION OF lt_range1 FOR VALUES FROM (30) TO (MAXVALUE);
会发现创建子分区的语句是分开的，多了双边范围，即FROM和TO。
LightDB-22.3版本做了兼容：
1）创建分区支持创建range子分区语句。
2）取值关键字支持VALUES（原FOR VALUES）。
3）支持关键字LESS THAN。
实现效果如下：
lightdb@test=# CREATE TABLE lt_oracle_range1
lightdb@test-# (
lightdb@test(# a int,
lightdb@test(# b float,
lightdb@test(# c date,
lightdb@test(# d timestamp
lightdb@test(# )PARTITION BY RANGE (a)
lightdb@test-# (
lightdb@test(# PARTITION p1 VALUES LESS THAN(10),
lightdb@test(# PARTITION p2 VALUES LESS THAN(20),
lightdb@test(# PARTITION p3 VALUES LESS THAN(30),
lightdb@test(# PARTITION p4 VALUES LESS THAN(MAXVALUE)
lightdb@test(# );
CREATE TABLE
lightdb@test=# \d+ lt_oracle_range1
Partitioned table "public.lt_oracle_range1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | date | | | | plain | |
d | timestamp without time zone | | | | plain | |
Partition key: RANGE (a)
Partitions: lt_oracle_range1_1_prt_p1 FOR VALUES FROM (MINVALUE) TO (10),
lt_oracle_range1_1_prt_p2 FOR VALUES FROM (10) TO (20),
lt_oracle_range1_1_prt_p3 FOR VALUES FROM (20) TO (30),
lt_oracle_range1_1_prt_p4 FOR VALUES FROM (30) TO (MAXVALUE)
lightdb@test=#
这里要注意的是分区表名称自动加了前缀， 来防止子分区表名称冲突。
关于range范围的确定：
1）p1分区范围的确定，首先LESS THAN(10)确定了范围区间的右为10，再找比10小的（小中取最大），找不到即区间左为MINVALUE。
2）p2分区范围的确定，首先LESS THAN(20)确定了范围区间的右为20，再找比20小的（小中取最大），即区间左为10。
3）p3分区范围的确定，首先LESS THAN(30)确定了范围区间的右为30，再找比30小的（小中取最大），即区间左为20。
4）p4分区范围的确定，首先LESS THAN(MAXVALUE)确定了范围区间的右为MAXVALUE，再找比MAXVALUE小的（小中取最大），即区间左为30。
5）所有分区范围均为左闭合，即[left, right);
RANGE分区兼容增强：
Oracle创建RANGE分区时，子分区范围必须有序，如下语句则报错：
CREATE TABLE oracle_range1
(
a int,
b float,
c date,
d timestamp
)PARTITION BY RANGE (a)
(
PARTITION p1 VALUES LESS THAN(10),
PARTITION p2 VALUES LESS THAN(20),
PARTITION p4 VALUES LESS THAN(MAXVALUE),
PARTITION p3 VALUES LESS THAN(30)
);
报错信息为’SQL 错误 [14037] [72000]: ORA-14037: 分区 “P4” 的分区界限过高’。
LightDB对这种语法做了增强，即可以建表成功，如下：
lightdb@test=# CREATE TABLE lt_oracle_range2
lightdb@test-# (
lightdb@test(# a int,
lightdb@test(# b float,
lightdb@test(# c date,
lightdb@test(# d timestamp
lightdb@test(# )PARTITION BY RANGE (a)
lightdb@test-# (
lightdb@test(# PARTITION p1 VALUES LESS THAN(10),
lightdb@test(# PARTITION p2 VALUES LESS THAN(20),
lightdb@test(# PARTITION p4 VALUES LESS THAN(MAXVALUE),
lightdb@test(# PARTITION p3 VALUES LESS THAN(30)
lightdb@test(# );
CREATE TABLE
lightdb@test=# \d+ lt_oracle_range2
Partitioned table "public.lt_oracle_range2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | date | | | | plain | |
d | timestamp without time zone | | | | plain | |
Partition key: RANGE (a)
Partitions: lt_oracle_range2_1_prt_p1 FOR VALUES FROM (MINVALUE) TO (10),
lt_oracle_range2_1_prt_p2 FOR VALUES FROM (10) TO (20),
lt_oracle_range2_1_prt_p3 FOR VALUES FROM (20) TO (30),
lt_oracle_range2_1_prt_p4 FOR VALUES FROM (30) TO (MAXVALUE)
lightdb@test=#
2. 添加RANGE分区
Oracle语法如下：
ALTER TABLE oracle_range3 ADD PARTITION p4 VALUES LESS THAN (40);
LightDB-22.3之前版本要想要实现，语句如下：
CREATE TABLE p4 PARTITION OF lt_oracle_range3 FOR VALUES FROM (30) TO (40);
会发现LightDB是建表语句，而Oracle是修改表语句。
LightDB-22.3版本做了兼容：
即：支持在修改表语句中新增range分区。
实现效果如下：
lightdb@test=# CREATE TABLE lt_oracle_range3
(
a int,
b float,
c date,
d timestamp
)PARTITION BY RANGE (a)
(
PARTITION p1 VALUES LESS THAN(10),
PARTITION p2 VALUES LESS THAN(20),
PARTITION p3 VALUES LESS THAN(30)
);
CREATE TABLE
lightdb@test=# \d+ lt_oracle_range3
Partitioned table "public.lt_oracle_range3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | date | | | | plain | |
d | timestamp without time zone | | | | plain | |
Partition key: RANGE (a)
Partitions: lt_oracle_range3_1_prt_p1 FOR VALUES FROM (MINVALUE) TO (10),
lt_oracle_range3_1_prt_p2 FOR VALUES FROM (10) TO (20),
lt_oracle_range3_1_prt_p3 FOR VALUES FROM (20) TO (30)
lightdb@test=# ALTER TABLE lt_oracle_range3 ADD PARTITION lt_oracle_range3_p4 VALUES LESS THAN (40);
ALTER TABLE
lightdb@test=# \d+ lt_oracle_range3
Partitioned table "public.lt_oracle_range3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | date | | | | plain | |
d | timestamp without time zone | | | | plain | |
Partition key: RANGE (a)
Partitions: lt_oracle_range3_1_prt_p1 FOR VALUES FROM (MINVALUE) TO (10),
lt_oracle_range3_1_prt_p2 FOR VALUES FROM (10) TO (20),
lt_oracle_range3_1_prt_p3 FOR VALUES FROM (20) TO (30),
lt_oracle_range3_p4 FOR VALUES FROM (30) TO (40)
lightdb@test=#
这里需要注意的是在修改表新增分区时，子分区名称并没有自动添加前缀。
3. 删除RANGE分区
Oracle语法如下：
ALTER TABLE oracle_range1 DROP PARTITION p4;
LightDB-22.3之前版本要想要实现，语句如下：
DROP TABLE p4;
会发现LightDB是直接删表语句，而Oracle是修改表语句。
LightDB-22.3版本做了兼容：
即：支持在修改表语句中删除range分区。
实现效果如下：
lightdb@test=# ALTER TABLE lt_oracle_range3 DROP PARTITION lt_oracle_range3_p4;
ALTER TABLE
lightdb@test=# \d+ lt_oracle_range3
Partitioned table "public.lt_oracle_range3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | date | | | | plain | |
d | timestamp without time zone | | | | plain | |
Partition key: RANGE (a)
Partitions: lt_oracle_range3_1_prt_p1 FOR VALUES FROM (MINVALUE) TO (10),
lt_oracle_range3_1_prt_p2 FOR VALUES FROM (10) TO (20),
lt_oracle_range3_1_prt_p3 FOR VALUES FROM (20) TO (30)
lightdb@test=#
4. RANGE INTERVAL自动间隔分区(时间间隔)兼容
Oracle语法如下：
CREATE TABLE oracle_interval1
(
a int,
b float,
c date,
d timestamp,
e varchar(20)
)PARTITION BY RANGE (c) INTERVAL (numtoyminterval(3, 'year'))
(
PARTITION p1 VALUES LESS THAN(to_date('2022-01-01', 'yyyy-mm-dd')),
PARTITION p2 VALUES LESS THAN(to_date('2022-10-15', 'yyyy-mm-dd'))
);
CREATE TABLE oracle_interval2
(
a int,
b float,
c date,
d timestamp,
e varchar(20)
)PARTITION BY RANGE (c) INTERVAL (numtodsinterval(3, 'day'))
(
PARTITION p1 VALUES LESS THAN(to_date('2022-01-01', 'yyyy-mm-dd')),
PARTITION p2 VALUES LESS THAN(to_date('2022-10-15', 'yyyy-mm-dd'))
);
语法说明：
按照时间进行自动分区，INTERVAL后面可以跟随NUMTOYMINTERVAL和NUMTODSINTERVAL。
1）NUMTOYMINTERVAL(x, c)
用法：x是一个数据，c是一个字符串，该函数是将x转为interval year to month类型。常用单位有：‘year’,‘month’。
2）NUMTODSINTERVAL(x, c)
用法：x是一个数据，c是一个字符串，该函数是将x转为interval day to second类型。常用单位有：‘day’,‘hour’,‘minute’,‘second’。
表oracle_interval1间隔时间为3年，表oracle_interval2间隔时间为3天。
LightDB-22.3之前的版本不支持range interval自动间隔分区。
LightDB-22.3版本做了兼容：
即支持RANGE INTERVAL自动间隔分区(时间间隔)。
实现效果如下：
lightdb@test=# CREATE TABLE lt_oracle_interval1
(
a int,
b date
)PARTITION BY RANGE (b) INTERVAL (numtodsinterval(1, 'day'))
(
PARTITION p1 VALUES LESS THAN(to_date('2022-01-01', 'yyyy-mm-dd'))
);
CREATE TABLE
lightdb@test=# \d+ lt_oracle_interval1
Partitioned table "public.lt_oracle_interval1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | date | | | | plain | |
Partition key: RANGE (b)
Partitions: lt_oracle_interval1_1_prt_p1 FOR VALUES FROM (MINVALUE) TO ('2022-01-01')
lightdb@test=# insert into lt_oracle_interval1 values
lightdb@test-# (1, '2022-01-02'),(1, '2022-01-03'),(1, '2022-01-04'),(1, '2022-01-05'),(1, '2022-01-06');
INSERT 0 5
lightdb@test=# \d+ lt_oracle_interval1
Partitioned table "public.lt_oracle_interval1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | date | | | | plain | |
Partition key: RANGE (b)
Partitions: lt_oracle_interval1_1_prt_p1 FOR VALUES FROM (MINVALUE) TO ('2022-01-01'),
lt_oracle_interval1_autoprt_1 FOR VALUES FROM ('2022-01-02') TO ('2022-01-03'),
lt_oracle_interval1_autoprt_2 FOR VALUES FROM ('2022-01-03') TO ('2022-01-04'),
lt_oracle_interval1_autoprt_3 FOR VALUES FROM ('2022-01-04') TO ('2022-01-05'),
lt_oracle_interval1_autoprt_4 FOR VALUES FROM ('2022-01-05') TO ('2022-01-06'),
lt_oracle_interval1_autoprt_5 FOR VALUES FROM ('2022-01-06') TO ('2022-01-07')
lightdb@test=#
关于自动分区时间范围确定的说明：
1）起始时间为创建表时，所有子分区中最大的时间范围为起始时间：
CREATE TABLE lt_oracle_interval2
(
a int,
b date
)PARTITION BY RANGE (b) INTERVAL (numtodsinterval(10, 'day'))
(
PARTITION p1 VALUES LESS THAN(to_date('2022-01-01', 'yyyy-mm-dd')),
PARTITION p2 VALUES LESS THAN(to_date('2022-02-15', 'yyyy-mm-dd')),
PARTITION p3 VALUES LESS THAN(to_date('2022-03-15', 'yyyy-mm-dd'))
);
起始时间为p3子分区的’2022-03-15’。
2）自动创建的分区时间范围都是以起始时间为基准，叠加时间间隔：
lightdb@test=# CREATE TABLE lt_oracle_interval2
lightdb@test-# (
lightdb@test(# a int,
lightdb@test(# b date
lightdb@test(# )PARTITION BY RANGE (b) INTERVAL (numtodsinterval(10, 'day'))
lightdb@test-# (
lightdb@test(# PARTITION p1 VALUES LESS THAN(to_date('2022-01-01', 'yyyy-mm-dd')),
lightdb@test(# PARTITION p2 VALUES LESS THAN(to_date('2022-02-15', 'yyyy-mm-dd')),
lightdb@test(# PARTITION p3 VALUES LESS THAN(to_date('2022-03-15', 'yyyy-mm-dd'))
lightdb@test(# );
CREATE TABLE
lightdb@test=# \d+ lt_oracle_interval2
Partitioned table "public.lt_oracle_interval2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | date | | | | plain | |
Partition key: RANGE (b)
Partitions: lt_oracle_interval2_1_prt_p1 FOR VALUES FROM (MINVALUE) TO ('2022-01-01'),
lt_oracle_interval2_1_prt_p2 FOR VALUES FROM ('2022-01-01') TO ('2022-02-15'),
lt_oracle_interval2_1_prt_p3 FOR VALUES FROM ('2022-02-15') TO ('2022-03-15')
lightdb@test=# insert into lt_oracle_interval2 values (100, '2022-04-01');
INSERT 0 1
lightdb@test=# \d+ lt_oracle_interval2
Partitioned table "public.lt_oracle_interval2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
a | integer | | | | plain | |
b | date | | | | plain | |
Partition key: RANGE (b)
Partitions: lt_oracle_interval2_1_prt_p1 FOR VALUES FROM (MINVALUE) TO ('2022-01-01'),
lt_oracle_interval2_1_prt_p2 FOR VALUES FROM ('2022-01-01') TO ('2022-02-15'),
lt_oracle_interval2_1_prt_p3 FOR VALUES FROM ('2022-02-15') TO ('2022-03-15'),
lt_oracle_interval2_autoprt_1 FOR VALUES FROM ('2022-03-25') TO ('2022-04-04')
lightdb@test=#
5. 二级分区兼容
兼容Oracle的range-list分区
兼容Oracle的range-hash分区
Oracle语法如下：
CREATE TABLE oracle_rl1
(
a int,
b float,
c varchar(20),
d date,
e timestamp
) PARTITION BY RANGE(d)
SUBPARTITION BY LIST (c)
SUBPARTITION TEMPLATE
(
SUBPARTITION p1 VALUES ('0000', '0001', '0002'),
SUBPARTITION p2 VALUES ('0003')
)
(
PARTITION p1 VALUES LESS THAN(to_date('2020-01-01','yyyy-mm-dd')),
PARTITION p2 VALUES LESS THAN(to_date('2021-01-01','yyyy-mm-dd')),
PARTITION p3 VALUES LESS THAN(to_date('2022-01-01','yyyy-mm-dd'))
);
CREATE TABLE oracle_rh1
(
a int,
b float,
c varchar(20),
d date,
e timestamp
)
PARTITION BY RANGE(d)
SUBPARTITION BY HASH(a)
SUBPARTITIONS 10
(
PARTITION p1 VALUES LESS THAN(to_date('2020-01-01','yyyy-mm-dd')),
PARTITION p2 VALUES LESS THAN(to_date('2021-01-01','yyyy-mm-dd')),
PARTITION p3 VALUES LESS THAN(to_date('2022-01-01','yyyy-mm-dd'))
);
LightDB-22.3之前版本要想要实现，语句如下：
CREATE TABLE lt_rl1
(
a int,
b float,
c varchar(20),
d date,
e timestamp
) PARTITION BY RANGE(d);
CREATE TABLE lt_rl1_p1
(
a int,
b float,
c varchar(20),
d date,
e timestamp
) PARTITION BY LIST(c);
CREATE TABLE lt_rl1_p2
(
a int,
b float,
c varchar(20),
d date,
e timestamp
) PARTITION BY LIST(c);
CREATE TABLE lt_rl1_p3
(
a int,
b float,
c varchar(20),
d date,
e timestamp
) PARTITION BY LIST(c);
CREATE TABLE lt_rl1_p1_p1 PARTITION OF lt_rl1_p1 FOR VALUES IN ('0000', '0001', '0002');
CREATE TABLE lt_rl1_p1_p2 PARTITION OF lt_rl1_p1 FOR VALUES IN ('0003');
CREATE TABLE lt_rl1_p2_p1 PARTITION OF lt_rl1_p2 FOR VALUES IN ('0000', '0001', '0002');
CREATE TABLE lt_rl1_p2_p2 PARTITION OF lt_rl1_p2 FOR VALUES IN ('0003');
CREATE TABLE lt_rl1_p3_p1 PARTITION OF lt_rl1_p3 FOR VALUES IN ('0000', '0001', '0002');
CREATE TABLE lt_rl1_p3_p2 PARTITION OF lt_rl1_p3 FOR VALUES IN ('0003');
ALTER TABLE lt_rl1 ATTACH PARTITION lt_rl1_p1 FOR VALUES FROM (MINVALUE) TO ('2020-01-01');
ALTER TABLE lt_rl1 ATTACH PARTITION lt_rl1_p2 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01');
ALTER TABLE lt_rl1 ATTACH PARTITION lt_rl1_p3 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01');
会发现LightDB会使用关键字ATTACH来创建二级分区，很是麻烦。
LightDB-22.3版本做了兼容：
1）支持创建range-list分区。
2）支持创建range-hash分区。
实现效果如下：
支持创建range-list分区:
lightdb@test=# CREATE TABLE lt_oracle_rl1
lightdb@test-# (
lightdb@test(# a int,
lightdb@test(# b float,
lightdb@test(# c varchar(20),
lightdb@test(# d date,
lightdb@test(# e timestamp
lightdb@test(# ) PARTITION BY RANGE(d)
lightdb@test-# SUBPARTITION BY LIST (c)
lightdb@test-# SUBPARTITION TEMPLATE
lightdb@test-# (
lightdb@test(# SUBPARTITION p1 VALUES ('0000', '0001', '0002'),
lightdb@test(# SUBPARTITION p2 VALUES ('0003')
lightdb@test(# )
lightdb@test-# (
lightdb@test(# PARTITION p1 VALUES LESS THAN(to_date('2020-01-01','yyyy-mm-dd')),
lightdb@test(# PARTITION p2 VALUES LESS THAN(to_date('2021-01-01','yyyy-mm-dd')),
lightdb@test(# PARTITION p3 VALUES LESS THAN(to_date('2022-01-01','yyyy-mm-dd'))
lightdb@test(# );
CREATE TABLE
lightdb@test=# \d+ lt_oracle_rl1
Partitioned table "public.lt_oracle_rl1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | character varying(20) | | | | extended | |
d | date | | | | plain | |
e | timestamp without time zone | | | | plain | |
Partition key: RANGE (d)
Partitions: lt_oracle_rl1_1_prt_p1 FOR VALUES FROM (MINVALUE) TO ('2020-01-01'), PARTITIONED,
lt_oracle_rl1_1_prt_p2 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'), PARTITIONED,
lt_oracle_rl1_1_prt_p3 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'), PARTITIONED
lightdb@test=# \d+ lt_oracle_rl1_1_prt_p1
Partitioned table "public.lt_oracle_rl1_1_prt_p1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | character varying(20) | | | | extended | |
d | date | | | | plain | |
e | timestamp without time zone | | | | plain | |
Partition of: lt_oracle_rl1 FOR VALUES FROM (MINVALUE) TO ('2020-01-01')
Partition constraint: ((d IS NOT NULL) AND (d < '2020-01-01'::date))
Partition key: LIST (c)
Partitions: lt_oracle_rl1_1_prt_p1_2_prt_p1 FOR VALUES IN ('0000', '0001', '0002'),
lt_oracle_rl1_1_prt_p1_2_prt_p2 FOR VALUES IN ('0003')
lightdb@test=# \d+ lt_oracle_rl1_1_prt_p2
Partitioned table "public.lt_oracle_rl1_1_prt_p2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | character varying(20) | | | | extended | |
d | date | | | | plain | |
e | timestamp without time zone | | | | plain | |
Partition of: lt_oracle_rl1 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01')
Partition constraint: ((d IS NOT NULL) AND (d >= '2020-01-01'::date) AND (d < '2021-01-01'::date))
Partition key: LIST (c)
Partitions: lt_oracle_rl1_1_prt_p2_2_prt_p1 FOR VALUES IN ('0000', '0001', '0002'),
lt_oracle_rl1_1_prt_p2_2_prt_p2 FOR VALUES IN ('0003')
lightdb@test=# \d+ lt_oracle_rl1_1_prt_p3
Partitioned table "public.lt_oracle_rl1_1_prt_p3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | character varying(20) | | | | extended | |
d | date | | | | plain | |
e | timestamp without time zone | | | | plain | |
Partition of: lt_oracle_rl1 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01')
Partition constraint: ((d IS NOT NULL) AND (d >= '2021-01-01'::date) AND (d < '2022-01-01'::date))
Partition key: LIST (c)
Partitions: lt_oracle_rl1_1_prt_p3_2_prt_p1 FOR VALUES IN ('0000', '0001', '0002'),
lt_oracle_rl1_1_prt_p3_2_prt_p2 FOR VALUES IN ('0003')
lightdb@test=#
支持创建range-hash分区:
lightdb@test=# CREATE TABLE lt_oracle_rh1
lightdb@test-# (
lightdb@test(# a int,
lightdb@test(# b float,
lightdb@test(# c varchar(20),
lightdb@test(# d date,
lightdb@test(# e timestamp
lightdb@test(# )
lightdb@test-# PARTITION BY RANGE(d)
lightdb@test-# SUBPARTITION BY HASH(a)
lightdb@test-# SUBPARTITIONS 10
lightdb@test-# (
lightdb@test(# PARTITION p1 VALUES LESS THAN(to_date('2020-01-01','yyyy-mm-dd')),
lightdb@test(# PARTITION p2 VALUES LESS THAN(to_date('2021-01-01','yyyy-mm-dd')),
lightdb@test(# PARTITION p3 VALUES LESS THAN(to_date('2022-01-01','yyyy-mm-dd'))
lightdb@test(# );
CREATE TABLE
lightdb@test=# \d+ lt_oracle_rh1
Partitioned table "public.lt_oracle_rh1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | character varying(20) | | | | extended | |
d | date | | | | plain | |
e | timestamp without time zone | | | | plain | |
Partition key: RANGE (d)
Partitions: lt_oracle_rh1_1_prt_p1 FOR VALUES FROM (MINVALUE) TO ('2020-01-01'), PARTITIONED,
lt_oracle_rh1_1_prt_p2 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'), PARTITIONED,
lt_oracle_rh1_1_prt_p3 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'), PARTITIONED
lightdb@test=# \d+ lt_oracle_rh1_1_prt_p1
Partitioned table "public.lt_oracle_rh1_1_prt_p1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | character varying(20) | | | | extended | |
d | date | | | | plain | |
e | timestamp without time zone | | | | plain | |
Partition of: lt_oracle_rh1 FOR VALUES FROM (MINVALUE) TO ('2020-01-01')
Partition constraint: ((d IS NOT NULL) AND (d < '2020-01-01'::date))
Partition key: HASH (a)
Partitions: lt_oracle_rh1_1_prt_p1_2_prt_p0 FOR VALUES WITH (modulus 10, remainder 0),
lt_oracle_rh1_1_prt_p1_2_prt_p1 FOR VALUES WITH (modulus 10, remainder 1),
lt_oracle_rh1_1_prt_p1_2_prt_p2 FOR VALUES WITH (modulus 10, remainder 2),
lt_oracle_rh1_1_prt_p1_2_prt_p3 FOR VALUES WITH (modulus 10, remainder 3),
lt_oracle_rh1_1_prt_p1_2_prt_p4 FOR VALUES WITH (modulus 10, remainder 4),
lt_oracle_rh1_1_prt_p1_2_prt_p5 FOR VALUES WITH (modulus 10, remainder 5),
lt_oracle_rh1_1_prt_p1_2_prt_p6 FOR VALUES WITH (modulus 10, remainder 6),
lt_oracle_rh1_1_prt_p1_2_prt_p7 FOR VALUES WITH (modulus 10, remainder 7),
lt_oracle_rh1_1_prt_p1_2_prt_p8 FOR VALUES WITH (modulus 10, remainder 8),
lt_oracle_rh1_1_prt_p1_2_prt_p9 FOR VALUES WITH (modulus 10, remainder 9)
lightdb@test=# \d+ lt_oracle_rh1_1_prt_p2
Partitioned table "public.lt_oracle_rh1_1_prt_p2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | character varying(20) | | | | extended | |
d | date | | | | plain | |
e | timestamp without time zone | | | | plain | |
Partition of: lt_oracle_rh1 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01')
Partition constraint: ((d IS NOT NULL) AND (d >= '2020-01-01'::date) AND (d < '2021-01-01'::date))
Partition key: HASH (a)
Partitions: lt_oracle_rh1_1_prt_p2_2_prt_p0 FOR VALUES WITH (modulus 10, remainder 0),
lt_oracle_rh1_1_prt_p2_2_prt_p1 FOR VALUES WITH (modulus 10, remainder 1),
lt_oracle_rh1_1_prt_p2_2_prt_p2 FOR VALUES WITH (modulus 10, remainder 2),
lt_oracle_rh1_1_prt_p2_2_prt_p3 FOR VALUES WITH (modulus 10, remainder 3),
lt_oracle_rh1_1_prt_p2_2_prt_p4 FOR VALUES WITH (modulus 10, remainder 4),
lt_oracle_rh1_1_prt_p2_2_prt_p5 FOR VALUES WITH (modulus 10, remainder 5),
lt_oracle_rh1_1_prt_p2_2_prt_p6 FOR VALUES WITH (modulus 10, remainder 6),
lt_oracle_rh1_1_prt_p2_2_prt_p7 FOR VALUES WITH (modulus 10, remainder 7),
lt_oracle_rh1_1_prt_p2_2_prt_p8 FOR VALUES WITH (modulus 10, remainder 8),
lt_oracle_rh1_1_prt_p2_2_prt_p9 FOR VALUES WITH (modulus 10, remainder 9)
lightdb@test=# \d+ lt_oracle_rh1_1_prt_p3
Partitioned table "public.lt_oracle_rh1_1_prt_p3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+-----------------------------+-----------+----------+---------+----------+--------------+-------------
a | integer | | | | plain | |
b | double precision | | | | plain | |
c | character varying(20) | | | | extended | |
d | date | | | | plain | |
e | timestamp without time zone | | | | plain | |
Partition of: lt_oracle_rh1 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01')
Partition constraint: ((d IS NOT NULL) AND (d >= '2021-01-01'::date) AND (d < '2022-01-01'::date))
Partition key: HASH (a)
Partitions: lt_oracle_rh1_1_prt_p3_2_prt_p0 FOR VALUES WITH (modulus 10, remainder 0),
lt_oracle_rh1_1_prt_p3_2_prt_p1 FOR VALUES WITH (modulus 10, remainder 1),
lt_oracle_rh1_1_prt_p3_2_prt_p2 FOR VALUES WITH (modulus 10, remainder 2),
lt_oracle_rh1_1_prt_p3_2_prt_p3 FOR VALUES WITH (modulus 10, remainder 3),
lt_oracle_rh1_1_prt_p3_2_prt_p4 FOR VALUES WITH (modulus 10, remainder 4),
lt_oracle_rh1_1_prt_p3_2_prt_p5 FOR VALUES WITH (modulus 10, remainder 5),
lt_oracle_rh1_1_prt_p3_2_prt_p6 FOR VALUES WITH (modulus 10, remainder 6),
lt_oracle_rh1_1_prt_p3_2_prt_p7 FOR VALUES WITH (modulus 10, remainder 7),
lt_oracle_rh1_1_prt_p3_2_prt_p8 FOR VALUES WITH (modulus 10, remainder 8),
lt_oracle_rh1_1_prt_p3_2_prt_p9 FOR VALUES WITH (modulus 10, remainder 9)
lightdb@test=#
以上就是LightDB 22.3版本兼容Oracle分区的一些特性
详细语法可参考LightDB官网查看：https://www.hs.net/lightdb
更多请登录LightDB官网进行查看：https://www.hs.net/lightdb
类似资料：