select count(*)
FROM flight_info f
INNER JOIN spa_plan p
ON f.dest_code=p.depAirportCode
WHERE f.area_type='D'
and (to_date(p.timeseries||' '||p.Deptime,'YYYY/MM/DD HH24mi')- (case when f.arrival_time < f.takeoff_time then to_date(f.flight_date||' '||substr(f.arrival_time,1,2)||':'||substr(f.arrival_time,3,2)||':00','yyyy-MM-dd HH24:mi:ss')+1 else to_date(f.flight_date||' '||substr(f.arrival_time,1,2)||':'||substr(f.arrival_time,3,2)||':00','yyyy-MM-dd HH24:mi:ss')end ) )* 24>=3;
加上最后时间大于3天 就不走索引两个表都走全表扫描 查询18秒
FROM flight_info f
INNER JOIN spa_plan p
ON f.dest_code=p.depAirportCode
WHERE f.area_type='D'
and (to_date(p.timeseries||' '||p.Deptime,'YYYY/MM/DD HH24mi')- (case when f.arrival_time < f.takeoff_time then to_date(f.flight_date||' '||substr(f.arrival_time,1,2)||':'||substr(f.arrival_time,3,2)||':00','yyyy-MM-dd HH24:mi:ss')+1 else to_date(f.flight_date||' '||substr(f.arrival_time,1,2)||':'||substr(f.arrival_time,3,2)||':00','yyyy-MM-dd HH24:mi:ss')end ) )* 24>=3;
加上最后时间大于3天 就不走索引两个表都走全表扫描 查询18秒
select count(*)
FROM flight_info f
INNER JOIN spa_plan p
ON f.dest_code = p.depAirportCode
WHERE f.area_type = 'D'
and p.timeseries >= to_char(sysdate-3, 'yyyy/mm/dd')
and p.timeseries < to_char(sysdate+1, 'yyyy/mm/dd')
不一定符合你的要求,但是你sql要往这个思路上靠。
SELECT/*+use_concat*/ COUNT(*)
FROM flight_info f
INNER JOIN spa_plan p
ON f.dest_code = p.depAirportCode
WHERE f.area_type = 'D'
AND ((f.arrival_time < f.takeoff_time AND
to_date(p.timeseries || ' ' || p.Deptime, 'YYYY/MM/DD HH24mi') =
to_date(f.flight_date || ' ' || substr(f.arrival_time, 1, 2) || ':' ||
substr(f.arrival_time, 3, 2) || ':00',
'yyyy-MM-dd HH24:mi:ss') + 1 + 3 / 24) OR
(f.arrival_time >= f.takeoff_time AND
to_date(p.timeseries || ' ' || p.Deptime, 'YYYY/MM/DD HH24mi') =
to_date(f.flight_date || ' ' || substr(f.arrival_time, 1, 2) || ':' ||
substr(f.arrival_time, 3, 2) || ':00',
'yyyy-MM-dd HH24:mi:ss') + 3 / 24));
没看具体数据量,如果你想走索引 猜想F表很小,走全表,再驱动P表,反过来也可以,试试吧
这样速度会快一点