上次写到MySQL在分区上做了加字段以后不能交换分区，看看Oracle怎么样？

表和MySQL一模一样，只是这里用了自动分区。（既然有这个功能为什么不用，还用了自增和MySQL一样了）

SQL> create table pa(
2 id int generated as identity (start with 1 increment by 1),
3 day date,
4 a int,
5 primary key (id)
6 )
7 partition by range(day)
8 interval (numtodsinterval(1,'day'))
9 (
10 partition p1 values less than (to_date('20200101','yyyyMMdd'))
11 );

Table created

写入几条数据，造成分区扩展。
SQL>
SQL> insert into pa (day,a) values (to_date('2020-05-01','yyyy-mm-dd'),1);

1 row inserted


SQL> insert into pa (day,a) values (to_date('2021-05-01','yyyy-mm-dd'),1);

1 row inserted


SQL> insert into pa (day,a) values (to_date('2022-05-01','yyyy-mm-dd'),1);

1 row inserted


SQL> insert into pa (day,a) values (to_date('2023-05-01','yyyy-mm-dd'),1);

1 row inserted


SQL> insert into pa (day,a) values (to_date('2024-05-01','yyyy-mm-dd'),1);

1 row inserted


SQL> insert into pa (day,a) values (to_date('2025-05-01','yyyy-mm-dd'),1);

1 row inserted


SQL> insert into pa (day,a) values (to_date('2026-05-01','yyyy-mm-dd'),1);

1 row inserted


SQL> commit;

Commit complete

然后建立一个一模一样结构的非分区表。记住一模一样。

create table a(
id int generated as identity (start with 1 increment by 1),
day date,
a int,
primary key (id)
);

Table created

查询分区表数据

SQL> select * from pa;
Warning: connection was lost and re-established

ID DAY A
--------------------------------------- ----------- ---------------------------------------
1 2020/5/1 1
2 2021/5/1 1
3 2022/5/1 1
4 2023/5/1 1
5 2024/5/1 1
6 2025/5/1 1
7 2026/5/1 1

7 rows selected


查询分区情况

SQL> select table_name,partition_name from user_tab_partitions;

TABLE_NAME PARTITION_NAME
-------------------------------------------------------------------------------- --------------------------------------------------------------------------------
PA P1
PA SYS_P1193
PA SYS_P1194
PA SYS_P1195
PA SYS_P1196
PA SYS_P1197
PA SYS_P1198
PA SYS_P1199

8 rows selected


测试分区有数据。
SQL> select * from pa partition (SYS_P1193);

ID DAY A
--------------------------------------- ----------- ---------------------------------------
1 2020/5/1 1

和MySQL一样交互。语法都一样。没有问题。

SQL> ALTER TABLE pa EXCHANGE PARTITION sys_p1193 WITH TABLE a;

Table altered

第一阶段完全符合预期。

SQL> select * from pa;

ID DAY A
--------------------------------------- ----------- ---------------------------------------
2 2021/5/1 1
3 2022/5/1 1
4 2023/5/1 1
5 2024/5/1 1
6 2025/5/1 1
7 2026/5/1 1

6 rows selected


SQL> select * from a;

ID DAY A
--------------------------------------- ----------- ---------------------------------------
1 2020/5/1 1

SQL> 

++++++++++++++++++++++++++++++++++++++++

下面就是看看加字段的影响了

SQL> alter table pa add new int ;  （这里注意，不能有默认值，也不能使用快速加字段的特性）

Table altered


SQL> select * from pa;

ID DAY A NEW
--------------------------------------- ----------- --------------------------------------- ---------------------------------------
2 2021/5/1 1
3 2022/5/1 1
4 2023/5/1 1
5 2024/5/1 1
6 2025/5/1 1
7 2026/5/1 1

6 rows selected


SQL>
SQL> create table b(
2 id int generated as identity (start with 1 increment by 1),
3 day date,
4 a int,
5 new int,
6 primary key (id)
7 );

Table created


SQL> ALTER TABLE pa EXCHANGE PARTITION sys_p1194 WITH TABLE b;

Table altered


SQL> select * from pa;

ID DAY A NEW
--------------------------------------- ----------- --------------------------------------- ---------------------------------------
3 2022/5/1 1
4 2023/5/1 1
5 2024/5/1 1
6 2025/5/1 1
7 2026/5/1 1



SQL> select * from b;

ID DAY A NEW
--------------------------------------- ----------- --------------------------------------- ---------------------------------------
2 2021/5/1 1 

实验成功。即Oracle在不带default 或者不带not null的时候可以成功。

但是注意一旦带上默认值或者用快速加字段的特性，那么就和MySQL一样了。那么问题又来了，这种要去做交互分区必然是大表，大表不用快速加字段，还有什么意义？

所以这种低价值的流水接口表要求上线前就定义好，不再进行增加字段的DDL。