分区表改造:脚本模板生成



欢迎关注公众号【

Ccww技术博客

】,原创技术文章第一时间推出


往期文章:

  • 《提升能力,涨薪可待》-Java并发之AQS全面详解

  • 《提升能力,涨薪可待》—Java并发之Synchronized

  • 《提升能力,涨薪可待》-Java多线程与并发之ThreadLoca

  • java线程并发系列–基础知识点(笔试、面试必备)


分区表改造

一、分区表简介

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

  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 in1 ..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));

endif;


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 = '表名'

and a.index_type != 'LOB';

对比授权用户列表

select *

from dba_tab_privs t

where t.table_name in

('表名')

and t.owner = '表属主';

各位看官还可以吗?
喜欢的话,动动手指点个:heartpulse:,点个关注呗!

谢谢支持!

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

分区表改造:脚本模板生成

原文 

https://mp.weixin.qq.com/s/S5auQHxMqucrMCA3M8rrzg

本站部分文章源于互联网,本着传播知识、有益学习和研究的目的进行的转载,为网友免费提供。如有著作权人或出版方提出异议,本站将立即删除。如果您对文章转载有任何疑问请告之我们,以便我们及时纠正。

PS:推荐一个微信公众号: askHarries 或者qq群:474807195,里面会分享一些资深架构师录制的视频录像:有Spring,MyBatis,Netty源码分析,高并发、高性能、分布式、微服务架构的原理,JVM性能优化这些成为架构师必备的知识体系。还能领取免费的学习资源,目前受益良多

转载请注明原文出处:Harries Blog™ » 分区表改造:脚本模板生成

赞 (0)
分享到:更多 ()

评论 0

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址