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