《干货分享》分区表改造（脚本模板生成），值得收藏起来实战再用-阿里云开发者社区

前言

太久没有更新技术博客，后续还是保持以前的更新速度，走向新的学习之路，也欢迎大家一起来学习学习。最后捞一下以前发的面试文章总结，后续将继续更新：

一、分区表简介

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常用系统表,视图——docs.oracle.com/cd/B14117_0…

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';
复制代码

各位看官还可以吗？喜欢的话，动动手指点个💗，点个关注呗！！谢谢支持！

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

前言

一、分区表简介

1.1 什么是分区表？

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

1.3 为什么使用分区表

1.4 表分区的类型

二、分区表改造方案

2.1 分区改造前准备

2.1.1 统计各个表空间大小

2.1.2 统计表的索引大小

2.2分区表改造步骤

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

创建分区表

添加分区表字段的默认值

添加表以及字段的注释

2.2.3 使用Insert..select from的方式，将原表数据导入到分区新表

2.2.4 创建分区新表的索引和约束

创建分区表索引

创建表约束

2.2.5 rename源表和新表的索引名和约束名称以及表名

将原表索引、表约束变更为临时索引，约束

将分区表的索引名、表约束变更为原表的索引、约束

2.2.6 给rename后的分区新表授权

2.3 分区表改造完成

热门文章

最新文章

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

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

前言

一、分区表简介

1.1 什么是分区表？

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

1.3 为什么使用分区表

1.4 表分区的类型

二、分区表改造方案

2.1 分区改造前准备

2.1.1 统计各个表空间大小

2.1.2 统计表的索引大小

2.2分区表改造步骤

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

创建分区表

添加分区表字段的默认值

添加表以及字段的注释

2.2.3 使用Insert..select from的方式，将原表数据导入到分区新表

2.2.4 创建分区新表的索引和约束

创建分区表索引

创建表约束

2.2.5 rename源表和新表的索引名和约束名称以及表名

将原表索引、表约束变更为临时索引，约束

将分区表的索引名、表约束变更为原表的索引、约束

2.2.6 给rename后的分区新表授权

2.3 分区表改造完成

热门文章

最新文章

相关电子书

相关实验场景