|
现在业务有一张usertrack 日志记录表。每天会产生30万条数据。数据量大查询效率会非常慢 所以我考虑通过表分区来提示效率 逻辑上是一张表。但是分区后会按照分区条件将数据分在不同的物理文件 优点： 1) 改善查询性能：对分区对象的查询可以仅搜索自己关心的分区，提高检索速度。 2) 增强可用性：如果表的某个分区出现故障，表在其他分区的数据仍然可用； 3) 维护方便：如果表的某个分区出现故障，需要修复数据，只修复该分区即可； 4) 均衡I/O：可以把不同的分区映射到磁盘以平衡I/O，改善整个系统性能。 缺点： 分区表相关，已经存在的表没有方法可以直接转化为分区表。
表分区主要分为 范围分区 列表分区 散列分区 组合范围散列分区 复合范围散列分区(这个大大的博客说的很详细:http://www.cnblogs.com/leiOOlei/archive/2012/06/08/2541306.html)
我的业务场景选择使用范围分区 因为分区表不能在已存在的表上创建 所以要备份数据删除原来的表 再重新创建这个表为分区表
--检查数据是否一致 select count(1) from usertracktemp select count(1) from usertrack --删除表 truncate table usertrack; drop table usertrack  --创建表同时创建分区 create table usertrack PARTITION BY RANGE(createtime) ( PARTITION prot20151101 values LESS THAN (TO_DATE('2015-11-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION prot20151201 values LESS THAN (TO_DATE('2015-12-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION prot20160201 values LESS THAN (TO_DATE('2016-02-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION prot20160301 values LESS THAN (TO_DATE('2016-03-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION prot20160401 values LESS THAN (TO_DATE('2016-04-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION prot20160501 values LESS THAN (TO_DATE('2016-05-01 00:00:00','yyyy-mm-dd hh24:mi:ss')), PARTITION protmaxt values less THAN (maxvalue) ) as select * from usertracktemp 可以通过这段代码根据现有数据按月度进行分区
我现在遇到一个问题就是其中有几个月的数据不稳定 比如 其中某月就产生了上百万 其他的才产生数万条。那么对以上存储过程进行了改进就是当数据小于100000的时候则按月分区 如果超过150000则将这些拆分子多个小分区再进行分
CREATE OR REPLACE PROCEDURE CreatePartition2 AS sumNumber number; dt varchar2(200); createSqlText varchar(300); partitionName varchar(50); toDate date; starDate date; endDate date; sumSonNum number; dtSon varchar2(200); everydayNumber number; BEGIN DECLARE CURSOR myCusor IS select to_char(ut.createtime,'yyyy-mm') dt,count(1) from usertracktemp ut group by to_char(ut.createtime,'yyyy-mm') order by to_char(ut.createtime,'yyyy-mm'); BEGIN OPEN myCusor; LOOP FETCH myCusor INTO dt,sumNumber; EXIT WHEN myCusor%NOTFOUND; --如果当月数据量小于等于10万则直接将当月数据分成一个区 if(dt is not null and sumNumber<=100000) then toDate:=to_date(dt,'yyyy-mm'); toDate:=ADD_MONTHS(toDate,1); partitionName:='part'||to_char(toDate,'yyyymmdd'); createSqlText:='ALTER TABLE usertracktemp SPLIT PARTITION protmaxt AT (TO_DATE('''||to_char(toDate,'yyyy-mm-dd hh24:mi:ss')||''',''yyyy-mm-dd hh24:mi:ss'')) INTO (PARTITION '|| partitionName||', PARTITION protmaxt) UPDATE GLOBAL INDEXES'; execute immediate createSqlText; --如果当月数据大于150000则拆分该月数据 每10000条数据分一个区 elsif (sumNumber>=150000) then starDate:=to_date(dt,'yyyy-mm'); endDate:=last_day(starDate); everydayNumber:=0; Declare cursor mycusor2 IS select to_char(t.createtime,'yyyy-mm-dd'),count(1) from usertracktemp t where t.createtime>=starDate and t.createtime<=endDate group by to_char(t.createtime,'yyyy-mm-dd') order by to_char(t.createtime,'yyyy-mm-dd'); begin OPEN mycusor2; LOOP FETCH mycusor2 INTO dtSon,sumSonNum; EXIT WHEN myCusor2%NOTFOUND; everydayNumber:=everydayNumber+sumSonNum; if(everydayNumber>=100000) then everydayNumber:=0; partitionName:='partDay'||to_char(toDate,'yyyymmdd'); toDate:=to_date(dtSon,'yyyy-mm-dd'); dbms_output.put_line(to_char(toDate,'yyyymmdd')); createSqlText:='ALTER TABLE usertracktemp SPLIT PARTITION protmaxt AT (TO_DATE('''||to_char(toDate,'yyyy-mm-dd hh24:mi:ss')||''',''yyyy-mm-dd hh24:mi:ss'')) INTO (PARTITION '|| partitionName||', PARTITION protmaxt) UPDATE GLOBAL INDEXES'; execute immediate createSqlText; end if; END LOOP; CLOSE myCusor2; end; else --如果小于100000同时又不大于150000则还是按第一个逻辑分区 toDate:=to_date(dt,'yyyy-mm'); toDate:=ADD_MONTHS(toDate,1); partitionName:='part'||to_char(toDate,'yyyymmdd'); createSqlText:='ALTER TABLE usertracktemp SPLIT PARTITION protmaxt AT (TO_DATE('''||to_char(toDate,'yyyy-mm-dd hh24:mi:ss')||''',''yyyy-mm-dd hh24:mi:ss'')) INTO (PARTITION '|| partitionName||', PARTITION protmaxt) UPDATE GLOBAL INDEXES'; execute immediate createSqlText; end if; END LOOP; CLOSE myCusor; END; END CreatePartition2;
我可以明确数据在这个范围区间。。按每月来分区 所以是直接写死了。不能明确的话先创建分区表。 写个存储过程 时间分组。然后通过指针遍历 再在分区表上建立新的分区 常用的几个命令 --查询指定分区的数据 select createtime from usertrack PARTITION(protmaxt) --查询指定表下面的所有分区 select * from user_segments where segment_type='TABLE PARTITION' and segment_name='USERTRACK'; segment_name为表名 记得统一大写 --有maxvalue分区条件的时候创建分区语法 ALTER TABLE usertrackTest SPLIT PARTITION protmaxt AT (TO_DATE('2016-06-06 16:00:15','yyyy-mm-dd hh24:mi:ss')) INTO (PARTITION part20160606, PARTITION protmaxt) UPDATE GLOBAL INDEXES --没有maxvalue分区条件的时候 ALTER TABLE SALES ADD PARTITION P3 VALUES LESS THAN(TO_DATE('2003-06-01','YYYY-MM-DD')); 这个时候我还要新建一个工作 每个月执行一次 创建新的分区。然后当前月产生的数据就插入到这个心的分区里面 新建一个每月执行一次的存储过程 create or replace procedure CreatePartition as --声明变量 partitionName varchar(20); createSqlText varchar(300); partitionDate date; begin partitionDate:=ADD_MONTHS(sysdate,1); partitionName:='part'||to_char(partitionDate,'yyyymmdd'); 新建一个工作
常用的时间间隔指令 1:每分钟执行 Interval => TRUNC(sysdate,'mi') + 1/ (24*60) 或 Interval => sysdate+1/1440 2:每天定时执行 例如：每天的凌晨1点执行 Interval => TRUNC(sysdate) + 1 +1/ (24) 3:每周定时执行 例如：每周一凌晨1点执行 Interval => TRUNC(next_day(sysdate,'星期一'))+1/24 4:每月定时执行 例如：每月1日凌晨1点执行 Interval =>TRUNC(LAST_DAY(SYSDATE))+1+1/24 5:每季度定时执行 例如每季度的第一天凌晨1点执行 Interval => TRUNC(ADD_MONTHS(SYSDATE,3),'Q') + 1/24 6:每半年定时执行 例如：每年7月1日和1月1日凌晨1点 Interval => ADD_MONTHS(trunc(sysdate,'yyyy'),6)+1/24 7:每年定时执行 例如：每年1月1日凌晨1点执行 Interval =>ADD_MONTHS(trunc(sysdate,'yyyy'),12)+1/24 性能测试 不加任何查询条件 使用了分区条件进行筛选
可以看出各项指标分区后的都优于没有分区的表 |
|
|
