统一SQL-Oracle2PostgreSQL支持分区表

统一SQL-Oracle2PostgreSQL支持分区表

【统一sql】:https://www.light-pg.com/docs/LTSQL/current/index.html

LightDB 统一SQL 是一款基于 Go 开发的 SQL 转换中间件，支持将 Oracle 常用 SQL 语法翻译转换为其他数据库的 SQL 。以主流数据库Oracle的SQL为基准，将SQL语句转换为其他信创数据库（LightDB、PostgreSQL、TDSQL、DM8、OceanBase、openGauss等）的SQL语句。目标是降低业务部门适配信创数据库的成本，让用户尽可能少地修改数据访问层代码，使基于Hibernate、JPA、MyBatis等框架的Java应用程序，基于oci、libmysqlclient、cres开发工具、hsdb的c/c++应用程序以及其它能够调用c函数的程序都能够直接切换到目标信创数据库，实现信创数据库之间的平滑迁移。

前言

Oracle2PostgreSQL需支持查询分区表的解析和改写，需与创建分区表配合（配套命名）：分区表名是通过主表名拼接’ 1_prt’再拼接分区名而成，而且只支持查询1个分区;分区表名需支持schema、双引号区分大小写的用法；

Oracle2PostgreSQL需支持创建hash分区表的解析和改写：CREATE TABLE … PARTITION BY HASH (column[,column]) [PARTITIONS hash_partition_quantity];如果不指定哈希分区数则默认创建一个分区；

Oracle2PostgreSQL需支持创建hash、list、range分区表时指定主键、唯一键约束的解析和改写：CREATE TABLE … (…, CONSTRAINT … {PRIMARY KEY|UNIQUE} …) PARTITON BY …;约束只支持列，不支持CHECK和列的嵌套表达式；

查询分区表

转化

oracle语句
SELECT … FROM table_name PARTITION (partition_name);

postgresql语句
SELECT … FROM table_name_1_prt_partition_name;

结果

通过统一sql转化之后的结果：

CREATE TABLE unisql_partition (
unisql_id NUMBER,
unisql_name VARCHAR2(50),
unisql_salary NUMBER(10,2),
unisql_hire_date DATE,
CONSTRAINT unisql_partition_pk PRIMARY KEY (unisql_id, unisql_hire_date)
)
PARTITION BY RANGE (unisql_hire_date) (
PARTITION unisql_partition_p1 VALUES LESS THAN (TO_DATE('2024-01-01', 'YYYY-MM-DD')),
PARTITION unisql_partition_p2 VALUES LESS THAN (TO_DATE('2025-01-01', 'YYYY-MM-DD'))
);

INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (1, 'John', 5000, TO_DATE('2023-01-01','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (2, 'Mary', 6000, TO_DATE( '2023-02-15','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (3, 'Tom', 7000, TO_DATE( '2023-03-20','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (4, 'Alice', 8000, TO_DATE('2023-04-10','YYYY-MM-DD'));
INSERT INTO unisql_partition (unisql_id,unisql_name,unisql_salary, unisql_hire_date)values (5, 'Bob', 9000, TO_DATE('2023-05-01','YYYY-MM-DD'));

-- 转换前Oracle SQL：
SELECT unisql_id,unisql_name,unisql_salary, unisql_hire_date FROM unisql_partition PARTITION (unisql_partition_p1);
UNISQL_ID|UNISQL_NAME|UNISQL_SALARY|UNISQL_HIRE_DATE       |
---------+-----------+-------------+-----------------------+
        1|John       |         5000|2023-01-01 00:00:00.000|
        2|Mary       |         6000|2023-02-15 00:00:00.000|
        3|Tom        |         7000|2023-03-20 00:00:00.000|
        4|Alice      |         8000|2023-04-10 00:00:00.000|
        5|Bob        |         9000|2023-05-01 00:00:00.000|

-- 转换后PostgreSQL SQL：
SELECT unisql_id,unisql_name,unisql_salary,unisql_hire_date FROM unisql_partition_1_prt_unisql_partition_p1
unisql_id|unisql_name|unisql_salary|unisql_hire_date       |
---------+-----------+-------------+-----------------------+
        1|John       |      5000.00|2023-01-01 00:00:00.000|
        2|Mary       |      6000.00|2023-02-15 00:00:00.000|
        3|Tom        |      7000.00|2023-03-20 00:00:00.000|
        4|Alice      |      8000.00|2023-04-10 00:00:00.000|
        5|Bob        |      9000.00|2023-05-01 00:00:00.000|


create table unisql_partition_by_list_test
    (
    id varchar2(15) not null,
    city varchar2(20),
    city_number NUMBER(10,2)
    )
    partition by list (city)(
    partition "P1" values ('beijing'),
    partition p2 values ('shanghai'),
    partition p3 values ('changsha'),
    partition p4 values (default)
    );

INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (1, 'beijing', 5000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (2, 'beijing', 9000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (3, 'beijing', 2000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (4, 'shanghai', 3000);
INSERT INTO unisql_partition_by_list_test (id,city, city_number)values (5, 'hangzhou', 6000);

-- 转换前Oracle SQL：
SELECT id, city, city_number FROM unisql_partition_by_list_test PARTITION ("P1");
ID|CITY   |CITY_NUMBER|
--+-------+-----------+
1 |beijing|       5000|
2 |beijing|       9000|
3 |beijing|       2000|

-- 转换后PostgreSQL SQL：
SELECT id, city,city_number FROM "unisql_partition_by_list_test_1_prt_P1"
id|city   |city_number|
--+-------+-----------+
1 |beijing|    5000.00|
2 |beijing|    9000.00|
3 |beijing|    2000.00|create table unisql_index_test(col1 int,col2 date);

创建hash分区表的改写

转化

源Oracle：
CREATE TABLE ... PARTITION BY HASH (part_params) PARTITIONS number ;

转到postgresql
CREATE TABLE ... PARTITION BY HASH (part_params);
CREATE REATE TABLE tableName_1_prt_p0 PARTITION OF tableName FOR VALUES WITH (MODULUS number, REMAINDER 0);
CREATE REATE TABLE tableName_1_prt_p1 PARTITION OF tableName FOR VALUES WITH (MODULUS number, REMAINDER 1);
...
CREATE REATE TABLE tableName_1_prt_p1 PARTITION OF tableName FOR VALUES WITH (MODULUS number, REMAINDER number-1);

结果

-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 INT, col2 VARCHAR(50), col3 TIMESTAMP )
PARTITION BY HASH (col1);

-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 bigint,col2 varchar(50),col3 timestamp) PARTITION BY HASH (col1);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p0 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 1, REMAINDER 0);

-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 int,col2 varchar(50),col3 timestamp,CONSTRAINT unisql_partition_uk UNIQUE (col1, col2))
PARTITION BY HASH (col1,col2) PARTITIONS 4;

-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 bigint,col2 varchar(50),col3 timestamp,CONSTRAINT unisql_partition_uk UNIQUE(col1, col2)) PARTITION BY HASH (col1,col2);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p0 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p1 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p2 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p3 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 3);

分区表指定主键、唯一键约束的改写

转化

创建分区表时指定主键、唯一键表级约束：CREATE TABLE … (…, CONSTRAINT … {PRIMARY KEY|UNIQUE} …) PARTITON BY …，这里值得是

转化到postgresql时候，直接转化出去，语法没有改变的

结果

-- 转换前Oracle SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 int,col2 varchar(50),col3 timestamp,CONSTRAINT unisql_partition_uk UNIQUE (col1, col2))
PARTITION BY HASH (col1,col2) PARTITIONS 4;

-- 转换后PostgreSQL SQL:
CREATE TABLE unisql_partition_by_hash_test (col1 bigint,col2 varchar(50),col3 timestamp,CONSTRAINT unisql_partition_uk UNIQUE(col1, col2)) PARTITION BY HASH (col1,col2);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p0 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p1 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p2 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE unisql_partition_by_hash_test_1_prt_p3 PARTITION OF unisql_partition_by_hash_test FOR VALUES WITH (MODULUS 4, REMAINDER 3);

总结

统一SQL分区键仅支持列，分区范围支持使用函数、字符串、常数表达，统一SQL会根据表名、分区名在转化库中创建分区表，分区表名为：表名_1_prt_分区名， Oracle 源语句HASH分区未指定分区名和分区数目时，转化到PostgreSQL表分区名默认为p0，指定多个分区数目，转化后的分区名依次递增（p0,p1,p2,…）。Oracle 和 PostgreSQL HASH分区算法不同，所以插入的数据分布到分区表表现也不一致。 分区表的唯一、主键的表级约束，必须与分区字段一致（或包含分区字段），才能保证全局的唯一性，否则无法实现转化，唯一、主键的列级约束的这个限制暂不考虑。

数据库底层HASH算法的不同，所有数据分区到各个分区表也会不同的，这边统一SQL只是提供转化方案，无法保证HASH分区插入数据之后储存的分区一致。

查询分区表，转化到PostgreSQL，这个拼接的分区表名是否有双引号，和源Oracle查询语句的分区名是否有双引号表现一致，不受表名双引号影响。