《干货分享》分区表改造（方案、脚本模板生成），值得收藏起来实战再用

前言

太久没有更新技术博客，后续还是保持以前的更新速度，走向2020的学习之路，也欢迎大家一起来学习学习.

一、分区表简介 1.1 什么是分区表？

分区表是将大表的数据分成称为分区的许多小的子集，分区表的种类划分主要有：range（范围）、list（列表）和hash（散列）分区。划分依据主要是根据其表内部属性。

分区表可以创建其独特的分区索引，分区表可以从物理上将一个大表分成几个小表，但是从逻辑上来看，还是一个大表。

1.2 什么情况下使用分区表呢？

表内的数据量很大的时候，影响到业务/技术方容忍的最大查询时间。但数据量并不是判断是否需要创建分区表的惟一条件，如果表内的数据都是基础数据、其数据查询都频率高，这样不建议使用分区表。通常情况下，可以将数据进行分段处理。

表的大小超过2GB可进去分区表改造

1.3 为什么使用分区表 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可； 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。 1.4 表分区的类型

范围分区（range）：基于一个范围将表的数据分配到其所属的分区内。

如果需要将行映射到基于列值范围的分区时，就使用范围分区方法–条件是数据可以被划分成逻辑范围；当数据在整个范围内能被均等地划分时性能最好，明显不能均分时须使用其他分区方式

“范围”是在创建分区表时指定的分区键决定的，分区方式是最为常用的，并且分区键经常采用日期

列表分区（list）：基于列某个特性分配其所属的分区

该分区的特点是某列的值只有几个，基于这样的特点我们可以采用列表分区

散列分区（hash）:在列值上使用散列算法来分配其所属分区

当列的值没有合适的条件时，建议使用散列分区,通过在I/O设备上进行散列分区，使得这些分区大小一致。

二、分区表改造方案

这次主要讨论的是以范围分区（range），并且以日期作为分区键。

2.1 分区改造前准备

在做表分区前，需要表统计分析，各个表、索引空间存储大小，每年或者每个月表的增长率等（可以找DBA）。这边提供DBA常用系统表,视图——https://docs.oracle.com/cd/B14117_01/server.101/b10755.pdf

2.1.1 统计各个表空间大小 select t.owner,t.segment_name,t.tablespace_name,sum(bytes/1024/1024/1024) gb from dba_segments t where t.segment_name in (select t2.OBJECT_NAME from dba_objects t2 where t2.OBJECT_TYPE = 'TABLE' AND t2.owner=upper('tabel_owner') ) group by t.owner,t.segment_name,t.tablespace_name order by 4 desc； 2.1.2 统计表的索引大小 select round(sum(bytes) / 1024 / 1024 / 1024, 4) IDX_GB --表上索引对象占用空间 from dba_segments where owner || segment_name in(select owner || index_name from dba_indexes where table_owner = upper('table_name') and table_name = upper('table_owner'))；

​

2.2分区表改造步骤

前面表的各项指标都分析统计出来，那就开始实际操作起来，首先进行的小于100G的改造方案：

创建与原表同构的分区新表 将原表设置成read only 使用Insert…select from的方式，将原表数据导入到分区新表 创建分区新表的索引和约束 rename源表和新表的索引名和约束名称，交换命名 删除源表的同义词 rename源表和分区新表的表名，交换命名 创建源表和新表的同义词 给rename后的分区新表授权 将rename后的源表设置为read write.

Note：在进行接下来的脚本的时候记得定义好所需的变量

declare
v_table_name varchar2(100) := upper(‘表名’);
----建表变量
v_sql_temp1 varchar2(1000);
v_sql_temp2 varchar2(1000);
v_sql_temp3 varchar2(1000);
----属主变量
v_owner varchar2(100) := upper(‘表属主’);
-----输出变量
type remark_list is varray(60) of varchar2(3000);
v_output_list remark_list;
-----授权变量
v_grantee varchar2(100);
v_grant_sql varchar2(1000);
type type_array is table of varchar(20) index by binary_integer;
grantee_list type_array;

2.2.1 创建与原表同构的分区新表

步骤如下：

创建分区表 添加字段的默认值 添加表以及字段的注释 创建分区表

创建分区表–脚本生成模板1：

罗列所有字段模板，涉及到dba_tab_columns（表的列信息）

-----------1.新建分区临时表 v_sql_temp1 := 'CREATE TABLE ' || v_owner || '.' || v_table_name || '_P( '; select COLUMN_NAME ||' '|| decode( DATA_TYPE,'DATE',DATA_TYPE, DATA_TYPE||'('|| DATA_LENGTH || ')') || decode(NULLABLE,'N',' not null','') || ',' bulk collect into v_output_list from dba_tab_columns t where table_name =v_table_name order by t.COLUMN_ID ; dbms_output.put_line(v_sql_temp1); for i in 1 ..v_output_list.count loop --去掉数组中的最后一个字段字符中的逗号"," if i = v_output_list.count then select REPLACE(v_output_list(i),',',' ') into v_sql_temp2 from dual; dbms_output.put_line(v_sql_temp2); else dbms_output.put_line(v_output_list(i)); end if; end loop; --输出分区信息 v_sql_temp3 := ')PARTITION BY RANGE (ARCHIVE_DATE) INTERVAL (NUMTOYMINTERVAL(1, ''YEAR'')) (PARTITION ' || v_table_name || '_2019' || ' VALUES LESS THAN (TO_DATE(''2020-01-01'', ''YYYY-MM-DD'')))ENABLE ROW MOVEMENT MONITORING INITRANS 6;'; dbms_output.put_line(v_sql_temp3);

生成的模板：

CREATE TABLE PASDATA.CLM_PERSON_HOSPITAL_P( CREATED_BY VARCHAR2(100) not null, CREATED_DATE DATE not null, UPDATED_BY VARCHAR2(100) not null, UPDATED_DATE DATE not null, ID_CLM_PERSON_HOSPITAL VARCHAR2(32) not null, REPORT_NO VARCHAR2(30) not null, ID_CLM_CHANNEL_PROCESS VARCHAR2(32) not null, CASE_TIMES NUMBER(22) not null, HOSPITAL_CODE VARCHAR2(22), HOSPITAL_NAME VARCHAR2(100), SUBJECT_CODE VARCHAR2(20), BED_CODE VARCHAR2(100), START_DATE DATE, END_DATE DATE, MIGRATE_FROM VARCHAR2(1), AFFIRM_SIGN VARCHAR2(1) not null, DOCUMENT_GROUP_ID VARCHAR2(30), HOSPITALIZATION_NUMBER VARCHAR2(30), HOSPITALIZE_DAYS NUMBER(22), ARCHIVE_DATE DATE )PARTITION BY RANGE (ARCHIVE_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'YEAR')) ( PARTITION CLM_PERSON_HOSPITAL_2018 VALUES LESS THAN (TO_DATE('2019-01-01', 'YYYY-MM-DD')) )ENABLE ROW MOVEMENT MONITORING INITRANS 6;

创建分区表–脚本生成模板2：

declare v_table_name varchar2(100) := upper('表名'); -------建表变量 v_sql_temp1 varchar2(1000); v_sql_temp2 varchar2(1000); v_sql_partion varchar2(1000); ----属主变量 v_owner varchar2(100) := upper('表属主'); -----输出变量 如果字段数量超过60个，修改数组大小即可 type remark_list is varray(60) of varchar2(3000); v_remark_list remark_list; begin ----在begin后面加上DBMS_OUTPUT.ENABLE(buffer_size => null) ，表示输出buffer不受限制。 DBMS_OUTPUT.ENABLE(buffer_size => null); ----------------------------------------1.新建分区临时表---------------------------------- v_sql_temp1 := 'CREATE TABLE ' || v_owner || '.' || v_table_name || '_P PARTITION BY RANGE (ARCHIVE_DATE) INTERVAL (NUMTOYMINTERVAL(1, ''YEAR'')) ('; v_sql_temp2 := 'PARTITION ' || v_table_name || '_2019' || ' VALUES LESS THAN (TO_DATE(''2020-01-01'', ''YYYY-MM-DD''))'; v_sql_temp3 := ')ENABLE ROW MOVEMENT MONITORING INITRANS 6 AS SELECT * FROM ' || v_owner || '.' || v_table_name || ' WHERE 1=0;'; dbms_output.put_line(v_sql_temp1 || v_sql_temp2 || v_sql_temp3);

生成的模板：

CREATE TABLE PASDATA.EDR_APPLY_PLAN_INFO_P PARTITION BY RANGE (ARCHIVE_DATE) INTERVAL (NUMTOYMINTERVAL(1, 'YEAR')) ( PARTITION PART_BEFORE_2018 VALUES LESS THAN (TO_DATE('2018-01-01', 'YYYY-MM-DD')) )ENABLE ROW MOVEMENT MONITORING INITRANS 6 AS SELECT * FROM PASDATA.EDR_APPLY_PLAN_INFO WHERE 1=0; 添加分区表字段的默认值 ------- 2.对分区新表字段增加默认值 select 'alter table ' || t.owner || '.' || t.table_name || '_NEW modify ' || t.column_name || ' default 记得填默认值;' bulk collect into v_output_list from DBA_TAB_COLS t where t.TABLE_NAME = v_table_name and t.owner = v_owner and t.data_default is not null; for i in 1 .. v_output_list.count loop dbms_output.put_line(v_output_list(i)); end loop; 添加表以及字段的注释

添加表以及字段的注释涉及到表 dba_tab_comments（表注释信息）、dba_col_comments（列注释信息）

------------- 3.对分区新表的表名，字段增加注释 select 'comment on table ' || a.owner || '.' || a.table_name || '_P is ''' || a.comments || ''';' bulk collect into v_output_list from dba_tab_comments a where a.table_name = upper(v_table_name) and a.owner = upper(v_owner); for i in 1 .. v_output_list.count loop dbms_output.put_line(v_output_list(i)); end loop; select 'comment on column ' || owner || '.' || table_name || '_P.' || column_name || ' is ' || '''' || comments || ''';' bulk collect into v_output_list from dba_col_comments where table_name = v_table_name; for i in 1 .. v_output_list.count loop dbms_output.put_line(v_output_list(i)); end loop; 2.2.3 使用Insert…select from的方式，将原表数据导入到分区新表 dbms_output.put_line('insert /*+ append parallel(A, 4) */ into ' || v_owner || '.' || v_table_name || '_P A select /*+ parallel(T, 4) */ * from ' || v_owner || '.' || v_table_name || ' T;'); dbms_output.put_line('commit;'); 2.2.4 创建分区新表的索引和约束 创建分区表索引

创建分区表索引涉及到dba_indexes （用户模式的索引信息）、dba_ind_columns（ 索引与表字段的相关信息）

select 'create ' || decode(a.uniqueness, 'UNIQUE', 'UNIQUE', '') || ' index ' || a.owner || '.' || a.index_name || '_N on ' || a.table_owner || '.' || a.table_name || '_P (' || (select wm_concat(b.column_name) from dba_ind_columns b where b.index_name = a.index_name and b.table_owner = v_owner) || ') initrans 16 PARALLEL 8 online;' bulk collect into v_output_list from dba_indexes a where a.table_name = v_table_name and a.index_type != 'LOB'; for i in 1 .. v_output_list.count loop dbms_output.put_line(v_output_list(i)); end loop; 创建表约束

创建分区表索约束涉及到dba_cons_columns（数据库所有列的约束信息）、dba_constraints（ 数据库中所有表的所有约束定义），当dba_constraints中的constraint_type值为为p时为表主键，值为R时为外键。

创建主键约束

----创建主键约束 select 'ALTER TABLE ' || a.owner || '.' || a.table_name || '_P ADD CONSTRAINT ' || a.constraint_name || '_N PRIMARY KEY (' || a.column_name || ');' bulk collect into v_output_list from dba_cons_columns a where a.constraint_name = (select constraint_name from dba_constraints b where b.table_name = v_table_name and b.owner = a.owner and constraint_type = 'P') and a.owner = v_owner; for i in 1 .. v_output_list.count loop dbms_output.put_line(v_output_list(i)); end loop;

创建外键约束

-----（如果有外键的话，创建外键约束） select 'alter table ' || a.owner || '.' || a.table_name || '_P add constraint ' || a.constraint_name || '_N foreign key(' || b.column_name || ') references ' || (select c.owner || '.' || c.table_name || '(' || c.column_name || ')' from dba_cons_columns c, dba_constraints d where c.constraint_name = d.constraint_name and d.constraint_type = 'P' and c.constraint_name = a.r_constraint_name and c.owner = v_owner and d.owner = v_owner) || ';' bulk collect into v_output_list from dba_constraints a, dba_cons_columns b where a.constraint_name = b.constraint_name and a.table_name = v_table_name and a.constraint_type = 'R'; for i in 1 .. v_output_list.count loop dbms_output.put_line(v_output_list(i)); end loop; 2.2.5 rename源表和新表的索引名和约束名称以及表名 将原表索引、表约束变更为临时索引，约束 select 'alter index ' || a.owner || '.' || a.index_name || ' rename to ' || a.index_name || '_T;' bulk collect into v_output_list from dba_indexes a where a.table_name = v_table_name and a.index_type != 'LOB'; for i in 1 .. v_output_list.count loop dbms_output.put_line(v_output_list(i)); end loop; select 'alter table ' || a.owner || '.' || a.table_name || ' rename constraint ' || a.constraint_name || ' to ' || a.constraint_name || '_T;' bulk collect into v_output_list from dba_constraints a where a.table_name = v_table_name and a.constraint_type in ('P', 'R'); for i in 1 .. v_output_list.count loop dbms_output.put_line(v_output_list(i)); end loop; 将分区表的索引名、表约束变更为原表的索引、约束 select 'alter index ' || a.owner || '.' || a.index_name || '_N rename to ' || a.index_name || ';' bulk collect into v_output_list from dba_indexes a where a.table_name = v_table_name and a.index_type != 'LOB'; for i in 1 .. v_output_list.count loop dbms_output.put_line(v_output_list(i)); end loop; select 'alter table ' || a.owner || '.' || a.table_name || '_P rename constraint ' || a.constraint_name || '_N to ' || a.constraint_name || ';' bulk collect into v_output_list from dba_constraints a where a.table_name = v_table_name and a.constraint_type in ('P', 'R'); for i in 1 .. v_output_list.count loop dbms_output.put_line(v_output_list(i)); end loop; 2.2.6 给rename后的分区新表授权

给rename后的分区新表授权涉及到dba_tab_privs（数据库所有列的授权信息），查询所有的授权列表进行输出，定义好grantee_list、v_grant_sql等变量

select distinct (t.grantee) bulk collect into grantee_list from dba_tab_privs t where (t.owner = upper(v_owner)) and t.table_name = v_table_name; for i in 1 .. grantee_list.count loop v_grantee := grantee_list(i); ``` select 'grant ' || (select wm_concat(t.privilege) from dba_tab_privs t where t.table_name = v_table_name and t.grantee = v_grantee) || ' on ' || t.owner || '.' || t.table_name || ' to ' || t.grantee || ';' into v_grant_sql from dba_tab_privs t where t.table_name = v_table_name and t.grantee = v_grantee and rownum = 1; dbms_output.put_line(v_grant_sql); end loop; 2.3 分区表改造完成

当分区表的改造完成后保险地进行验证一下，数据量，索引，授权列表

对比索引

select * from dba_indexes a where a.table_name = 'CLM_HIS_RECIPE_DETAIL_NEW' and a.index_type != 'LOB';

对比授权用户列表

select * from dba_tab_privs t where t.table_name in ('CLM_PERSON_HOSPITAL_NEW') and t.owner = 'CLAIMDATA';

各位看官还可以吗？喜欢的话，动动手指点个

实战 模板 分区表 脚本