create or replace procedure SP_GIS_Get_Vehicle_SpeedOmeter
(
mcuid IN INTEGER,
selectYear IN INTEGER,
selectMonth IN INTEGER,
curCursor OUT SYS_REFCURSOR
)
as
strSql varchar2(2000);
mcuid_value INTEGER;
selectYear_value INTEGER;
selectMonth_value INTEGER;begin
mcuid_value := mcuid;
selectYear_value := selectYear;
selectMonth_value := selectMonth; strSql:='select t.MCUID,
extract(YEAR FROM t.RECEIVETIME) as syear,
extract(MONTH FROM t.RECEIVETIME) as smonth,
extract(DAY FROM t.RECEIVETIME) as sday,
(MAX(SPEEDOMETER)-MIN(SPEEDOMETER))/1000.0 as DayOfSpeedOmeter
from bmps_his_receive_gpsinfo t
where t.speedometer!=0 and t.MCUID=:p1
AND extract(YEAR FROM t.RECEIVETIME)=:p2
AND extract(MONTH FROM t.RECEIVETIME)=:p3
GROUP BY t.MCUID,extract(YEAR FROM t.RECEIVETIME),extract(Month FROM t.RECEIVETIME),extract(DAY FROM t.RECEIVETIME)
ORDER BY t.MCUID ASC,extract(YEAR FROM t.RECEIVETIME) asc,extract(Month FROM t.RECEIVETIME) asc,extract(DAY FROM t.RECEIVETIME) ASC';
OPEN curCursor FOR strSql USING mcuid_value,selectYear_value,selectMonth_value;end SP_GIS_Get_Vehicle_SpeedOmeter;这条SQL目的是统计某年、某月、某MCUID下的车辆里程数统计。该数据表有很多列,我取关键三列,MCUID,SPEEDOMETER,RECEIVETIME。
这个表是每秒都有数据插入进来,有不同的MCUID,插入时的SPEEDOMETER记录是累加的,
比如 MCUID:1000001002,RECEIVETIME:2011-08-15 09:55:50 时SPEEDOMETER:189258(米)
那么下一秒可能是:MCUID:1000001002,RECEIVETIME:2011-08-15 09:55:51 时SPEEDOMETER:189260(米)图例:
所以我的思路是将RECEIVETIME分解成年、月 分组,然后取该MCUID下的最大值减去最小值,得到的是该年该月该MCUID的行驶里程数,可是在提取时很慢很慢不知给位有什么好的建议??如何优化???
(
mcuid IN INTEGER,
selectYear IN INTEGER,
selectMonth IN INTEGER,
curCursor OUT SYS_REFCURSOR
)
as
strSql varchar2(2000);
mcuid_value INTEGER;
selectYear_value INTEGER;
selectMonth_value INTEGER;begin
mcuid_value := mcuid;
selectYear_value := selectYear;
selectMonth_value := selectMonth; strSql:='select t.MCUID,
extract(YEAR FROM t.RECEIVETIME) as syear,
extract(MONTH FROM t.RECEIVETIME) as smonth,
extract(DAY FROM t.RECEIVETIME) as sday,
(MAX(SPEEDOMETER)-MIN(SPEEDOMETER))/1000.0 as DayOfSpeedOmeter
from bmps_his_receive_gpsinfo t
where t.speedometer!=0 and t.MCUID=:p1
AND extract(YEAR FROM t.RECEIVETIME)=:p2
AND extract(MONTH FROM t.RECEIVETIME)=:p3
GROUP BY t.MCUID,extract(YEAR FROM t.RECEIVETIME),extract(Month FROM t.RECEIVETIME),extract(DAY FROM t.RECEIVETIME)
ORDER BY t.MCUID ASC,extract(YEAR FROM t.RECEIVETIME) asc,extract(Month FROM t.RECEIVETIME) asc,extract(DAY FROM t.RECEIVETIME) ASC';
OPEN curCursor FOR strSql USING mcuid_value,selectYear_value,selectMonth_value;end SP_GIS_Get_Vehicle_SpeedOmeter;这条SQL目的是统计某年、某月、某MCUID下的车辆里程数统计。该数据表有很多列,我取关键三列,MCUID,SPEEDOMETER,RECEIVETIME。
这个表是每秒都有数据插入进来,有不同的MCUID,插入时的SPEEDOMETER记录是累加的,
比如 MCUID:1000001002,RECEIVETIME:2011-08-15 09:55:50 时SPEEDOMETER:189258(米)
那么下一秒可能是:MCUID:1000001002,RECEIVETIME:2011-08-15 09:55:51 时SPEEDOMETER:189260(米)图例:
所以我的思路是将RECEIVETIME分解成年、月 分组,然后取该MCUID下的最大值减去最小值,得到的是该年该月该MCUID的行驶里程数,可是在提取时很慢很慢不知给位有什么好的建议??如何优化???
--建立索引
create index 索引名称 on 表名 (字段名);--物化视图参考
http://blog.csdn.net/hdhai9451/article/details/3875172
AND extract(MONTH FROM t.RECEIVETIME)=:p3这两个条件能否合并一下,再建个索引
create INDEX INDEX_GPSINFO on bmps_his_receive_gpsinfo(RECEIVETIME)
global
PARTITION BY RANGE(RECEIVETIME)(
PARTITION part_182 VALUES LESS THAN (TO_DATE('2011-7-1','YYYY-MM-DD')),
PARTITION part_183 VALUES LESS THAN (TO_DATE('2011-7-2','YYYY-MM-DD')),
PARTITION part_184 VALUES LESS THAN (TO_DATE('2011-7-3','YYYY-MM-DD')),
PARTITION part_185 VALUES LESS THAN (TO_DATE('2011-7-4','YYYY-MM-DD')),
PARTITION part_186 VALUES LESS THAN (TO_DATE('2011-7-5','YYYY-MM-DD')),
PARTITION part_187 VALUES LESS THAN (TO_DATE('2011-7-6','YYYY-MM-DD')),
PARTITION part_188 VALUES LESS THAN (TO_DATE('2011-7-7','YYYY-MM-DD')),
PARTITION part_189 VALUES LESS THAN (TO_DATE('2011-7-8','YYYY-MM-DD')),
PARTITION part_190 VALUES LESS THAN (TO_DATE('2011-7-9','YYYY-MM-DD')),
PARTITION part_191 VALUES LESS THAN (TO_DATE('2011-7-10','YYYY-MM-DD')),
PARTITION part_192 VALUES LESS THAN (TO_DATE('2011-7-11','YYYY-MM-DD')),
PARTITION part_193 VALUES LESS THAN (TO_DATE('2011-7-12','YYYY-MM-DD')),
PARTITION part_194 VALUES LESS THAN (TO_DATE('2011-7-13','YYYY-MM-DD')),
PARTITION part_195 VALUES LESS THAN (TO_DATE('2011-7-14','YYYY-MM-DD')),
PARTITION part_196 VALUES LESS THAN (TO_DATE('2011-7-15','YYYY-MM-DD')),
PARTITION part_197 VALUES LESS THAN (TO_DATE('2011-7-16','YYYY-MM-DD')),
PARTITION part_198 VALUES LESS THAN (TO_DATE('2011-7-17','YYYY-MM-DD')),
PARTITION part_199 VALUES LESS THAN (TO_DATE('2011-7-18','YYYY-MM-DD')),
PARTITION part_200 VALUES LESS THAN (TO_DATE('2011-7-19','YYYY-MM-DD')),
PARTITION part_201 VALUES LESS THAN (TO_DATE('2011-7-20','YYYY-MM-DD')),
PARTITION part_202 VALUES LESS THAN (TO_DATE('2011-7-21','YYYY-MM-DD')),
PARTITION part_203 VALUES LESS THAN (TO_DATE('2011-7-22','YYYY-MM-DD')),
PARTITION part_204 VALUES LESS THAN (TO_DATE('2011-7-23','YYYY-MM-DD')),
PARTITION part_205 VALUES LESS THAN (TO_DATE('2011-7-24','YYYY-MM-DD')),
PARTITION part_206 VALUES LESS THAN (TO_DATE('2011-7-25','YYYY-MM-DD')),
PARTITION part_207 VALUES LESS THAN (TO_DATE('2011-7-26','YYYY-MM-DD')),
PARTITION part_208 VALUES LESS THAN (TO_DATE('2011-7-27','YYYY-MM-DD')),
PARTITION part_209 VALUES LESS THAN (TO_DATE('2011-7-28','YYYY-MM-DD')),
PARTITION part_210 VALUES LESS THAN (TO_DATE('2011-7-29','YYYY-MM-DD')),
PARTITION part_211 VALUES LESS THAN (TO_DATE('2011-7-30','YYYY-MM-DD')),
PARTITION part_212 VALUES LESS THAN (TO_DATE('2011-7-31','YYYY-MM-DD')),
PARTITION part_213 VALUES LESS THAN (TO_DATE('2011-8-1','YYYY-MM-DD')),
PARTITION part_214 VALUES LESS THAN (TO_DATE('2011-8-2','YYYY-MM-DD')),
PARTITION part_215 VALUES LESS THAN (TO_DATE('2011-8-3','YYYY-MM-DD')),
PARTITION part_216 VALUES LESS THAN (TO_DATE('2011-8-4','YYYY-MM-DD')),
PARTITION part_217 VALUES LESS THAN (TO_DATE('2011-8-5','YYYY-MM-DD')),
PARTITION part_218 VALUES LESS THAN (TO_DATE('2011-8-6','YYYY-MM-DD')),
PARTITION part_219 VALUES LESS THAN (TO_DATE('2011-8-7','YYYY-MM-DD')),
PARTITION part_220 VALUES LESS THAN (TO_DATE('2011-8-8','YYYY-MM-DD')),
PARTITION part_221 VALUES LESS THAN (TO_DATE('2011-8-9','YYYY-MM-DD')),
PARTITION part_222 VALUES LESS THAN (TO_DATE('2011-8-10','YYYY-MM-DD')),
PARTITION part_223 VALUES LESS THAN (TO_DATE('2011-8-11','YYYY-MM-DD')),
PARTITION part_224 VALUES LESS THAN (TO_DATE('2011-8-12','YYYY-MM-DD')),
PARTITION part_225 VALUES LESS THAN (TO_DATE('2011-8-13','YYYY-MM-DD')),
PARTITION part_226 VALUES LESS THAN (TO_DATE('2011-8-14','YYYY-MM-DD')),
PARTITION part_227 VALUES LESS THAN (TO_DATE('2011-8-15','YYYY-MM-DD')),
PARTITION part_228 VALUES LESS THAN (TO_DATE('2011-8-16','YYYY-MM-DD')),
PARTITION part_229 VALUES LESS THAN (TO_DATE('2011-8-17','YYYY-MM-DD')),
PARTITION part_230 VALUES LESS THAN (TO_DATE('2011-8-18','YYYY-MM-DD')),
PARTITION part_231 VALUES LESS THAN (TO_DATE('2011-8-19','YYYY-MM-DD')),
PARTITION part_232 VALUES LESS THAN (TO_DATE('2011-8-20','YYYY-MM-DD')),
PARTITION part_233 VALUES LESS THAN (TO_DATE('2011-8-21','YYYY-MM-DD')),
PARTITION part_234 VALUES LESS THAN (TO_DATE('2011-8-22','YYYY-MM-DD')),
PARTITION part_235 VALUES LESS THAN (TO_DATE('2011-8-23','YYYY-MM-DD')),
PARTITION part_236 VALUES LESS THAN (TO_DATE('2011-8-24','YYYY-MM-DD')),
PARTITION part_237 VALUES LESS THAN (TO_DATE('2011-8-25','YYYY-MM-DD')),
PARTITION part_238 VALUES LESS THAN (TO_DATE('2011-8-26','YYYY-MM-DD')),
PARTITION part_239 VALUES LESS THAN (TO_DATE('2011-8-27','YYYY-MM-DD')),
PARTITION part_240 VALUES LESS THAN (TO_DATE('2011-8-28','YYYY-MM-DD')),
PARTITION part_241 VALUES LESS THAN (TO_DATE('2011-8-29','YYYY-MM-DD')),
PARTITION part_242 VALUES LESS THAN (TO_DATE('2011-8-30','YYYY-MM-DD')),
PARTITION part_243 VALUES LESS THAN (TO_DATE('2011-8-31','YYYY-MM-DD')),
PARTITION part_244 VALUES LESS THAN (TO_DATE('2011-9-1','YYYY-MM-DD')),
PARTITION part_245 VALUES LESS THAN (TO_DATE('2011-9-2','YYYY-MM-DD')),
PARTITION part_246 VALUES LESS THAN (TO_DATE('2011-9-3','YYYY-MM-DD')),
PARTITION part_247 VALUES LESS THAN (TO_DATE('2011-9-4','YYYY-MM-DD')),
PARTITION part_248 VALUES LESS THAN (TO_DATE('2011-9-5','YYYY-MM-DD')),
PARTITION part_249 VALUES LESS THAN (TO_DATE('2011-9-6','YYYY-MM-DD')),
PARTITION part_250 VALUES LESS THAN (TO_DATE('2011-9-7','YYYY-MM-DD')),
PARTITION part_251 VALUES LESS THAN (TO_DATE('2011-9-8','YYYY-MM-DD')),
PARTITION part_252 VALUES LESS THAN (TO_DATE('2011-9-9','YYYY-MM-DD')),
PARTITION part_253 VALUES LESS THAN (TO_DATE('2011-9-10','YYYY-MM-DD')),
PARTITION part_254 VALUES LESS THAN (TO_DATE('2011-9-11','YYYY-MM-DD')),
PARTITION part_255 VALUES LESS THAN (TO_DATE('2011-9-12','YYYY-MM-DD')),
PARTITION part_256 VALUES LESS THAN (TO_DATE('2011-9-13','YYYY-MM-DD')),
PARTITION part_257 VALUES LESS THAN (TO_DATE('2011-9-14','YYYY-MM-DD')),
PARTITION part_258 VALUES LESS THAN (TO_DATE('2011-9-15','YYYY-MM-DD')),
PARTITION part_259 VALUES LESS THAN (TO_DATE('2011-9-16','YYYY-MM-DD')),
PARTITION part_260 VALUES LESS THAN (TO_DATE('2011-9-17','YYYY-MM-DD')),
PARTITION part_261 VALUES LESS THAN (TO_DATE('2011-9-18','YYYY-MM-DD')),
PARTITION part_262 VALUES LESS THAN (TO_DATE('2011-9-19','YYYY-MM-DD')),
PARTITION part_263 VALUES LESS THAN (TO_DATE('2011-9-20','YYYY-MM-DD')),
PARTITION part_264 VALUES LESS THAN (TO_DATE('2011-9-21','YYYY-MM-DD')),
PARTITION part_265 VALUES LESS THAN (TO_DATE('2011-9-22','YYYY-MM-DD')),
PARTITION part_266 VALUES LESS THAN (TO_DATE('2011-9-23','YYYY-MM-DD')),
PARTITION part_267 VALUES LESS THAN (TO_DATE('2011-9-24','YYYY-MM-DD')),
PARTITION part_268 VALUES LESS THAN (TO_DATE('2011-9-25','YYYY-MM-DD')),
PARTITION part_269 VALUES LESS THAN (TO_DATE('2011-9-26','YYYY-MM-DD')),
PARTITION part_270 VALUES LESS THAN (TO_DATE('2011-9-27','YYYY-MM-DD')),
PARTITION part_271 VALUES LESS THAN (TO_DATE('2011-9-28','YYYY-MM-DD')),
PARTITION part_272 VALUES LESS THAN (TO_DATE('2011-9-29','YYYY-MM-DD')),
PARTITION part_273 VALUES LESS THAN (TO_DATE('2011-9-30','YYYY-MM-DD')),
PARTITION part_275 VALUES LESS THAN (TO_DATE('2011-10-2','YYYY-MM-DD')),
PARTITION part_276 VALUES LESS THAN (TO_DATE('2011-10-3','YYYY-MM-DD')),
PARTITION part_277 VALUES LESS THAN (TO_DATE('2011-10-4','YYYY-MM-DD')),
PARTITION part_278 VALUES LESS THAN (TO_DATE('2011-10-5','YYYY-MM-DD')),
PARTITION part_279 VALUES LESS THAN (TO_DATE('2011-10-6','YYYY-MM-DD')),
PARTITION part_280 VALUES LESS THAN (TO_DATE('2011-10-7','YYYY-MM-DD')),
PARTITION part_281 VALUES LESS THAN (TO_DATE('2011-10-8','YYYY-MM-DD')),
PARTITION part_282 VALUES LESS THAN (TO_DATE('2011-10-9','YYYY-MM-DD')),
PARTITION part_283 VALUES LESS THAN (TO_DATE('2011-10-10','YYYY-MM-DD')),
PARTITION part_284 VALUES LESS THAN (TO_DATE('2011-10-11','YYYY-MM-DD')),
PARTITION part_285 VALUES LESS THAN (TO_DATE('2011-10-12','YYYY-MM-DD')),
PARTITION part_286 VALUES LESS THAN (TO_DATE('2011-10-13','YYYY-MM-DD')),
PARTITION part_287 VALUES LESS THAN (TO_DATE('2011-10-14','YYYY-MM-DD')),
PARTITION part_288 VALUES LESS THAN (TO_DATE('2011-10-15','YYYY-MM-DD')),
PARTITION part_289 VALUES LESS THAN (TO_DATE('2011-10-16','YYYY-MM-DD')),
PARTITION part_290 VALUES LESS THAN (TO_DATE('2011-10-17','YYYY-MM-DD')),
PARTITION part_291 VALUES LESS THAN (TO_DATE('2011-10-18','YYYY-MM-DD')),
PARTITION part_292 VALUES LESS THAN (TO_DATE('2011-10-19','YYYY-MM-DD')),
PARTITION part_293 VALUES LESS THAN (TO_DATE('2011-10-20','YYYY-MM-DD')),
PARTITION part_294 VALUES LESS THAN (TO_DATE('2011-10-21','YYYY-MM-DD')),
PARTITION part_295 VALUES LESS THAN (TO_DATE('2011-10-22','YYYY-MM-DD')),
PARTITION part_296 VALUES LESS THAN (TO_DATE('2011-10-23','YYYY-MM-DD')),
PARTITION part_297 VALUES LESS THAN (TO_DATE('2011-10-24','YYYY-MM-DD')),
PARTITION part_298 VALUES LESS THAN (TO_DATE('2011-10-25','YYYY-MM-DD')),
PARTITION part_299 VALUES LESS THAN (TO_DATE('2011-10-26','YYYY-MM-DD')),
PARTITION part_300 VALUES LESS THAN (TO_DATE('2011-10-27','YYYY-MM-DD')),
PARTITION part_301 VALUES LESS THAN (TO_DATE('2011-10-28','YYYY-MM-DD')),
PARTITION part_302 VALUES LESS THAN (TO_DATE('2011-10-29','YYYY-MM-DD')),
PARTITION part_303 VALUES LESS THAN (TO_DATE('2011-10-30','YYYY-MM-DD')),
PARTITION part_304 VALUES LESS THAN (TO_DATE('2011-10-31','YYYY-MM-DD')),
PARTITION part_305 VALUES LESS THAN (TO_DATE('2011-11-1','YYYY-MM-DD')),
PARTITION part_306 VALUES LESS THAN (TO_DATE('2011-11-2','YYYY-MM-DD')),
PARTITION part_307 VALUES LESS THAN (TO_DATE('2011-11-3','YYYY-MM-DD')),
PARTITION part_308 VALUES LESS THAN (TO_DATE('2011-11-4','YYYY-MM-DD')),
PARTITION part_309 VALUES LESS THAN (TO_DATE('2011-11-5','YYYY-MM-DD')),
PARTITION part_310 VALUES LESS THAN (TO_DATE('2011-11-6','YYYY-MM-DD')),
PARTITION part_311 VALUES LESS THAN (TO_DATE('2011-11-7','YYYY-MM-DD')),
PARTITION part_312 VALUES LESS THAN (TO_DATE('2011-11-8','YYYY-MM-DD')),
PARTITION part_313 VALUES LESS THAN (TO_DATE('2011-11-9','YYYY-MM-DD')),
PARTITION part_314 VALUES LESS THAN (TO_DATE('2011-11-10','YYYY-MM-DD')),
PARTITION part_315 VALUES LESS THAN (TO_DATE('2011-11-11','YYYY-MM-DD')),
PARTITION part_316 VALUES LESS THAN (TO_DATE('2011-11-12','YYYY-MM-DD')),
PARTITION part_317 VALUES LESS THAN (TO_DATE('2011-11-13','YYYY-MM-DD')),
PARTITION part_318 VALUES LESS THAN (TO_DATE('2011-11-14','YYYY-MM-DD')),
PARTITION part_319 VALUES LESS THAN (TO_DATE('2011-11-15','YYYY-MM-DD')),
PARTITION part_320 VALUES LESS THAN (TO_DATE('2011-11-16','YYYY-MM-DD')),
PARTITION part_321 VALUES LESS THAN (TO_DATE('2011-11-17','YYYY-MM-DD')),
PARTITION part_322 VALUES LESS THAN (TO_DATE('2011-11-18','YYYY-MM-DD')),
PARTITION part_323 VALUES LESS THAN (TO_DATE('2011-11-19','YYYY-MM-DD')),
PARTITION part_324 VALUES LESS THAN (TO_DATE('2011-11-20','YYYY-MM-DD')),
PARTITION part_325 VALUES LESS THAN (TO_DATE('2011-11-21','YYYY-MM-DD')),
PARTITION part_326 VALUES LESS THAN (TO_DATE('2011-11-22','YYYY-MM-DD')),
PARTITION part_327 VALUES LESS THAN (TO_DATE('2011-11-23','YYYY-MM-DD')),
PARTITION part_328 VALUES LESS THAN (TO_DATE('2011-11-24','YYYY-MM-DD')),
PARTITION part_329 VALUES LESS THAN (TO_DATE('2011-11-25','YYYY-MM-DD')),
PARTITION part_330 VALUES LESS THAN (TO_DATE('2011-11-26','YYYY-MM-DD')),
PARTITION part_331 VALUES LESS THAN (TO_DATE('2011-11-27','YYYY-MM-DD')),
PARTITION part_332 VALUES LESS THAN (TO_DATE('2011-11-28','YYYY-MM-DD')),
PARTITION part_333 VALUES LESS THAN (TO_DATE('2011-11-29','YYYY-MM-DD')),
PARTITION part_334 VALUES LESS THAN (TO_DATE('2011-11-30','YYYY-MM-DD')),
PARTITION part_335 VALUES LESS THAN (TO_DATE('2011-12-1','YYYY-MM-DD')),
PARTITION part_336 VALUES LESS THAN (TO_DATE('2011-12-2','YYYY-MM-DD')),
PARTITION part_337 VALUES LESS THAN (TO_DATE('2011-12-3','YYYY-MM-DD')),
PARTITION part_338 VALUES LESS THAN (TO_DATE('2011-12-4','YYYY-MM-DD')),
PARTITION part_339 VALUES LESS THAN (TO_DATE('2011-12-5','YYYY-MM-DD')),
PARTITION part_340 VALUES LESS THAN (TO_DATE('2011-12-6','YYYY-MM-DD')),
PARTITION part_341 VALUES LESS THAN (TO_DATE('2011-12-7','YYYY-MM-DD')),
PARTITION part_342 VALUES LESS THAN (TO_DATE('2011-12-8','YYYY-MM-DD')),
PARTITION part_343 VALUES LESS THAN (TO_DATE('2011-12-9','YYYY-MM-DD')),
PARTITION part_344 VALUES LESS THAN (TO_DATE('2011-12-10','YYYY-MM-DD')),
PARTITION part_345 VALUES LESS THAN (TO_DATE('2011-12-11','YYYY-MM-DD')),
PARTITION part_346 VALUES LESS THAN (TO_DATE('2011-12-12','YYYY-MM-DD')),
PARTITION part_347 VALUES LESS THAN (TO_DATE('2011-12-13','YYYY-MM-DD')),
PARTITION part_348 VALUES LESS THAN (TO_DATE('2011-12-14','YYYY-MM-DD')),
PARTITION part_349 VALUES LESS THAN (TO_DATE('2011-12-15','YYYY-MM-DD')),
PARTITION part_350 VALUES LESS THAN (TO_DATE('2011-12-16','YYYY-MM-DD')),
PARTITION part_351 VALUES LESS THAN (TO_DATE('2011-12-17','YYYY-MM-DD')),
PARTITION part_352 VALUES LESS THAN (TO_DATE('2011-12-18','YYYY-MM-DD')),
PARTITION part_353 VALUES LESS THAN (TO_DATE('2011-12-19','YYYY-MM-DD')),
PARTITION part_354 VALUES LESS THAN (TO_DATE('2011-12-20','YYYY-MM-DD')),
PARTITION part_355 VALUES LESS THAN (TO_DATE('2011-12-21','YYYY-MM-DD')),
PARTITION part_356 VALUES LESS THAN (TO_DATE('2011-12-22','YYYY-MM-DD')),
PARTITION part_357 VALUES LESS THAN (TO_DATE('2011-12-23','YYYY-MM-DD')),
PARTITION part_358 VALUES LESS THAN (TO_DATE('2011-12-24','YYYY-MM-DD')),
PARTITION part_359 VALUES LESS THAN (TO_DATE('2011-12-25','YYYY-MM-DD')),
PARTITION part_360 VALUES LESS THAN (TO_DATE('2011-12-26','YYYY-MM-DD')),
PARTITION part_361 VALUES LESS THAN (TO_DATE('2011-12-27','YYYY-MM-DD')),
PARTITION part_362 VALUES LESS THAN (TO_DATE('2011-12-28','YYYY-MM-DD')),
PARTITION part_363 VALUES LESS THAN (TO_DATE('2011-12-29','YYYY-MM-DD')),
PARTITION part_364 VALUES LESS THAN (TO_DATE('2011-12-30','YYYY-MM-DD')),
PARTITION part_365 VALUES LESS THAN (TO_DATE('2011-12-31','YYYY-MM-DD')),
PARTITION part_max VALUES LESS THAN (MAXVALUE)
);
已经存在的表进行分区比较麻烦,具体怎么做你可以google下简单一点就是建一个中间表,存储select的统计结果(跟物化视图的作用一样的),然后要统计的时候就从中间表取
简单一点就是建一个中间表,存储select的统计结果(跟物化视图的作用一样的),然后要统计的时候就从中间表取 这个具体怎么操作呢?with tmptab as (select t.MCUID,
extract(YEAR FROM t.RECEIVETIME) as syear,
extract(MONTH FROM t.RECEIVETIME) as smonth,
extract(DAY FROM t.RECEIVETIME) as sday,
SPEEDOMETER
from bmps_his_receive_gpsinfo t
where t.speedometer != 0
and t.MCUID = 1000000997
),
tmptab1 as(
select MCUID,syear,smonth,sday,SPEEDOMETER from tmptab
where syear=2011 and smonth=8
)
select MCUID,
syear,
smonth,
sday,
(MAX(SPEEDOMETER) - MIN(SPEEDOMETER)) / 1000.0 as DayOfSpeedOmeter
from tmptab1
GROUP BY MCUID,
syear,
smonth,
sday
ORDER BY MCUID ASC,
syear asc,
smonth asc,
sday ASC 我现在这样写 少了10几秒,还是有130多秒崩溃。。
with tmptab as (select t.MCUID,
extract(YEAR FROM t.RECEIVETIME) as syear,
extract(MONTH FROM t.RECEIVETIME) as smonth,
extract(DAY FROM t.RECEIVETIME) as sday,
SPEEDOMETER
from bmps_his_receive_gpsinfo t
where t.speedometer != 0
and t.MCUID = 1000000997
),
tmptab1 as(
select MCUID,syear,smonth,sday,SPEEDOMETER from tmptab
where syear=2011 and smonth=8
)
select MCUID,
syear,
smonth,
sday,
(MAX(SPEEDOMETER) - MIN(SPEEDOMETER)) as DayOfSpeedOmeter
from tmptab1
GROUP BY MCUID,
syear,
smonth,
sday还能怎么优化啊??
目的是统计某年、某月、某MCUID下的车辆里程数统计。
该数据表有很多列,我取关键三列,MCUID,SPEEDOMETER,RECEIVETIME。
这个表是每秒都有数据插入进来,有不同的MCUID,插入时的SPEEDOMETER记录是累加的,
比如 MCUID:1000001002,RECEIVETIME:2011-08-15 09:55:50 时SPEEDOMETER:189258(米)
那么下一秒可能是:MCUID:1000001002,RECEIVETIME:2011-08-15 09:55:51 时SPEEDOMETER:189260(米)
依照这个需求看:
1.分表,或者分区。如果数据真的非常巨量的话,可能分到天。
2.如果从整个系统看。每月做一个月结,也就得到一个本月期末的里程放入表中。只需要查出每月最后一笔即可(本月,RECEIVETIME desc),如果每月没有记录,就要抄前一个月的月结里程数作为本月期末里程,否则后续月份没法算,然后和上个月比较即可。我看没你写的如此复杂。
3.select 的where要落在索引上。具体可以建立并优化。
4.将RECEIVETIME分解成年、月 分组这是没事找事做,因为对字段做函数,要整个表遍历。没法走索引。从你的需求看,只需本月最大和最小时间,而你因为要这样去取年月,就是因为你的数据全在一个表中,如果数据插入时,能运用计算字段自动产生年月,我想比你这样处理要快,而且后续查询可以使用年月这两个条件。
--extract(YEAR FROM t.RECEIVETIME) 导致索引实效
--2个解决方法:
--1、在你的t.RECEIVETIME 字段上建立函数索引
create index index_1 on bmps_his_receive_gpsinfo(extract(YEAR FROM t.RECEIVETIME));
create index index_1 on bmps_his_receive_gpsinfo(extract(MONTH FROM t.RECEIVETIME));
--2、把函数去掉,后面的:p1 :p2 改成to_date 转换的
and t.RECEIVETIME < to_date(:p1+1||''0101'',''yyyymmdd'')
and ....
--类似上面的写法,因为是在字符串里面,因此单引号要写俩。
--另外你的group by 也用到了 extract 函数,用第一种方法可能会提高排序的速度。
---------------------------------------------------------------------------------------------------- ---------- -------------------
jp001 10 2011-08-15 16:55:00
jp002 100 2011-08-15 16:55:06
jp001 18 2011-08-15 16:55:21
jp002 160 2011-08-15 16:55:30
jp001 180 2011-08-15 16:55:38
jp002 660 2011-08-15 16:55:456 rows selected.anbob@ORCL> col carno for a10
anbob@ORCL> select carno,max(ways)-min(ways) len from testcar where rectime between to_date('2011-08-15') and sysdate+1;
select carno,max(ways)-min(ways) len from testcar where rectime between to_date('2011-08-15') and sysdate+1
*
ERROR at line 1:
ORA-00937: not a single-group group function
anbob@ORCL> select carno,max(ways)-min(ways) len from testcar where rectime between to_date('2011-08-15') and sysdate+1 group by carno;CARNO LEN
---------- ----------
jp001 170
jp002 560大致意思就是这个,自己用fuction也好,procedure,传两个参数,carno,datetime(字符串转一下)
那么 to_date(:p1||''0101'',''yyyymmdd'') 就是日期型的 20110101
to_date(:p1+1||''0101'',''yyyymmdd'')就是日期型的 20120101
那么在这两个日期之间的不就是你想要的数据么。实际上你可以把月份信息放进去 日期 >=to_date(:p1||:p2||''01'',''yyyymmdd'')
and 日期 < add_months(to_date(:p1||:p2||''01'',''yyyymmdd''),1)
插入数据的时候用触发器把日期拆开分别填到三个字段里面。
分析的时候直接用这三个字段来查。
比用Extract等等之类的函数肯定要快么。
如果你在DOS下面写过C的代码,就知道,所有的函数在调用的时候要经过入栈,跳转,绑定参数,运算,返回,出栈的过程,SQL语句里面的函数越多,Oracle的开销就越大,有时候增加几个辅助字段是很有必要的。
2.你写的sql可以改成下面的,你试验看
SELECT t.MCUID,
trunc(t.RECEIVETIME) RECEIVETIME,
(MAX(SPEEDOMETER) - MIN(SPEEDOMETER)) / 1000.0 AS DayOfSpeedOmeter
FROM bmps_his_receive_gpsinfo t
WHERE t.speedometer != 0
AND t.MCUID = :p1
AND t.RECEIVETIME >= -- 参数,你参考下28#
AND t.RECEIVETIME <= --
GROUP BY t.MCUID,
trunc(t.RECEIVETIME)
ORDER BY t.MCUID ASC,
trunc(t.RECEIVETIME) ASC
create index INDEX_FUNCTION on BMPS_HIS_RECEIVE_GPSINFO (MCUID, EXTRACT(YEAR FROM RECEIVETIME), EXTRACT(MONTH FROM RECEIVETIME), EXTRACT(DAY FROM RECEIVETIME, SPEEDOMETER));