公司的一张数据表数据量太大,为提高数据查询速度,对其进行在线重定义分区,网上查阅各路大神帖子,自我实验总结如下:
测试数据为一张4亿+多条数据量源表:TAB_SJ_SIGNAL_VALUE_61 ;
特点:表数据根据列 TIME 有明显的时间划分;
1--建中间表t3,手动创建分区,下面操作都是默认使用的中间表t3;
create table t3 partition by range(time) --创建中间表t3,按时间time分区;
(partition a1 values less than (to_date('2016/01/26','yyyy/mm/dd')) tablespace db_sj_61_v1, --分区a1
partition a2 values less than (to_date('2016/01/27','yyyy/mm/dd')) tablespace db_sj_61_v1, --分区a2
partition a3 values less than (to_date('2016/01/28','yyyy/mm/dd')) tablespace db_sj_61_v1,
partition a4 values less than (to_date('2016/01/29','yyyy/mm/dd')) tablespace db_sj_61_v1,
partition a5 values less than (maxva
测试数据为一张4亿+多条数据量源表:TAB_SJ_SIGNAL_VALUE_61 ;
特点:表数据根据列 TIME 有明显的时间划分;
1--建中间表t3,手动创建分区,下面操作都是默认使用的中间表t3;
create table t3 partition by range(time) --创建中间表t3,按时间time分区;
(partition a1 values less than (to_date('2016/01/26','yyyy/mm/dd')) tablespace db_sj_61_v1, --分区a1
partition a2 values less than (to_date('2016/01/27','yyyy/mm/dd')) tablespace db_sj_61_v1, --分区a2
partition a3 values less than (to_date('2016/01/28','yyyy/mm/dd')) tablespace db_sj_61_v1,
partition a4 values less than (to_date('2016/01/29','yyyy/mm/dd')) tablespace db_sj_61_v1,
partition a5 values less than (maxva