【LightDB 22.4版本新特性】Oracle分区语法兼容-普通表转分区表

原创已于 2022-11-07 14:21:03 修改 · 475 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #数据库

于 2022-11-07 14:19:08 首次发布

LightDB 同时被 2 个专栏收录

34 篇文章

订阅专栏

postgresql

32 篇文章

订阅专栏

本文介绍如何使用Oracle12c将普通表在线转换为范围分区表，并演示了具体的步骤与语法。通过实例展示了数据如何按新分区结构重新分布。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1. 普通表转分区表

由于实际项目需要，需要兼容Oracle12c在线改普通表为分区表。

Oracle语法示例如下：

alter table t1 modify partition by range (a) 
(
    partition p1 values less than (10),
    partition p2 values less than (20),
    partition p3 values less than (MAXVALUE)
)online update indexes
(
    i1 global,
    i2 local
);

根据需求总结如下：

被转表为普通表。
仅支持转为range分区表(自动分区不支持)。
支持online语法。
支持update indexes语法。

实现效果如下：

1. 先定义一个普通表

lightdb@lt_test=# CREATE TABLE lt_oracle_mod_partition1
lightdb@lt_test-# (
lightdb@lt_test(#     a int,
lightdb@lt_test(#     b float,
lightdb@lt_test(#     c date,
lightdb@lt_test(#     d timestamp
lightdb@lt_test(# );
CREATE TABLE

2. 插入一些数据

lightdb@lt_test=# begin  
lightdb@lt_test$# for n in 1..10000 LOOP  
lightdb@lt_test$# 

lightdb@lt_test$# insert into lt_oracle_mod_partition1 VALUES(1,1.5,to_date('2022-01-05','yyyy-mm-dd'),to_timestamp('2022-01-05 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));
lightdb@lt_test$# 

lightdb@lt_test$# insert into lt_oracle_mod_partition1 VALUES(2,2.5,to_date('2022-02-05','yyyy-mm-dd'),to_timestamp('2022-02-05 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));
lightdb@lt_test$# 

lightdb@lt_test$# insert into lt_oracle_mod_partition1 VALUES(3,3.5,to_date('2022-03-05','yyyy-mm-dd'),to_timestamp('2022-03-05 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));
lightdb@lt_test$# 

lightdb@lt_test$# insert into lt_oracle_mod_partition1 VALUES(4,4.5,to_date('2022-04-05','yyyy-mm-dd'),to_timestamp('2022-04-05 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));
lightdb@lt_test$# 

lightdb@lt_test$# insert into lt_oracle_mod_partition1 VALUES(5,5.5,to_date('2022-05-05','yyyy-mm-dd'),to_timestamp('2022-05-05 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));
lightdb@lt_test$# 

lightdb@lt_test$# insert into lt_oracle_mod_partition1 VALUES(6,6.5,to_date('2022-06-05','yyyy-mm-dd'),to_timestamp('2022-06-05 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));
lightdb@lt_test$# 

lightdb@lt_test$# insert into lt_oracle_mod_partition1 VALUES(7,7.5,to_date('2022-07-05','yyyy-mm-dd'),to_timestamp('2022-07-05 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));
lightdb@lt_test$# 

lightdb@lt_test$# insert into lt_oracle_mod_partition1 VALUES(8,8.5,to_date('2022-08-05','yyyy-mm-dd'),to_timestamp('2022-08-05 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));
lightdb@lt_test$# 

lightdb@lt_test$# insert into lt_oracle_mod_partition1 VALUES(9,9.5,to_date('2022-09-05','yyyy-mm-dd'),to_timestamp('2022-09-05 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));
lightdb@lt_test$# 

lightdb@lt_test$# insert into lt_oracle_mod_partition1 VALUES(10,10.5,to_date('2022-10-05','yyyy-mm-dd'),to_timestamp('2022-10-05 12:00:00', 'yyyy-mm-dd hh24:mi:ss'));
lightdb@lt_test$# end loop;   
lightdb@lt_test$# end;
lightdb@lt_test$# /
DO

3. 建立索引

lightdb@lt_test=# create index lt_oracle_mod_partition1_i1 on lt_oracle_mod_partition1(a);
CREATE INDEX
lightdb@lt_test=# create index lt_oracle_mod_partition1_i2 on lt_oracle_mod_partition1(b);
CREATE INDEX
lightdb@lt_test=# create index lt_oracle_mod_partition1_i3 on lt_oracle_mod_partition1(c);
CREATE INDEX
lightdb@lt_test=# create index lt_oracle_mod_partition1_i4 on lt_oracle_mod_partition1(d);
CREATE INDEX

4. 查看一下表信息

lightdb@lt_test=# \d+ lt_oracle_mod_partition1
                                   Table "public.lt_oracle_mod_partition1"
 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   |              | 
Indexes:
    "lt_oracle_mod_partition1_i1" btree (a)
    "lt_oracle_mod_partition1_i2" btree (b)
    "lt_oracle_mod_partition1_i3" btree (c)
    "lt_oracle_mod_partition1_i4" btree (d)
Access method: heap

5. 在线转为分区表

lightdb@lt_test=# alter table lt_oracle_mod_partition1 modify partition by range (a) 
lightdb@lt_test-# (
lightdb@lt_test(#     partition lt_oracle_mod_partition1_p1 values less than (3),
lightdb@lt_test(#     partition lt_oracle_mod_partition1_p2 values less than (6),
lightdb@lt_test(#     partition lt_oracle_mod_partition1_p3 values less than (MAXVALUE)
lightdb@lt_test(# )ONLINE UPDATE INDEXES
lightdb@lt_test-# (
lightdb@lt_test(#     lt_oracle_mod_partition1_i1 global,
lightdb@lt_test(#     lt_oracle_mod_partition1_i2 local,
lightdb@lt_test(#     lt_oracle_mod_partition1_i3 local,
lightdb@lt_test(#     lt_oracle_mod_partition1_i4 global
lightdb@lt_test(# );
ALTER TABLE

6. 看一下新表的表结构

lightdb@lt_test=# \d+ lt_oracle_mod_partition1
                             Partitioned table "public.lt_oracle_mod_partition1"
 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)
Indexes:
    "lt_oracle_mod_partition1_i1" btree (a)
    "lt_oracle_mod_partition1_i2" btree (b)
    "lt_oracle_mod_partition1_i3" btree (c)
    "lt_oracle_mod_partition1_i4" btree (d)
Partitions: lt_oracle_mod_partition1_p1 FOR VALUES FROM (MINVALUE) TO (3),
            lt_oracle_mod_partition1_p2 FOR VALUES FROM (3) TO (6),
            lt_oracle_mod_partition1_p3 FOR VALUES FROM (6) TO (MAXVALUE)

可以看出表名与索引名均保持不变。

7. 看一下表数据信息

lightdb@lt_test=# select count(*) from lt_oracle_mod_partition1;
 count  
--------
 100000
(1 row)

lightdb@lt_test=# select count(*) from lt_oracle_mod_partition1_p1;
 count 
-------
 20000
(1 row)

lightdb@lt_test=# select count(*) from lt_oracle_mod_partition1_p2;
 count 
-------
 30000
(1 row)

lightdb@lt_test=# select count(*) from lt_oracle_mod_partition1_p3;
 count 
-------
 50000
(1 row)

可以看出原先的表数据会按照新的分区结构分配在新的分区表上。

8. 再看一下分区表结构

lightdb@lt_test=# \d+ lt_oracle_mod_partition1_p1
                                  Table "public.lt_oracle_mod_partition1_p1"
 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 of: lt_oracle_mod_partition1 FOR VALUES FROM (MINVALUE) TO (3)
Partition constraint: ((a IS NOT NULL) AND (a < 3))
Indexes:
    "lt_oracle_mod_partition1_p1_a_idx" btree (a)
    "lt_oracle_mod_partition1_p1_b_idx" btree (b)
    "lt_oracle_mod_partition1_p1_c_idx" btree (c)
    "lt_oracle_mod_partition1_p1_d_idx" btree (d)
Access method: heap

lightdb@lt_test=# \d+ lt_oracle_mod_partition1_p2
                                  Table "public.lt_oracle_mod_partition1_p2"
 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 of: lt_oracle_mod_partition1 FOR VALUES FROM (3) TO (6)
Partition constraint: ((a IS NOT NULL) AND (a >= 3) AND (a < 6))
Indexes:
    "lt_oracle_mod_partition1_p2_a_idx" btree (a)
    "lt_oracle_mod_partition1_p2_b_idx" btree (b)
    "lt_oracle_mod_partition1_p2_c_idx" btree (c)
    "lt_oracle_mod_partition1_p2_d_idx" btree (d)
Access method: heap

lightdb@lt_test=# \d+ lt_oracle_mod_partition1_p3
                                  Table "public.lt_oracle_mod_partition1_p3"
 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 of: lt_oracle_mod_partition1 FOR VALUES FROM (6) TO (MAXVALUE)
Partition constraint: ((a IS NOT NULL) AND (a >= 6))
Indexes:
    "lt_oracle_mod_partition1_p3_a_idx" btree (a)
    "lt_oracle_mod_partition1_p3_b_idx" btree (b)
    "lt_oracle_mod_partition1_p3_c_idx" btree (c)
    "lt_oracle_mod_partition1_p3_d_idx" btree (d)
Access method: heap