oracle拆分分区表及重建索引

原创

人称左直拳 2022-08-15 12:13:33 博主文章分类：oracle ©著作权

©著作权归作者所有：来自51CTO博客作者人称左直拳的原创作品，请联系作者获取转载授权，否则将追究法律责任

分区表的好处，应该就是提高性能。

提高性能的套路，一般就是垂直扩展和水平扩展。垂直扩展就是换更强的服务器，水平扩展就是加更多的服务器。垂直扩展适合单机版，水平扩展常用于分布式系统。而传统的数据库，如oracle、sql server等，本质上是一个单机系统，你说你多加几台服务器，一起跑oracle，没啥意义。所以，对于oracle等数据库，常用的提升策略，就是垂直扩展。关系型数据库里面，好像是mysql比较接近分布式系统，主从复制，读写分离，玩得很溜。oracle嘛，如果硬要说水平扩展的，可能就是共享磁盘方式了吧，多个磁盘或者是网络存储什么的，分散硬盘IO的压力。

以上都是对硬件提升而言。在软件设置里，数据库性能提升，还可以采用反规范化的策略，比如将表进行分割。这里也有所谓水平分割，垂直分割。垂直分割就是将表拆了，一个大表拆成多个小表，比如一个大表，几十上百个字段，密密麻麻，拆成几个小表，每个表十来个字段。好处就是提高查询命中率。因为关系型数据库采用的是按页存储，一条记录越短，那么每页存储的记录数就越多，你要查点什么，返回给你的页数就越少，性能自然就提升了。水平分割就是将记录进行分割，比如一年的表分成12个表来存，一个月一个表，每个表记录数也少了。

一、建立分区表

水平分割除了物理分表，采用分区表也有类似的效果。比如按照某字段的值进行分区。以下是一个存储文件信息的表，按照所属文件夹ID进行分区：

CREATE TABLE "WORK"."FILES" 
   (   "ID" NUMBER NOT NULL ENABLE, 
   "FOLDERID" NUMBER NOT NULL ENABLE, 
   "FILENAME" VARCHAR2(50), 
   "SUFFIX" VARCHAR2(10), 
   "FILESIZE" NUMBER, 
   "ACCEPTDATE" DATE, 
   "DATADATE" DATE, 
   "NOTE" VARCHAR2(100), 
   "STATUS" VARCHAR2(2), 
    CONSTRAINT "PK_FILES" PRIMARY KEY ("ID")
   )   partition by range (FOLDERID) --分区依据列
(partition p_1 values less than (20000),
partition p_2 values less than  (40000),
partition p_3 values less than  (60000),
partition p_4 values less than  (80000),
partition p_5 values less than  (100000),
partition p_6 values less than  (120000),
partition p_7 values less than  (140000),
partition p_8 values less than  (160000),
partition p_9 values less than  (180000),
partition p_10 values less than (200000),
partition p_11 values less than (220000),
partition p_12 values less than (240000),
partition p_13 values less than (260000),
partition p_14 values less than (280000),
partition p_15 values less than (300000),
partition p_16 values less than (320000),
partition p_17 values less than (340000),
partition p_18 values less than (360000),
partition p_19 values less than (380000),
partition p_20 values less than (400000),
partition p_21 values less than (420000),
partition p_22 values less than (440000),
partition p_23 values less than (460000),
partition p_24 values less than (480000),
partition p_25 values less than (500000),
partition p_26 values less than (520000),
partition p_27 values less than (540000),
partition p_28 values less than (560000),
partition p_29 values less than (580000),
partition p_30 values less than (600000),
partition p_31 values less than (maxvalue)
 );

这个分区表的意图很明显，就是folderid每2万的记录就分作一个区，这样用folderId进行查找的时候，就到某个区直接找就行了，一个区的记录数毕竟小多了。

二、拆分最大分区

这个分区方案，在系统运行之初是没有问题的。但因为数据量很大，数据增长很快，分区依据列FolderID现在已经突破了60万，所以后来插进来的记录，全部都挤到最后一个分区 p_31里了，查了一下，有差不多接近6千万条，系统反应非常慢，最终卡死。

所以拆分最大分区，刻不容缓。

三、如何拆分最大分区

在oracle中，拆一个分区，每次只能一分为二，而不能直接将一个分区拆成多个。因此，网上有些文章给出的方案就是搞一个存储过程，里面循环执行，直至拆成多个分区。

循环是要循环的，但不一定要用存储过程，直接用代码块也可以。代码块其实就相当于匿名存储过程了。

以下给出完整步骤：

1、先停止所有业务系统
如果是7 * 24小时的生产系统怎么办？这个我没想好。也许可以用什么备用程序或数据库顶上。否则数据量大的情况下，一边跑业务系统，数据不停进来，这边还在拆，速度肯定很慢。

2、观察分区情况

select table_name,partition_name,high_value,initial_extent,next_extent,num_rows,blocks from user_tab_partitions a 
where a.table_name=upper('FILES')
order by partition_name;

select  count(*) from  files partition (p_31) where folderid > 600000;

3、先拆出一个试试水

alter table files split partition p_31 at (620000) into (PARTITION p_31,PARTITION p_default) update index;

请注意，后面这个update index这个选项必不可少，否则拆分以后，索引不可用，报ORA-01502错误，新记录会插不进去！（错误ORA-01502: 索引或这类索引的分区处于不可用状态）

4、批量拆分
发现一切尽在掌握，开始大规模拆分。代码无非主要就是执行拼凑的SQL语句。

declare 
  i number;
  j number;
  k number;
  sql1 varchar2(2000);
begin
  --运行前先确定好以下三个变量：
  -----------------------------------------------------------------
  i:= 620000;--目前次大分区临界点，最大是 p_default
  j:= 31;--次大分区序号
  k:= 2000000;--分拆后次大分区临界点
  -----------------------------------------------------------------
  while i < k loop
    i := i + 20000;
    j := j + 1;
    sql1 := 'alter table files_his split partition p_default at (' || i || ') into (PARTITION p_' || j || ',PARTITION p_default) update index';
    execute immediate sql1;
  end loop;
end;

5、观察分区情况

select table_name,partition_name,high_value,initial_extent,next_extent,num_rows,blocks from user_tab_partitions a 
where a.table_name=upper('FILES')
order by partition_name;

6、查找一下数量

select  count(*) from  files partition (p_31) where folderid > 600000;
select  count(*) from  files partition (p_32) where folderid > 620000;
select  count(*) from  files partition (p_33) where folderid > 640000;

四、重建索引

分区时，语句务必带上“update index”选项，否则拆分以后，索引不可用，报ORA-01502错误，新记录会插不进去！（错误ORA-01502: 索引或这类索引的分区处于不可用状态）。但网上许多教程，根本没提这一点，以致我辛辛苦苦分完区，又要重建索引。

--此为拆分分区不加update index的补救措施

--查看索引情况
select owner,table_name,index_name,uniqueness,status from dba_indexes i
  where i.owner = 'WORK' and i.table_name = 'FILES';

--重建...
alter index IX_FOLDER_FILENAME rebuild online;

alter index IX_FOLDER rebuild online;

alter index PK_FILES rebuild online;