数据库分表

如果数据库中的一张表在建表的时候通过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’)

【数据库SQL】分区的新建与删除 原创
2022-05-26 16:45:28 Donald_Knight 码龄1年关注如果数据库中的一张表在建表的时候通过partitioned by()指定了表中的某字段为分区，那么它就是一张分区表。向分区表中插入数据时，必须通过partition on()指明要把数据插入到哪个分区中，如果不指明分区则无法插入数据。查看表中有哪些分区查出所有的主分区的信息select * from user_tab_partitions where TABLE_NAME = '表名’11查出所有的子分区的信息select * from user_tab_subpartitions where TABLE_NAME = '表名’11由此可查出partition_name（主分区名称）和subpartition_name（子分区名称）字段中是否有对应数据，以此来判断分区是否已经存在。如何判断某一分区是否已经存在？语法：select count() from USER_TAB_PARTITIONS where table_name = ‘表名’ and partition_name = '分区名’11如果得到的结果大于0，说明该分区已经存在。user_tab_partitions 是一张系统表，专门用来记录分区的信息。其中有一个partition_name字段。示例：select count() from USER_TAB_PARTITIONS where table_name = 'DM_TABLE_M_V ’ and partition_name = 'PART202104’11我们可以仔细查看“分区表user_tab_partitions”中记录的全部具体信息：select * from user_tab_partitions where table_name = 'DM_TABLE_M_V ’ and partition_name = 'PART202102’11如何判断某一子分区是否已经存在？分为两步：①判断主分区是否存在；②判断主分区下的子分区是否存在。第1步的判断方法与前边写的一样，就是在分区表user_tab_partitions中根据名称查看主分区是否存在。关键是判断第2步，语法如下：select count() from user_tab_subpartitions where table_name = ‘表名’ and subpartition_name=‘子分区名’;11如果得到的结果大于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’11我们可以仔细查看“子分区表user_tab_subpartitions”中记录的全部具体信息：select * from user_tab_subpartitions where table_name = 'DM_TABLE_M_V ’ and subpartition_name = 'PART202101_SUBPART_01’11新建分区给一张表新建分区的方法如下。Oracle数据库上，新建分区的写法是这样的：alter table 表名 add partition 分区名 values (‘分区的值’)示例：alter table DM_TABLE_M_V add partition PART_202205 values (‘202205’)11有一些其他的数据库，有另外一种写法：alter table 表名 add partition 分区名 (字段=‘分区的值’)注意：这里没有写values示例：alter table DM_TABLE_S_D add partition PART_202206 (MONTH_ID=‘202206’)11删除分区删除分区有两种含义：① 保留分区，只是把分区中的内容全部删除；② 直接删除分区。如果是想把分区中的内容全部删除，使用truncate，语法如下：alter table 表名 truncate partition 分区名示例：alter table DM_TABLE_M_V truncate partition PART_20220511如果是想直接删除分区，使用drop，语法如下：alter table 表名 drop partition 分区名示例：alter table DM_TABLE_S_D drop partition PART_202206 11包含子分区的情况对于这种情况，最好是创建主分区的同时一并创建子分区。如下所示：–创建主分区并同时创建子分区–语法：alter table 表名 add partition 主分区名 values (‘主分区值’) (subpartition 子分区名 values (‘子分区值’))–实例：alter table S_KPI_DATA_D add partition PART202102 values (‘202102’) (subpartition PART202102_SUBPART_01 values (‘01’))12345671234567如果在新建分区的事后，只建主分区不建子分区的话，系统会自动创建一个默认子分区：–只创建主分区（系统会自动创建一个默认子分区）alter table spreport_kpi_data_single_dim_d add partition PART202102 values (‘202102’)1212最好不再要像上面这样写（只建主分区）！如果系统自动创建了一个默认子分区的话，之后我们就不能自己往这个主分区下边添加新的子分区了！最好还是在创建主分区的同时，也创建至少一个子分区。创建子分区（在指定主分区下）只有第一次是创建主分区（并同时创建子分区），之后再新建子分区的时候，对这个主分区不再是“创建”而是“修改”。不能再写add而是要写modify！语法如下：–在指定主分区下创建子分区alter table 表名 modify partition 主分区名 add subpartition 子分区名 values (‘子分区值’);1212注意对主分区是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’);123456123456主分区的截断和删除语法：alter table 表名 truncate partition 主分区名 --截断alter table 表名 drop partition 主分区名 --删除1212实例：alter table S_KPI_DATA_D truncate partition PART202101 --截断alter table S_KPI_DATA_D drop partition PART202101 --删除1212子分区的截断和删除语法：alter table 表名 truncate subpartition 子分区名 update indexes --截断alter table 表名 drop subpartition 子分区名 --删除1212实例：alter table S_KPI_DATA_D truncate subpartition PART202101_SUBPART_02 update indexes --截断alter table S_KPI_DATA_D drop subpartition PART202101_SUBPART_02 --删除1212注意：如果表中含有自增主键，那么对子分区进行truncate截断的时候，后边要加上update indexes。这是因为：对表进行alter的时候会导致索引失效，需要重新建立索引。而update indexes可以解决这一问题。

我们也可以手动查看是哪里的索引失效了，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;

truncate关键字类似于 delete drap
truncate table user_indexes

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