上次写到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。
