Oracle有沒有MySQL的分割槽DDL遇到的問題

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