楼主外号坑王,不是写代码坑,是到处填坑。。
目前有个项目接手处理几个问题,之前跟我说什么SQL查不出来,好吧,我想也没什么,估计就是调整下SQL语句,再不行增加下索引,特么当我看到真实数据后,我特么傻逼了
业务表,主要有2张,一个订单主表,一个商品表,目前客户需要很多查询报表。。都是需要这两张表的数据,可是目前订单表内数据都已经到了3.5亿了。。商品表至少是1:2的关系,那么至少在7亿,收款表的估计也差不多7亿。而且每天主表以40W的数据递增,商品表100W数据递增
目前有个项目接手处理几个问题,之前跟我说什么SQL查不出来,好吧,我想也没什么,估计就是调整下SQL语句,再不行增加下索引,特么当我看到真实数据后,我特么傻逼了
业务表,主要有2张,一个订单主表,一个商品表,目前客户需要很多查询报表。。都是需要这两张表的数据,可是目前订单表内数据都已经到了3.5亿了。。商品表至少是1:2的关系,那么至少在7亿,收款表的估计也差不多7亿。而且每天主表以40W的数据递增,商品表100W数据递增
create table liusheng (orderid number(10),name varchar2(10),ls_date date);
insert into liusheng values (1,'ls1',to_date('1981-01-02','yyyy-mm-dd'));
insert into liusheng values (1,'ls2',to_date('1998-01-03','yyyy-mm-dd'));
insert into liusheng values (1,'ls3',to_date('1999-01-04','yyyy-mm-dd'));
insert into liusheng values (1,'ls4',to_date('2000-01-05','yyyy-mm-dd'));
insert into liusheng values (1,'ls5',to_date('2000-01-06','yyyy-mm-dd'));
insert into liusheng values (1,'ls6',to_date('2001-01-07','yyyy-mm-dd'));
insert into liusheng values (1,'ls7',to_date('2001-01-08','yyyy-mm-dd'));
insert into liusheng values (1,'ls8',to_date('2002-01-09','yyyy-mm-dd'));
insert into liusheng values (1,'ls9',to_date('2002-01-10','yyyy-mm-dd'));
insert into liusheng values (1,'ls10',to_date('2011-01-11','yyyy-mm-dd'));
创建range分区表
create table liusheng_part
partition by range (ls_date)
(
partition liusheng_part_1999_1 values less than (to_date('1999-01-01','yyyy-mm-dd')) ,
partition liusheng_part_2000_1 values less than (to_date('2000-01-01','yyyy-mm-dd')) ,
partition liusheng_part_2001_1 values less than (to_date('2001-01-01','yyyy-mm-dd')) ,
partition liusheng_part_2002_1 values less than (to_date('2002-01-01','yyyy-mm-dd')) ,
partition liusheng_part_2003_1 values less than (to_date('2003-01-01','yyyy-mm-dd')) ,
partition other values less than (maxvalue)
)
as select * from liusheng;
查看分区
select * from liusheng_part partition (liusheng_part_1999_1);
select * from liusheng_part partition (liusheng_part_2000_1);
select * from liusheng_part partition (liusheng_part_2001_1);
select * from liusheng_part partition (liusheng_part_2002_1);
select * from liusheng_part partition (liusheng_part_2003_1);
select * from liusheng_part partition (other);