lightdb-a 分区表支持nocompress compress关键字

bit-byte

于 2023-08-09 15:03:08 发布

阅读量141

点赞数

分类专栏： lightdb 文章标签： postgresql 数据库

本文链接：https://blog.csdn.net/z465759695/article/details/132188549

版权

lightdb 专栏收录该内容

39 篇文章 0 订阅

订阅专栏

文章目录

概述
案例演示
结论

概述

在信创移植DDL SQL语句中，有来源于Oracle数据库的SQL语句。

在Oracle中，创建分区表时，可以为每个分区显示的指定nocompress/compress关键字，分别表示不压缩/压缩，一般不显示指定时默认为nocompress状态。

LightDB-a在23.3版本中创建分区表对nocompress compress关键字做了支持，来保证DDL语句正常执行，减少业务移植难度，降低业务移植成本。

案例演示

环境准备

进入psql交互界面执行如下命令：

create database test_oracle lightdb_syntax_compatible_type oracle;
\c test_oracle

List分区

创建List分区

CREATE TABLE lt_oracle_partition_list1
(
    a int,
    b float,
    c date,
    d timestamp,
    e char(10)
) PARTITION BY LIST(e)
(
    PARTITION p1 VALUES ('0001', '0002', '0003', '0004', '0005') compress,
    PARTITION p2 VALUES ('0006', '0007', '0008', '0009') nocompress,
    PARTITION p3 VALUES ('0010', '0011')
) distributed by (a);

查看分区情况

test_oracle=# \d+ lt_oracle_partition_list1
                             Partitioned table "public.lt_oracle_partition_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(10)               |           |          |         | extended |              | 
Partition key: LIST (e)
Partitions: lt_oracle_partition_list1_1_prt_p1 FOR VALUES IN ('0001      ', '0002      ', '0003      ', '0004      ', '0005      '),
            lt_oracle_partition_list1_1_prt_p2 FOR VALUES IN ('0006      ', '0007      ', '0008      ', '0009      '),
            lt_oracle_partition_list1_1_prt_p3 FOR VALUES IN ('0010      ', '0011      ')
Distributed by: (a)
Access method: heap

Range分区

创建Range分区

CREATE TABLE lt_oracle_interval1
(
    a int,
    b float,
    c date,
    d timestamp,
    e char(10)
)PARTITION BY RANGE (c)
(
        PARTITION p1 VALUES LESS THAN(to_date('2020-01-01', 'yyyy-mm-dd')) nocompress,
        PARTITION p2 VALUES LESS THAN(to_date('2021-01-01', 'yyyy-mm-dd')) compress,
        PARTITION p3 VALUES LESS THAN(to_date('2022-01-01', 'yyyy-mm-dd'))
) distributed by (a);

查看分区情况

test_oracle=# \d+ lt_oracle_interval1
                                Partitioned table "public.lt_oracle_interval1"
 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(10)               |           |          |         | extended |              | 
Partition key: RANGE (c)
Partitions: lt_oracle_interval1_1_prt_p1 FOR VALUES FROM (MINVALUE) TO ('2020-01-01'),
            lt_oracle_interval1_1_prt_p2 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
            lt_oracle_interval1_1_prt_p3 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01')
Distributed by: (a)
Access method: heap

Hash分区

创建分区

CREATE TABLE lt_oracle_partition_hash1
(
    a int,
    b float,
    c VARCHAR(20),
    d DATE,
    e timestamp
) PARTITION BY HASH (a)
(
	PARTITION p1,
    PARTITION p2 compress,
	PARTITION p3 nocompress
) distributed by (a);

查看分区情况

test_oracle=# \d+ lt_oracle_partition_hash1
                             Partitioned table "public.lt_oracle_partition_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_partition_hash1_1_prt_p1 FOR VALUES WITH (modulus 3, remainder 0),
            lt_oracle_partition_hash1_1_prt_p2 FOR VALUES WITH (modulus 3, remainder 1),
            lt_oracle_partition_hash1_1_prt_p3 FOR VALUES WITH (modulus 3, remainder 2)
Distributed by: (a)
Access method: heap

Range + List分区

创建分区

CREATE TABLE lt_oracle_rl3
(
    a int,
    b float,
    c varchar(20),
    d date,
    e timestamp
) PARTITION BY RANGE(a)
SUBPARTITION BY LIST (b)
SUBPARTITION TEMPLATE
(
    SUBPARTITION p1 VALUES (1.1, 2.1, 3.1) COMPRESS ,
    SUBPARTITION p2 VALUES (4.1) NOCOMPRESS
)
(
    PARTITION p1 VALUES LESS THAN(10) COMPRESS ,
    PARTITION p2 VALUES LESS THAN(20) NOCOMPRESS ,
    PARTITION p3 VALUES LESS THAN(30)
) distributed by (a);

查看分区情况

test_oracle=# \d+ lt_oracle_rl3
                                   Partitioned table "public.lt_oracle_rl3"
 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 (a)
Partitions: lt_oracle_rl3_1_prt_p1 FOR VALUES FROM (MINVALUE) TO (10), PARTITIONED,
            lt_oracle_rl3_1_prt_p2 FOR VALUES FROM (10) TO (20), PARTITIONED,
            lt_oracle_rl3_1_prt_p3 FOR VALUES FROM (20) TO (30), PARTITIONED
Distributed by: (a)
Access method: heap

test_oracle=# \d+ lt_oracle_rl3_1_prt_p1
                               Partitioned table "public.lt_oracle_rl3_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_rl3 FOR VALUES FROM (MINVALUE) TO (10)
Partition constraint: ((a IS NOT NULL) AND (a < 10))
Partition key: LIST (b)
Partitions: lt_oracle_rl3_1_prt_p1_2_prt_p1 FOR VALUES IN ('1.1', '2.1', '3.1'),
            lt_oracle_rl3_1_prt_p1_2_prt_p2 FOR VALUES IN ('4.1')
Distributed by: (a)
Access method: heap

test_oracle=# \d+ lt_oracle_rl3_1_prt_p2
                               Partitioned table "public.lt_oracle_rl3_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_rl3 FOR VALUES FROM (10) TO (20)
Partition constraint: ((a IS NOT NULL) AND (a >= 10) AND (a < 20))
Partition key: LIST (b)
Partitions: lt_oracle_rl3_1_prt_p2_2_prt_p1 FOR VALUES IN ('1.1', '2.1', '3.1'),
            lt_oracle_rl3_1_prt_p2_2_prt_p2 FOR VALUES IN ('4.1')
Distributed by: (a)
Access method: heap

test_oracle=# \d+ lt_oracle_rl3_1_prt_p3
                               Partitioned table "public.lt_oracle_rl3_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_rl3 FOR VALUES FROM (20) TO (30)
Partition constraint: ((a IS NOT NULL) AND (a >= 20) AND (a < 30))
Partition key: LIST (b)
Partitions: lt_oracle_rl3_1_prt_p3_2_prt_p1 FOR VALUES IN ('1.1', '2.1', '3.1'),
            lt_oracle_rl3_1_prt_p3_2_prt_p2 FOR VALUES IN ('4.1')
Distributed by: (a)
Access method: heap

增加分区

创建并增加分区

create table tt6(id int)
PARTITION by range(id)(
    partition t1 VALUES LESS THAN (202000) nocompress,
    partition t2 VALUES LESS THAN (202001) compress,
    partition t3 VALUES LESS THAN (202002)
) distributed by (id);

alter table tt6 add partition t4 values less than (202003) nocompress;
alter table tt6 add partition t5 values less than (202004) compress;
alter table tt6 add partition t6 values less than (202005) ;

查看分区

test_oracle=# \d+ tt6
                              Partitioned table "public.tt6"
 Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description 
--------+---------+-----------+----------+---------+---------+--------------+-------------
 id     | integer |           |          |         | plain   |              | 
Partition key: RANGE (id)
Partitions: tt6_1_prt_t1 FOR VALUES FROM (MINVALUE) TO (202000),
            tt6_1_prt_t2 FOR VALUES FROM (202000) TO (202001),
            tt6_1_prt_t3 FOR VALUES FROM (202001) TO (202002),
            tt6_1_prt_t4 FOR VALUES FROM (202002) TO (202003),
            tt6_1_prt_t5 FOR VALUES FROM (202003) TO (202004),
            tt6_1_prt_t6 FOR VALUES FROM (202004) TO (202005)
Distributed by: (id)
Access method: heap