oracle大数据量。表分区提示查询效率

--删除表
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 

我可以明确数据在这个范围区间。。按每月来分区 所以是直接写死了。不能明确的话先创建分区表。 写个存储过程 时间分组。然后通过指针遍历 再在分区表上建立新的分区

常用的几个命令

 --查询指定分区的数据
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‘);
 
 createSqlText:=‘ALTER TABLE usertrackTest SPLIT PARTITION protmaxt AT (TO_DATE(‘‘‘||to_char(partitionDate,‘yyyy-mm-dd hh24:mi:ss‘)||‘‘‘,‘‘yyyy-mm-dd hh24:mi:ss‘‘)) INTO (PARTITION  ‘|| partitionName||‘, PARTITION protmaxt) UPDATE GLOBAL INDEXES‘;
  dbms_output.put_line(createSqlText);
 execute immediate createSqlText;
end;

新建一个工作

--查询工作
select * from sys.user_jobs

常用的时间间隔指令

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

性能测试

不加任何查询条件

使用了分区条件进行筛选

可以看出各项指标分区后的都优于没有分区的表

oracle大数据量。表分区提示查询效率

标签：