Oracle大数据量查询实际分析

Oracle大数据量查询实际分析

 

Oracle 数据库：

 

刚做一张5000万条数据的数据抽取，当前表同时还在继续insert操作，每分钟几百条数据。

 

该表按照时间，以月份为单位做的表分区，没有任何索引，当前共有14个字段，平均每个字段30个字节。当前表分区从201101到201512每月一个分区

 

测试服务器：xeno 5650，32核cpu，win2003操作 系统，物理内存16G；测试工具plsql

 

1.最开始的查询：

 

?

1 2 3 4 5 6 7 8

string.Format(@ "select * from                                      (select r.id,r.carcode,r.longtitude,r.latitude,r.velocity,r.gpstime,r.isonline from t_gps_record r where id in(                                      select min(id) from t_gps_record r where carcode='{0}'                                      group by to_char(gpstime,'yyyy-MM-dd HH24:mi'))                                      and carcode='{0}'                                      and gpstime>(select nvl((select max(gpstime) from t_gps_carposition where carcode='{0}'),(select min(gpstime) from t_gps_record where carcode='{0}')) from dual)                                      order by gpstime asc                                      ) where rownum<=200 " , row[ "carcode" ].ToString());

 

一开始以200条数据为段进行查询，查询一次2分钟16秒；

 

后来查20条，2分钟14秒；基本跟条数无关。

 

 

2.后来把最小时间写成固定的：

 

?

1 2 3 4 5 6 7 8

string.Format(@ "select * from                                      (select r.id,r.carcode,r.longtitude,r.latitude,r.velocity,r.gpstime,r.isonline from t_gps_record r where id in(                                      select min(id) from t_gps_record r where carcode='{0}'                                      group by to_char(gpstime,'yyyy-MM-dd HH24:mi'))                                      and carcode='{0}'                                      and gpstime>to_date('2011-11-1 00:00:00','yyyy-mm-dd HH24:mi:ss')                                      order by gpstime asc                                      ) where rownum<=200 " , row[ "carcode" ].ToString());

 

查询时间 1分34秒。

 

 

3.不加分区查询

 

?

1 2 3 4 5

select r.id,r.carcode,r.longtitude,r.latitude,r.velocity,r.gpstime,r.isonline from t_gps_record r where id in (                                      select min (id) from t_gps_record r                                      group by carcode, to_char(gpstime, 'yyyy-MM-dd HH24:mi' ))                                                                         and gpstime>=to_date( '2011-11-1 9:00:00' , 'yyyy-mm-dd HH24:mi:ss' ) and gpstime<=to_date( '2011-11-1 9:59:59' , 'yyyy-mm-dd HH24:mi:ss' )                                      order by gpstime asc

 

查询时间：3分29秒，共1426条

 

4.添加分区查询

 

?

1 2 3 4 5

select r.id,r.carcode,r.longtitude,r.latitude,r.velocity,r.gpstime,r.isonline from t_gps_record r where id in (                                      select min (id) from t_gps_record partition(GPSHISTORY201111) r                                       group by carcode, to_char(gpstime, 'yyyy-MM-dd HH24:mi' ))                                       and gpstime>=to_date( '2011-11-1 9:00:00' , 'yyyy-mm-dd HH24:mi:ss' ) and gpstime<=to_date( '2011-11-1 9:59:59' , 'yyyy-mm-dd HH24:mi:ss' )                                      order by gpstime asc

 

添加分区后查询：17s，共1426条

 

所以加分区后的查询效率提高十几倍，所以大数据量建立分区表是相当重要的。