Oracle大数据量查询实际分析

Oracle大数据量查询实际分析

Oracle数据库

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

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

测试服务器xeno 核cpuwin操作系统物理内存G;测试工具plsql

最开始的查询

stringFormat(@select * from (select ridrcarcoderlongtituderlatitudervelocityrgpstimerisonline from t_gps_record r where id in( select min(id) from t_gps_record r where carcode={} group by to_char(gpstimeyyyyMMdd HH:mi)) and carcode={} and gpstime>(select nvl((select max(gpstime) from t_gps_carposition where carcode={})(select min(gpstime) from t_gps_record where carcode={})) from dual) order by gpstime asc ) where rownum<= row[carcode]ToString());

一开始以条数据为段进行查询查询一次分钟秒;

后来查条分钟秒;基本跟条数无关

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

stringFormat(@select * from (select ridrcarcoderlongtituderlatitudervelocityrgpstimerisonline from t_gps_record r where id in( select min(id) from t_gps_record r where carcode={} group by to_char(gpstimeyyyyMMdd HH:mi)) and carcode={} and gpstime>to_date( ::yyyymmdd HH:mi:ss) order by gpstime asc ) where rownum<= row[carcode]ToString());

查询时间 分秒

不加分区查询

select ridrcarcoderlongtituderlatitudervelocityrgpstimerisonline from t_gps_record r where id in( select min(id) from t_gps_record r group by carcode to_char(gpstimeyyyyMMdd HH:mi)) and gpstime>=to_date( ::yyyymmdd HH:mi:ss) and gpstime<=to_date( ::yyyymmdd HH:mi:ss) order by gpstime asc

查询时间分秒共条

添加分区查询

select ridrcarcoderlongtituderlatitudervelocityrgpstimerisonline from t_gps_record r where id in( select min(id) from t_gps_record partition(GPSHISTORY) r group by carcode to_char(gpstimeyyyyMMdd HH:mi)) and gpstime>=to_date( ::yyyymmdd HH:mi:ss) and gpstime<=to_date( ::yyyymmdd HH:mi:ss) order by gpstime asc

添加分区后查询s共条

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