【数据库SQL】分区的新建与删除（Oracle）

如果数据库中的一张表在建表的时候通过partitioned by()指定了表中的某字段为分区，那么它就是一张分区表。

向分区表中插入数据时，必须通过partition on()指明要把数据插入到哪个分区中，如果不指明分区则无法插入数据。

查看表中有哪些分区

查出所有的主分区的信息

select * from user_tab_partitions where TABLE_NAME = '表名'

查出所有的子分区的信息

select * from user_tab_subpartitions where TABLE_NAME = '表名'

由此可查出partition_name（主分区名称）和subpartition_name（子分区名称）字段中是否有对应数据，以此来判断分区是否已经存在。

如何判断某一分区是否已经存在？

语法：

select count(*) from USER_TAB_PARTITIONS where table_name = '表名' and partition_name = '分区名'

如果得到的结果大于0，说明该分区已经存在。

user_tab_partitions 是一张系统表，专门用来记录分区的信息。其中有一个partition_name字段。

示例：

select count(*) from USER_TAB_PARTITIONS where table_name = 'DM_TABLE_M_V ' and partition_name = 'PART202104'

我们可以仔细查看“分区表user_tab_partitions”中记录的全部具体信息：

select * from user_tab_partitions where table_name = 'DM_TABLE_M_V ' and partition_name = 'PART202102'

如何判断某一子分区是否已经存在？

分为两步：
①判断主分区是否存在；
②判断主分区下的子分区是否存在。

第1步的判断方法与前边写的一样，就是在分区表user_tab_partitions中根据名称查看主分区是否存在。

关键是判断第2步，语法如下：

select count(*) from user_tab_subpartitions where table_name = '表名' and subpartition_name='子分区名';

如果得到的结果大于0，说明该子分区已经存在。

user_tab_subpartitions 是一张系统表，专门用来记录子分区的信息。其中有一个partition_name字段和一个subpartition_name字段。

示例：

select count(*) from user_tab_subpartitions where table_name = 'DM_TABLE_M_V ' and subpartition_name = 'PART202101_SUBPART_01'

我们可以仔细查看“子分区表user_tab_subpartitions”中记录的全部具体信息：

select * from user_tab_subpartitions where table_name = 'DM_TABLE_M_V ' and subpartition_name = 'PART202101_SUBPART_01'

---

新建分区

给一张表新建分区的方法如下。

Oracle数据库上，新建分区的写法是这样的：

alter table 表名 add partition 分区名 values (‘分区的值’)

示例：

alter table DM_TABLE_M_V add partition PART_202205 values ('202205')

有一些其他的数据库，有另外一种写法：

alter table 表名 add partition 分区名 (字段=‘分区的值’)

注意：这里没有写values

示例：

alter table DM_TABLE_S_D add partition PART_202206 (MONTH_ID='202206')

删除分区

删除分区有两种含义：
① 保留分区，只是把分区中的内容全部删除；
② 直接删除分区。

如果是想把分区中的内容全部删除，使用truncate，语法如下：

alter table 表名 truncate partition 分区名

示例：

alter table DM_TABLE_M_V truncate partition PART_202205

如果是想直接删除分区，使用drop，语法如下：

alter table 表名 drop partition 分区名

示例：

alter table DM_TABLE_S_D drop partition PART_202206 

---

包含子分区的情况

对于这种情况，最好是创建主分区的同时一并创建子分区。如下所示：

--创建主分区并同时创建子分区

--语法：
alter table 表名 add partition 主分区名 values ('主分区值') (subpartition 子分区名 values ('子分区值'))

--实例：
alter table S_KPI_DATA_D add partition PART202102 values ('202102') (subpartition PART202102_SUBPART_01 values ('01'))

如果在新建分区的时候，只建主分区不建子分区的话，系统会自动创建一个默认子分区：

--只创建主分区（系统会自动创建一个默认子分区）
alter table spreport_kpi_data_single_dim_d add partition PART202102 values ('202102')

最好不再要像上面这样写（只建主分区）！如果系统自动创建了一个默认子分区的话，之后我们就不能自己往这个主分区下边添加新的子分区了！

最好还是在创建主分区的同时，也创建至少一个子分区。

创建子分区（在指定主分区下）

只有第一次是创建主分区（并同时创建子分区），之后再新建子分区的时候，对这个主分区不再是“创建”而是“修改”。不能再写add而是要写modify！

语法如下：

--在指定主分区下创建子分区
alter table 表名 modify partition 主分区名 add subpartition 子分区名 values ('子分区值');

注意对主分区是modify ，对子分区是add 。

实例：

--第一次（add）
alter table S_KPI_DATA_D add partition PART202204 values ('202204') (subpartition PART202204_SUBPART_01 values ('01'));

--第二次及之后（modify）
alter table S_KPI_DATA_D modify partition PART202204 add subpartition PART202204_SUBPART_02 values ('02');
alter table S_KPI_DATA_D modify partition PART202204 add subpartition PART202204_SUBPART_03 values ('03');

主分区的截断和删除

语法：

alter table 表名 truncate partition 主分区名    --截断
alter table 表名 drop partition 主分区名        --删除

实例：

alter table S_KPI_DATA_D truncate partition PART202101    --截断
alter table S_KPI_DATA_D drop partition PART202101        --删除

子分区的截断和删除

语法：

alter table 表名 truncate subpartition 子分区名 update indexes   --截断
alter table 表名 drop subpartition 子分区名       --删除

实例：

alter table S_KPI_DATA_D truncate subpartition PART202101_SUBPART_02 update indexes   --截断
alter table S_KPI_DATA_D drop subpartition PART202101_SUBPART_02       --删除

注意：
如果表中含有自增主键，那么对子分区进行truncate截断的时候，后边要加上update indexes。

这是因为：
对表进行alter的时候会导致索引失效，需要重新建立索引。
而update indexes可以解决这一问题。

更多可参考：
ORACLE索引失效的原因

我们也可以手动查看是哪里的索引失效了，SQL语句如下：

select 
       t.*,
       i.index_type 
from 
       user_ind_columns t,
       user_indexes i 
where 
       t.index_name = i.index_name 
       and t.table_name = i.table_name 
       and t.table_name = '表名'

得到的结果表中，有一个index_name字段。其中的数据就是失效的索引。
（以index_name字段中的值为SYS_C00343988作为例子）

根据index_name的值来判断该索引当前的状态：unusable表示失效，valid表示可用

select index_name,index_type,tablespace_name,table_type,status from user_indexes where index_name='SYS_C00343988';

如果索引是失效（unusable）状态，可使用下方的命令重建索引：

alter index SYS_C00343988 rebuild;

---

本节内容涉及到了Oracle数据库中的4张系统表：
user_tab_partitions
user_tab_subpartitions
user_indexes
user_ind_columns