oracle拆分分区表及重建索引

骑猪看日落 2023-02-09 11:54 7阅读 0赞

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

提高性能的套路，一般就是垂直扩展和水平扩展。垂直扩展就是换更强的服务器，水平扩展就是加更多的服务器。垂直扩展适合单机版，水平扩展常用于分布式系统。而传统的数据库，如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;

## 五、结后语 ##

拆分分区感觉比较麻烦，最好的方式就是防微杜渐，预先估计好数据量，在分区表建立之初就预留足够的分区。