概述
在信创移植DDL SQL语句中，有来源于Oracle数据库的SQL语句。
在Oracle中，创建分区表时，可以为每个分区显示的指定nocompress/compress关键字，分别表示不压缩/压缩，一般不显示指定时默认为nocompress状态。
LightDB-x在23.3版本中创建分区表对nocompress compress关键字做了支持，来保证DDL语句正常执行，减少业务移植难度，降低业务移植成本。
案例演示
环境准备
进入ltsql交互界面执行如下命令：
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 varchar2(20)
) 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')
);
查看分区情况
lightdb@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 varying | | | | extended | |
Partition key: LIST (e)
Partitions: "lt_oracle_partition_list1$p$p1" FOR VALUES IN ('0001', '0002', '0003', '0004', '0005'),
"lt_oracle_partition_list1$p$p2" FOR VALUES IN ('0006', '0007', '0008', '0009'),
"lt_oracle_partition_list1$p$p3" FOR VALUES IN ('0010', '0011')
Range分区
创建Range分区
CREATE TABLE lt_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('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'))
);
查看分区情况
lightdb@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 varying(20) | | | | extended | |
Partition key: RANGE (c)
Partitions: "lt_oracle_interval1$p$p1" FOR VALUES FROM (MINVALUE) TO ('2020-01-01'),
"lt_oracle_interval1$p$p2" FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'),
"lt_oracle_interval1$p$p3" FOR VALUES FROM ('2021-01-01') TO ('2022-01-01')
Hash分区
创建分区
CREATE TABLE oracle_hash1
(
a int,
b float,
c VARCHAR(20),
d DATE,
e timestamp
) PARTITION BY HASH (a) PARTITIONS 3 ;
CREATE TABLE oracle_hash2
(
a int,
b float,
c VARCHAR(20),
d DATE,
e timestamp
) PARTITION BY HASH (a) PARTITIONS 3 NOCOMPRESS ;
CREATE TABLE oracle_hash3
(
a int,
b float,
c VARCHAR(20),
d DATE,
e timestamp
) PARTITION BY HASH (a) PARTITIONS 3 COMPRESS ;
查看分区情况
lightdb@test_oracle=# \d+ oracle_hash1
Partitioned table "public.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: "oracle_hash1$p$p0" FOR VALUES WITH (modulus 3, remainder 0),
"oracle_hash1$p$p1" FOR VALUES WITH (modulus 3, remainder 1),
"oracle_hash1$p$p2" FOR VALUES WITH (modulus 3, remainder 2)
lightdb@test_oracle=# \d+ oracle_hash2
Partitioned table "public.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: "oracle_hash2$p$p0" FOR VALUES WITH (modulus 3, remainder 0),
"oracle_hash2$p$p1" FOR VALUES WITH (modulus 3, remainder 1),
"oracle_hash2$p$p2" FOR VALUES WITH (modulus 3, remainder 2)
lightdb@test_oracle=# \d+ oracle_hash3
Partitioned table "public.oracle_hash3"
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: "oracle_hash3$p$p0" FOR VALUES WITH (modulus 3, remainder 0),
"oracle_hash3$p$p1" FOR VALUES WITH (modulus 3, remainder 1),
"oracle_hash3$p$p2" FOR VALUES WITH (modulus 3, remainder 2)
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)
);
查看分区情况
lightdb@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$p$p1" FOR VALUES FROM (MINVALUE) TO (10), PARTITIONED,
"lt_oracle_rl3$p$p2" FOR VALUES FROM (10) TO (20), PARTITIONED,
"lt_oracle_rl3$p$p3" FOR VALUES FROM (20) TO (30), PARTITIONED
lightdb@test_oracle=# \d+ lt_oracle_rl3$p$p1
Partitioned table "public.lt_oracle_rl3$p$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$p$p1_p1" FOR VALUES IN ('1.1', '2.1', '3.1'),
"lt_oracle_rl3$p$p1_p2" FOR VALUES IN ('4.1')
lightdb@test_oracle=# \d+ lt_oracle_rl3$p$p2
Partitioned table "public.lt_oracle_rl3$p$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$p$p2_p1" FOR VALUES IN ('1.1', '2.1', '3.1'),
"lt_oracle_rl3$p$p2_p2" FOR VALUES IN ('4.1')
lightdb@test_oracle=# \d+ lt_oracle_rl3$p$p3
Partitioned table "public.lt_oracle_rl3$p$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$p$p3_p1" FOR VALUES IN ('1.1', '2.1', '3.1'),
"lt_oracle_rl3$p$p3_p2" FOR VALUES IN ('4.1')
Range + Hash分区
创建分区
create table lt_oracle_partition_rh1
(
transaction_id number,
item_id number(8) not null,
item_description varchar(300),
transaction_date DATE
)
partition by range(transaction_date)
subpartition by hash(transaction_id)
subpartitions 2
(
partition p1 values less than(to_date('2020-01-01','yyyy-mm-dd')) COMPRESS ,
partition p2 values less than(to_date('2021-01-01','yyyy-mm-dd')) NOCOMPRESS ,
partition p3 values less than(to_date('2022-01-01','yyyy-mm-dd'))
);
查看分区
lightdb@test_oracle=# \d+ lt_oracle_partition_rh1
Partitioned table "public.lt_oracle_partition_rh1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
transaction_id | numeric | | | | main | |
item_id | numeric(8,0) | | not null | | main | |
item_description | character varying(300) | | | | extended | |
transaction_date | date | | | | plain | |
Partition key: RANGE (transaction_date)
Partitions: "lt_oracle_partition_rh1$p$p1" FOR VALUES FROM (MINVALUE) TO ('2020-01-01'), PARTITIONED,
"lt_oracle_partition_rh1$p$p2" FOR VALUES FROM ('2020-01-01') TO ('2021-01-01'), PARTITIONED,
"lt_oracle_partition_rh1$p$p3" FOR VALUES FROM ('2021-01-01') TO ('2022-01-01'), PARTITIONED
lightdb@test_oracle=# \d+ lt_oracle_partition_rh1$p$p1
Partitioned table "public.lt_oracle_partition_rh1$p$p1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
transaction_id | numeric | | | | main | |
item_id | numeric(8,0) | | not null | | main | |
item_description | character varying(300) | | | | extended | |
transaction_date | date | | | | plain | |
Partition of: lt_oracle_partition_rh1 FOR VALUES FROM (MINVALUE) TO ('2020-01-01')
Partition constraint: ((transaction_date IS NOT NULL) AND (transaction_date < '2020-01-01'::date))
Partition key: HASH (transaction_id)
Partitions: "lt_oracle_partition_rh1$p$p1_p0" FOR VALUES WITH (modulus 2, remainder 0),
"lt_oracle_partition_rh1$p$p1_p1" FOR VALUES WITH (modulus 2, remainder 1)
lightdb@test_oracle=# \d+ lt_oracle_partition_rh1$p$p2
Partitioned table "public.lt_oracle_partition_rh1$p$p2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
transaction_id | numeric | | | | main | |
item_id | numeric(8,0) | | not null | | main | |
item_description | character varying(300) | | | | extended | |
transaction_date | date | | | | plain | |
Partition of: lt_oracle_partition_rh1 FOR VALUES FROM ('2020-01-01') TO ('2021-01-01')
Partition constraint: ((transaction_date IS NOT NULL) AND (transaction_date >= '2020-01-01'::date) AND (transaction_date < '2021-01-01'::date))
Partition key: HASH (transaction_id)
Partitions: "lt_oracle_partition_rh1$p$p2_p0" FOR VALUES WITH (modulus 2, remainder 0),
"lt_oracle_partition_rh1$p$p2_p1" FOR VALUES WITH (modulus 2, remainder 1)
lightdb@test_oracle=# \d+ lt_oracle_partition_rh1$p$p3
Partitioned table "public.lt_oracle_partition_rh1$p$p3"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
------------------+------------------------+-----------+----------+---------+----------+--------------+-------------
transaction_id | numeric | | | | main | |
item_id | numeric(8,0) | | not null | | main | |
item_description | character varying(300) | | | | extended | |
transaction_date | date | | | | plain | |
Partition of: lt_oracle_partition_rh1 FOR VALUES FROM ('2021-01-01') TO ('2022-01-01')
Partition constraint: ((transaction_date IS NOT NULL) AND (transaction_date >= '2021-01-01'::date) AND (transaction_date < '2022-01-01'::date))
Partition key: HASH (transaction_id)
Partitions: "lt_oracle_partition_rh1$p$p3_p0" FOR VALUES WITH (modulus 2, remainder 0),
"lt_oracle_partition_rh1$p$p3_p1" FOR VALUES WITH (modulus 2, remainder 1)
结论
在LightDB23.3版本中，创建分区表无论是List分区、Range分区、Hash分区、Range+List分区以及Range+Hash分区都可以添加nocompress、compress关键字，并正常执行。
9617
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
