Ccww
  • 662

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

前言

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

  • 一、分区表简介

    1.1 什么是分区表?

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

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

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

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

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

    1.3 为什么使用分区表

    1. 改善查询性能:对分区对象的查询可以仅搜索自己关心的分区,提高检索速度。
    2. 增强可用性:如果表的某个分区出现故障,表在其他分区的数据仍然可用;
    3. 维护方便:如果表的某个分区出现故障,需要修复数据,只修复该分区即可;
    4. 均衡I/O:可以把不同的分区映射到磁盘以平衡I/O,改善整个系统性能。

    1.4 表分区的类型

    范围分区(range):基于一个范围将表的数据分配到其所属的分区内。

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

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

    列表分区(list):基于列某个特性分配其所属的分区

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

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

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

    二、分区表改造方案

    这次主要讨论的是以范围分区(range),并且以日期作为分区键。

    2.1 分区改造前准备

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

    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的改造方案:

    1. 创建与原表同构的分区新表
    2. 将原表设置成read only
    3. 使用Insert..select from的方式,将原表数据导入到分区新表
    4. 创建分区新表的索引和约束
    5. rename源表和新表的索引名和约束名称,交换命名
    6. 删除源表的同义词
    7. rename源表和分区新表的表名,交换命名
    8. 创建源表和新表的同义词
    9. 给rename后的分区新表授权
    10. 将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. 创建分区表
    2. 添加字段的默认值
    3. 添加表以及字段的注释
    创建分区表

    创建分区表--脚本生成模板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时为外键。

    1. 创建主键约束

      ----创建主键约束
        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;
    2. 创建外键约束

      -----(如果有外键的话,创建外键约束)
        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';
    各位看官还可以吗?喜欢的话,动动手指点个💗,点个关注呗!!谢谢支持!

    欢迎关注公众号【Ccww技术博客】,原创技术文章第一时间推出

    image.png

    阅读 429

    推荐阅读
    架构师之路
    用户专栏

    需要达成架构师,必经之路:知识点大全

    352 人关注
    44 篇文章
    专栏主页