Oracle 19C入门到精通之表分区策略与管理表分区

1. 表分区策略

对表进行分区设计时，首先要考虑和分析分区表中每个分区的数据量，其次要为每个分区创建相应的表空间。

1.1. 识别大表

一般来说，数据占用存储空间大的表就是大表，系统架构师要做的就是确定哪些表属于大表。如果要在目前运行的系统上进行表数据量分析，那么主要采用ANALYZE TABLE语句进行分析，然后查询数据字典获得相应的数据量；如果面对的是一个正在进行需求分析的表，则只能采用估计的方法。

1.2. 大表如何区分

大表一般可以按时间分区。例如，如果按照月份分区，则需要为每个月创建一个数据表空间；如果按照季度分区，则一年要创建4个表空间；如果要存储5年用的表空间，则需要创建20个表空间。

1.3. 分区的表空间规划

分区方法确定后，就要着手创建表空间，创建表空间前要对每个表空间的大小进行估算。如果每个季度的数据为100 MB，则最好创建120 MB的表空间。另外，还要考虑数据量的增长，如当年的数据是每季度100MB，则下一年可能要增长20%～30%，这些变化都要在表空间的大小上给予考虑。

2. 管理表分区

当在应用设计中采用分区技术创建表和索引时，数据库管理员需要对分区进行管理，包括经常查看各个有关表空间的存储情况、是否需要增加新的表空间、合并小的分区以及删除不需要的表分区等。

2.1. 添加表分区

对于已经存在表分区的某个表，如果要添加一个新的表分区，通常使用ALTER TABLE…ADD PARTITION语句。

在客户信息表clients中，添加一个省份为“吉林省”的表分区，代码如下：

alter table clients

add partition jilin values('吉林省')

storage(initial 10K next 20k) tablespace TBS_TEST_1

nologging;

上述例子不仅增加了分区jilin，而且给增加的分区指定了存储属性。

2.2. 合并表分区

Oracle可以对表和索引进行分区，也可以对分区进行合并，从而减少散列分区或者复合分区的个数。在合并表分区之后，Oracle系统将做以下处理：

在合并分区时，HASH列函数将分区内容分布到一个或多个保留分区中。

原来内容所在的分区完全被清除，与分区对应的索引也被清除。

将一个或多个索引的本地索引分区标识为不可用(UNUSABLE)，对不可用的索引进行重建。

2.2.1. 合并散列分区

使用ALTER TABLE…COALESCE PARTITION语句可以完成HASH列分区的合并。

--合并person分区表中的一个HASH分区

alter table person coalesce partition;

2.2.2. 合并复合分区

使用ALTER TABLE…MODIFY语句可以将某个子分区的内容重新分配到一个或者多个保留的子分区中。

--把person2分区表中的par3分区合并到其他保留的子分区中

alter table person2 modify partition par3 coalesce subpartition;

2.3. 删除表分区

可以从范围分区或复合分区中删除分区。但是散列分区和复合分区的散列子分区只能通过合并来达到删除的目的。

2.3.1. 删除一个表分区

可以使用ALTER TABLE…DROP PARTITION语句删除范围分区和复合分区。删除分区时，该分区的数据也被删除。如果不希望删除数据，则必须采用合并分区的方法。

--把ware_retail_part分区表中的par_04分区删除

alter table ware_retail_part drop partition par_04;

2.3.2. 删除有数据和全局索引的表分区

如果分区表中包含了数据，并且在表中定义了一个或者多个全局索引，则可以使用ALTER TABLE…DROP PARTITION语句删除表分区，这样可以保留全局索引，但是索引会被标识为不可用(UNUSABLE)，因而需要重建索引。

--删除ware_retail_part分区表中的par_04分区

alter table ware_retail_part drop partition par_04;

--重建索引ware_index

alter index ware_index rebuild;

在上述例子中，如果ware_index是范围分区的全局索引，则需要重建所有索引的分区，代码如下：

alter index ware_index rebuild index_01;

alter index ware_index rebuild index_02;

alter index ware_index rebuild index_03;

2.3.3. 使用DELETE和ALTER TABLE…DROP PARTITION语句

在执行ALTER TABLE…DROP PARTITION语句前首先执行DELETE语句来删除分区的所有数据行，然后执行ALTER TABLE…DROP PARTITION语句，但是执行DELETE语句时需要更新全局索引。

删除ware_retail_part分区表中第四季度的数据，再删除第四季度数据对应的par_04分区，代码如下：

delete from ware_retail_part where retail_date >= to_date('2011-10-01','yyyy-mm-dd');

alter table ware_retail_part drop partition par_04;

2.3.4. 删除具有完整性约束的分区

如果分区的表具有完整性约束，则可以采用以下两种办法。

禁止完整性约束，然后执行ALTER TABLE…DROP PARTITION语句，最后激活约束。

禁用books_1表的主键约束BOOK_PK，然后删除books_1表的分区part_01，最后激活books_1表的主键约束BOOK_PK，代码如下：

alter table books_1 disable constraints BOOK_PK;

alter table books_1 drop partition part_01;

alter table books_1 enable constraints BOOK_PK;

首先执行DELETE语句删除分区中的行，然后使用ALTER TABLE…DROP PARTITION语句删除分区。

首先删除books_1表part_01分区中的所有记录，然后再删除part_01分区，代码如下：

delete from books_1 where bookno 

alter table books_1 drop partition part_01;

2.4. 并入范围分区

可以使用ALTER TABLE…MERGE PARTITION，语句将相邻的范围分区合并在一起变为一个新的分区，该分区继承原来两个分区的边界，原来的两个分区与相应的索引将一起被删除。如果被合并的分区非空，则该分区被标识为UNUSABLE。不能对HASH分区表执行ALTER TABLE…MERGE PARTITION语句。

并入范围分区是将两个以上的分区合并到一个存在的分区中，合并后一般要重建索引。

在sales表中创建4个范围分区，将第三个分区并入第四个分区中，操作步骤及代码如下：

创建一个销售记录表sales，然后对该表的记录按照销售日期（即季度）分为4个范围分区，代码如下：

create table sales             --创建一个销售记录表

(

id number primary key,       --记录编号

goodsname varchar2(10),      --商品名

saledate date                --销售日期

)

partition by range(saledate)   --按照日期分区

(

--第一季度数据

partition part_sea1 values less than(to_date('2023-04-01','yyyy-mm-dd')) tablespace TBS_TEST_1,

--第二季度数据

partition part_sea2 values less than(to_date('2023-07-01','yyyy-mm-dd')) tablespace TBS_TEST_2,

--第三季度数据

partition part_sea3 values less than(to_date('2023-10-01','yyyy-mm-dd')) tablespace TBS_TEST_1,

--第四季度数据

partition part_sea4 values less than(to_date('2024-01-01','yyyy-mm-dd')) tablespace TBS_TEST_2

);

在sales表中创建局部索引，代码如下：

create index index_3_4 on sales(saledate)

local(

partition part_sea1 tablespace TBS_TEST_1,

partition part_sea2 tablespace TBS_TEST_2,

partition part_sea3 tablespace TBS_TEST_1,

partition part_sea4 tablespace TBS_TEST_2

);

使用ALTER TABLE…MERGE PARTITION语句把第三个分区并入第四个分区中，代码如下：

alter table sales merge partitions part_sea3,part_sea4 into partition part_sea4;

最后重新建立局部索引，代码如下：

alter table sales modify partition part_sea4 rebuild unusable local indexes;