当前位置:数据库 > Oracle >>

Oracle大数据量查询实际分析

Oracle大数据量查询实际分析
 
Oracle数据库:
 
刚做一张5000万条数据的数据抽取,当前表同时还在继续insert操作,每分钟几百条数据。
 
该表按照时间,以月份为单位做的表分区,没有任何索引,当前共有14个字段,平均每个字段30个字节。当前表分区从201101到201512每月一个分区
 
测试服务器:xeno 5650,32核cpu,win2003操作系统,物理内存16G;测试工具plsql
 
1.最开始的查询:
 
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.后来把最小时间写成固定的:
 
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.不加分区查询
 
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.添加分区查询
 
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条
 
所以加分区后的查询效率提高十几倍,所以大数据量建立分区表是相当重要的。
 
CopyRight © 2022 站长资源库 编程知识问答 zzzyk.com All Rights Reserved
部分文章来自网络,