LightDB23.4 支持普通表修改为list分区表

功能介绍

为了兼容Oracle数据库的功能，在LightDB23.4版本上支持修改普通表为List分区表。这个功能只在LightDB的Oracle兼容模式下生效。

使用示例

1. 进入Oracle兼容模式的数据库

lightdb@oracle_test=# show lightdb_dblevel_syntax_compatible_type ;lightdb_dblevel_syntax_compatible_type 
----------------------------------------Oracle
(1 row)lightdb@oracle_test=# 

2. 创建普通表，插入数据

lightdb@oracle_test=# create table test_list_tab(id int, name varchar(20));
CREATE TABLE
lightdb@oracle_test=# 
lightdb@oracle_test=# insert into test_list_tab values(1,'lier');
alues(6,'chenjing');
insert into test_list_tab values(7,'sunya');
insert into test_list_tab values(0,'xika');
insert into test_list_tab values(-1,'zixingche');INSERT 0 1
lightdb@oracle_test=# insert into test_list_tab values(2,'zhangsan');
INSERT 0 1
lightdb@oracle_test=# insert into test_list_tab values(3,'wangwu');
INSERT 0 1
lightdb@oracle_test=# insert into test_list_tab values(4,'zhaosi');
INSERT 0 1
lightdb@oracle_test=# insert into test_list_tab values(5,'liuda');
INSERT 0 1
lightdb@oracle_test=# insert into test_list_tab values(6,'chenjing');
INSERT 0 1
lightdb@oracle_test=# insert into test_list_tab values(7,'sunya');
INSERT 0 1
lightdb@oracle_test=# insert into test_list_tab values(0,'xika');
INSERT 0 1
lightdb@oracle_test=# insert into test_list_tab values(-1,'zixingche');
INSERT 0 1
lightdb@oracle_test=# 

3. 将普通表修改成list分区表

lightdb@oracle_test=# ALTER TABLE test_list_tab MODIFY PARTITION BY LIST (id) (
lightdb@oracle_test(#    PARTITION even VALUES (2, 4, 6, 8),
lightdb@oracle_test(#    PARTITION odd VALUES (1, 3, 5, 7),
lightdb@oracle_test(#    PARTITION rest VALUES (DEFAULT));
ALTER TABLE
lightdb@oracle_test=# 
lightdb@oracle_test=# \d+ test_list_tabPartitioned table "public.test_list_tab"Column |         Type          | Collation | Nullable | Default | Storage  | Stats target | Description 
--------+-----------------------+-----------+----------+---------+----------+--------------+-------------id     | integer               |           |          |         | plain    |              | name   | character varying(20) |           |          |         | extended |              | 
Partition key: LIST (id)
Partitions: "test_list_tab$p$even" FOR VALUES IN (2, 4, 6, 8),"test_list_tab$p$odd" FOR VALUES IN (1, 3, 5, 7),"test_list_tab$p$rest" DEFAULTlightdb@oracle_test=# 

4. 查询各分区表中的数据

lightdb@oracle_test=# select * from test_list_tab$p$even;id |   name   
----+----------2 | zhangsan4 | zhaosi6 | chenjing
(3 rows)lightdb@oracle_test=# select * from test_list_tab$p$odd;id |  name  
----+--------1 | lier3 | wangwu5 | liuda7 | sunya
(4 rows)lightdb@oracle_test=# select * from test_list_tab$p$rest;id |   name    
----+-----------0 | xika-1 | zixingche
(2 rows)lightdb@oracle_test=#