一次oracle数据库大数据表的分区方案设计，以及所踩的坑
1.分区方案的背景设计由于系统架构老旧，模型方案设计时的欠缺，导致分区方案的整改碰到了很多问题，以往使用的是历史归档表的形式归档数据，使得主表数据量减少，核心功能的读写就不会碰到问题，但是缺点时导致数据库表数量增加，账单整理困难，以往历史数据查询连表太多，查询速度太慢等问题，考虑用分区方案解决，分区方案的设计使用了组合分区的形式。2.问题集合1）由于表结构的设计太过集中，没有按照三个范
·
1.分区方案的背景设计
由于系统架构老旧，模型方案设计时的欠缺，导致分区方案的整改碰到了很多问题，以往使用的是历史归档表的形式归档数据，使得主表数据量减少，核心功能的读写就不会碰到问题，但是缺点时导致数据库表数量增加，账单整理困难，以往历史数据查询连表太多，查询速度太慢等问题，考虑用分区方案解决，分区方案的设计使用了组合分区的形式。
2.问题集合
1）由于表结构的设计太过集中，没有按照三个范式的原则进行设计，导致业务增加及数据量增加后，只能把业务集中在一张数据表，导致应用功能与数据库表之间耦合度太强，动一发而牵全身，数据库业务主表出现问题，会使得整个平台无法运转，甚至崩溃的程度，容错性较低，原则上的设计提出的方案是进行整体架构的升级重构，但是碍于时间原因，以及保守观念的影响，无法进行原则上的整改，只能退一步只采用分区的方案。
2）分区方案的采用分区方案的形式，oracle11g的子分区没有自动分区的功能，所有需要用pl/sql进行以往分区的建立
BEGIN
DBMS_OUTPUT.ENABLE(1000000);
OPEN C_CUR;
LOOP
FETCH C_CUR
INTO V_DAY;
EXIT WHEN C_CUR%NOTFOUND;
V_DAYTIME := V_DAY || ' 23:59:59';
V_NAME := replace(V_DAY, '-');
DBMS_OUTPUT.PUT_LINE(V_DAYTIME);
EXECUTE IMMEDIATE 'alter table tablename modify partition P2 add subpartition P2' ||
V_NAME || ' ' || 'VALUES LESS THAN (' || 'TO_DATE(''' ||
V_DAYTIME ||
''' ,''YYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN''))' ||
'tablespace <tablespaceName>';
END LOOP;
CLOSE C_CUR;
END;
DBMS_OUTPUT.ENABLE(1000000);
OPEN C_CUR;
LOOP
FETCH C_CUR
INTO V_DAY;
EXIT WHEN C_CUR%NOTFOUND;
V_DAYTIME := V_DAY || ' 23:59:59';
V_NAME := replace(V_DAY, '-');
DBMS_OUTPUT.PUT_LINE(V_DAYTIME);
EXECUTE IMMEDIATE 'alter table tablename modify partition P2 add subpartition P2' ||
V_NAME || ' ' || 'VALUES LESS THAN (' || 'TO_DATE(''' ||
V_DAYTIME ||
''' ,''YYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN''))' ||
'tablespace <tablespaceName>';
END LOOP;
CLOSE C_CUR;
END;
然后这段pl/sql改一改变成存储过程，再用job定时执行就可以按照你约定的时间进行创建子分区。
CREATE OR REPLACE PROCEDURE PAR_PRODUCE is
V_DAY VARCHAR2(30);
V_DAYTIME VARCHAR2(60);
V_NAME VARCHAR2(60);
BEGIN
V_DAY := TO_CHAR(sysdate, 'yyyy-mm-dd');
V_NAME := replace(V_DAY, '-');
V_DAYTIME := V_DAY || ' 23:59:59';
EXECUTE IMMEDIATE 'alter table <talename> modify partition P2 add subpartition P2' ||
V_NAME || ' ' || 'VALUES LESS THAN (' || 'TO_DATE(''' ||
V_DAYTIME ||
''' ,''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN''))' ||
'tablespace <tablespaceName>';
END PAR_PRODUCE;
V_DAY VARCHAR2(30);
V_DAYTIME VARCHAR2(60);
V_NAME VARCHAR2(60);
BEGIN
V_DAY := TO_CHAR(sysdate, 'yyyy-mm-dd');
V_NAME := replace(V_DAY, '-');
V_DAYTIME := V_DAY || ' 23:59:59';
EXECUTE IMMEDIATE 'alter table <talename> modify partition P2 add subpartition P2' ||
V_NAME || ' ' || 'VALUES LESS THAN (' || 'TO_DATE(''' ||
V_DAYTIME ||
''' ,''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN''))' ||
'tablespace <tablespaceName>';
END PAR_PRODUCE;
再贴上job的语法
declare
job number;
begin
dbms_job.submit(job,
'PAR_PRODUCE;',
to_date('24-11-2017 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),
'sysdate+86400/86400');
end;
job number;
begin
dbms_job.submit(job,
'PAR_PRODUCE;',
to_date('24-11-2017 01:00:00', 'dd-mm-yyyy hh24:mi:ss'),
'sysdate+86400/86400');
end;
以上就是组合分区的实现。
3）做过分区的同学估计都知道行迁移，原则上分区方案选择的字段都不应该选用经常会被修改的字段，因为这可能会导致被修改的数据改变分区，也就是行迁移，这样会使io变高，一次update操作相当于执行了delete，insert，update，大数据量的写操作，会使数据库服务器本身的负载过高，也有可能导致服务器的崩溃，锁表，或者其他业务读取写入速度变慢，但是本次修改无法避免的用了行迁移的形式，本人是考虑整体修改，但是碍于技术经理的一些原则，尽量不做大的变动，所以考虑了多种分区，导入数据测试的结构还是采用了使用行迁移的方式。而且技术经理考虑oracle本身性能高，但是我是觉得性能在高，也不能浪费，但是架构的整改重构本身要承受很大的风险问题，权衡下还是觉得领导说对就是对吧。。。。。
4）数据导入采用的是oracle的数据泵，导入dmp文件，导入的方式：
impdp mobileplat/hjhz6666 directory=dmporacle dumpfile=$command logfile=logfile.log remap_table=$commands:SEND_NEW2 remap_schema=MOBILEINFOPLAT:MOBILEPLAT table_exists_action=append
directory是需要在oracle上创建虚拟文件路径，要不然你的dmp文件无法执行会报错
create or replace directory directory文件名称 as '你的dmp文件路径'
remap_schema 是同步不同的schema remap_table 是同步不同表名字，相同表结构。
导入数据后可以在分区表创建组合分区建的联合索引，建议创建本地索引，效率应该会更快。
3.这个过程耗费了很多时间和精力，导入大数据量建议数据表不要建立索引主键，导入速度会很快，导入数据先考虑表空间是否足够，不够的话需要扩展表空间，创建索引建议不要超过6个，不然索引的占用空间可能比表本身都要大，不太利于写操作。分区后统计功能，也就是count函数可能速度较慢，考虑位图索引，但是需要谨慎使用，因为本身查询条件带入了非分区字段，oracle11g的解释计划会看到回表的操作，这个操作会导致时间的大幅度增长，而且很难避免。
开源、云原生的融合云平台
更多推荐
1
0- 0
已为社区贡献1条内容
所有评论(0)